w3resource
SQL exercises

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


Sample Solution:

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

Sample Output:

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

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Produces the name and number of each salesman and each customer with more than one current order - Duration

Rows:

Query visualization of Produces the name and number of each salesman and each customer with more than one current order - Rows

Cost:

Query visualization of Produces the name and number of each salesman and each customer with more than one current order - Cost

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?



New Content: Composer: Dependency manager for PHP, R Programming