SQL MCQ

What does SQL stand for?

Which SQL statement is used to retrieve data from a database?

Which clause is used to filter rows based on a condition?

Which keyword is used to sort the result set of a query?

Which SQL statement is used to add new rows to a table?

Which statement modifies existing data in a table?

Which statement removes rows from a table?

What is the purpose of a PRIMARY KEY constraint?

What does the wildcard "%" represent in a LIKE pattern?

Which keyword removes duplicate rows from a SELECT result?

What does NULL represent in SQL?

How do you check if a column value is NULL in a WHERE clause?

Which SQL command creates a new table?

What is the purpose of the LIMIT clause (or TOP in SQL Server)?

Which aggregate function returns the total number of rows matching a query?

What does the BETWEEN operator do?

Which clause groups rows that have the same values into summary rows?

What is the purpose of the FOREIGN KEY constraint?

Which logical operator returns true if both conditions are true?

What does the "AS" keyword do in a SELECT statement?

Which data type is typically used to store whole numbers?

Which data type is best for storing variable-length text with a maximum size, e.g. names?

What does the IN operator do in a WHERE clause?

What is a "view" in SQL?

Which command grants or removes a column's default value, structure, or constraints on an existing table?

What is the result of "5 = NULL" in SQL?

Which clause is used to filter groups after a GROUP BY, based on an aggregate condition?

What does "ORDER BY column DESC" do?

Which function returns the current date and/or time in most SQL databases?

What does "SELECT * FROM table" do?

Which constraint ensures a column cannot contain NULL values?

What does "DROP TABLE table_name" do?

What does the AVG() function do?

What is the purpose of the UNIQUE constraint?

How do you add a new column to an existing table?

Which operator negates a condition, e.g. "WHERE NOT (age > 18)"?

What does "COUNT(DISTINCT column)" return?

What is the difference between CHAR and VARCHAR data types?

Which keyword combines the result sets of two SELECT statements, removing duplicate rows?

What is the result of "NULL OR TRUE" in SQL's three-valued logic?

What is the difference between an INNER JOIN and a LEFT JOIN?

What is a subquery (nested query)?

What does a self-join allow you to do?

What is the purpose of an INDEX on a column?

What is the difference between UNION and UNION ALL?

What does a transaction with "COMMIT" and "ROLLBACK" provide?

What does the ACID acronym stand for in the context of database transactions?

What is the purpose of "GROUP BY" combined with "HAVING COUNT(*) > 1"?

What is a "composite key"?

What does "CASE WHEN condition THEN result ELSE other_result END" provide in a SELECT statement?

What is database normalization?

What is the difference between "DELETE", "TRUNCATE", and "DROP" for removing data from a table?

What does a window function with "OVER (PARTITION BY column)" do, e.g. "SUM(amount) OVER (PARTITION BY dept)"?

What is the purpose of the "EXISTS" operator in a subquery?

What is a "CTE" (Common Table Expression), defined with WITH?

What does the "ON DELETE CASCADE" option on a foreign key do?

What is the difference between a clustered index and a non-clustered index?

What does "GROUP BY" require regarding columns in the SELECT list (in standard SQL)?

What does "RIGHT JOIN" return that differs from "LEFT JOIN"?

What is the effect of "SELECT ... FOR UPDATE" in a transaction?

What does the "COALESCE(a, b, c)" function return?

What is the difference between "WHERE" and "ON" clauses when used with JOINs?

What does "DENSE_RANK()" return differently from "RANK()" in a window function?

What is the purpose of "EXPLAIN" (or "EXPLAIN PLAN") before a query?

What does a "self-referencing foreign key" typically model, e.g. an "employees" table with a "manager_id" column referencing "employees.id"?

What does "INSERT INTO table (cols) SELECT cols FROM other_table" accomplish?

What does "ANY" / "SOME" do when used with a comparison operator and a subquery, e.g. "WHERE salary > ANY (subquery)"?

What is the purpose of "GENERATED ALWAYS AS IDENTITY" (or AUTO_INCREMENT in MySQL)?

What does the "CROSS JOIN" produce?

What does "SELECT TOP 10 PERCENT * FROM table ORDER BY score DESC" (SQL Server syntax) accomplish?

What is a "materialized view" and how does it differ from a regular view?

What does the "LEAD()" / "LAG()" window functions do?

What is the purpose of "CHECK" constraints?

What does "FULL OUTER JOIN" return?

What does "ROUND(123.456, 2)" return?

What is the purpose of the "STRING_AGG" / "GROUP_CONCAT" function?

What does adding "WITH CHECK OPTION" to a view definition enforce?

What is the effect of using a transaction with "SAVEPOINT"?

What does "CAST(column AS DECIMAL(10,2))" do?

What does the "OFFSET" clause do when combined with LIMIT, e.g. "LIMIT 10 OFFSET 20"?

What is the difference between the four standard transaction isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)?

What is a "deadlock" in a database, and how do most database systems handle it?

What is the purpose of "query plan caching" and how can parameterized queries affect it?

How does a recursive CTE (Common Table Expression) work, e.g. for traversing a hierarchy?

What is the difference between "covering index" and a regular index?

What problem does "MVCC" (Multi-Version Concurrency Control), used by databases like PostgreSQL and MySQL InnoDB, solve?

What does "SARGable" mean in the context of a WHERE clause predicate, and why does it matter for performance?

What is the purpose of "PARTITIONING" a large table (e.g. by date range)?

What is the "N+1 query problem" often encountered in application code using an ORM, and how does SQL help avoid it?

What is the difference between a "hash join", "merge join" (sort-merge join), and "nested loop join" as physical join strategies?

What is "write-ahead logging" (WAL) and why is it important for durability?

What is the difference between "optimistic locking" and "pessimistic locking" concurrency control strategies?

What is a "lateral join" (or CROSS APPLY in SQL Server) used for?

What does "denormalization" trade off, and when might it be appropriate?

What is a "phantom read" anomaly, and which isolation level is required to fully prevent it according to the SQL standard?

What is the purpose of "UPSERT" (e.g. "INSERT ... ON CONFLICT DO UPDATE" in PostgreSQL or "ON DUPLICATE KEY UPDATE" in MySQL)?

What does "query plan parameter sniffing" mean in the context of stored procedures, and what issue can it cause?

What is the difference between a "scalar subquery" and a "correlated subquery"?

What does "GROUPING SETS", "CUBE", and "ROLLUP" extend GROUP BY to do?

What is the practical significance of the difference between "NOT IN (subquery)" and "NOT EXISTS (correlated subquery)" when the subquery can return NULL values?