SQL Exercises: View to find salesman with the highest order of a day
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
customer_id | cust_name | city | grade | salesman_id
-------------+----------------+------------+-------+-------------
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 5005
Sample table: Salesman
salesman_id | name | city | commission
-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
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.
Go to:
PREV : View to show for each order the salesman and customer.
NEXT : View to find the salesman with the highest order.
Inventory database model:
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.