SQL Exercises 8 — Subqueries

1. From the salesman and orders tables, find all the orders issued by the salesman ‘Paul Adam’. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

2. From the salesman and orders tables, find all the orders, which are generated by those salespeople, who live in the city of London.Return ord_no, purch_amt, ord_date, customer_id, salesman_id.

3. From the orders table, find the orders generated by the salespeople who works for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id. A customer can works only with a salespeople.

4. From the orders table, 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.

5. From the salesman and orders tables, find all the orders generated in New York city. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

-

6. From the customer and salesman tables, find the commission of the salespeople work in Paris City. Return commission.

7. Write a query to display all the customers whose id is 2001 bellow the salesman ID of Mc Lyon.

8. From the customer table, count number of customers with grades above the average grades of New York City. Return grade and count.

9. From the salesman and orders tables, find those salespeople who earned the maximum commission. Return ord_no, purch_amt, ord_date, and salesman_id.

10. From the customer and orders tables, find the customers whose orders issued on 17th August, 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id and cust_name.

11. From the customer and salesman tables, find the salespeople who had more than one customer. Return salesman_id and name.

12. From the orders table, find those orders, which amount is higher than the average amount of the related customer. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

13. From the orders table, find those orders, which are equal or higher than average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

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.

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.

16. From the customer and salesman tables, find the salespeople who deal multiple customers. Return salesman_id, name, city and commission.

17. From the customer and salesman tables, find the salespeople who deal a single customer. Return salesman_id, name, city and commission.

18. From the salesman and orders tables, find the salespeople who deal the customers with more than one order. Return salesman_id, name, city and commission.

19. From the customer and salesman tables, find the salespeople who deals those customers who live in the same city. Return salesman_id, name, city and commission.

20. From the customer and salesman tables, find the salespeople whose place of living (city) matches with any of the city where customers live. Return salesman_id, name, city and commission.

21. From the orders table, find all those orders whose order amount greater than at least one of the orders of September 10th 2012. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

22. From the customer and orders tables, find those orders where an order amount less than any order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

23. From the customer and orders tables, find those orders where every order amount less than the maximum order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

24. From the customer table, find those customers whose grade are higher than customers living in New York City. Return customer_id, cust_name, city, grade and salesman_id.

25. From the customer, salesman, and orders tables, calculate the total order amount generated by a salesman. The salesman should belong to the cities where any of the customer living. Return salesman name, city and total order amount.

26. From the customer table, find those customers whose grade doesn’t same of those customers live in London City. Return customer_id, cust_name, city, grade and salesman_id.

27. From the customer table, find those customers whose grade are not same of those customers living in Paris. Return customer_id, cust_name, city, grade and salesman_id.

28. From the company master and item master tables, find the average price of each manufacturer’s product along with their name. Return Average Price and Company.

29. From the company master and item master tables, calculate the average price of the products and find price which are more than or equal to 350. Return Average Price and Company.

30. From the company master and item master tables, find the most expensive product of each company. Return Product Name, Price and Company.

31. From the employee department and employee details tables, find those employees who work for the department where the department allotment amount is more than Rs. 50000. Return emp_fname and emp_lname.

32. From the employee department table, find the departments where the sanction amount is higher than the average sanction amount of all the departments. Return dpt_code, dpt_name and dpt_allotment.

33. From the employee department and employee details tables, find the departments where more than two employees work. Return dpt_name.

34. From the employee department and employee details tables, find the departments where the sanction amount is second lowest. Return emp_fname and emp_lname.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store