Back-End Development Development Technical (SQL) 3 — Questions and Answers
Question 1: What type of SQL index is best suited for columns with very few distinct values, such as a boolean status field?
- B-tree index
- Bitmap index (Correct answer)
- Full-text index
- Spatial index
Correct answer: Bitmap index
Bitmap indexes are efficient for low-cardinality columns because they store a bit per row per distinct value, enabling fast bitwise operations.
Question 2: Which SQL statement correctly adds a foreign key constraint to an existing table?
- ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id); (Correct answer)
- UPDATE TABLE orders SET FOREIGN KEY (customer_id);
- CREATE FOREIGN KEY ON orders(customer_id) TO customers(id);
- MODIFY TABLE orders FOREIGN KEY customer_id LINK customers;
Correct answer: ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);
ALTER TABLE with ADD FOREIGN KEY is the correct DDL syntax to add a referential integrity constraint to an existing table.
Question 3: What does the SQL COALESCE function return?
- The sum of all non-NULL arguments
- The first non-NULL value from its argument list (Correct answer)
- NULL if any argument is NULL
- The last argument regardless of NULL status
Correct answer: The first non-NULL value from its argument list
COALESCE evaluates arguments left to right and returns the first one that is not NULL, making it useful for providing default values.
Question 4: Which type of SQL subquery is executed once for the entire outer query?
- Correlated subquery
- Non-correlated subquery (Correct answer)
- Inline view
- Lateral join
Correct answer: Non-correlated subquery
A non-correlated subquery runs independently of the outer query and its result is computed once, then reused for every row of the outer query.
Question 5: In a B-tree index, what is the typical time complexity for a point lookup?
- O(n)
- O(log n) (Correct answer)
- O(1)
- O(n log n)
Correct answer: O(log n)
B-tree indexes are balanced trees where a point lookup traverses from root to leaf in O(log n) time proportional to the number of index entries.
Question 6: What is the purpose of the SQL EXPLAIN (or EXPLAIN ANALYZE) command?
- Runs the query and returns results with column descriptions
- Shows the query execution plan the optimizer will use (Correct answer)
- Validates SQL syntax without executing the query
- Converts a query into an optimized stored procedure
Correct answer: Shows the query execution plan the optimizer will use
EXPLAIN displays the query execution plan chosen by the optimizer, revealing index usage, join strategies, and estimated costs.
Question 7: Which SQL aggregate function would you use to find the second highest salary without using LIMIT/TOP?
- MAX(salary) WHERE salary < MAX(salary)
- SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) (Correct answer)
- SECOND_MAX(salary)
- RANK() OVER (ORDER BY salary DESC) = 2
Correct answer: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)
A correlated subquery that finds MAX where the value is less than the overall MAX correctly returns the second highest salary.
Back-End Development Development Technical (SQL) 3
What type of SQL index is best suited for columns with very few distinct values, such as a boolean status field?