GK One-Line Question & Answer

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

DEFAULT clause in CREATE TABLE specifies the value to use when an INSERT statement does not provide a value for that column - can be a literal value a function call (like NOW()) or a sequence reference

Functionally they are equivalent in most DBMS - a UNIQUE constraint is typically implemented internally as a UNIQUE index; the difference is declarative (UNIQUE constraint) vs. explicit (CREATE UNIQUE INDEX) - both prevent duplicate values

A view is created from a valid SELECT statement; key requirements vary by DBMS but typically: no ORDER BY in the view definition (without LIMIT/TOP), consistent column names (either from SELECT or via aliases), and the underlying tables/views must exist

ALTER TABLE old_name RENAME TO new_name changes the table name; most DBMS automatically update references in foreign key constraints but views stored procedures and application code may break - the impact depends on DBMS version and dependency management capabilities

COMMENT ON TABLE/COLUMN/INDEX stores metadata descriptions in the system catalog (standard SQL); in production databases metadata management also includes: documentation in data catalogs (Apache Atlas, Collibra), ERD tools, and data dictionaries maintained alongside the schema

INSERT VALUES: inserts one or more explicitly specified rows; INSERT SELECT: inserts rows derived from a SELECT query result enabling bulk insertion from other tables or complex expressions

It updates ALL rows in the table - a common and dangerous mistake that can corrupt entire tables. The UPDATE executes successfully but affects every row

REPLACE INTO: if a duplicate key violation occurs deletes the existing row and inserts the new row (auto-increments change); INSERT OR IGNORE: if duplicate key silently ignores the insert and keeps the original row unchanged

Updating rows in one table based on conditions or values from another table by joining them within the UPDATE statement

PostgreSQL syntax for upsert: attempts an INSERT and if a conflict on a specified constraint/column occurs updates the existing row instead of failing - atomic single-statement operation

Deletes rows from table1 whose id values exist in the result set of the subquery from table2 - a correlated delete that removes specific rows based on data in another table

Returns the values of specified columns from rows that were inserted updated or deleted - useful for retrieving auto-generated values (like IDENTITY/serial IDs) or changed values without a subsequent SELECT

In most DBMS this fails with subquery returns more than one row error because the = operator expects a single value - should use IN instead of = for multi-row subqueries

Marking rows as deleted (is_deleted=1 or deleted_at=TIMESTAMP) instead of physically removing them preserving data for audit/recovery at the cost of increased table size and requiring all queries to filter out soft-deleted rows

Sets the column value to its defined DEFAULT value (as specified in the column definition) resetting it to the default without needing to know the actual default value

Multi-row INSERT sends one network round-trip parses one SQL statement and applies one transaction commit for all rows vs. N round-trips/parses/commits for N single-row inserts - orders of magnitude faster for bulk loads

A high-performance bulk data loading operation that reads data directly from a file (CSV TSV etc.) into a table bypassing many SQL processing overheads - typically 10-100x faster than INSERT statements for large datasets

A phenomenon where a transaction re-executes a query and finds new rows that were inserted by another committed transaction since the first read - occurs at READ COMMITTED isolation level prevented by SERIALIZABLE

EXPLAIN shows the query execution plan (without executing); EXPLAIN ANALYZE actually executes the query and shows the plan with real timing/row counts - used to diagnose slow queries check index usage identify full table scans

A stored procedure automatically executed by the DBMS in response to specific DML events (INSERT, UPDATE, DELETE) on a table either BEFORE or AFTER the event or INSTEAD OF for views

Executing 1 query to fetch N parent records then N additional queries to fetch related child records for each parent (N+1 total queries) instead of using a single JOIN - causes severe performance problems as N grows

The DBMS may either skip the update entirely (optimized) or perform a no-op update; triggers still fire in most DBMS; the behavior depends on the DBMS implementation but no data change occurs