w3resource

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:

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.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS