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:

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.