w3resource

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:

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.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS