SQL VIEW Exercises: Create a view that shows all matches of customers with salesmen

SQL VIEW: Exercise-13 with Solution

13. Write a query to create a view that shows all matches of customers with salesman such that at least one customer in the city of customer served by a salesman in the city of the salesman.

Sample table: customer

Sample table: salesman

Sample Solution:

CREATE VIEW citymatch(custcity, salescity)
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)

Inventory database model:

Contribute your code and comments through Disqus.

Previous: Write a query to create a view that shows each salesman with more than one customers.
Next: Write a query to create a view that shows the number of orders in each day.