GK One-Line Question & Answer

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

Pessimistic: locks data when reading to prevent concurrent modifications (SELECT FOR UPDATE); Optimistic: no locks checks at update time if data changed since read (using version column or timestamp) fails with retry if another update occurred

MySQL supports DELETE FROM t WHERE condition ORDER BY col LIMIT n to safely delete/update a bounded number of rows - prevents accidentally deleting millions of rows and allows safe batch operations

It ensures that INSERT and UPDATE operations through the view only accept rows that satisfy the view WHERE clause - preventing modifications that would make the row invisible through the view

Updating an indexed column requires updating both the table row AND all indexes on that column making writes more expensive (each index requires a delete+insert in the B-tree structure) - the more indexes the slower the UPDATE

Updating many rows efficiently by using a single UPDATE with a JOIN or CASE expression to set different values per row based on a mapping table rather than executing N individual UPDATE statements

It returns the values of modified rows from INSERT/UPDATE/DELETE operations enabling patterns like: capturing deleted row data into an audit table in a single statement or retrieving auto-generated IDs after INSERT without a separate SELECT

MERGE performs INSERT UPDATE or DELETE in a single atomic statement based on whether rows match between source and target - preferred when you need to synchronize two tables and want to avoid race conditions of separate read-then-write operations

DELETE logs each individual row deletion (fully logged) enabling row-level rollback and trigger firing; TRUNCATE uses minimal logging (deallocates entire data pages) making it much faster but in most DBMS still rollbackable within an explicit transaction

It acquires an exclusive lock on the selected rows preventing other transactions from modifying or locking those rows until the current transaction commits or rolls back - used to implement pessimistic locking for critical operations

Both raise constraint violation errors but the error codes differ: NOT NULL violation raises a specific null constraint error and CHECK constraint violation raises a check constraint violation - both prevent the INSERT from completing and the transaction remains in an error state requiring rollback

Oracle-specific SQL that inserts multiple rows into multiple tables in a single statement (unconditional or conditional INSERT ALL INTO t1...INTO t2...) solving the problem of making multiple table inserts atomic and efficient from a single data scan

Nested Loop: O(n*m) good for small tables/indexed access; Hash Join: O(n+m) good for large unindexed tables (requires memory for hash table); Sort-Merge: O(n log n + m log m) good when inputs are already sorted

All rows from BOTH tables with NULLs for non-matching sides: rows matched on both sides (complete data) unmatched rows from left table (right side NULL) and unmatched rows from right table (left side NULL)

A query pattern that returns rows from the left table that have NO matching rows in the right table implemented as: LEFT JOIN WHERE right.key IS NULL or NOT EXISTS (subquery) or NOT IN (subquery)

The query optimizer moves filtering conditions (predicates) as early as possible in the execution plan - before joining - to reduce the number of rows that need to be joined dramatically reducing the join cost

Using a FROM clause with multiple tables without specifying join conditions or using CROSS JOIN - detected by the absence of WHERE/ON conditions between tables and the result set size = product of table sizes

A join using comparison operators other than equality (>, <, >=, <=, BETWEEN, LIKE) in the join condition - e.g. joining employee salary to a salary_grade table where salary BETWEEN grade_low AND grade_high

A join where the right side (subquery or function) can reference columns from tables to its left in the FROM clause - allowing correlation within the FROM clause useful for top-N per group queries