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:

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.