SQL Exercises: View to show the salesmen issued orders on given dates

SQL VIEW : Exercise-16 with Solution

16. From the following table, create a view to find the salespersons who issued orders on either August 17^th, 2012 or October 10^th, 2012. Return salesperson ID, order number and customer ID.

Sample table: orders

Sample Solution:

-- Creating a VIEW named 'sorder' with selected columns from the 'orders' table
CREATE VIEW sorder

-- Selecting columns 'salesman_id', 'ord_no', and 'customer_id' from the 'orders' table
-- Filtering rows where 'ord_date' is in the range from '2012-08-17' to '2012-10-10'
AS SELECT salesman_id, ord_no, customer_id
FROM orders
WHERE ord_date IN ('2012-08-17', '2012-10-10');

output:

sqlpractice=# SELECT *
sqlpractice-# FROM sorder;
 salesman_id | ord_no | customer_id
-------------+--------+-------------
        5003 |  70004 |        3009
        5006 |  70010 |        3004
        5003 |  70003 |        3009
        5007 |  70011 |        3003
        5007 |  70014 |        3005
(5 rows)

Code Explanation:

The provided statement in SQL creates a new view called sorder that selects three columns - salesman_id, ord_no, and customer_id - from the orders table.
The view only includes orders made between August 17th, 2012 and October 10th, 2012.
The WHERE clause selects only the orders made between August 17th, 2012 and October 10th, 2012.

Inventory database model:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to show the salesmen issued orders on given date.
Next SQL Exercise: SQL User Management Exercises Home