﻿ SQL - View to show all matches of customers with salesmen

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

## SQL VIEW: Exercise-13 with Solution

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: