w3resource

SQL Exercises: View to find salesman with the highest order of a day

SQL VIEW: Exercise-7 with Solution

7. From the following table, create a view to find the salesperson who handles a customer who makes the highest order of the day. Return order date, salesperson ID, name.

Sample table: customer


Sample table: salesman


Sample table: orders


Sample Solution:

-- Creating a VIEW named 'elitsalesman'
CREATE VIEW elitsalesman

-- Selecting columns 'ord_date', 'salesman_id', and 'name' from the 'salesman' and 'orders' tables
-- Using aliases 'a' and 'b' for the respective tables
-- Joining tables based on matching 'salesman_id' and filtering records where 'purch_amt' is the maximum for each 'ord_date'
AS SELECT b.ord_date, a.salesman_id, a.name
FROM salesman a, orders b
WHERE a.salesman_id = b.salesman_id
AND b.purch_amt =
    (SELECT MAX (purch_amt)
       FROM orders c
       WHERE c.ord_date = b.ord_date);

output:

sqlpractice=# SELECT *
sqlpractice-# FROM elitsalesman;
  ord_date  | salesman_id |     name
------------+-------------+--------------
 2012-08-17 |        5003 | Lauson Hense
 2012-07-27 |        5001 | James Hoog 
 2012-09-10 |        5001 | James Hoog 
 2012-10-10 |        5003 | Lauson Hense
 2012-06-27 |        5002 | Nail Knite
 2012-04-25 |        5001 | James Hoog 
 2012-10-05 |        5002 | Nail Knite
 2012-09-22 |        5006 | Mc Lyon
(8 rows)

Code Explanation:

The said query in SQL creates a view named 'elitsalesman' which shows the order date, salesman ID, and salesman name for the top-performing salesman for each order date.
The join condition include rows where the salesman ID in the 'salesman' table matches the salesman ID in the 'orders' table.
The WHERE clause will filter and only include rows where the purchase amount for the given order date is equal to the maximum purchase amount for that date. This effectively selects the top-performing salesman for each order date.

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to show for each order the salesman and customer.
Next SQL Exercise: View to find the salesman with the highest order.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.