SQL Exercises: View to show all matches of customers with salesmen

Last update on January 12 2026 12:48:23 (UTC/GMT +8 hours)

13. From the following table, create a view that shows all matching customers with salespeople, ensuring that at least one customer in the city of the customer is served by the salesperson in the city of the salesperson.

Sample table: Salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

Sample table: Customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005

Sample Solution:

-- Creating a VIEW named 'citymatch' with columns 'custcity' and 'salescity'
CREATE VIEW citymatch(custcity, salescity)

-- Selecting distinct pairs of customer city and salesman city
-- Matching rows from 'customer' and 'salesman' tables where the salesman has customers
-- Using a common column 'salesman_id' for the match
AS SELECT DISTINCT a.city, b.city
FROM customer a, salesman b
WHERE a.salesman_id = b.salesman_id;

output:

sqlpractice=# SELECT *
sqlpractice-# FROM citymatch;
  custcity  | salescity
------------+-----------
 Seattle    | Paris
 Moscow     | Rome
 New York   | New York
 NC         |
 Paris      | Paris
 California | Paris
 Berlin     |
 London     | Paris
 London     | London
 Dallas     | New York
(10 rows)

Code Explanation:

The said statement in SQL creates a view named citymatch, which returns a list of distinct customer and salesman cities that match based on the salesman_id foreign key relationship.
It selects the distinct city values from both the customer and salesman tables and returns them as custcity and salescity, respectively.
The JOIN clause joins the customer and salesman tables on the salesman_id columns and filters the results to only include matching cities between the two tables.

Go to:

PREV : View to shows each salesman has more than one customer.
NEXT : View to show the number of orders in each day.

Inventory database model:

Contribute your code and comments through Disqus.