SQL Exercises 9 — Union

1. From the customer and salesman tables, find all salespersons and customer who located in ‘London’ city.

2. From the salesman table, find distinct salesperson and their cities. Return salesperson ID and city.

3. From the customer and orders tables, find all those salespersons and customers who involved in inventory management system. Return salesperson ID, customer ID.

4. From the salesman and orders table, find those salespersons generated the largest and smallest orders on each date. Return salesperson ID, name, order no., highest on/ lowest on, order date.

5. From the customer and salesman table, find those salespersons who have same cities where customer lives as well as do not have customers in their cities and indicate it by ‘NO MATCH’. Sort the result set on 2nd column (i.e. name) in descending order. Return salesperson ID, name, customer name, commission.

6. From the customer and salesman tables, appends strings to the selected fields, indicating whether a specified city of any salesperson was matched to the city of any customer. Return salesperson ID, name, city, MATCHED/NO MATCH.

7. From the customer table, 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’.

8. From the customer, salesman and orders tables, find those salesperson and customer where more than one order executed. Sort the result-set on 2nd field. Return ID, name.



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