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

Sample Solution:

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;

output:

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.