SQL Exercises, Practice, Solution - SUBQUERIES
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
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
3. Write a query to find all the orders issued against the salesman who works for customer whose id is 3007. Go to the editor
Sample table: Salesman
Sample table: Orders
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
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
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
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. Write a query to count the customers with grades above New York's average. Go to the editor
Sample table: Customer
9. Write a query to display all customers with orders on October 5, 2012. Go to the editor
Sample table: Customer
Sample table: Orders
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
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
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
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
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 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
16. Write a query to find the salesmen who have multiple customers. Go to the editor
Sample table: Customer
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
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
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
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
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
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
23. Write a query to display all the orders that had amounts that were greater than at least one of the orders on October 9th 2012. Go to the editor
Sample table: Orders
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: Salesman
Sample table: Customer
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: Salesman
Sample table: Customer
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: Salesman
Sample table: Customer
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
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: Salesman
Sample table: Customer
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: Salesman
Sample table: Customer
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: Salesman
Sample table: Customer
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
32. Write a SQL query to display the average price of the products which is more than or equal to 350 along with theri names.
Sample table: company_mast
Sample table: item_mast
33. Write a SQL query to display the name of each company, price for their most expensive product along with their ID.
Sample table: company_mast
Sample table: item_mast
34. Write a query in SQL to find all the details of employees whose last name is Gabriel or Dosio.
Sample table: emp_department
Sample table: emp_details
35. Write a query in SQL to display all the details of employees who works in department 89 or 63.
Sample table: emp_department
Sample table: emp_details
36. Write a query in SQL to display the first name and last name of employees working for the department which allotment amount is more than Rs.50000.
Sample table: emp_department
Sample table: emp_details
37. Write a query in SQL to find the departments which sanction amount is larger than the average sanction amount of all the departments.
Sample table: emp_department
Sample table: emp_details
38. Write a query in SQL to find the names of departments with more than two employees are working.
Sample table: emp_department
Sample table: emp_details
39. Write a query in SQL to find the first name and last name of employees working for departments which sanction amount is second lowest.
Sample table: emp_department
Sample table: emp_details