SQL Exercises, Practice, Solution - SUBQUERIES

Last update on April 14 2022 09:59:54 (UTC/GMT +8 hours)

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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.