SQL VIEW Exercises: Define a view that shows for each order the salesman and customer by name
SQL VIEW: Exercise-6 with Solution
6. Write a query to create a view that shows for each order the salesman and customer by name.
Sample table: salesman
Sample table: customer
Sample table: orders
CREATE VIEW nameorders AS SELECT ord_no, purch_amt, a.salesman_id, name, cust_name FROM orders a, customer b, salesman c WHERE a.customer_id = b.customer_id AND a.salesman_id = c.salesman_id;
sqlpractice=# SELECT * sqlpractice-# FROM nameorders sqlpractice-# WHERE name = 'Mc Lyon'; ord_no | purch_amt | salesman_id | name | cust_name --------+-----------+-------------+---------+---------------- 70010 | 1983.43 | 5006 | Mc Lyon | Fabian Johnson 70015 | 322.00 | 5006 | Mc Lyon | Varun (2 rows)
Inventory database model:
Contribute your code and comments through Disqus.
Previous: Write a query to create a view to keeping track the number of customers ordering number of salesmen attached, average amount of orders and the total amount of orders in a day.
Next: Write a query to create a view that finds the salesman who has the customer with the highest order of a day.
What is the difficulty level of this exercise?