SQL VIEW Exercises: Create a view that shows the salesmen who issued orders on October 10th, 2012

SQL VIEW: Exercise-15 with Solution

15. From the following tables, create a view to find the salespersons who issued orders on October 10th, 2012. Return all the fields of salesperson.

Sample table: salesman

Sample table: orders

Sample Solution:

CREATE VIEW salesmanonoct
AS SELECT *
FROM salesman
WHERE salesman_id IN
    (SELECT salesman_id
         FROM orders
         WHERE ord_date = '2012-10-10');

output:

sqlpractice=# SELECT *
sqlpractice-# FROM salesmanonoct;
 salesman_id |     name     | city  | commission
-------------+--------------+-------+------------
        5006 | Mc Lyon      | Paris |       0.14
        5003 | Lauson Hense |       |       0.12
(2 rows)

Inventory database model:

Contribute your code and comments through Disqus.

Previous: From the following table, create a view to get number of orders in each day. Return order date and number of orders.
Next:From the following table, create a view to find the salespersons who issued orders on either August 17th, 2012 or October 10th, 2012. Return salesperson ID, order number and customer ID.