SQL Exercises, Practice, Solution - SUBQUERIES
SQL SUBQUERIES [39 exercises with solution]
You may read our SQL Subqueries tutorial before solving the following exercises.
[An editor is available at the bottom of the page to write and execute the scripts. Go to the editor]
1. From the following tables, write a SQL query to find all the orders issued by the salesman 'Paul Adam'. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table: Salesman
Sample table: Orders
2. From the following tables write a SQL query to find all orders generated by London-based salespeople. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
Sample table: Salesman
Sample table: Orders
3. From the following tables write a SQL query to find all orders generated by the salespeople who may work for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
Sample table: Salesman
Sample table: Orders
4. From the following tables write a SQL query to find the order values greater than the average order value of 10th October 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
Sample table: Salesman
Sample table: Orders
5. From the following tables, write a SQL query to find all the orders generated in New York city. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table: Salesman
Sample table: Orders
6. From the following tables write a SQL query to determine the commission of the salespeople in Paris. Return commission.
Sample table: Salesman
Sample table : Customer
7. Write a query to display all the customers whose ID is 2001 below the salesperson ID of Mc Lyon.
Sample table: Salesman
Sample table : Customer
8. From the following tables write a SQL query to count the number of customers with grades above the average in New York City. Return grade and count.
Sample table: Customer
9. From the following tables, write a SQL query to find those salespeople who earned the maximum commission. Return ord_no, purch_amt, ord_date, and salesman_id.
Sample table: Customer
Sample table: Orders
Sample table: salesman
10. From the following tables write SQL query to find the customers who placed orders on 17th August 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id and cust_name.
Sample table: Orders
Sample table: Customer
11. From the following tables write a SQL query to find salespeople who had more than one customer. Return salesman_id and name.
Sample table: Customer
Sample table: Salesman
12. From the following tables write a SQL query to find those orders, which are higher than the average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table: Orders
Sample table: Customer
13. From the following tables write a SQL query to find those orders that are equal or higher than the average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table: Orders
Sample table: Customer
14. Write a query to find the sums of the amounts from the orders table, grouped by date, and eliminate all dates where the sum was not at least 1000.00 above the maximum order amount for that date.
Sample table: Orders
Sample table: Customer
15. Write a query to extract all data from the customer table if and only if one or more of the customers in the customer table are located in London.
Sample table: Customer
16. From the following tables write a SQL query to find salespeople who deal with multiple customers. Return salesman_id, name, city and commission.
Sample table: Customer
Sample table: Salesman
17. From the following tables write a SQL query to find salespeople who deal with a single customer. Return salesman_id, name, city and commission.
Sample table: Customer
Sample table: Salesman
18. From the following tables, write a SQL query to find the salespeople who deal the customers with more than one order. Return salesman_id, name, city and commission.
Sample table: Salesman
Sample table: Orders
Sample table: Customer
19. From the following tables write a SQL query to find the salespeople who deal with those customers who live in the same city. Return salesman_id, name, city and commission.
Sample table: Salesman
Sample table: customer
20. From the following tables write a SQL query to find salespeople whose place of residence matches any city where customers live. Return salesman_id, name, city and commission.
Sample table: Salesman
Sample table: customer
21. From the following tables write a SQL query to find all those salespeople whose names appear alphabetically lower than the customer’s name. Return salesman_id, name, city, commission.
Sample table: Salesman
Sample table: Customer
22. From the following table write a SQL query to find all those customers with a higher grade than all the customers alphabetically below the city of New York. Return customer_id, cust_name, city, grade, salesman_id.
Sample table: Customer
23. From the following table write a SQL query to find all those orders whose order amount exceeds at least one of the orders placed on September 10th 2012. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table: Orders
24. From the following tables write a SQL query to find orders where the order amount is less than the order amount of a customer residing in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table: Orders
Sample table: Customer
25. From the following tables write a SQL query to find those orders where every order amount is less than the maximum order amount of a customer who lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table: Orders
Sample table: Customer
26. From the following tables write a SQL query to find those customers whose grades are higher than those living in New York City. Return customer_id, cust_name, city, grade and salesman_id.
Sample table: Customer
27. From the following tables write a SQL query to calculate the total order amount generated by a salesperson. Salespersons should be from the cities where the customers reside. Return salesperson name, city and total order amount.
Sample table: Orders
Sample table: Salesman
Sample table: Customer
28. From the following tables write a SQL query to find those customers whose grades are not the same as those who live in London City. Return customer_id, cust_name, city, grade and salesman_id.
Sample table: Customer
29. From the following tables write a SQL query to find those customers whose grades are different from those living in Paris. Return customer_id, cust_name, city, grade and salesman_id.
Sample table: Customer
30. From the following tables write a SQL query to find all those customers who have different grades than any customer who lives in Dallas City. Return customer_id, cust_name,city, grade and salesman_id.
Sample table: Customer
31. From the following tables write a SQL query to calculate the average price of each manufacturer's product along with their name. Return Average Price and Company.
Sample table: company_mast
Sample table: item_mast
32. From the following tables write a SQL query to calculate the average price of each manufacturer's product of 350 or more. Return Average Price and Company.
Sample table: company_mast
Sample table: item_mast
33. From the following tables, write a SQL query to find the most expensive product of each company. Return Product Name, Price and Company.
Sample table: company_mast
Sample table: item_mast
34. From the following tables write a SQL query to find employees whose last name is Gabriel or Dosio. Return emp_idno, emp_fname, emp_lname and emp_dept.
Sample table: emp_details
35. From the following tables, write a SQL query to find the employees who work in department 89 or 63. Return emp_idno, emp_fname, emp_lname and emp_dept.
Sample table: emp_department
Sample table: emp_details
36. From the following tables write a SQL query to find those employees who work for the department where the departmental allotment amount is more than Rs. 50000. Return emp_fname and emp_lname.
Sample table: emp_department
Sample table: emp_details
37. From the following tables write a SQL query to find the departments whose sanction amount is higher than the average sanction amount for all departments. Return dpt_code, dpt_name and dpt_allotment.
Sample table: emp_department
38. From the following tables write a SQL query to find which departments have more than two employees. Return dpt_name.
Sample table: emp_department
Sample table: emp_details
39. From the following tables write a SQL query to find the departments with the second lowest sanction amount. Return emp_fname and emp_lname.
Sample table: emp_department
Sample table: emp_details
Keep Learning: SQL Subqueries, SQL Single Row Subqueries, SQL Multiple Row and Column Subqueries, SQL Correlated Subqueries, SQL Nested subqueries.
More to Come !
Practice Online
Query visualizations are generated using Postgres Explain Visualizer (pev).
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
SQL: Tips of the Day
MySQL select 10 random rows from 600K rows fast:
SELECT name
FROM random AS r1 JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
Ref: https://bit.ly/3GdCTM3
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook