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. 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: Salesmansalesman_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.13Sample 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. 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: Salesmansalesman_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.13Sample 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
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.
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
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.
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
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.
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. From the following tables write a SQL query to determine the commission of the salespeople in Paris. Return commission.
Sample table: Salesmansalesman_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.13Sample 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. Write a query to display all the customers whose ID is 2001 below the salesperson ID of Mc Lyon.
Sample table: Salesmansalesman_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.13Sample 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. 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 : 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
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.
Sample table: Salesmansalesman_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.13Sample 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. 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: 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 5001Sample 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. From the following tables write a SQL query to find salespeople who had more than one customer. Return salesman_id and name.
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 5007Sample 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. 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 : 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
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.
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
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.
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
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. 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. 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 5007Sample 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. 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 : 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 5007Sample 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. 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: Salesmansalesman_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.13Sample 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 5001Sample 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. 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.
Sample table: Salesmansalesman_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.13Sample 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. 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: Salesmansalesman_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.13Sample 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. 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: Salesmansalesman_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.13Sample 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. 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 : 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
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.
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
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.
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 5001Sample 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. 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: 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 5001Sample 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. 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 : 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
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.
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 5001Sample 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.13Sample 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. 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 : 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
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.
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
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.
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
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
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
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
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. 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
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
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_detailsEMP_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. 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_detailsEMP_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. 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. 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. 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. 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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics