SQL SESSION 5

SESSION-05

TABLE:-SALESMAN_ORDER_DETAIL

Column Name	Data Type	Size	Attribute Constraints
S_order_no	Varchar2	6	Primary Key/Foreign Key references          s_order_no Of Sales_Order table
product_no	Varchar2	6	Primary Key/Foreign Key references          product_no Of Product_Master table
Qty_ordered	Number	8
Qty_desp	Number	8
Product_rate	Number	10,2

Write the SQL commands for,

a) creating the above ‘Sales_Order_Details’ table.

b) inserting the values- O19001,P00001,4,4,525

                                     O19001,P07965,2,1,8400

                                     O19002,P00001,10,0,525

                                     O19003,P06734,1,1,12000

c) Displaying occurrences with below requirements,

                               i) sales order number more than CH19000 and

                              ii) product rate more than 8000.

    

Solution:

a) Creating the above ‘Sales_Order_Details’ table.

Step 1: Create Sales_Order Table

create table Sales_Order(

S_Order_No Varchar(6) Not null Primary key,

Name Varchar(15));

Step2: Create Product_Master Table

create table Product_Master(

Product_No Varchar(6) Not null primary key,

Product_Name Varchar(15));

Step3: Create Salesman_Order_Detail Table

create table Salesman_Order_Detail(

S_Order_No Varchar(6) references Sales_Order,

Product_No Varchar(6) references Product_Master,

Qty_Ordered Number,

Qty_Desp Number,

Product_Rate Number);

b) Inserting the values -      O19001, P00001, 4, 4, 525

                                                O19001, P07965, 2, 1, 8400

                                                O19002, P00001, 10, 0, 525

                                                O19003, P06734, 1, 1, 12000

Step1:  

INSERT INTO Product_Master(

Product_No, Product_Name)

VALUES (‘P00001’,’Sandals’);

Step2:

INSERT INTO Product_Master(

Product_No, Product_Name)

VALUES ('P07965','Hawaii');

Step3:

INSERT INTO Product_Master(

Product_No, Product_Name)

VALUES ('P06734','Dishwasher');

Step4:

INSERT INTO Sales_Order(

S_Order_No, Name)

VALUES (‘O19001’,’One’);

Step5:

INSERT INTO Sales_Order(

S_Order_No, Name)

VALUES (‘O19002’,’Two’);

Step6:

INSERT INTO Sales_Order(

S_Order_No, Name)

VALUES (‘O19003’,’Three’);

Step7:

INSERT INTO Salesman_Order_Detail(

S_Order_No, Product_No, Qty_Ordered, Qty_Desp, Product_Rate)

VALUES (‘O19001’, ‘ P00001’, 4, 4, 525);

Step8:

INSERT INTO Salesman_Order_Detail(

S_Order_No, Product_No, Qty_Ordered, Qty_Desp, Product_Rate)

VALUES (‘O19001’, ‘P07965’, 2, 1, 8400);

Step9:

INSERT INTO Salesman_Order_Detail(

S_Order_No, Product_No, Qty_Ordered, Qty_Desp, Product_Rate)

VALUES (‘O19002’, ‘P00001’, 10, 0, 525);

Step10:

INSERT INTO Salesman_Order_Detail(

S_Order_No, Product_No, Qty_Ordered, Qty_Desp, Product_Rate)

VALUES (‘O19003’, ‘P06734’, 1, 1, 12000);

c) Displaying occurrences with below requirements,

i) Sales order number more than CH19000

SELECT *

FROM Salesman_Order_Detail

WHERE S_Order_No>'CH19001';

ii) Product rate more than 8000.

                                SELECT *

                                FROM Salesman_Order_Detail

                                WHERE Product_Rate>8000;