w3resource

SQL Exercises: View to shows each salesman has more than one customer.

SQL VIEW : Exercise-12 with Solution

12. From the following table, create a view to identify salespeople who work with multiple clients. Return all the fields of salesperson.

Sample table: customer


Sample table: salesman


Sample Solution:

-- Creating a VIEW named 'mcustomer'
CREATE VIEW mcustomer

-- Selecting all columns from the 'salesman' table as 'a'
-- Filtering the rows where a salesman has more than one customer
-- Using a subquery to count the number of customers for each salesman and comparing it to 1
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)

Code Explanation:

The said statement in SQL creates a view named mcustomer, which returns all columns from the salesman table where the salesman has more than one customer associated with them.
This filters the results to only include records where the count of associated customers is greater than 1.
The subquery counts the number of customers associated with each salesman using the salesman_id foreign key in the customer table.

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to show average and total orders for each salesman.
Next SQL Exercise: View to show all matches of customers with salesmen.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/view/sql-view-exercise-12.php