SQL Exercises, Practice, Solution - SUBQUERIES

Last update on June 17 2022 10:12:06 (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 orders generated by London-based salespeople. 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 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. 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 determine the commission of the salespeople in Paris. 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 below the salesperson 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 the number of customers with grades above the average in 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 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. 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 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 are higher than the 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

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. 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, and eliminate all 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 salespeople who deal with 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 salespeople who deal with 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 deal with 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 salespeople whose place of residence matches any 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 names appear 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 with a higher grade than all the customers alphabetically below 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 exceeds at least one of the orders placed on 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 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. 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 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. 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 grades are higher than those 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 salesperson. Salespersons should be from the cities where the customers reside. Return salesperson 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 grades are not the same as those who 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 grades are different from those 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 grades than any customer who 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 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

Click me to see the solution

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

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 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.