Question

Create a view that will list the order number, order date, part number, part description, and item class for each part that makes up the order from the TAL database.

TAL Database:

Table_Name	Column Name	Column Type	Column Length	Nullable	Key
Orders	Order_Num	Character	5	No	Primary
orders	order_date	Date		Yes
Orders	Customer_num	Character	3	Yes	Foreign
Order_Line	Order_Num	Character	5	No	Primary
Order_Line	Part_Num	Character	4	No	Primary
Order_Line	Num_Ordered	Number	3,0	Yes
Order_Line	Quoted_Price	Number	6,2	Yes
Part	part_num	Character	4	No	Primary
Part	Description	Character	150	Yes
Part	on_hand	Number	4,0	Yes
Part	class	character	2	Yes
Part	warehouse	character	1	Yes
Part	price	number	6.2	Yes

Answer 1

Views are created by using the syntax given below:

CREATE VIEW view_name AS statement;

The following select statement you use to query your results.

SELECT o.Order_Num, o.order_date, p.part_num, p.Description, p.class

FROM Part p

JOIN Order_Line ol ON p.part_num=ol.part_num

JOIN Orders o ON o.Order_num=ol.Order_Num

The following sql statement creates view.

CREATE VIEW orderview

AS

SELECT o.Order_Num, o.order_date, p.part_num, p.Description, p.class

FROM Part p

JOIN Order_Line ol ON p.part_num=ol.part_num

JOIN Orders o ON o.Order_num=ol.Order_Num