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;




No comments: