﻿ SQL - View to show for each order the salesman and customer

# SQL Exercises: View to show for each order the salesman and customer

## SQL VIEW: Exercise-6 with Solution

6. Fr0om 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:

``````-- Creating a VIEW named 'nameorders'
CREATE VIEW nameorders

-- Selecting columns 'ord_no', 'purch_amt', 'salesman_id', 'name', and 'cust_name' from the 'orders', 'customer', and 'salesman' tables
-- Using aliases 'a', 'b', and 'c' for the respective tables
-- Joining tables based on matching 'customer_id' and 'salesman_id'
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)
```

Code Explanation:

The given SQL statement creates a view called "nameorders" that shows the column order number, purchase amount, the salesman ID, the salesman name, and the customer name for each order.
The WHERE clause filters the results to only include rows where the "customer_id" in the 'orders' table matches the "customer_id" in the 'customer' table and the "salesman_id" in the 'orders' table matches the "salesman_id" in the 'salesman' table.

Inventory database model: