SQL 50 Practice Problems - Answers

Tables

# Table Key Columns
1 departments dept_id, dept_name, location
2 employees emp_id, first_name, last_name, dept_id, manager_id, hire_date, salary, email
3 salary_history id, emp_id, old_salary, new_salary, change_date
4 customers cust_id, cust_name, city, state, join_date
5 products product_id, product_name, category, price
6 orders order_id, cust_id, order_date, total_amount, status
7 order_items item_id, order_id, product_id, quantity, unit_price
8 transactions txn_id, cust_id, txn_date, txn_type (credit/debit), amount
9 user_logins login_id, emp_id, login_date, login_time
10 daily_sales sale_date, revenue

Relationships: employees.dept_id -> departments, employees.manager_id -> employees (self-ref), orders.cust_id -> customers, order_items -> orders & products, transactions.cust_id -> customers, user_logins.emp_id -> employees

Q1. Retrieve all employees who earn more than 80,000 and were hired after 2020. Sort by salary descending.

Q2. Find all products in the 'Electronics' category with price between 5,000 and 50,000 (inclusive).

Q3. List all orders that are NOT 'Cancelled'. Show order_id, cust_id, order_date, and status.

Q4. Find all customers whose name contains 'Tech' or whose city is 'Pune'. Sort alphabetically by name.

Q5. Retrieve the top 5 highest-paid employees.

Q6. Retrieve employee details along with their department name. Include employees even if they have no department assigned.

Q7. Get each employee's name along with their manager's name. If an employee has no manager, show 'No Manager'.

Q8. Show how many employees report to each manager. Include managers with 0 direct reports.

Q9. List all customers and the total number of orders they have placed. Include customers with zero orders.

Q10. For each order, show the order_id, customer name, product names purchased, quantity, and unit_price.

Q11. Find the total revenue generated by each product category. Sort by revenue descending.

Q12. Find departments where the average salary is greater than 80,000. Show department name and average salary.

Q13. Find duplicate records in the employees table based on (first_name, last_name) combination.

Q14. Find the month with the highest total order amount in 2024.

Q15. Count the number of transactions per customer, split by transaction type (credit vs debit). Also show total amounts for each type.

Q16. Find the second highest salary in each department.

Q17. Rank all employees by salary across the entire company. Show three different ranking approaches side by side to demonstrate how they handle ties differently.

Q18. Calculate the running total of daily revenue over time.

Q19. Calculate a 7-day rolling average of daily revenue.

Q20. For each employee, show their salary and how much it differs from their department's average salary.

Q21. Find the top 3 highest-paid employees in each department.

Q22. Calculate Month-over-Month order revenue growth percentage. Exclude cancelled orders.

Q23. For each order, show the previous and next order date for the same customer.

Q24. Find each employee's salary percentile and quartile within their department.

Q25. For each product category, find the cumulative percentage of total revenue contributed by each product.

Q26. Find the total salary expenditure per department and the company-wide total. Show each department's percentage of the total.

Q27. Find customers who have placed orders in at least 3 different months.

Q28. Build the full organizational hierarchy starting from the top-level managers. Show each employee's level in the hierarchy.

Q29. Generate a date series from '2024-01-01' to '2024-01-31'.

Q30. Find employees whose salary is above the average salary of their department.

Q31. Find the employee(s) with the highest salary in the entire company without using LIMIT or window functions.

Q32. Find employees who earn more than the average salary of their own department.

Q33. Find customers who have never placed an order. Provide two different approaches.

Q34. Find products that have been ordered more than 3 times.

Q35. For each department, find the employee with the longest tenure (earliest hire_date).

Q36. Find all employees who do not have an email address on file.

Q37. Display each employee's email. If email is missing, show 'not_provided@company.com' as default.

Q38. Show all customers and their total order amount. For customers with no orders, display 0 instead of NULL.

Q39. Find employees who have been with the company for more than 3 years as of 2026-06-05. Show years of service.

Q40. Count the number of orders placed in each quarter of 2024. Also show the total revenue per quarter.

Q41. Find customers who have not placed any orders in the last 3 months (relative to '2024-10-20'). Include customers who never placed an order.

Q42. Find the average number of days between consecutive orders for each customer.

Q43. Write a query to give a 10% salary raise to all employees in the 'Engineering' department.

Q44. Delete duplicate rows from the employees table, keeping only the row with the lowest emp_id for each (first_name, last_name) combination.

Q45. Transfer 5,000 from customer 101's balance to customer 102. Demonstrate using a transaction with rollback safety.

Q46. Find the maximum number of consecutive login days for each employee.

Q47. Identify customers who placed at least 2 orders but whose last order was more than 90 days ago (relative to '2024-10-20'). These are potentially churned customers.

Q48. Using the salary_history table, show each employee's complete salary timeline including: old salary, new salary, change date, and the number of days each salary was effective.

Q49. Find the top 2 products by total revenue for each month in 2024. If there are ties, include all tied products.

Q50. Calculate the Year-over-Year growth of total transaction amounts per customer. Identify customers with declining spending.

Solutions with full explanations are in the companion file.