SQL Exercises, Practice, Solution - UNION

SQL UNION [9 exercises with solution]

[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 salespeople and customers located in the city of London.

Sample table: Salesman

Sample table: Customer

Sample Output:

ID	name	?column?
3001	Brad Guzan	Customer
3008	Julian Green	Customer
5005	Pit Alex	Salesman

Click me to see the solution

2. From the following tables, write a SQL query to find distinct salespeople and their cities. Return salesperson ID and city.

Sample table: Salesman

Sample table: Customer

Sample Output:

salesman_id	city
5001	    New York
5002	    London
5002	    California
5006	    Paris
.....

Click me to see the solution

3. From the following tables, write a SQL query to find all those salespeople and customers who are involved in the inventory management system. Return salesperson ID, customer ID.

Sample table: orders

Sample table: customer

Sample Output:

salesman_id	customer_id
5005		3001
5007		3003
5001		3007
5002		3008
.....

Click me to see the solution

4. From the following table, write a SQL query to find the salespersons who generated the largest and smallest orders on each date. Return salesperson ID, name, order no., highest on/lowest on, order date.

Sample table: Salesman

Sample table: Orders

Sample Output:

salesman_id	name	   	ord_no	?column?	ord_date
5001		James Hoog	70002	lowest on	2012-10-05
5001		James Hoog	70005	highest on	2012-07-27
5001		James Hoog	70005	lowest on	2012-07-27
5001		James Hoog	70008	highest on	2012-09-10
.....

Click me to see the solution

5. From the following tables, write a SQL query to find the salespeople who generated the largest and smallest orders on each date. Sort the result-set on third field. Return salesperson ID, name, order no., highest on/lowest on, order date.

Sample table: Salesman

Sample table: Orders

Sample Output:

salesman_id	name		ord_no	?column?	ord_date
5002		Nail Knite	70001	highest on	2012-10-05
5001		James Hoog	70002	lowest on	2012-10-05
5003		Lauson Hen	70003	highest on	2012-10-10
5003		Lauson Hen	70004	highest on	2012-08-17
.....

Click me to see the solution

6. From the following tables, write a SQL query to find those salespeople who live in the same city where the customer lives as well as those who do not have customers in their cities by indicating 'NO MATCH'. Sort the result set on 2nd column (i.e. name) in descending order. Return salesperson ID, name, customer name, commission.

Sample table: Salesman

Sample table: Customer

Sample Output:

salesman_id	name		cust_name		commission
5005		Pit Alex	Julian Green		0.11
5005		Pit Alex	Brad Guzan		0.11
5007		Paul Adam	NO MATCH		0.13
5002		Nail Knite	Fabian Johnson		0.13
.....

Click me to see the solution

7. From the following tables, write a SQL query that appends strings to the selected fields, indicating whether the city of any salesperson is matched with the city of any customer. Return salesperson ID, name, city, MATCHED/NO MATCH.

Sample table: Salesman

Sample table: Customer

Sample Output:

salesman_id	name		city		?column?
5005		Pit Alex	London		MATCHED
5007		Paul Adam	Rome		NO MATCH
5002		Nail Knite	Paris		MATCHED
5006		Mc Lyon		Paris		MATCHED
5003		Lauson Hen	San Jose	NO MATCH
5001		James Hoog	New York	MATCHED

Click me to see the solution

8. From the following table, write a SQL query to create a union of two queries that shows the customer id, cities, and ratings of all customers. Those with a rating of 300 or greater will have the words 'High Rating', while the others will have the words 'Low Rating'.

Sample table: Customer

Sample Output:

customer_id	city		grade		?column?
3002		New York	100		Low Rating
3003		Moscow		200		Low Rating
3004		Paris		300		High Rating
3008		London		300		High Rating
....

Click me to see the solution

9. From the following table, write a SQL query to find those salespersons and customers who have placed more than one order. Return ID, name.

Sample table: Customer

Sample table: salesman

Sample table: orders

Sample Output:

ID	        NAME
3009		Geoff Cameron
3005		Graham Zusi
5001		James Hoog
5003		Lauson Hen
5002		Nail Knite
3002		Nick Rimando

Click me to see the solution

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.