SQL Exercises, Practice, Solution - SUBQUERIES

Last update on November 27 2019 06:24:32 (UTC/GMT +8 hours)

SQL SUBQUERIES [39 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

1. Write a query to display all the orders from the orders table issued by the salesman 'Paul Adam'. Go to the editor

Sample table: Salesman

Sample table: Orders

Click me to see the solution

2. Write a query to display all the orders for the salesman who belongs to the city London. Go to the editor

Sample table: Salesman

Sample table: Orders

Click me to see the solution

3. Write a query to find all the orders issued against the salesman who may works for customer whose id is 3007. Go to the editor

Sample table: Salesman

Sample table: Orders

Click me to see the solution

4. Write a query to display all the orders which values are greater than the average order value for 10th October 2012. Go to the editor

Sample table: Salesman

Sample table: Orders

Click me to see the solution

5. Write a query to find all orders attributed to a salesman in New york. Go to the editor

Sample table: Salesman

Sample table: Orders

Click me to see the solution

6. Write a query to display the commission of all the salesmen servicing customers in Paris. 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. Write a query to count the customers with grades above New York's average. Go to the editor

Sample table: Customer

Click me to see the solution

9. Write a query to extract the data from the orders table for those salesman who earned the maximum commission Go to the editor

Sample table: Customer

Sample table: Orders

Click me to see the solution

10. Write a query to display all the customers with orders issued on date 17th August, 2012. Go to the editor

Sample table: Orders

Sample table: Customer

Click me to see the solution

11. Write a query to find the name and numbers of all salesmen who had more than one customer. Go to the editor

Sample table: Customer

Sample table: Salesman

Click me to see the solution

12. Write a query to find all orders with order amounts which are above-average amounts for their customers. Go to the editor

Sample table: Orders

Sample table: Customer

Click me to see the solution

13. Write a queries to find all orders with order amounts which are on or above-average amounts for their customers. 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 the 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. Write a query to find the salesmen who have multiple customers. Go to the editor

Sample table: Customer

Sample table: Salesman

Click me to see the solution

17. Write a query to find all the salesmen who worked for only one customer. Go to the editor

Sample table: Customer

Sample table: Salesman

Click me to see the solution

18. Write a query that extract the rows of all salesmen who have customers with more than one orders. Go to the editor

Sample table: Salesman

Sample table: Orders

Sample table: Customer

Click me to see the solution

19. Write a query to find salesmen with all information who lives in the city where any of the customers lives. Go to the editor

Sample table: Salesman

Sample table: customer

Click me to see the solution

20. Write a query to find all the salesmen for whom there are customers that follow them. Go to the editor

Sample table: Salesman

Sample table: customer

Click me to see the solution

21. Write a query to display the salesmen which name are alphabetically lower than the name of the customers. Go to the editor

Sample table: Salesman

Sample table: Customer

Click me to see the solution

22. Write a query to display the customers who have a greater gradation than any customer who belongs to the alphabetically lower than the city New York. Go to the editor

Sample table: Salesman

Sample table: Customer

Click me to see the solution

23. Write a query to display all the orders that had amounts that were greater than at least one of the orders on September 10th 2012. Go to the editor

Sample table: Orders

Click me to see the solution

24. Write a query to find all orders with an amount smaller than any amount for a customer in London. Go to the editor

Sample table: Orders

Sample table: Customer

Click me to see the solution

25. Write a query to display all orders with an amount smaller than any amount for a customer in London. Go to the editor

Sample table: Orders

Sample table: Customer

Click me to see the solution

26. Write a query to display only those customers whose grade are, in fact, higher than every customer in New York. Go to the editor

Sample table: Customer

Click me to see the solution

27. Write a query to find only those customers whose grade are, higher than every customer to the city New York. Go to the editor

Sample table: Salesman

Sample table: Customer

Click me to see the solution

28. Write a query to get all the information for those customers whose grade is not as the grade of customer who belongs to the city London. Go to the editor

Sample table: Customer

Click me to see the solution

29. Write a query to find all those customers whose grade are not as the grade, belongs to the city Paris. Go to the editor

Sample table: Customer

Click me to see the solution

30. Write a query to find all those customers who hold a different grade than any customer of the city Dallas. Go to the editor

Sample table: Customer

Click me to see the solution

31. Write a SQL query to find the average price of each manufacturer's products along with their name.

Sample table: company_mast

Sample table: item_mast

Click me to see the solution

32. Write a SQL query to display the average price of the products which is more than or equal to 350 along with their names.

Sample table: company_mast

Sample table: item_mast

Click me to see the solution

33. Write a SQL query to display the name of each company, price for their most expensive product along with their Name.

Sample table: company_mast

Sample table: item_mast

Click me to see the solution

34. Write a query in SQL to find all the details of employees whose last name is Gabriel or Dosio.

Sample table: emp_details

Click me to see the solution

35. Write a query in SQL to display all the details of employees who works in department 89 or 63.