w3resource

SQL Exercises: Salesmen who do not have customers in their cities

SQL UNION : Exercise-6 with 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 Solution: 

SELECT salesman.salesman_id, name, cust_name, commission
FROM salesman, customer
WHERE salesman.city = customer.city
UNION
(SELECT salesman_id, name, 'NO MATCH', commission
FROM salesman
WHERE NOT city = ANY
	(SELECT city
        FROM customer))
ORDER BY 2 DESC

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
5006		Mc Lyon		Fabian Johnson		0.14
5003		Lauson Hen	NO MATCH		0.12
5001		James Hoog	Nick Rimando		0.15
5001		James Hoog	Brad Davis		0.15

code Explanation:

The said query in SQL that retrieves data from the 'salesman' and 'customer' tables, and outputs a list of salespeople and their commissions based on the city where they are located and the city of their customers.
The query first joins the 'salesman' and 'customer' tables based on the condition that the salesman's city matches the customer's city. It then uses the UNION operator to combine this result with another query that selects salespeople who do not have any customers in any city. For these salespeople, the query outputs "NO MATCH" instead of the customer name.
The ORDER BY clause sorted the output by the second column in descending order.

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of List all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do - Duration

Rows:

Query visualization of List all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do - Rows

Cost:

Query visualization of List all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Largest and smallest orders on each date.
Next SQL Exercise: Any salesman was matched to the city of any customer.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook