Back-End Development Development Technical (SQL) 2

Question 1

Which SQL window function returns the rank of a row within a partition, with no gaps in ranking values for ties?

Accepted Answer

DENSE_RANK()

Answer

DENSE_RANK() assigns consecutive ranks without gaps when ties occur, unlike RANK() which skips numbers after ties.

Question 2

What does the SQL clause HAVING filter on compared to WHERE?

Accepted Answer

Aggregated group results after GROUP BY

Answer

HAVING filters aggregate results after GROUP BY has been applied, while WHERE filters individual rows before grouping.

Question 3

Which isolation level prevents dirty reads but still allows non-repeatable reads?

Accepted Answer

READ COMMITTED

Answer

READ COMMITTED prevents dirty reads by only reading committed data, but rows read earlier can change if re-read within the same transaction.

Question 4

What is the result of a LEFT JOIN when no matching row exists in the right table?

Accepted Answer

NULL values fill right-table columns

Answer

A LEFT JOIN returns all rows from the left table; when no match exists in the right table, its columns appear as NULL.

Question 5

Which SQL command is used to permanently save changes made during a transaction?

Accepted Answer

COMMIT

Answer

COMMIT permanently writes all changes made within a transaction to the database, making them visible to other sessions.

Back-End Development Practice Test