SQL UNION Exercises: Produces the name and number of each salesman and each customer with more than one current order
SQL UNION: Exercise-9 with Solution
9. Write a query that produces the name and number of each salesman and each customer with more than one current order. Put the results in alphabetical order.
Sample table: customer
Sample table: salesman
Sample table: orders
SELECT customer_id, cust_name FROM customer a WHERE 1< (SELECT COUNT (*) FROM orders b WHERE a.customer_id = b.customer_id) UNION (SELECT salesman_id, name FROM salesman a WHERE 1 < (SELECT COUNT (*) FROM orders b WHERE a.salesman_id = b.salesman_id)) ORDER BY 2
customer_id cust_name 3009 Geoff Cameron 3005 Graham Zusi 5001 James Hoog 5003 Lauson Hen 5002 Nail Knite 3002 Nick Rimando
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query to create a union of two queries that shows the names, cities, and ratings of all customers. Those with a rating of 300 or greater will also have the words High Rating, while the others will have the words Low Rating.
Next: SQL VIEW Exercises Home
What is the difficulty level of this exercise?