w3resource

SQL VIEW Exercises: Define a view that finds the salesman who has the customer with the highest order at least 3 times on a day

SQL VIEW : Exercise-8 with Solution

8. From the following tables, create a view to find the salesperson who handles the customer with the highest order, at least 3 times on a day. Return salesperson ID and name.

Sample table: customer


Sample table: elitsalesman


Sample Solution:

CREATE VIEW incentive
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)

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous: From the following tables, create a view to find the salesperson who handles a customer who makes the highest order of a day. Return order date, salesperson ID, name.
Next: From the following table, create a view to find all the customers who have the highest grade. Return all the fields of customer.

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