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.
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
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework