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.]
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. Go to the editor
Sample table: Salesman
Sample table: Orders
2. From the following tables, write a SQL query to find all the orders, which are generated by those salespeople, who live in the city of London.Return ord_no, purch_amt, ord_date, customer_id, salesman_id. Go to the editor
Sample table: Salesman
Sample table: Orders
3. From the following tables, write a SQL query to find the orders generated by the salespeople who works for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id. A customer can works only with a salespeople. Go to the editor
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. Go to the editor
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. Go to the editor
Sample table: Salesman
Sample table: Orders
6. From the following tables, write a SQL query to find the commission of the salespeople work in Paris City. Return commission. Go to the editor
Sample table: Salesman
Sample table : Customer
7. Write a query to display all the customers whose id is 2001 bellow the salesman ID of Mc Lyon. Go to the editor
Sample table: Salesman
Sample table : Customer
8. From the following tables, write a SQL query to count number of customers with grades above the average grades of New York City. Return grade and count. Go to the editor
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. Go to the editor
Sample table: Customer
Sample table: Orders
Sample table: salesman
10. From the following tables, write a SQL query to find the customers whose orders issued on 17th August, 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id and cust_name. Go to the editor
Sample table: Orders
Sample table: Customer
11. From the following tables, write a SQL query to find the salespeople who had more than one customer. Return salesman_id and name. Go to the editor
Sample table: Customer
Sample table: Salesman
12. From the following tables, write a SQL query to find those orders, which amount is higher than the average amount of the related customer. Return ord_no, purch_amt, ord_date, customer_id and salesman_id. Go to the editor
Sample table: Orders
Sample table: Customer
13. From the following tables, write a SQL query to find those orders, which are equal or higher than average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id. Go to the editor
Sample table: Orders
Sample table: Customer
14. Write a query to find the sums of the amounts from the orders table, grouped by date, eliminating all those dates where the sum was not at least 1000.00 above the maximum order amount for that date. Go to the editor
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. Go to the editor
Sample table: Customer
16. From the following tables, write a SQL query to find the salespeople who deal multiple customers. Return salesman_id, name, city and commission. Go to the editor
Sample table: Customer
Sample table: Salesman
17. From the following tables, write a SQL query to find the salespeople who deal a single customer. Return salesman_id, name, city and commission. Go to the editor
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. Go to the editor
Sample table: Salesman
Sample table: Orders
Sample table: Customer
19. From the following tables, write a SQL query to find the salespeople who deals those customers who live in the same city. Return salesman_id, name, city and commission. Go to the editor
Sample table: Salesman
Sample table: customer
20. From the following tables, write a SQL query to find the salespeople whose place of living (city) matches with any of the city where customers live. Return salesman_id, name, city and commission. Go to the editor
Sample table: Salesman
Sample table: customer
21. From the following tables, write a SQL query to find all those salespeople whose name exist alphabetically after the customer’s name. Return salesman_id, name, city, commission. Go to the editor
Sample table: Salesman
Sample table: Customer
22. From the following table, write a SQL query to find all those customers who have a greater grade than any customer who belongs to the alphabetically lower than the city of New York. Return customer_id, cust_name, city, grade, salesman_id Go to the editor
Sample table: Customer
23. From the following table, write a SQL query to find all those orders whose order amount greater than at least one of the orders of September 10th 2012. Return ord_no, purch_amt, ord_date, customer_id and salesman_id. Go to the editor
Sample table: Orders
24. From the following tables, write a SQL query to find those orders where an order amount less than any order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id. Go to the editor
Sample table: Orders
Sample table: Customer
25. From the following tables, write a SQL query to find those orders where every order amount less than the maximum order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id. Go to the editor
Sample table: Orders
Sample table: Customer
26. From the following tables, write a SQL query to find those customers whose grade are higher than customers living in New York City. Return customer_id, cust_name, city, grade and salesman_id. Go to the editor
Sample table: Customer
27. From the following tables, write a SQL query to calculate the total order amount generated by a salesman. The salesman should belong to the cities where any of the customer living. Return salesman name, city and total order amount. Go to the editor
Sample table: Orders
Sample table: Salesman
Sample table: Customer
28. From the following tables, write a SQL query to find those customers whose grade doesn't same of those customers live in London City. Return customer_id, cust_name, city, grade and salesman_id. Go to the editor
Sample table: Customer
29. From the following tables, write a SQL query to find those customers whose grade are not same of those customers living in Paris. Return customer_id, cust_name, city, grade and salesman_id. Go to the editor
Sample table: Customer
30. From the following tables, write a SQL query to find all those customers who have different grade than any customer lives in Dallas City. Return customer_id, cust_name,city, grade and salesman_id. Go to the editor
Sample table: Customer
31. From the following tables, write a SQL query to find 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 the products and find price which are more than or equal to 350. 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 those 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 department 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 where the sanction amount is higher than the average sanction amount of all the departments. Return dpt_code, dpt_name and dpt_allotment.
Sample table: emp_department
38. From the following tables, write a SQL query to find the departments where more than two employees work. Return dpt_name.
Sample table: emp_department
Sample table: emp_details
39. From the following tables, write a SQL query to find the departments where the sanction amount is second lowest. 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.
Practice Online
More to Come !
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
How to restore a dump file from mysqldump?
It should be as simple as running this:
mysql -u <user> -p < db_backup.dump
If the dump is of a single database you may have to add a line at the top of the file:
USE <database-name-here>;
Ref: https://bit.ly/3xKmyw2
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework