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

Last update on December 20 2024 11:46:20 (UTC/GMT +8 hours)

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:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to keep track the number of customers ordering.
Next SQL Exercise: View to find salesman with the highest order of a day.