SQL Practice Set with Solutions
Section 1: Conceptual Questions
1. What is the difference between UNION and UNION ALL?
Answer: UNION removes duplicate rows from the result set, while UNION ALL includes all
duplicates. UNION performs an implicit DISTINCT operation, which can affect
performance, whereas UNION ALL is faster.
2. Explain the difference between COALESCE and ISNULL in SQL.
Answer: ISNULL is vendor-specific (mainly SQL Server) and accepts exactly two
arguments. COALESCE is ANSI standard and accepts multiple arguments, returning the
first non-NULL value.
3. What is the sequence of SQL query execution?
Answer: The general order is: FROM → WHERE → GROUP BY → HAVING → SELECT
→ ORDER BY → LIMIT. This explains why aliases defined in SELECT are not available in
WHERE.
Section 2: Script Breakdown
4. Query:
SELECT department, COUNT(*) FROM employees WHERE salary > 50000
GROUP BY department HAVING COUNT(*) > 5;
Explanation: This query retrieves departments where more than 5 employees earn over
50,000. It filters rows (WHERE), groups them by department (GROUP BY), counts
employees per group, and filters groups with HAVING.
5. Query:
SELECT [Link], d.dept_name FROM employees e INNER JOIN departments
d ON e.dept_id = [Link];
Explanation: This joins employees and departments tables using dept_id. It fetches
employee names along with their respective department names.
6. Query:
SELECT product_id, SUM(quantity) AS total_qty FROM sales GROUP BY
product_id ORDER BY total_qty DESC LIMIT 5;
Explanation: This finds the top 5 products with the highest sales volume. It groups sales by
product_id, sums quantities, sorts them in descending order, and limits results to 5.
Section 3: Business Problems
7. Find the second highest salary from the employees table.
Answer:
SELECT MAX(salary) AS second_highest_salary FROM employees WHERE
salary < (SELECT MAX(salary) FROM employees);
8. List customers who placed more than 10 orders.
Answer:
SELECT customer_id FROM orders GROUP BY customer_id HAVING
COUNT(order_id) > 10;
9. Get monthly sales totals for the year 2024.
Answer:
SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(amount) AS
total_sales FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024
GROUP BY month ORDER BY month;
10. Find employees who have never been assigned to any project.
Answer:
SELECT [Link], [Link] FROM employees e LEFT JOIN project_assignments
p ON [Link] = p.emp_id WHERE p.emp_id IS NULL;
Section 4: Performance Tip – Indexing
Indexing is like a table of contents in a book. It allows the database to quickly locate rows
without scanning the entire table. Use indexes on columns that are frequently used in
WHERE clauses, JOIN conditions, or ORDER BY clauses. However, over-indexing can
slow down INSERT/UPDATE operations, so balance is key. Composite indexes (on
multiple columns) can be powerful when queries filter by those columns together.