SQL Exercises, Practice, Solution - JOINS
SQL [29 exercises with solution]
You may read our SQL Joins, SQL Left Join, SQL Right Join, 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 the salesperson and customer who reside in the 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 the 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 represents. 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 salespeople who received commissions of more than 12 percent from the company. 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 locate those salespeople who do not live in the same city where their customers live and have received a commission of more than 12% from the company. 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 join the tables salesman, customer and orders so that the same column of each table appears once and only the relational rows are returned. 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 customer name, customer city, grade, salesman, salesman city. The results should be sorted by ascending 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 with a grade less than 300. Return cust_name, customer city, grade, Salesman, salesmancity. 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 determine whether any of the existing customers have placed an order or not. Go to the editor
Sample table: orders
Sample table: customer
Click me to see the solution with pictorial presentation
11. SQL statement to generate a report with customer name, city, order number, order date, order amount, salesperson name, and commission to determine if any of the existing customers have not placed orders or if they have placed orders through their salesman or by themselves. 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 generate a list in ascending order of salespersons who work either for one or more customers or have not yet joined 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.For those customers from the existing list who put one or more orders, or which orders have been placed by the customer who is not on the list, create a report containing the customer name, city, order number, order date, and purchase amount 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 generate a report with the 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 neither is on the list nor has 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 the salesman table with each row of the 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 create a Cartesian product between salesperson and customer, i.e. each salesperson will appear for all customers and vice versa for that salesperson who belongs to that 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 create a Cartesian product between salesperson and customer, i.e. each salesperson will appear for every customer and vice versa for those salesmen who belong to a city and customers who require 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 customers and vice versa for those salesmen who must belong to a city which is not the same as his customer and the customers should have their 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 query to display the first and last names of each employee, as well as the department name and sanction amount. 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 budgets 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 employed. Return dpt_name. Go to the editor
Sample table: emp_department
Sample table: emp_details
Click me to see the solution with result
Keep Learning: SQL Joins, SQL Left Join, SQL Right Join, SQL Equi Join, SQL Non Equi Join, SQL Inner Join, SQL Natural Join, SQL Cross Join, SQL Outer Join, SQL Full Outer Join, SQL Self Join.
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
MySQL export schema without data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql
Ref: https://bit.ly/3xzB9dS
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook