w3resource

SQL Exercises, Practice, Solution - Query on Multiple Tables

SQL [ 8 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 salespersons and customers who live in same city. Return customer name, salesperson name and salesperson city.  Go to the editor

Sample table: salesman


Sample table: customer


Sample Output:

cust_name	name		city
Nick Rimando	James Hoog	New York
Brad Davis	James Hoog	New York
Julian Green	Pit Alex	London
Fabian Johnson	Mc Lyon		Paris
Fabian Johnson	Nail Knite	Paris
Brad Guzan	Pit Alex	London

Click me to see the solution with pictorial presentation

2. From the following tables, write a SQL query to find all the customers along with the salesperson who works for them. Return customer name, and salesperson name.   Go to the editor

Sample table: customer


Sample table: salesman


Sample Output:

cust_name	name
Nick Rimando	James Hoog
Brad Davis	James Hoog
Graham Zusi	Nail Knite
Julian Green	Nail Knite
Fabian Johnson	Mc Lyon
Geoff Cameron	Lauson Hen
Jozy Altidor	Paul Adam
Brad Guzan	Pit Alex

Click me to see the solution with pictorial presentation

3. From the following tables, write a SQL query to find those sales people who generated orders for their customers but not located in the same city. Return ord_no, cust_name, customer_id (orders table), salesman_id (orders table).   Go to the editor

Sample table: salesman


Sample table: customer


Sample table: orders


Sample Output:

ord_no	cust_name	customer_id	salesman_id
70004	Geoff Cameron	3009		5003
70003	Geoff Cameron	3009		5003
70011	Jozy Altidor	3003		5007
70001	Graham Zusi	3005		5002
70007	Graham Zusi	3005		5002
70012	Julian Green	3008		5002

Click me to see the solution with pictorial presentation

4. From the following tables, write a SQL query to find those orders made by customers. Return order number, customer name.   Go to the editor

Sample table: orders


Sample table: customer


Sample Output:

ord_no	cust_name
70009	Brad Guzan
70002	Nick Rimando
70004	Geoff Cameron
70005	Brad Davis
70008	Nick Rimando
70010	Fabian Johnson
70003	Geoff Cameron
70011	Jozy Altidor
70013	Nick Rimando
70001	Graham Zusi
70007	Graham Zusi
70012	Julian Green

Click me to see the solution with pictorial presentation

5. From the following tables, write a SQL query to find those customers where each customer has a grade and served by at least a salesperson who belongs to a city. Return cust_name as "Customer", grade as "Grade".    Go to the editor

Sample table: salesman


Sample table: customer


Sample table: orders


Sample Output:

Customer	Grade
Nick Rimando	100
Geoff Cameron	100
Brad Davis	200
Nick Rimando	100
Fabian Johnson	300
Geoff Cameron	100
Jozy Altidor	200
Nick Rimando	100
Graham Zusi	200
Graham Zusi	200
Julian Green	300

Click me to see the solution with pictorial presentation

6. From the following table, write a SQL query to find those customers who served by a salesperson and the salesperson works at the commission in the range 12% to 14% (Begin and end values are included.). Return cust_name AS "Customer", city AS "City".  Go to the editor

Sample table: salesman


Sample table: customer


Sample Output:

Customer	City		Salesman	commission
Graham Zusi	California	Nail Knite	0.13
Julian Green	London		Nail Knite	0.13
Fabian Johnson	Paris		Mc Lyon		0.14
Geoff Cameron	Berlin		Lauson Hen	0.12
Jozy Altidor	Moscow		Paul Adam	0.13

Click me to see the solution with pictorial presentation

7. From the following tables, write a SQL query to find those orders executed by the salesperson, ordered by the customer whose grade is greater than or equal to 200. Compute purch_amt*commission as "Commission". Return customer name, commission as "Commission%" and Commission.  Go to the editor

Sample table: salesman


Sample table: customer


Sample table: orders


Sample Output:

ord_no	cust_name	Commission%	Commission
70005	Brad Davis	0.15		360.0900
70010	Fabian Johnson	0.14		277.6802
70011	Jozy Altidor	0.13		9.7877
70001	Graham Zusi	0.13		19.5650
70007	Graham Zusi	0.13		123.3050
70012	Julian Green	0.13		32.5585

Click me to see the solution with pictorial presentation

8.From the following table, write a SQL query to find those customers who made orders on October 5, 2012. Return customer_id, cust_name, city, grade, salesman_id, ord_no, purch_amt, ord_date, customer_id and salesman_id.  Go to the editor

Sample table: salesman


Sample table: customer


Sample table: orders


Sample Output:

customer_id	cust_name	city		grade	salesman_id	ord_no	purch_amt	ord_date	customer_id	salesman_id
3002		Nick Rimando	New York	100	5001		70002	65.26		2012-10-05	3002		5001
3005		Graham Zusi	California	200	5002		70001	150.50		2012-10-05	3005		5002

Click me to see the solution with pictorial presentation

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.