Back-End Development Development Technical (SQL) 5 — Questions and Answers
Question 1: What is a SQL CTE (Common Table Expression) and how is it defined?
- A permanent stored query defined with CREATE CTE
- A temporary named result set defined with the WITH clause before a SELECT (Correct answer)
- A session-level variable storing query results
- A cached materialized view refreshed automatically
Correct answer: A temporary named result set defined with the WITH clause before a SELECT
A CTE is a temporary named result set introduced by the WITH keyword, scoped to the single query that follows it, improving readability and enabling recursion.
Question 2: Which SQL constraint ensures that a column value in one table must exist as a primary key in another table?
- UNIQUE constraint
- CHECK constraint
- FOREIGN KEY constraint (Correct answer)
- NOT NULL constraint
Correct answer: FOREIGN KEY constraint
A FOREIGN KEY constraint enforces referential integrity by requiring the column value to match an existing primary key (or unique key) in the referenced table.
Question 3: What does the SQL LAG() window function do?
- Returns the next row's value in the partition
- Returns a prior row's value based on an offset within the partition (Correct answer)
- Calculates the lag between two timestamp columns
- Delays query execution by a specified interval
Correct answer: Returns a prior row's value based on an offset within the partition
LAG() accesses a value from a previous row within the same partition without requiring a self-join, commonly used for period-over-period comparisons.
Question 4: When is a covering index most beneficial in SQL?
- When the query joins more than three tables
- When all columns needed by a query are included in the index itself (Correct answer)
- When the table has fewer than 1000 rows
- When using aggregate functions like SUM or AVG
Correct answer: When all columns needed by a query are included in the index itself
A covering index satisfies a query entirely from the index without accessing the base table, eliminating expensive table lookups for the matched rows.
Question 5: What is the difference between UNION and UNION ALL in SQL?
- UNION ALL requires matching column names; UNION does not
- UNION removes duplicate rows; UNION ALL keeps all rows including duplicates (Correct answer)
- UNION is faster because it skips sorting; UNION ALL sorts results
- UNION ALL only works on tables; UNION works on any query
Correct answer: UNION removes duplicate rows; UNION ALL keeps all rows including duplicates
UNION performs a DISTINCT operation to eliminate duplicate rows in the combined result set, while UNION ALL returns every row from both queries including duplicates.
Question 6: Which SQL clause is used to sort query results in descending order?
- ORDER BY column ASC
- ORDER BY column DESC (Correct answer)
- SORT BY column REVERSE
- GROUP BY column DESC
Correct answer: ORDER BY column DESC
ORDER BY column DESC sorts the result set from highest to lowest value for that column; ASC (ascending) is the default if no direction is specified.
Question 7: What happens when you execute a ROLLBACK statement in SQL?
- Saves the current transaction state as a savepoint
- Undoes all changes made since the last COMMIT or the start of the transaction (Correct answer)
- Drops and recreates the affected tables
- Deletes only the most recently inserted row
Correct answer: Undoes all changes made since the last COMMIT or the start of the transaction
ROLLBACK reverts the database to its state at the beginning of the current transaction, discarding all uncommitted changes made during that transaction.
Back-End Development Development Technical (SQL) 5
What is a SQL CTE (Common Table Expression) and how is it defined?