w3resource

SQL Exercises: View to find the salesman with the highest order

SQL VIEW : Exercise-8 with Solution

8. From the following table, create a view to find the salesperson who deals with the customer with the highest order at least three times per day. Return salesperson ID and name.

Sample table: customer


Sample table: elitsalesman


Sample Solution:

-- Creating a VIEW named 'incentive'
CREATE VIEW incentive

-- Selecting distinct 'salesman_id' and 'name' columns from the 'elitsalesman' VIEW
-- Using an alias 'a' for the 'elitsalesman' VIEW
-- Filtering records where a salesman has at least three entries in the 'elitsalesman' VIEW
AS SELECT DISTINCT salesman_id, name
FROM elitsalesman a
WHERE 3 <=
   (SELECT COUNT (*)
    FROM elitsalesman b
    WHERE a.salesman_id = b.salesman_id);

output:

sqlpractice=# SELECT *
sqlpractice-# FROM incentive;
 salesman_id | name
-------------+------------
        5001 | James Hoog 
(1 row)

Code Explanation:

The said statement in SQL creates a view named 'incentive' that selects the unique salesman ID and name from a table named 'elitsalesman', but only includes those salespeople who have made at least three sales.
Using a subquery counts the number of sales made by each salesman, and then checking that the count is greater than or equal to 3. The subquery is correlated to the main query by comparing the salesman ID of each record in the 'elitsalesman' table to the salesman ID of the current record being considered by the main query.

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to find salesman with the highest order of a day.
Next SQL Exercise: View to show all customers with the highest grade.

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-8.php