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

SQL VIEW: Exercise-13 with Solution

13. From the following tables, create a view that shows all matches of customers with salesperson such that at least one customer in the city of customer served by a salesperson in the city of the salesperson.

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: From the following tables, create a view to find those salespeople who handle more than one customer. Return all the fields of salesperson.
Next: From the following table, create a view to get number of orders in each day. Return order date and number of orders.