GK One-Line Question & Answer

Fc = A to B, B to C, C to D, D to A - each FD is individually necessary (no FD is derivable from the others) since removing any one breaks the cycle

A set of FDs where none of them can be derived from the others - important because it means each FD adds genuine new information about the schema and removing any one would change the closure F+

For soundness: Reflexivity holds trivially by definition. Augmentation: if t1[X]=t2[X] then t1[XZ]=t2[XZ] which implies t1[Y]=t2[Y] gives t1[YZ]=t2[YZ]. Transitivity: if t1[X]=t2[X] implies t1[Y]=t2[Y] and t1[Y]=t2[Y] implies t1[Z]=t2[Z] then t1[X]=t2[X] implies t1[Z]=t2[Z]

Testing whether a decomposition has the lossless join property and whether functional dependencies are preserved by applying FDs to a canonical table (tableau)

For a set of attributes X the dependency basis is the finest partition of (R-X) such that X multidetermines each block; this partitions the other attributes into independent groups that X independently multidetermines

FROM WHERE GROUP BY HAVING SELECT ORDER BY LIMIT

WHERE filters individual rows BEFORE grouping; HAVING filters groups AFTER GROUP BY and aggregation - HAVING can reference aggregate functions, WHERE cannot

NULL if expr1 equals expr2 (returns expr1 otherwise); used to avoid division-by-zero errors: NULLIF(count, 0) returns NULL instead of causing error when count=0

Aggregate functions (except COUNT(*)) IGNORE NULL values - COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values

To define named temporary result sets that can be referenced multiple times within a query improving readability and enabling recursive queries (WITH RECURSIVE)

CHAR(n) is fixed-length (always uses n bytes, padded with spaces if shorter); VARCHAR(n) is variable-length (uses only the space needed plus 1-2 bytes for length storage)

It returns NULL (the CASE expression evaluates to NULL when no WHEN matches and no ELSE is provided)

A subquery that references a column from the outer query causing it to be executed once for each row of the outer query (vs. non-correlated subquery which executes once independently)

EXISTS returns TRUE if a subquery returns at least one row (stops at first match), preferred over IN when the subquery could return NULLs (IN with NULL has counterintuitive behavior) or when checking existence is more efficient

An empty result set - the condition WHERE location = NULL is always FALSE (must use IS NULL instead of = NULL)

ROW_NUMBER(): unique sequential number with no gaps or ties; RANK(): same rank for ties then skips numbers (1,1,3); DENSE_RANK(): same rank for ties no gaps (1,1,2)

Accessing values from subsequent rows (LEAD) or preceding rows (LAG) within the result partition useful for computing differences between consecutive rows without self-joins

Defining the window frame for window functions - specifying which rows to include in each computation relative to the current row

Transforming row values into column headers converting a narrow table into a wide table - implemented via conditional aggregation (CASE + GROUP BY) in standard SQL

Returns the first non-NULL expression from left to right - short-circuits (stops evaluating) once a non-NULL value is found

It limits the result set to the first n rows (equivalent to LIMIT n in MySQL/PostgreSQL), introduced by SQL:2008 standard

Performing INSERT, UPDATE, or DELETE operations in a single statement based on whether a match exists between source and target tables - useful for ETL and synchronization operations

TRUE, FALSE, and UNKNOWN - where UNKNOWN results from comparisons involving NULL values; logical operations follow specific rules: TRUE AND UNKNOWN = UNKNOWN, FALSE AND UNKNOWN = FALSE, TRUE OR UNKNOWN = TRUE

TRUNCATE removes all rows without logging individual row deletions (faster, minimal logging, resets auto-increment), cannot be rolled back in some DBMS, and cannot have triggers. DELETE logs each row deletion (slower, fully transactional, triggers fire, can be rolled back)

It counts only unique non-NULL values of the column eliminating duplicates before counting - e.g. COUNT(DISTINCT dept_id) counts how many distinct departments have employees

To enforce a condition that must be true for all rows in a table; limitations include: cannot reference other tables, cannot contain subqueries in standard SQL, and in some DBMS it was parsed but not enforced

DISTINCT returns unique values without aggregation capability; GROUP BY allows adding aggregate functions. However, for just listing unique values with no aggregation, they produce identical results - GROUP BY is more powerful but DISTINCT is cleaner for simple deduplication

BETWEEN a AND b is INCLUSIVE of both endpoints - equivalent to >= a AND <= b (both a and b are included in the range)

% matches zero or more characters (any sequence); _ matches exactly one character (any single character) - used for pattern matching in strings

INNER JOIN returns only rows with matching values in both tables based on a join condition; CROSS JOIN returns the Cartesian product (every combination of rows from both tables, no join condition)

A CTE that references itself enabling traversal of hierarchical/graph data (like org charts, bill of materials, file systems) without knowing the depth in advance - queries tree/graph structures iteratively until no more rows are added

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single result row - unlike aggregate functions which collapse groups into single rows

A subquery is any query nested within another query; a derived table (inline view) is specifically a subquery in the FROM clause that acts as a named temporary table for the outer query - derived tables require an alias

HAVING filters groups after GROUP BY aggregation and can reference aggregate function results (e.g. HAVING COUNT(*) > 5); WHERE cannot reference aggregate functions because it runs before aggregation - HAVING is necessary for filtering based on aggregated data

DROP TABLE is DDL - removes the table structure, all data, indexes, triggers, and constraints permanently; TRUNCATE TABLE is DDL in most DBMS - removes all data but preserves the table structure, indexes, and column definitions

When a referenced row in the parent table is deleted, automatically delete all corresponding rows in the child table that reference it

MODIFY changes a column definition (data type, constraints) but keeps the column name; CHANGE can rename the column AND change its definition (requires specifying the new column definition even if unchanged)

Creates a new physical table populated with data from the SELECT query results - but does NOT copy constraints (primary keys, foreign keys, indexes, check constraints) from the source

A domain is a named reusable column specification that combines a data type with optional constraints (DEFAULT, CHECK) allowing the domain to be used across multiple tables for consistency - defined with CREATE DOMAIN in standard SQL

Constraints are checked at the end of a transaction (COMMIT) rather than after each statement allowing intermediate states to violate the constraint during a transaction