Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

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


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

Sample table: emp_department


Sample table: emp_details


Click me to see the solution

36. From the following tables write a SQL query to find those employees who work for the department where the departmental allotment amount is more than Rs. 50000. Return emp_fname and emp_lname.

Sample table: emp_department


Sample table: emp_details


Click me to see the solution

37. From the following tables write a SQL query to find the departments whose sanction amount is higher than the average sanction amount for all departments. Return dpt_code, dpt_name and dpt_allotment.

Sample table: emp_department


Click me to see the solution

38. From the following tables write a SQL query to find which departments have more than two employees. Return dpt_name.

Sample table: emp_department


Sample table: emp_details


Click me to see the solution

39. From the following tables write a SQL query to find the departments with the second lowest sanction amount. Return emp_fname and emp_lname.

Sample table: emp_department


Sample table: emp_details


Click me to see the solution

 

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

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3