GK One-Line Question & Answer

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

A unique index enforces that no two rows have the same indexed column values (effectively a UNIQUE constraint); a non-unique index allows duplicate values and is used purely for query performance optimization

It defines a column whose value is automatically computed from an expression and stored (STORED) or computed on-the-fly (VIRTUAL) cannot be manually set or updated

CREATE SCHEMA creates a logical namespace/grouping for database objects within a database; CREATE DATABASE creates an entirely new database instance. In some DBMS (PostgreSQL) SCHEMA is a namespace within a database; in MySQL SCHEMA and DATABASE are synonymous

The ALTER TABLE command fails with an error because existing NULLs would violate the constraint - you must first update NULLs to valid values before adding the NOT NULL constraint

It prevents dropping the table if any other database objects depend on it (views, foreign keys, triggers) requiring dependencies to be removed first

A table divided into smaller physical storage units (partitions) based on partition key values; RANGE partitioning: each partition holds rows where partition key falls within specified ranges

To define user-defined types (UDTs) including structured types with attributes and methods array types row types and enum types - extending the built-in type system

Replicating structural changes (CREATE TABLE, ALTER TABLE, DROP INDEX, etc.) from primary to replica databases ensuring schema stays synchronized - not all replication solutions support DDL replication

SEQUENCE is a database object that generates unique sequential numbers independently of any table - can be used across multiple tables allows fine-grained control (INCREMENT BY, START WITH, MINVALUE, MAXVALUE, CYCLE); AUTO_INCREMENT is a column property tied to a specific table

In most DBMS this requires rewriting the entire table to add the column value to every row (table-level lock, long operation). PostgreSQL 11+ handles DEFAULT WITHOUT NULL as instant; MySQL 8.0+ supports instant ADD COLUMN for some cases

Creates a table that is visible only to the current session and is automatically dropped when the session ends or connection closes

They are DCL (Data Control Language) statements - GRANT gives specific privileges (SELECT, INSERT, UPDATE, DELETE, EXECUTE) to users/roles on database objects; REVOKE removes those privileges

ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (child_col) REFERENCES parent_table(parent_col) ON DELETE CASCADE ON UPDATE CASCADE

A technique/tool that allows schema modifications on large production tables without long table locks or downtime typically by creating a shadow table copying data incrementally and atomically swapping the tables

A named constraint that can span multiple tables (unlike table-level CHECK constraints which are limited to one table) - allows complex business rules to be defined at the schema level

Creates a child table that inherits all columns and constraints from a parent table with child-specific additional columns - part of PostgreSQLs table inheritance feature for object-relational capabilities

Column-level: can only define single-column constraints; Table-level: can define single or multi-column (composite) constraints and allows explicit naming at the table level - functionally equivalent for single-column but ONLY table-level syntax works for composite keys

Most DBMS automatically drop all indexes that include the dropped column and reject the operation if any foreign key references the dropped column (requiring you to drop the FK first) - or CASCADE can be used to drop dependent objects too