Back-End Development Development Technical (SQL) 5

Question 1

What is a SQL CTE (Common Table Expression) and how is it defined?

Accepted Answer

A temporary named result set defined with the WITH clause before a SELECT

Answer

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?

Accepted Answer

FOREIGN KEY constraint

Answer

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?

Accepted Answer

Returns a prior row's value based on an offset within the partition

Answer

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?

Accepted Answer

When all columns needed by a query are included in the index itself

Answer

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?

Accepted Answer

UNION removes duplicate rows; UNION ALL keeps all rows including duplicates

Answer

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.

Back-End Development Practice Test

Back-End Development Practice Test

Back-End Development Development Technical (SQL) 5