SQL Advanced

Question 1

What is a recursive Common Table Expression (CTE)?

Answer 1

A CTE that references itself to perform iterative queries such as hierarchical data traversal

Question 2

How do you write a recursive CTE to find all subordinates of an employee?

Answer 2

Use WITH RECURSIVE subordinates AS (SELECT id manager_id FROM employees WHERE id = ? UNION ALL SELECT e.id e.manager_id FROM employees e INNER JOIN subordinates s ON e.manager_id = s.id) SELECT * FROM subordinates

Question 3

What is the difference between ROW_NUMBER() RANK() and DENSE_RANK()?

Answer 3

ROW_NUMBER assigns unique sequential numbers RANK leaves gaps on ties DENSE_RANK assigns consecutive ranks without gaps

Question 4

What are window frames in SQL window functions?

Answer 4

Specifies the subset of rows used for the calculation relative to the current row

Question 5

How do you calculate a moving average using window functions?

Answer 5

Use AVG() OVER (ORDER BY date ROWS BETWEEN n PRECEDING AND CURRENT ROW)

Question 6

What is the EXPLAIN command used for?

Answer 6

To analyze and display the query execution plan for performance tuning

Question 7

What are execution plans?

Answer 7

Detailed steps the database takes to run a query showing index usage and join methods

Question 8

How do you optimize slow queries?

Answer 8

Analyze execution plans add indexes rewrite joins avoid unnecessary subqueries limit result sets

Question 9

What is a deadlock in SQL?

Answer 9

A situation where two or more transactions are waiting indefinitely for each other’s locks

Question 10

How do you prevent deadlocks?

Answer 10

Keep transactions short access objects in a consistent order use lower isolation levels avoid user interaction in transactions

Question 11

What is isolation level in databases?

Answer 11

Controls how transaction integrity is visible to other transactions (eg READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE)

Question 12

What isolation level prevents dirty reads but allows non-repeatable reads?

Answer 12

READ COMMITTED

Question 13

What is a dirty read?

Answer 13

Reading uncommitted data from another transaction

Question 14

What is a phantom read?

Answer 14

New rows added by another transaction appear in subsequent reads within the same transaction

Question 15

How do you implement pagination efficiently in SQL?

Answer 15

Use OFFSET and LIMIT with indexed columns or keyset pagination for large datasets

Question 16

What is keyset pagination?

Answer 16

Pagination using WHERE clauses with indexed columns instead of OFFSET to improve performance

Question 17

What are materialized views and when would you use them?

Answer 17

Physical storage of query results for faster reads used when query performance is critical but data freshness can be delayed

Question 18

How do you refresh a materialized view?

Answer 18

Using REFRESH MATERIALIZED VIEW command syntax varies by DBMS

Question 19

What are stored procedures?

Answer 19

Precompiled SQL code stored in the database that can accept parameters and perform operations

Question 20

What are the benefits of stored procedures?

Answer 20

Improved performance security and encapsulation of business logic

Question 21

What is a trigger in SQL?

Answer 21

A procedure that automatically executes in response to certain events on a table (INSERT UPDATE DELETE)

Question 22

What is the difference between AFTER and BEFORE triggers?

Answer 22

BEFORE triggers run before the operation AFTER triggers run after the operation completes

Question 23

What is normalization up to Boyce-Codd Normal Form (BCNF)?

Answer 23

An advanced normal form ensuring every determinant is a candidate key to eliminate redundancy

Question 24

What are foreign key constraints with ON DELETE CASCADE?

Answer 24

Deletes rows in child tables automatically when a referenced row in the parent table is deleted

Question 25

How do you handle JSON data in SQL?

Answer 25

Use JSON functions like JSON_EXTRACT JSONB in PostgreSQL or JSON_VALUE in SQL Server to query and manipulate JSON

Question 26

What is a lateral join?

Answer 26

A join that allows a subquery to reference columns from preceding tables in the FROM clause

Question 27

How does a lateral join differ from a regular join?

Answer 27

It executes the subquery for each row of the preceding table useful for correlated subqueries

Question 28

What is partitioning in databases?

Answer 28

Splitting a large table into smaller manageable pieces for performance and maintenance

Question 29

What types of partitioning exist?

Answer 29

Range list hash and composite partitioning

Question 30

What is sharding?

Answer 30

Horizontal partitioning across multiple servers to scale database systems

Question 31

What is a bitmap index and when is it useful?

Answer 31

An index type using bitmaps efficient for low cardinality columns like gender or boolean flags

Question 32

How do you detect and resolve index fragmentation?

Answer 32

Use DBMS specific commands to analyze fragmentation and rebuild or reorganize indexes

Question 33

What is a hash join?

Answer 33

A join algorithm using a hash table to find matching rows efficient for large unsorted datasets

Question 34

What is the difference between a hash join and a nested loop join?

Answer 34

Hash join uses hashing efficient for large joins nested loop is simpler but less efficient for large data

Question 35

What is a CTE's performance impact?

Answer 35

Generally improves readability but can affect performance if not optimized some DBMS materialize CTEs

Question 36

How do you optimize queries using EXISTS instead of IN?

Answer 36

EXISTS can be more efficient with correlated subqueries as it stops searching once a match is found

Question 37

What is a pivot query?

Answer 37

Transforms rows into columns for easier reporting

Question 38

How do you write a pivot query in SQL Server?

Answer 38

Using PIVOT operator with aggregation functions

Question 39

What is the use of the WITH TIES option?

Answer 39

Includes additional rows that tie with the last row in the result set when using ORDER BY and TOP or FETCH

Question 40

What is the difference between DELETE and TRUNCATE regarding transactions?

Answer 40

DELETE logs individual row deletions and can be rolled back TRUNCATE is faster but often can’t be rolled back

Question 41

How do you implement temporal tables?

Answer 41

Tables that track historical data automatically using system-versioned temporal table features