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. Go to the editor]
1. Display Orders Issued by Salesman 'Paul Adam'
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.
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
2. Display Orders Generated by London-Based Salespeople
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
3. Display Orders from Salespeople Handling Customer ID 3007
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
4. Display Orders Exceeding Average Value on 10-Oct-2012
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
5. Display Orders Generated in New York City
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.
Sample table: Ordersord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
6. Display Commission of Salespeople in Paris
From the following tables write a SQL query to determine the commission of the salespeople in Paris. Return commission.
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
7. Display Customers with ID Below 2001 Under Salesperson Mc Lyon
Write a query to display all the customers whose ID is 2001 below the salesperson ID of Mc Lyon.
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
8. Count of Customers with Above-Average Grades in New York City
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
9. Display Orders of Salespeople with Maximum Commission
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.
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
10. Display Customers Who Placed Orders on 17th August 2012
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
11. Display Salespeople with More Than One Customer
From the following tables write a SQL query to find salespeople who had more than one customer. Return salesman_id and name.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
12. Display Orders with Amount Above Average Order Value
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
13. Display Orders with Amount ≥ Average Order Value
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
14. Show Order Sums by Date Exceeding Max Order by 1000
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
15. Show All Customers If Any Are Located in London
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
16. Find Salespeople Handling Multiple Customers
From the following tables write a SQL query to find salespeople who deal with multiple customers. Return salesman_id, name, city and commission.
Sample table : Customercustomer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
17. Find Salespeople Handling Only One Customer
From the following tables write a SQL query to find salespeople who deal with a single customer. Return salesman_id, name, city and commission.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
18. Find Salespeople Handling Customers with Multiple Orders
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.
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
19. Find Salespeople in Cities with at Least One Customer
From the following tables write a SQL query to find all salespeople who are located in any city where there is at least one customer. Return salesman_id, name, city and commission.
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
20. Find Salespeople Living in a Customer's City
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.Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
21. Find Salespeople with Names Alphabetically Before Customers
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.
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table: Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
22. Find Customers with Higher Grade Than Those Below New York
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
23. Find Orders Exceeding Any Order from September 10, 2012
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.
Sample table: Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
24. Find Orders with Amount Less Than Any Order from London
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
25. Find Orders with Amount Less Than the Max Order from London
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
26. Find Customers with Higher Grades Than Those in New York
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
27. Calculate Total Order Amount by Salespeople in Customer Cities
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.
Sample table: Orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table: Salesman
salesman_id name city commission ----------- ---------- ---------- ---------- 5001 James Hoog New York 0.15 5002 Nail Knite Paris 0.13 5005 Pit Alex London 0.11 5006 Mc Lyon Paris 0.14 5003 Lauson Hen San Jose 0.12 5007 Paul Adam Rome 0.13
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
28. Find Customers with Grades Different from Those in London
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
29. Find Customers with Grades Different from Those in Paris
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
30. Find Customers with Grades Different from Any in Dallas
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.
Sample table : Customer
customer_id cust_name city grade salesman_id ----------- ------------ ---------- ---------- ----------- 3002 Nick Rimando New York 100 5001 3005 Graham Zusi California 200 5002 3001 Brad Guzan London 100 5005 3004 Fabian Johns Paris 300 5006 3007 Brad Davis New York 200 5001 3009 Geoff Camero Berlin 100 5003 3008 Julian Green London 300 5002 3003 Jozy Altidor Moncow 200 5007
31. Calculate Average Price of Products by Manufacturer
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: item_mast
PRO_ID PRO_NAME PRO_PRICE PRO_COM
------- ------------------------- -------------- ----------
101 Mother Board 3200.00 15
102 Key Board 450.00 16
103 ZIP drive 250.00 14
104 Speaker 550.00 16
105 Monitor 5000.00 11
106 DVD drive 900.00 12
107 CD drive 800.00 12
108 Printer 2600.00 13
109 Refill cartridge 350.00 13
110 Mouse 250.00 12
Sample table: company_mast
COM_ID COM_NAME
------ -------------
11 Samsung
12 iBall
13 Epsion
14 Zebronics
15 Asus
16 Frontech
32. Calculate Average Price of Manufacturer's Products (≥ 350)
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
COM_ID COM_NAME
------ -------------
11 Samsung
12 iBall
13 Epsion
14 Zebronics
15 Asus
16 Frontech
Sample table: item_mast
PRO_ID PRO_NAME PRO_PRICE PRO_COM
------- ------------------------- -------------- ----------
101 Mother Board 3200.00 15
102 Key Board 450.00 16
103 ZIP drive 250.00 14
104 Speaker 550.00 16
105 Monitor 5000.00 11
106 DVD drive 900.00 12
107 CD drive 800.00 12
108 Printer 2600.00 13
109 Refill cartridge 350.00 13
110 Mouse 250.00 12
33. Find Most Expensive Product of Each Company
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: item_mast
PRO_ID PRO_NAME PRO_PRICE PRO_COM
------- ------------------------- -------------- ----------
101 Mother Board 3200.00 15
102 Key Board 450.00 16
103 ZIP drive 250.00 14
104 Speaker 550.00 16
105 Monitor 5000.00 11
106 DVD drive 900.00 12
107 CD drive 800.00 12
108 Printer 2600.00 13
109 Refill cartridge 350.00 13
110 Mouse 250.00 12
Sample table: company_mast
COM_ID COM_NAME
------ -------------
11 Samsung
12 iBall
13 Epsion
14 Zebronics
15 Asus
16 Frontech
34. Find Employees with Last Name Gabriel or Dosio
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
EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT --------- --------------- --------------- ---------- 127323 Michale Robbin 57 526689 Carlos Snares 63 843795 Enric Dosio 57 328717 Jhon Snares 63 444527 Joseph Dosni 47 659831 Zanifer Emily 47 847674 Kuleswar Sitaraman 57 748681 Henrey Gabriel 47 555935 Alex Manuel 57 539569 George Mardy 27 733843 Mario Saule 63 631548 Alan Snappy 27 839139 Maria Foster 57
35. Find Employees in Departments 89 or 63
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_details
EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT --------- --------------- --------------- ---------- 127323 Michale Robbin 57 526689 Carlos Snares 63 843795 Enric Dosio 57 328717 Jhon Snares 63 444527 Joseph Dosni 47 659831 Zanifer Emily 47 847674 Kuleswar Sitaraman 57 748681 Henrey Gabriel 47 555935 Alex Manuel 57 539569 George Mardy 27 733843 Mario Saule 63 631548 Alan Snappy 27 839139 Maria Foster 57
36. Find Employees in Departments with Allotment > Rs. 50000
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
DPT_CODE DPT_NAME DPT_ALLOTMENT
-------- --------------- -------------
57 IT 65000
63 Finance 15000
47 HR 240000
27 RD 55000
89 QC 75000
Sample table: emp_details
EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT --------- --------------- --------------- ---------- 127323 Michale Robbin 57 526689 Carlos Snares 63 843795 Enric Dosio 57 328717 Jhon Snares 63 444527 Joseph Dosni 47 659831 Zanifer Emily 47 847674 Kuleswar Sitaraman 57 748681 Henrey Gabriel 47 555935 Alex Manuel 57 539569 George Mardy 27 733843 Mario Saule 63 631548 Alan Snappy 27 839139 Maria Foster 57
37. Find Departments with Sanction Amount Above Average
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
DPT_CODE DPT_NAME DPT_ALLOTMENT
-------- --------------- -------------
57 IT 65000
63 Finance 15000
47 HR 240000
27 RD 55000
89 QC 75000
38. Find Departments with More Than Two Employees
From the following tables write a SQL query to find which departments have more than two employees. Return dpt_name.
Sample table: emp_department
DPT_CODE DPT_NAME DPT_ALLOTMENT
-------- --------------- -------------
57 IT 65000
63 Finance 15000
47 HR 240000
27 RD 55000
89 QC 75000
Sample table: emp_details
EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT --------- --------------- --------------- ---------- 127323 Michale Robbin 57 526689 Carlos Snares 63 843795 Enric Dosio 57 328717 Jhon Snares 63 444527 Joseph Dosni 47 659831 Zanifer Emily 47 847674 Kuleswar Sitaraman 57 748681 Henrey Gabriel 47 555935 Alex Manuel 57 539569 George Mardy 27 733843 Mario Saule 63 631548 Alan Snappy 27 839139 Maria Foster 57
39. Find Employees in Departments with Second Lowest Allotment
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
DPT_CODE DPT_NAME DPT_ALLOTMENT
-------- --------------- -------------
57 IT 65000
63 Finance 15000
47 HR 240000
27 RD 55000
89 QC 75000
Sample table: emp_details
EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT --------- --------------- --------------- ---------- 127323 Michale Robbin 57 526689 Carlos Snares 63 843795 Enric Dosio 57 328717 Jhon Snares 63 444527 Joseph Dosni 47 659831 Zanifer Emily 47 847674 Kuleswar Sitaraman 57 748681 Henrey Gabriel 47 555935 Alex Manuel 57 539569 George Mardy 27 733843 Mario Saule 63 631548 Alan Snappy 27 839139 Maria Foster 57
Keep Learning: SQL Subqueries, SQL Single Row Subqueries, SQL Multiple Row and Column Subqueries, SQL Correlated Subqueries, SQL Nested subqueries.
More to Come !
Practice Online
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.