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

Last update on December 20 2024 12:05:19 (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: customer

Sample table: salesman

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.

Inventory database model:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to shows each salesman has more than one customer.
Next SQL Exercise: View to show the number of orders in each day.