SQL MCQ

Explanation

SQL stands for Structured Query Language, used to manage and query relational databases.

Explanation

SELECT is the SQL statement used to query and retrieve data from one or more tables.

Explanation

WHERE filters rows before grouping based on a specified condition.

Explanation

ORDER BY sorts the result set by one or more columns, ascending (ASC, default) or descending (DESC).

Explanation

INSERT INTO table_name (columns) VALUES (values) adds new rows to a table.

Explanation

UPDATE table_name SET column = value WHERE condition modifies existing rows that match the condition.

Explanation

DELETE FROM table_name WHERE condition removes rows matching the condition; DROP removes the entire table structure.

Explanation

A PRIMARY KEY uniquely identifies each record in a table; it must contain unique values and cannot contain NULLs.

Explanation

In a LIKE pattern, "%" matches any sequence of zero or more characters, while "_" matches exactly one character.

Explanation

SELECT DISTINCT returns only unique combinations of the selected column values, removing duplicates.

Explanation

NULL represents the absence of a value or an unknown value; it is not equal to 0 or an empty string.

Explanation

NULL cannot be compared with "=" because NULL is not a value; "IS NULL" (or "IS NOT NULL") is the correct way to test for it.

Explanation

CREATE TABLE table_name (column definitions...) defines a new table and its columns.

Explanation

LIMIT n restricts the result set to at most n rows, commonly used for pagination or sampling.

Explanation

COUNT() returns the number of rows that match a specified condition, or COUNT(*) counts all rows.

Explanation

BETWEEN x AND y selects values within the inclusive range from x to y.

Explanation

GROUP BY groups rows sharing the same value(s) in specified columns, typically used together with aggregate functions.

Explanation

A FOREIGN KEY establishes a relationship between two tables by referencing the primary key (or unique key) of another table.

Explanation

AND requires both conditions to be true for a row to be included in the result.

Explanation

AS assigns a temporary alias name to a column or table, often used to make output more readable.

Explanation

INT (or INTEGER) is the standard SQL data type for storing whole numbers.

Explanation

VARCHAR(n) stores variable-length character strings up to a maximum length n, more space-efficient than fixed-length CHAR for varying text.

Explanation

IN (value1, value2, ...) returns true if the column value matches any of the listed values, equivalent to multiple OR conditions.

Explanation

A view is a saved query that behaves like a virtual table, presenting data from one or more underlying tables without storing the data itself.

Explanation

ALTER TABLE is used to add, modify, or drop columns and constraints on an existing table.

Explanation

Any comparison with NULL using "=" yields NULL (unknown), not TRUE or FALSE, because NULL represents an unknown value.

Explanation

HAVING filters groups after aggregation, whereas WHERE filters individual rows before grouping.

Explanation

DESC sorts results from highest to lowest (or Z to A for text); ASC (the default) sorts ascending.

Explanation

NOW() (MySQL) or CURRENT_TIMESTAMP (standard SQL) return the current date and time.

Explanation

The "*" wildcard in SELECT means "all columns", returning every column for every matching row.

Explanation

The NOT NULL constraint requires that a column always have a value, rejecting any insert or update that would leave it empty.

Explanation

DROP TABLE completely removes the table definition along with all data, indexes, and constraints associated with it.

Explanation

AVG() computes the arithmetic mean of the values in the specified numeric column, ignoring NULLs.

Explanation

UNIQUE ensures that all values in a column (or combination of columns) are distinct across the table, though unlike PRIMARY KEY, multiple NULLs may be allowed depending on the database.

Explanation

ALTER TABLE ... ADD COLUMN adds a new column with the specified data type to an existing table.

Explanation

NOT reverses the result of a boolean condition, so rows where the condition is true are excluded and vice versa.

Explanation

COUNT(DISTINCT column) counts only distinct non-NULL values, removing duplicates before counting.

Explanation

CHAR(n) always occupies n characters of storage (padding shorter strings with spaces), while VARCHAR(n) stores only the actual string length up to n.

Explanation

UNION combines the results of two or more SELECT statements into a single result set, removing duplicate rows by default (UNION ALL keeps duplicates).

Explanation

In three-valued logic, "NULL OR TRUE" is TRUE because the result is true regardless of the unknown operand; only "NULL OR FALSE" and "NULL AND TRUE" yield NULL.

Explanation

INNER JOIN excludes rows without a match in both tables, whereas LEFT (OUTER) JOIN preserves all rows from the left table, filling unmatched right-side columns with NULL.

Explanation

A subquery is a SELECT statement embedded within another SQL statement, used to filter, compute, or supply values for the outer query.

Explanation

A self-join treats the same table as two separate instances (via aliases) to compare or relate rows to other rows in the same table, e.g. finding employees and their managers.

Explanation

An index creates an auxiliary data structure (often a B-tree) that allows the database to find rows matching a condition much faster, but adds overhead to INSERT/UPDATE/DELETE operations.

Explanation

UNION performs an implicit DISTINCT on the combined results (requiring a sort/dedup step), whereas UNION ALL simply concatenates results without removing duplicates.

Explanation

Transactions allow a sequence of operations to be treated as a single atomic unit: COMMIT makes all changes permanent, while ROLLBACK reverts all changes made since the transaction began.

Explanation

ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee reliable processing of database transactions.

Explanation

After grouping, HAVING COUNT(*) > 1 filters to only those groups that contain more than one row, a common pattern for detecting duplicate entries.

Explanation

A composite key combines multiple columns to form a unique identifier when no single column is sufficient to guarantee uniqueness.

Explanation

The CASE expression allows row-level conditional logic directly in SQL queries, evaluating conditions in order and returning the corresponding result.

Explanation

Normalization applies a series of rules (normal forms) to reduce data duplication and improve data integrity by structuring tables around well-defined relationships.

Explanation

DELETE is a row-by-row, logged operation supporting WHERE and rollback; TRUNCATE deallocates all data pages at once (faster, minimally logged) and resets auto-increment; DROP deletes the table definition entirely.

Explanation

Window functions compute values across a set of related rows (the "window" or partition) without collapsing the result into one row per group, preserving the original row-level granularity.

Explanation

EXISTS evaluates to true as soon as the subquery produces any row, often allowing the database to short-circuit and avoid scanning all rows, useful for correlated existence checks.

Explanation

A CTE, defined with "WITH name AS (SELECT ...)", creates a temporary named result set scoped to the enclosing query, useful for breaking complex queries into readable steps or writing recursive queries.

Explanation

ON DELETE CASCADE propagates deletions: removing a parent row automatically removes any child rows that reference it via the foreign key.

Explanation

A clustered index defines the actual physical order of data rows in storage (a table can have only one), while non-clustered indexes maintain a separate sorted structure with pointers/references to the actual rows.

Explanation

In standard SQL, non-aggregated columns in the SELECT clause must appear in the GROUP BY clause; otherwise their value would be ambiguous across multiple rows in the same group.

Explanation

RIGHT JOIN preserves all rows from the right-hand table (with NULLs for non-matching left-side columns), the opposite orientation of a LEFT JOIN.

Explanation

FOR UPDATE places a row-level lock on the selected rows, preventing concurrent transactions from modifying them until the lock is released, useful for preventing race conditions.

Explanation

COALESCE evaluates its arguments in order and returns the first one that is not NULL, often used to supply default values.

Explanation

For OUTER JOINs, placing a condition in ON affects which rows are matched (and thus which produce NULLs), while the same condition in WHERE filters out rows after the join, potentially changing results.

Explanation

Both assign the same rank to tied rows, but RANK() skips subsequent rank numbers proportional to the number of ties, while DENSE_RANK() continues with the next consecutive integer.

Explanation

EXPLAIN reveals how the database planner intends to execute a query (table scans vs index usage, join order, estimated costs), guiding optimization efforts.

Explanation

A self-referencing foreign key allows rows in a table to reference other rows in the same table, commonly used to represent tree/hierarchy structures like org charts.

Explanation

INSERT ... SELECT populates a table directly from the result set of another query, useful for bulk copying or transforming data between tables.

Explanation

"> ANY (subquery)" is true if the comparison holds for at least one row returned by the subquery; "> ALL (subquery)" requires it to hold for every row.

Explanation

Identity/auto-increment columns automatically assign an incrementing numeric value on each insert, commonly used to generate surrogate primary keys.

Explanation

CROSS JOIN returns all possible combinations of rows from the joined tables (rows_A × rows_B), with no join condition restricting the pairing.

Explanation

TOP n PERCENT returns approximately that percentage of the total row count, here the highest-scoring 10% after sorting.

Explanation

Materialized views trade storage and freshness (requiring REFRESH) for faster read performance, since the result set is precomputed and stored rather than recalculated on each query.

Explanation

LAG() and LEAD() let you reference values from other rows (previous or next, respectively, based on the specified ordering) without using a self-join.

Explanation

A CHECK constraint defines a condition that every row must satisfy, e.g. "CHECK (age >= 0)", rejecting inserts/updates that violate it.

Explanation

FULL OUTER JOIN combines LEFT and RIGHT JOIN behavior, returning all rows from both tables and filling unmatched columns with NULL.

Explanation

ROUND(value, decimals) rounds the number to the specified number of decimal places, here rounding 123.456 to 123.46.

Explanation

STRING_AGG (PostgreSQL/SQL Server) or GROUP_CONCAT (MySQL) aggregates values from multiple rows into one string separated by a specified delimiter.

Explanation

WITH CHECK OPTION prevents modifications through an updatable view from creating rows that fall outside the view's defining WHERE clause.

Explanation

SAVEPOINT marks an intermediate point in a transaction; ROLLBACK TO SAVEPOINT undoes only the changes made after that point, while keeping earlier changes within the transaction intact.

Explanation

CAST converts a value to a specified data type; DECIMAL(10,2) defines fixed-point precision with 10 total significant digits and 2 after the decimal point.

Explanation

OFFSET skips a specified number of rows before starting to return rows, and LIMIT caps how many are returned — together implementing pagination (e.g. page 3 of 10 results per page).

Explanation

Each isolation level addresses progressively more concurrency anomalies: READ UNCOMMITTED allows dirty reads, READ COMMITTED prevents them but allows non-repeatable reads, REPEATABLE READ prevents those but allows phantoms, and SERIALIZABLE prevents all three at the cost of throughput.

Explanation

A deadlock is a circular wait condition between transactions holding conflicting locks; database engines typically run a deadlock detector that picks a "victim" transaction to roll back, allowing the others to proceed.

Explanation

Plan caching avoids the cost of re-parsing and re-optimizing identical query shapes, but "parameter sniffing" issues can arise when a plan compiled for atypical parameter values is reused for very different data distributions.

Explanation

A recursive CTE defines a base case and a recursive case combined with UNION (or UNION ALL); the database repeatedly evaluates the recursive part against the previous iteration's results until no new rows are produced, commonly used for hierarchical/tree data.

Explanation

When an index contains every column referenced by a query (via key columns and/or included columns), the database can satisfy the query entirely from the index, avoiding extra lookups to the table ("covering" the query).

Explanation

MVCC keeps multiple row versions so that read operations can proceed against a consistent snapshot while writes create new versions, reducing the need for read locks and improving concurrency.

Explanation

For an index to be used efficiently, the predicate generally must leave the indexed column unmodified on one side of the comparison; applying functions to the column (non-SARGable predicates) typically forces a full scan since the index is stored on the raw column values.

Explanation

Table partitioning divides data into segments (e.g. by month), so queries filtering on the partition key can skip irrelevant partitions entirely, and maintenance operations (like dropping old data) can operate on whole partitions efficiently.

Explanation

The N+1 problem causes excessive round trips to the database; rewriting the access pattern to use a JOIN or a single query with "WHERE id IN (...)" retrieves all needed data in one or few queries instead of N+1.

Explanation

These are physical implementation strategies the query optimizer chooses based on data size, sorting, and indexes available: nested loop suits small/indexed cases, hash join handles large unsorted datasets by hashing one side, and merge join is efficient when both inputs are already sorted on the join key.

Explanation

By writing changes to a durable log before modifying data pages, the database guarantees that committed transactions can be recovered (replayed from the log) even if a crash occurs before the data pages themselves were flushed to disk.

Explanation

Pessimistic locking trades concurrency for safety by locking resources before use; optimistic locking assumes conflicts are rare, allowing concurrent reads/writes but validating (often via a version/timestamp column) before committing, rejecting the transaction if the data changed underneath it.

Explanation

Unlike a normal join where both sides are evaluated independently, a LATERAL (or CROSS/OUTER APPLY) join lets the right-hand subquery reference columns from the left-hand row, enabling row-by-row correlated subqueries like fetching the top N related records per row.

Explanation

Denormalization is a deliberate performance trade-off: duplicating or pre-aggregating data avoids costly joins on read-heavy paths at the cost of update complexity and storage, common in data warehouses and reporting systems.

Explanation

Phantom reads involve new rows appearing in a repeated range query due to concurrent inserts; the ANSI SQL standard places full prevention at the SERIALIZABLE level, though implementations like PostgreSQL's snapshot-based REPEATABLE READ also prevent it via different mechanisms.

Explanation

UPSERT combines insert-or-update logic into a single atomic statement, avoiding the race condition where a separate "check if exists, then insert or update" sequence could fail under concurrent access.

Explanation

Because the optimizer "sniffs" the first parameter values to build a plan it then caches and reuses, a plan well-suited for one parameter value (e.g. a very selective filter) may perform poorly for another (e.g. a filter matching most rows), a classic performance pitfall in parameterized stored procedures.

Explanation

A scalar subquery is self-contained and returns one value usable as an expression; a correlated subquery depends on the current row of the outer query (referencing its columns), conceptually executing once per outer row, though optimizers often rewrite these into joins for efficiency.

Explanation

These GROUP BY extensions generate multiple grouping levels in one pass: ROLLUP produces hierarchical subtotals (e.g. by day, then by month, then grand total), CUBE produces every combination of the specified columns, and GROUPING SETS lets you specify exactly which combinations you want.

Explanation

Due to SQL's three-valued logic, "x NOT IN (1, NULL)" evaluates to NULL (not TRUE) for any x, causing the row to be excluded unexpectedly; NOT EXISTS avoids this pitfall since it only checks for row existence, not value equality against potentially-NULL values.