w3resource

SQL VIEW Exercises: Create a view that shows each salesman with more than one customers

SQL VIEW : Exercise-12 with Solution

12. From the following tables, create a view to find those salespeople who handle more than one customer. Return all the fields of salesperson.

Sample table: customer


Sample table: salesman


Sample Solution:

CREATE VIEW mcustomer
AS SELECT *
FROM salesman a
WHERE 1 <
   (SELECT COUNT(*)
     FROM customer b
     WHERE a.salesman_id = b.salesman_id);

output:

sqlpractice=# SELECT *
sqlpractice-# FROM mcustomer;
 salesman_id |     name     |   city   | commission
-------------+--------------+----------+------------
        5002 | Nail Knite   | Paris    |       0.13
        5001 | James Hoog   | New York |       0.15
(2 rows)

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous: From the following table, create a view to compute average purchase amount and total purchase amount for each salesperson. Return name, average purchase and total purchase amount. (Assume all names are unique).
Next: 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.

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