Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
Welcome to SQL Glossary , courtesy of Frontlines EduTech – the most all-inclusive guide to give you easy-to-understand knowledge on the most important key concepts, terminology, and functionalities of Structured Query Language (SQL). Mastering this powerful language has never been more important as businesses increasingly rely on data-driven decision-making, and SQL stands at the heart of managing, querying, and manipulating data.
That’s why this glossary is built to demystify SQL, defining its core concepts, features, and best practices to make it approachable for everyone. Whether you’re a beginner learning the fundamentals of databases, queries, and data manipulation, or an advanced user looking to sharpen your SQL skills and dive deeper into complex queries and optimizations, this glossary will serve as your go-to reference.
Explore key terms such as tables, queries, joins, and primary keys, and delve deeper into advanced functionalities like indexes, stored procedures, and triggers. This guide covers everything you need to know to become proficient in SQL, empowering you to unlock the full potential of your data.
A
ABS(): A function that returns the absolute value of a numeric expression.
ABSOLUTE: A keyword used to indicate the absolute position of a cursor in some database systems.
ACTION: A keyword in trigger definitions to define what action should be taken upon certain database events.
ADD COLUMN: A command used to add a new column to an existing table.
ADD CONSTRAINT: A command to add constraints to columns in a table.
AVG(): A function used to calculate the average value of a numeric column.
ABSOLUTE: A keyword used to indicate the absolute position of a cursor in some database systems.
ACTION: A keyword in trigger definitions to define what action should be taken upon certain database events.
AFTER: A keyword used in triggers to specify that the trigger should execute after a specified event (e.g., after insert).
AGGREGATE FUNCTION: A function that operates on a set of values and returns a single value (e.g., SUM(), AVG()).
ALIAS: A temporary name given to a table or column for the purpose of a query, using the AS keyword.
ALL: A keyword used to compare a value to all values in another set.
Example: SELECT * FROM Employees WHERE Age > ALL (SELECT Age FROM Managers);
ALTER: A command used to modify an existing database object, such as a table.
ALTER TABLE: A command used to modify the structure of an existing table.
ALWAYS: A keyword used in some databases for defining a column that is auto-generated based on expressions.
AND: A logical operator used to combine two or more conditions in a WHERE clause.
ANY: A keyword used to compare a value to any value in another set.
ANALYZE: A command used to collect statistics about a table for query optimization.
ARRAY_AGG(): A function that returns an array formed by aggregating values.
AS: A keyword used to rename a column or table with an alias.
ASC (Ascending): A keyword used to sort the result set in ascending order.
ASENSITIVE: A type of cursor that reflects changes made to the underlying data while it is open.
ASYNCHRONOUS: Refers to processes that occur independently of each other. Some databases allow asynchronous replication or execution of queries.
AUTOCOMMIT: A mode where each individual statement is committed to the database as soon as it is executed.
AUTOINCREMENT: A feature that automatically generates a unique value for a column (common with PRIMARY KEY).
B
BACKUP: A command used to create a copy of a database to prevent data loss.
BETWEEN: A conditional operator is used to filter the result set within a specified range.
BIGINT: A data type for large integer values.
BINARY: A data type for binary data (0s and 1s).
BIT: A data type representing a single binary digit (0 or 1).
BOOLEAN: A data type used for values representing TRUE or FALSE.
BY: A keyword often used with ORDER BY or GROUP BY to specify sorting or grouping criteria.
BINARY LARGE OBJECT (BLOB): A data type used to store large binary data such as images or audio.
BOOLEAN EXPRESSION: An expression that returns either TRUE or FALSE.
BUFFER POOL: A reserved area in memory used for caching data from the database.
C
CACHED: Data that has been stored temporarily to improve performance by reducing access time.
CARDINALITY: The uniqueness of data in a column. High cardinality means many unique values.
CASCADE: A keyword used with foreign keys to delete or update related rows automatically.
CASE: A conditional expression that returns different values based on conditions.
Example: SELECT CASE WHEN Age > 30 THEN ‘Senior’ ELSE ‘Junior’ END FROM Employees;
CAST: A function that converts one data type to another.
Example: SELECT CAST(Salary AS VARCHAR) FROM Employees;
CHAR(): A data type for fixed-length strings.
CHARINDEX(): A function that returns the position of a substring within a string.
CHECK: A constraint used to limit the range of values that can be placed in a column.
CLOB (Character Large Object): A data type for storing large blocks of text.
COALESCE(): A function that returns the first non-null expression among its arguments.
COLLATE: A keyword used to specify how string comparison should be done (case-sensitive or case-insensitive).
COLUMN: A field in a table that stores specific data, like Name or Age.
COMMIT: A command used to save all changes made during the current transaction.
CONCAT(): A function used to join two or more strings together.
CONSTRAINT: A rule enforced on data columns to ensure the integrity of the data.
CONTAINS: A keyword used in full-text search to find records containing a specified word or phrase.
CONVERT(): A function that converts one data type to another.
COUNT(): A function used to return the number of rows in a result set.
CREATE: A command used to create a new table, database, or other database object.
CREATE INDEX: A command used to create an index on a table.
CREATE TABLE: A command used to create a new table in a database.
CROSS JOIN: A type of join that returns the Cartesian product of two tables.
CURRENT_DATE(): A function that returns the current date.
CURRENT_TIMESTAMP(): A function that returns the current date and time.
CHECKPOINT: A point in time where the state of the database is saved to disk for recovery purposes.
CLUSTERED INDEX: A type of index where the table’s rows are physically stored in the same order as the index.
CLOSE: A command used to close an open cursor, freeing up system resources.
COLLATION: A set of rules determining how strings are compared, often affecting case sensitivity and ordering.
COLUMN FAMILY: A set of columns that are stored together, typically used in NoSQL databases like Cassandra.
COMMENT: A way to add non-executable text in SQL scripts, typically starting with — or /* */.
COMPILE: The process of converting SQL queries into a format that can be executed by the database engine.
CONNECTION POOLING: A method of reusing existing database connections to improve performance.
CONSTRAINT VIOLATION: An error that occurs when data does not satisfy a table constraint such as UNIQUE or CHECK.
CONTAINS(): A full-text search function that checks whether a string contains a specified phrase or word.
CROSS APPLY: A join-like operation in SQL Server that applies a table-valued function to each row in the result set.
CUBE: A grouping set used for summarizing data across multiple dimensions in GROUP BY queries.
CURSOR FOR LOOP: A loop construct used to iterate through rows returned by a cursor.
CURSOR: A database object used to retrieve a set of rows one at a time.
D
DATE(): A data type used to store date values (year, month, day).
DATABASE: An organized collection of structured data, typically stored electronically.
DATA DEFINITION LANGUAGE (DDL): A set of SQL commands used to define or modify database objects like tables and indexes (CREATE, ALTER, DROP).
DATA INTEGRITY: The accuracy and consistency of data stored in a database, often maintained using constraints.
DATA MANIPULATION LANGUAGE (DML): SQL commands that interact with data, such as INSERT, UPDATE, and DELETE.
DATA MASKING: A process of hiding or obfuscating specific data within a database for privacy or security.
DATA MINING: The practice of analyzing large datasets to discover patterns and relationships.
DATA PARTITIONING: A method for dividing a large table into smaller, more manageable pieces based on specific criteria.
DATA RECOVERY: The process of restoring data that has been lost, corrupted, or accidentally deleted.
DATABASE SNAPSHOT: A read-only, static view of a database at a specific point in time.
DATABASE TRIGGER: A procedural code that is automatically executed in response to certain events on a database table.
DATEADD(): A function that adds a specified time interval to a date.
DATEDIFF(): A function that returns the difference between two dates.
DATETIME: A data type that stores date and time values.
DAY(): A function that returns the day of the month from a date.
DECIMAL: A data type for fixed-point numbers with defined precision and scale.
DEFAULT: A constraint that provides a default value for a column if none is specified.
DELETE: An SQL command used to remove records from a table.
DESC (Descending): A keyword used to sort the result set in descending order.
DISTINCT: A keyword used to return only distinct (unique) values.
DROP: A command used to delete an entire table, database, or other database object.
DROP COLUMN: A command used to delete a column from a table.
DROP INDEX: A command used to delete an index.
DROP TABLE: A command used to delete an entire table.
DBMS (Database Management System): A software system that manages databases and provides an interface for interacting with them.
DEALLOCATE: A command used to free resources associated with cursors or prepared statements.
DECODE(): A function in some SQL implementations used to perform conditional logic, similar to a CASE statement.
DEFRAGMENTATION: The process of reorganizing data to improve database performance.
DENORMALIZATION: The process of adding redundant data to a database to improve read performance at the expense of write performance.
DESCENDANT QUERY: A query that retrieves hierarchical data, including all children, grandchildren, and so on.
DESCRIBE: A command used to display the structure of a database object, such as a table.
DETACHED: Refers to objects that are no longer managed by the database session, commonly used in ORM frameworks.
DISTRIBUTED DATABASE: A database that is stored on multiple servers or locations but functions as a single unit.
DISTRIBUTED TRANSACTION: A transaction that spans multiple databases or servers.
DOUBLE(): A data type used to store floating-point numbers with double precision.
DRILL-DOWN: The process of navigating through a dataset to view more detailed information.
DROP DATABASE: A command used to delete an entire database.
DYNAMIC SQL: SQL statements that are constructed and executed at runtime rather than at compile time.
E
END TRANSACTION: A command used to mark the end of a transaction and make its changes permanent.
ELSE: A keyword used in CASE statements to specify what to do if no condition is met.
ENTITY-RELATIONSHIP DIAGRAM (ERD): A diagram that visually represents the relationships between entities in a database.
EXCEPT: A set operator is used to return the difference between two queries, returning only rows from the first query that are not in the second.
EXECUTE: A command used to run a stored procedure or dynamically constructed SQL.
EXISTS: A keyword used to test for the existence of any record in a subquery.
EXPLAIN: A command used to display information about how SQL queries are executed.
EXPLAIN PLAN: A command used to display how the database will execute a query.
EXTEND: A command used to increase the size of a database object, like a table or partition.
EXISTS: A keyword used to check whether a subquery returns any rows.
F
FAILOVER: The process of switching to a backup system or database in case of failure.
FETCH: A command used to retrieve a specific row or set of rows from a query result.
FLOAT: A data type for approximate numeric values.
FOREIGN KEY: A column in one table that references the primary key in another table to create a relationship between the two.
FORMAT(): A function used to format a number or date as a string.
FROM: A keyword used to specify the table from which to retrieve data in a SELECT statement.
FULL JOIN: A type of join that returns all records when there is a match in either the left or right table.
FUNCTION: A reusable piece of SQL code that performs a specific task and returns a value.
FETCH FIRST: A clause used to limit the number of rows returned by a query.
FIRE: Refers to the execution of a trigger.
FOR UPDATE: A clause used to lock selected rows in a table for updates, preventing other transactions from modifying them.
FOREIGN DATA WRAPPER (FDW): A feature in some databases that allows access to external data sources as if they were tables.
FOREIGN KEY: A constraint that ensures a column’s value corresponds to the primary key in another table.
FORMAT(): A function used to format strings, numbers, or dates according to a specified pattern.
FULL OUTER JOIN: A type of join that returns all rows when there is a match in either table, with NULLs in places where no match exists.
G
GRANT: A command used to give privileges to users or roles.
GROUP BY: A clause used to group rows that have the same values in specified columns.
GENERATE_SERIES(): A function that generates a series of numbers or dates over a specified range.
GLOBAL TEMPORARY TABLE: A temporary table that is available to all sessions, but the data in it is session-specific.
H
HANDLER: A procedure used in triggers or procedures to handle specific conditions such as exceptions.
HAVING: A clause used to filter groups of rows created by the GROUP BY clause, often based on aggregate functions.
HEAP TABLE: A table without a clustered index, where rows are not stored in any specific order.
HINT: A directive used to suggest how the query optimizer should process a query, such as forcing the use of a specific index.
I
IDENTITY: A column that automatically generates unique numbers when new records are inserted.
IFNULL(): A function that returns an alternative value if a column is null.
IN: A conditional operator used to specify multiple values in a WHERE clause.
INDEX: A data structure that improves the speed of data retrieval in a database table.
INNER JOIN: A type of join that returns rows from both tables when there is a match.
INSERT: An SQL command used to add new records to a table.
INT: A data type for storing whole numbers.
IS NOT NULL: A condition used to find rows where a column’s value is not NULL.
IS NULL: A condition used to find rows where a column’s value is NULL.
IDENTITY: A property used to automatically generate unique values for a column, typically used with PRIMARY KEY.
IF EXISTS: A clause used to check for the existence of a database object before performing an action.
IFNULL(): A function that returns a specified value if the expression is NULL, otherwise it returns the expression itself.
IGNORE: A keyword used in some SQL implementations to skip errors and continue with the query.
IMPLICIT CASTING: The automatic conversion of one data type to another when needed in a query.
INFILE: A clause used in some databases to load data from an external file into a table.
INITCAP(): A function that capitalizes the first letter of each word in a string.
INSERT: A command used to add new rows to a table.
INSTEAD OF: A type of trigger that is executed in place of an INSERT, UPDATE, or DELETE operation.
INTERSECT: A set operator that returns only the rows that appear in both of two queries.
INTERVAL: A data type used to represent a span of time.
INTO: A keyword used to insert the results of a query into a new table.
IS NULL: A condition used to check whether a column contains a NULL value.
ISOLATION LEVEL: Defines the level of isolation between transactions to prevent issues like dirty reads or phantom reads.
J
JOIN: A command used to combine rows from two or more tables based on a related column.
K
KEY: A column or set of columns that uniquely identify a row in a table.
KILL: A command used to terminate a running query or session.
L
LEFT JOIN: A type of join that returns all rows from the left table, even if there are no matches in the right table.
LIKE: A keyword used to search for a specified pattern in a column.
LIMIT: A keyword used to specify the number of rows to return in a result set.
LN(): A function that returns the natural logarithm of a number.
LOG(): A function that returns the logarithm of a number to a specified base.
LONGTEXT: A data type used for storing large amounts of text.
LOWER(): A function that converts a string to lowercase.
LTRIM(): A function that removes leading spaces from a string.
M
MAX(): A function used to find the maximum value in a column.
MEDIUMTEXT: A data type used to store medium-sized text values.
MERGE: A command used to perform insert, update, or delete operations based on conditions.
MIN(): A function used to find the minimum value in a column.
MOD(): A function that returns the remainder of a division operation.
N
NOT: A keyword used to negate a condition.
NOT BETWEEN: A keyword used to filter results outside a specified range.
NOT NULL: A constraint that ensures a column cannot have a NULL value.
NOW(): A function that returns the current date and time.
NULL: A special marker in SQL used to indicate that a data value does not exist.
O
ON: A keyword used to specify the condition for a JOIN.
ORDER BY: A clause used to sort the result set in ascending or descending order.
OR: A logical operator used to combine multiple conditions in a WHERE clause.
OUTER JOIN: A type of join that returns rows from both tables, with NULLs for unmatched rows.
OUTPUT: A clause used in some databases (like SQL Server) to return information from an INSERT, UPDATE, DELETE, or MERGE statement.
P
PARTITION BY: A clause used with window functions to divide the result set into partitions. – Example: SELECT Name, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) FROM Employees;
PERCENT_RANK(): A window function that calculates the relative rank of a row within a partition.
PI(): A function that returns the mathematical constant pi.
POSITION(): A function that returns the position of a substring within a string.
POWER(): A function that returns the result of raising a number to a power.
PRIMARY KEY: A column (or set of columns) that uniquely identifies each row in a table.
PRIVILEGES: Rights granted to users to perform specific actions on database objects.
PROCEDURE: A stored set of SQL statements that can be executed as a program.
PRODUCT(): A mathematical function that returns the product of a group of numbers.
PUBLIC: A default role that all database users belong to, typically with minimal permissions.
R
RAND(): A function that generates a random number.
RANK(): A window function that assigns a rank to rows in a partition, with ties.
REAL: A data type used for floating-point numbers.
REFERENCES: A keyword used in foreign key constraints to specify the table and column being referenced.
REINDEX: A command used to rebuild an index, improving database performance.
RELEASE SAVEPOINT: A command used to remove a named transaction savepoint.
RENAME: A command used to rename a database object, such as a table or column.
REPEAT(): A function that repeats a string a specified number of times.
REPLACE(): A function that replaces occurrences of a substring with another substring.
RESTRICT: A keyword used with foreign keys to prevent deletion or updates if related records exist.
RETURN: A statement in a stored procedure that returns a value to the caller.
REVOKE: A command used to remove privileges from a user or role.
RIGHT JOIN: A type of join that returns all rows from the right table, even if there are no matches in the left table.
ROUND(): A function that rounds a number to a specified number of decimal places.
ROW_NUMBER(): A window function that assigns a unique row number to each row in a result set.
RTRIM(): A function that removes trailing spaces from a string.
S
SQL (Structured Query Language): A standard programming language used to manage and manipulate databases.
SAVEPOINT: A command used to set a savepoint within a transaction to which one can roll back.
SCHEMA: A structure that contains the definitions of database objects, such as tables and views.
SECOND(): A function that returns the second part of a time value.
SELECT: An SQL command used to retrieve data from a database.
SELF JOIN: A join where a table is joined with itself.
SEQUENCE: A database object that generates a sequence of unique numbers.
SESSION: A connection between the client and database, lasting for the duration of a user’s interaction.
SET: A command used to assign a value to a variable or modify a database option.
SET NULL: A foreign key constraint action that sets the foreign key to NULL if the referenced record is deleted.
SHA1(): A function that returns a SHA-1 hash of a string.
SIGN(): A function that returns the sign of a number: -1 for negative, 0 for zero, and 1 for positive.
SIMILAR TO: A pattern-matching operator similar to LIKE, but with regular expressions.
SMALLINT: A data type used for small integer values.
SOME: A keyword used to compare a value to any value in a set (same as ANY).
SPACE(): A function that returns a string of repeated spaces.
SQL: Structured Query Language, used to manage and manipulate relational databases.
SQRT(): A function that returns the square root of a number.
START TRANSACTION: A command used to start a new transaction.
STORED PROCEDURE: A set of SQL statements stored in the database that can be executed as a single unit.
STRING_AGG(): A function that concatenates values into a single string with a separator.
SUBQUERY: A query nested inside another query.
SUBSTRING(): A function that extracts a portion of a string based on start and length.
SUM(): A function used to calculate the sum of a numeric column.
SYNONYM: An alias for a database object, like a table or view.
SYSDATE(): A function that returns the current system date and time.
T
TABLE: A collection of data organized into rows and columns.
TEMPORARY TABLE: A table that is created and exists only within the session or transaction in which it is used.
TEXT: A data type for storing large amounts of text.
TIME(): A data type for storing time values (hour, minute, second).
TIMESTAMP: A data type that combines date and time.
TO_CHAR(): A function that converts a number or date to a string with a specific format.
TO_DATE(): A function that converts a string to a date.
TOP: A keyword used to limit the number of rows returned by a query. – Example: SELECT TOP 10 * FROM Employees;
TRIM(): A function that removes leading and trailing spaces from a string.
TRIGGER: A piece of SQL code automatically executed in response to certain events on a table.
TRUNCATE: A command used to remove all rows from a table, but the table structure remains.
TRY_CAST(): A function that attempts to cast a value to another data type, returning NULL if it fails.
U
UNION: A command used to combine the results of two or more SELECT statements.
UNION ALL: A command similar to UNION, but includes all duplicates in the result set.
UNIQUE: A constraint that ensures all values in a column or group of columns are unique.
UPDATE: An SQL command used to modify existing records in a table.
UPPER(): A function that converts a string to uppercase.
USAGE: A permission that allows a user to use a schema or a sequence.
USE: A command used to select a specific database to work with.
USER(): A function that returns the name of the current database user.
V
VALUES: A keyword used to specify the values to be inserted into a table.
VARCHAR(): A data type for variable-length strings.
VARIANCE(): A function that returns the variance of a set of numbers.
VIEW: A virtual table based on the result of an SQL query.
W
WHEN: A keyword used in CASE statements to define conditions.
WHERE: A clause used to filter results based on specified conditions.
WHILE: A control-of-flow statement used to execute a block of code repeatedly while a condition is true.
WITH: A keyword used to define a common table expression (CTE) for use within a query.
X
XML(): A data type and set of functions for working with XML data.
Y
YEAR(): A function that returns the year part of a date.
Z
ZONED DECIMAL: A data type for packed decimal numbers used in certain systems.
Training in SQL has become one of the must-do activities within today’s data management and analytics world, and we at Frontlines EduTech – Building Trust & Careers are here to guide you on this journey. Our courses offer sessions led by expert professionals who provide hands-on experience and deep knowledge, empowering you to manage data efficiently and make data-driven decisions that create impactful results for your organization.
Don’t miss this opportunity! Enroll in an SQL course with us today and unlock your potential in the world of data analytics and management!
