Back-End Development Development Technical (SQL) 2 — Questions and Answers
Question 1: Which SQL window function returns the rank of a row within a partition, with no gaps in ranking values for ties?
- RANK()
- DENSE_RANK() (Correct answer)
- ROW_NUMBER()
- NTILE()
Correct answer: DENSE_RANK()
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?
- Individual rows before grouping
- Aggregated group results after GROUP BY (Correct answer)
- Column aliases defined in SELECT
- Joined table rows only
Correct answer: Aggregated group results after GROUP BY
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?
- READ UNCOMMITTED
- READ COMMITTED (Correct answer)
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ COMMITTED
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?
- The left row is excluded from results
- NULL values fill right-table columns (Correct answer)
- An error is raised
- The join falls back to a CROSS JOIN
Correct answer: NULL values fill right-table columns
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?
- SAVE
- FLUSH
- COMMIT (Correct answer)
- PERSIST
Correct answer: COMMIT
COMMIT permanently writes all changes made within a transaction to the database, making them visible to other sessions.
Question 6: In SQL, what does a UNIQUE constraint allow that a PRIMARY KEY constraint does not?
- Duplicate values across rows
- NULL values in the column (Correct answer)
- References from foreign keys
- Composite multi-column indexing
Correct answer: NULL values in the column
A UNIQUE constraint permits NULL values (in most databases multiple NULLs are allowed), whereas PRIMARY KEY columns must be NOT NULL.
Question 7: Which SQL function returns the number of rows in a group, including rows with NULL values?
- COUNT(column_name)
- COUNT(*) (Correct answer)
- SUM(1)
- TOTAL()
Correct answer: COUNT(*)
COUNT(*) counts all rows including those with NULLs, while COUNT(column_name) skips rows where that column is NULL.
Back-End Development Development Technical (SQL) 2
Which SQL window function returns the rank of a row within a partition, with no gaps in ranking values for ties?