w3resource

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:

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/sql-exercises/view/sql-view-exercise-6.php