Fundamentals of Database Systems - Chapter 4: Structured Query Language (SQL)

Comprehensive vocabulary flashcards covering SQL concepts from Chapter 4 of Fundamentals of Database Systems by Elmasri and Navathe, including SQL categories (DDL, DML, DCL, DQL, TCL), integrity constraints, set operations, aggregate functions, joins, subqueries, and triggers.

Structured Query Language (SQL)

A database language used to operate databases, including database creation, database deletion, fetching data rows, and modifying and deleting data rows.

SQL Data Types

The categories of data formats supported in SQL: Numeric (int, float), Character String (char, varchar), Unicode Character String (nchar, ntext), Binary (binary, image), Date and Time (date, datetime), and Miscellaneous (xml, json).

SQL Command Categories

The five main categories into which SQL commands are classified: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL).

Data Definition Language (DDL)

A category of SQL commands used to define data structures and modify data by creating, altering, and deleting database objects such as tables, views, indexes, and users (e.g., CREATE, ALTER, DROP, TRUNCATE).

CREATE TABLE

A DDL statement used to establish and define new database objects such as tables, specifying column names and their data types.

ALTER TABLE

A DDL statement used to modify the structure of an existing relation, such as adding a new attribute (column) and its data type to a base table.

TRUNCATE TABLE

A DDL command used to delete all complete data rows from a table while preserving the table structure.

DROP TABLE

A DDL command used to remove a base relation and its definition entirely from the database, preventing it from being queried or updated thereafter.

Integrity Constraints

A set of predefined rules in a DBMS that ensure data accuracy, consistency, and reliability while preventing accidental database damage, automatically checked during INSERT, UPDATE, and DELETE operations.

Entity Integrity Constraint

A rule stating that the primary key of a table cannot contain a NULL value because it uniquely identifies each specific record.

Key Constraint

A rule specifying that at least one attribute or combination of attributes must uniquely identify every row in a table to prevent duplicate rows from being inserted.

Domain Constraint

A rule ensuring that every value entered into a column belongs to a valid, predefined set of acceptable values, defining its data type, size, format, and permissible values.

Referential Integrity Constraint

A rule that maintains consistency between two related tables by requiring that a foreign key in a child table must either match a primary key in the parent table or be completely NULL.

Check Constraint

A rule that validates whether values in a column satisfy a specific logical, boolean condition (such as $Salary \ge 0$ or $Age \ge 18$) before saving data.

Data Manipulation Language (DML)

A class of SQL statements used to query, edit, add, and delete row-level data from database tables or views (includes INSERT, UPDATE, and DELETE).

INSERT Command

A DML command that allows users to add new rows or records of data into a database table.

UPDATE Command

A DML command used to modify the values of single or multiple existing columns in a database table based on a specified condition.

DELETE Command

A DML statement used to remove one or more specific data records/rows from a table satisfying a WHERE condition.

Data Control Language (DCL)

SQL commands used to manage database security, user access, and permissions by granting or revoking authorities on database objects.

GRANT Command

A DCL command that provides specific access privileges (such as SELECT, INSERT, UPDATE, DELETE, or ALL PRIVILEGES) on a database object to a user account or role.

REVOKE Command

A DCL command that removes previously granted privileges from a user account or role, thereby restricting access.

Data Query Language (DQL)

A subset of SQL used to fetch data from the database, consisting of the SELECT statement.

SELECT Statement

A DQL statement used to retrieve subsets of information and attributes from one or more tables, functioning similarly to the projection operation of relational algebra.

Asterisk (*) Wildcard

A symbol used in a SELECT statement to retrieve all columns from a table without listing each column name individually.

Compound Queries

SQL queries containing set operators that combine the results of two component queries into a single unified result set.

UNION Operator

A set operator that combines results from two or more SELECT statements and eliminates duplicate rows; both queries must have the same number of columns and compatible datatypes.

UNION ALL Operator

A set operator that combines the results of two queries and returns all rows including all duplicates without sorting the data.

INTERSECT Operator

A set operator that combines two SELECT statements and returns only the distinct rows that are common to both queries.

MINUS Operator

A set operator that returns distinct rows present in the first query but absent in the second query, arranged in ascending order by default with no duplicates.

Aggregate Functions

SQL functions that perform calculations on multiple values to return a single value, ignoring NULL values in computations (except when using COUNT).

SUM Function

An aggregate function that calculates the total sum of all selected values across a numeric field.

MIN Function

An aggregate function used to find the smallest value among all selected values in a column.

MAX Function

An aggregate function used to find the largest value among all selected values in a column.

AVG Function

An aggregate function that calculates the mean value of a numeric column across all non-NULL values.

COUNT Function

An aggregate function that counts the number of rows in a database table, applicable to both numeric and non-numeric types and including NULLs and duplicates when using COUNT(*).

DISTINCT Clause

A clause used within a SELECT query to filter out duplicate rows and retrieve only unique values from a column.

LIKE Operator

A pattern-matching operator used in a WHERE clause to compare strings against specified wildcard patterns.

Percent (%) Wildcard

A pattern-matching character used with the LIKE operator that matches any substring of any length (including zero characters).

Underscore (_) Wildcard

A pattern-matching character used with the LIKE operator that matches exactly one single character.

NULL Value

A marker in a table field indicating the absence of any value, evaluated in SQL using the IS NULL or IS NOT NULL operators.

JOIN Clause

A SQL clause that links and retrieves data from two or more related tables based on a specified join condition, usually involving foreign keys.

INNER JOIN

The default and most common join type that returns only the records from multiple tables that satisfy the specified join condition (matching rows in both tables).

OUTER JOIN

A join type that returns both matching rows and non-matching rows from the joined tables, subdivided into Left, Right, and Full outer joins.

LEFT OUTER JOIN

A join that returns all matching rows as well as all non-matching rows from the left table, placing NULL values in attributes originating from the right table.

RIGHT OUTER JOIN

A join that returns all matching rows as well as all non-matching rows from the right table, inserting NULL values for attributes originating from the left table.

FULL OUTER JOIN

A join that returns matching data from both tables alongside all remaining non-matching rows from both the left and right tables, using NULLs where matches do not exist.

GROUP BY Clause

A clause that groups rows sharing identical values in specified columns into summary subgroups so that aggregate functions can be applied to each subgroup independently.

HAVING Clause

A clause used following GROUP BY to filter aggregated groups based on a specified condition, allowing the inclusion of aggregate functions which the WHERE clause cannot support.

View

A virtual table in SQL that does not store data physically but presents dynamic rows and columns derived from one or more underlying base tables.

Nested Query (Subquery)

A complete SELECT query embedded within another query (typically within the WHERE clause), where the outer query uses the intermediate results of the inner query.

IN Operator

A set membership connective operator that evaluates whether an outer query column value is present in the collection of values returned by an inner query or specified list.

NOT IN Operator

A connective operator that tests for the absence of set membership, confirming that an outer query column value does not exist within the inner query's result set.

Trigger

A special stored database procedure tied to a specific table that automatically executes in response to defined data manipulation (INSERT, UPDATE, DELETE) or schema definition events.

Statement-Level Trigger

A trigger that executes exactly once for the entire triggering event, performing an action based on the overall effect of an INSERT, UPDATE, or DELETE statement rather than per row.

Row-Level Trigger

A trigger that executes once for each individual row affected by the triggering INSERT, UPDATE, or DELETE statement (indicated by FOR EACH ROW).

DML Triggers

Triggers that automatically execute in response to table data manipulation operations, consisting of INSERT, UPDATE, and DELETE triggers.

DDL Triggers

Triggers that fire in response to schema changes and structural definition events in the database, including CREATE, ALTER, and DROP actions.