w3resource

SQL Exercises, Practice, Solution - JOINS

SQL [29 exercises with solution]

[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 the salesperson and customer who belongs to same city. Return Salesman, cust_name and city.  Go to the editor

Sample table: salesman


Sample table: customer


Click me to see the solution with pictorial presentation

2. From the following tables write a SQL query to find those orders where order amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.  Go to the editor

Sample table: orders


Sample table: customer


Click me to see the solution with pictorial presentation

3. From the following tables write a SQL query to find the salesperson(s) and the customer(s) he handle. Return Customer Name, city, Salesman, commission.  Go to the editor

Sample table: customer


Sample table: salesman


Click me to see the solution with pictorial presentation

4. From the following tables write a SQL query to find those salespersons who received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, commission.   Go to the editor

Sample table: customer


Sample table: salesman


Click me to see the solution with pictorial presentation

5. From the following tables write a SQL query to find those salespersons do not live in the same city where their customers live and received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, salesman city, commission.   Go to the editor

Sample table: customer


Sample table: salesman


Click me to see the solution with pictorial presentation

6. From the following tables write a SQL query to find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission. Go to the editor

Sample table: orders


Sample table: customer


Sample table: salesman


Click me to see the solution with pictorial presentation

7. Write a SQL statement to make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.  Go to the editor

Sample table: orders


Sample table: customer


Sample table : salesman


Click me to see the solution with pictorial presentation

8. From the following tables write a SQL query to display the cust_name, customer city, grade, Salesman, salesman city. The result should be ordered by ascending on customer_id.   Go to the editor

Sample table: customer


Sample table: salesman


Click me to see the solution with pictorial presentation

9. From the following tables write a SQL query to find those customers whose grade less than 300. Return cust_name, customer city, grade, Salesman, saleman city. The result should be ordered by ascending customer_id.  Go to the editor

Sample table: customer


Sample table: salesman


Click me to see the solution with pictorial presentation

10. Write a SQL statement to make a report with customer name, city, order number, order date, and order amount in ascending order according to the order date to find that either any of the existing customers have placed no order or placed one or more orders.  Go to the editor

Sample table: orders


Sample table: customer


Click me to see the solution with pictorial presentation

11. Write a SQL statement to make a report with customer name, city, order number, order date, order amount salesman name and commission to find that either any of the existing customers have placed no order or placed one or more orders by their salesman or by own.  Go to the editor

Sample table: customer


Sample table: orders


Sample table: salesman


Click me to see the solution with pictorial presentation

12. Write a SQL statement to make a list in ascending order for the salesmen who works either for one or more customer or not yet join under any of the customers.  Go to the editor

Sample table: customer


Sample table: salesman


Click me to see the solution with pictorial presentation

13. From the following tables write a SQL query to list all salespersons along with customer name, city, grade, order number, date, and amount.  Go to the editor

Sample table: customer


Sample table: salesman


Sample table: orders


Click me to see the solution with pictorial presentation

14. Write a SQL statement to make a list for the salesmen who either work for one or more customers or yet to join any of the customer. The customer may have placed, either one or more orders on or above order amount 2000 and must have a grade, or he may not have placed any order to the associated supplier.  Go to the editor

Sample table: customer


Sample table: salesman


Sample table: orders


Click me to see the solution with pictorial presentation

15. Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for those customers from the existing list who placed one or more orders or which order(s) have been placed by the customer who is not on the list.  Go to the editor

Sample table: customer


Sample table: orders


Click me to see the solution with pictorial presentation

16. Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for only those customers on the list who must have a grade and placed one or more orders or which order(s) have been placed by the customer who is neither in the list nor have a grade.  Go to the editor

Sample table: customer


Sample table: orders


Click me to see the solution with pictorial presentation

17. Write a SQL query to combine each row of salesman table with each row of customer table.  Go to the editor

Sample table: salesman


Sample table: customer


Click me to see the solution with pictorial presentation

18. Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city.  Go to the editor

Sample table: salesman


Sample table: customer


Click me to see the solution with pictorial presentation

19. Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who belongs to a city and the customers who must have a grade.  Go to the editor

Sample table: salesman


Sample table: customer


Click me to see the solution with pictorial presentation

20. Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who must belong a city which is not the same as his customer and the customers should have an own grade.  Go to the editor

Sample table: salesman


Sample table: customer


Click me to see the solution with pictorial presentation

21. From the following tables write a SQL query to select all rows from both participating tables as long as there is a match between pro_com and com_id. Go to the editor

Sample table: company_mast


Sample table: item_mast


Click me to see the solution with result

22. Write a SQL query to display the item name, price, and company name of all the products.  Go to the editor

Sample table: company_mast


Sample table: item_mast


Click me to see the solution with result

23. From the following tables write a SQL query to calculate the average price of items of each company. Return average value and company name.  Go to the editor

Sample table: company_mast


Sample table: item_mast


Click me to see the solution with result

24. From the following tables write a SQL query to calculate and find the average price of items of each company higher than or equal to Rs. 350. Return average value and company name.  Go to the editor

Sample table: company_mast


Sample table: item_mast


Click me to see the solution with result

25. From the following tables write a SQL query to find the most expensive product of each company. Return pro_name, pro_price and com_name.  Go to the editor

Sample table: company_mast


Sample table: item_mast


Click me to see the solution with result

26. From the following tables write a SQL query to display all the data of employees including their department.  Go to the editor

Sample table: emp_department


Sample table: emp_details


Click me to see the solution with result

27. From the following tables write a SQL to display the first name and last name of each employee, along with the name and sanction amount for their department.  Go to the editor

Sample table: emp_department


Sample table: emp_details


Click me to see the solution with result

28. From the following tables write a SQL query to find the departments with a budget more than Rs. 50000 and display the first name and last name of employees.  Go to the editor

Sample table: emp_department


Sample table: emp_details


Click me to see the solution with result

29. From the following tables write a SQL query to find the names of departments where more than two employees are working. Return dpt_name.  Go to the editor

Sample table: emp_department


Sample table: emp_details


Click me to see the solution with result

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.