SQL VIEW Exercises: Define a view that shows for each order the salesman and customer by name

SQL VIEW: Exercise-6 with Solution

6. From the following tables, create a view to get the salesperson and customer by name. Return order name, purchase amount, salesperson ID, name, customer 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: From the following table, create a view to count the number of unique customer, compute average and total purchase amount of customer orders by each date.
Next: From the following tables, create a view to find the salesperson who handles a customer who makes the highest order of a day. Return order date, salesperson ID, name.