1/58
Vocabulary flashcards covering core SQL concepts, syntax, and foundational terms from Chapter 2 Part 1.
SQL (Structured Query Language)
A data sublanguage used to communicate with and manipulate relational databases; developed by IBM in the 1970s; ubiquitous across DBMS; not a full programming language.
DDL
Data Definition Language; SQL commands used to define or modify the database schema (e.g., creating tables, relationships, and structures).
DML
Data Manipulation Language; SQL statements used for querying, inserting, updating, and deleting data (e.g., SELECT, INSERT, UPDATE, DELETE).
DQL
Data Query Language; a subset of SQL focused on querying data; SELECT is the primary DQL command.
TCL
Transaction Control Language; commands that manage transactions and their boundaries (BEGIN/COMMIT/ROLLBACK).
DCL
Data Control Language; commands that grant or revoke database permissions (GRANT, REVOKE).
SELECT FROM WHERE framework
The fundamental structure of an SQL query: SELECT {columns}, FROM {tables}, WHERE {conditions}.
DISTINCT
Keyword to remove duplicate rows from a query’s results.
TOP
Clause to limit the number of rows returned by a query (e.g., TOP 5).
TOP PERCENT
Variant of TOP that returns a percentage of rows from the result set.
ORDER BY
Clause to sort query results by one or more columns in ascending or descending order.
GROUP BY
Clause to group rows for aggregation, enabling calculations like SUM, COUNT per group.
HAVING
Clause to filter groups after aggregation, similar to WHERE for groups.
BETWEEN
Operator to filter values within a specified range (inclusive).
LIKE
Pattern-matching operator using wildcards _ (single char) and % (any sequence).
IN
Operator to specify a set of possible values for a column.
IS NULL
Operator to test whether a column contains NULL values.
AND/OR/NOT
Logical operators to combine or negate conditions in WHERE clauses.
SUM/AVG/MIN/MAX/COUNT
Aggregate functions used to perform calculations on column data (e.g., SUM, COUNT, AVG, MIN, MAX).
SUBQUERY
A query nested inside another query; can derive values for the outer query.
JOIN
Operation to combine data from two or more tables based on a related column (ON specifies the join condition).
OUTER JOIN
JOIN variant that includes rows with no matching rows in one table (LEFT/RIGHT/FULL OUTER JOIN).
UNION/INTERSECT/EXCEPT
Set operators to combine or compare result sets from queries.
ANSI SQL-92
SQL standard endorsed by ANSI in 1992 (SQL-92); modern versions add features like XML/JSON support.
Data warehouse
Central repository for integrated data from multiple sources used to support BI analysis and decision making.
Data extraction
Process of extracting data from operational databases into an offline store for BI use.
Semicolon
Statement terminator in SQL; ends each SQL statement in many DBMS.
SQL (Structured Query Language)
A data sublanguage used to communicate with and manipulate relational databases; developed by IBM in the 1970s; ubiquitous across DBMS; not a full programming language.
DDL
Data Definition Language; SQL commands used to define or modify the database schema (e.g., creating tables, relationships, and structures).
DML
Data Manipulation Language; SQL statements used for querying, inserting, updating, and deleting data (e.g., SELECT, INSERT, UPDATE, DELETE).
DQL
Data Query Language; a subset of SQL focused on querying data; SELECT is the primary DQL command.
TCL
Transaction Control Language; commands that manage transactions and their boundaries (BEGIN/COMMIT/ROLLBACK).
DCL
Data Control Language; commands that grant or revoke database permissions (GRANT, REVOKE).
SELECT FROM WHERE framework
The fundamental structure of an SQL query: SELECT {columns}, FROM {tables}, WHERE {conditions}.
DISTINCT
Keyword to remove duplicate rows from a query’s results.
TOP
Clause to limit the number of rows returned by a query (e.g., TOP 5).
TOP PERCENT
Variant of TOP that returns a percentage of rows from the result set.
ORDER BY
Clause to sort query results by one or more columns in ascending or descending order.
GROUP BY
Clause to group rows for aggregation, enabling calculations like SUM, COUNT per group.
HAVING
Clause to filter groups after aggregation, similar to WHERE for groups.
BETWEEN
Operator to filter values within a specified range (inclusive).
LIKE
Pattern-matching operator using wildcards _ (single char) and % (any sequence).
IN
Operator to specify a set of possible values for a column.
IS NULL
Operator to test whether a column contains NULL values.
AND/OR/NOT
Logical operators to combine or negate conditions in WHERE clauses.
SUM/AVG/MIN/MAX/COUNT
Aggregate functions used to perform calculations on column data (e.g., SUM, COUNT, AVG, MIN, MAX).
SUBQUERY
A query nested inside another query; can derive values for the outer query.
JOIN
Operation to combine data from two or more tables based on a related column (ON specifies the join condition).
OUTER JOIN
JOIN variant that includes rows with no matching rows in one table (LEFT/RIGHT/FULL OUTER JOIN).
UNION/INTERSECT/EXCEPT
Set operators to combine or compare result sets from queries.
ANSI SQL-92
SQL standard endorsed by ANSI in 1992 (SQL-92); modern versions add features like XML/JSON support.
Data warehouse
Central repository for integrated data from multiple sources used to support BI analysis and decision making.
Data extraction
Process of extracting data from operational databases into an offline store for BI use.
Semicolon
Statement terminator in SQL; ends each SQL statement in many DBMS.
PRIMARY KEY
A column or set of columns that uniquely identifies each row in a table.
FOREIGN KEY
A column or set of columns that refers to the primary key in another table, establishing a link between them.
ALIAS (AS)
Temporary name given to a table or a column in an SQL query to make it more readable or to simplify complex queries.
CONSTRAINT
Rules enforced on data columns in a table to limit the type of data that can go into a table (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL).
UNION ALL
Set operator that combines the result sets of two or more SELECT statements, retaining duplicate rows.