EX.
NO:4 SALES ORDER DETAILS
DATE:
AIM:
TO Create a table sales_order with s_order_no and product_no as primary key. Set other fields to store
client number, delivery address, delivery date,order status.
a. Add a new column for storing salesman number using ALTER Command
b. Set the s_order_no as foregin key as column constraints.
c. Set the s_order_no as foreign key as table constraints.
d. Enforce the integrity rules using CHECK.
PROGRAM :
//CREATE TABLE
CREATE TABLE Sales_Order_Details ( son int NOT NULL PRIMARY KEY , product_no
number, description varchar(255), qorder number, qty_disp number, product_rate number,
profit_percent number, sell_price number, supplier_name varchar(25));
//INSERT DATA
insert into Sales_Order_Details values(1, 22, 'dove', 50, 60, 19,56,78,'muni')
insert into Sales_Order_Details values(3, 12, 'meera', 60, 70, 89, 86, 90,'hari')
insert into Sales_Order_Details values(4, 22, 'karthika', 50, 60, 87, 86, 88,'jeeva')
//VIEWING TABLE
select * from Sales_Order_Details;
// SELECT EACH ROW AND COMPUTE SELL_PRICE*.50 AND SELL_PRICE*1.50 FOR
EACH ROW SELECTED.
select sell_price*.50,sell_price*1.50 from Sales_Order_Details;
// SELECT PRODUCT_NO, PROFIT_PERCENT, SELL_PRICE WHERE PROFIT_PER
IS NOT BETWEEN 10 AND 20 BOTH INCLUSIVE
SELECT product_no, profit_percent, sell_price FROM Sales_Order_Details where profit_percent
between 10 and 20
// SELECT PRODUCT_NO, DESCRIPTION, PROFIT_PERCENT, SELL_PRICE WHERE
PROFIT_PERCENT IS NOT BETWEEN 20 AND 30.
SELECT product_no, profit_percent, description, sell_price FROM Sales_Order_Details where
profit_percent between 20 and 30
// SELECT THE SUPPLIERNAME AND PRODUCT_NO WHERE SUPPLIERNAME HAS ‘R’
OR ‘H’AS SECOND CHARACTER.
SELECT product_no, profit_percent, description, sell_price FROM Sales_Order_Details
WHERE supplier_name LIKE '_r_h%'
OUTPUT:
VIEW TABLE:
// SELECT EACH ROW AND COMPUTE SELL_PRICE*.50 AND SELL_PRICE*1.50 FOR
EACH ROW SELECTED.
SELECT PRODUCT_NO, PROFIT_PERCENT, SELL_PRICE WHERE PROFIT_PER IS
NOT BETWEEN 10 AND 20 BOTH INCLUSIVE
SELECT PRODUCT_NO, DESCRIPTION, PROFIT_PERCENT, SELL_PRICE WHERE
PROFIT_PERCENT IS NOT BETWEEN 20 AND 30.
// SELECT THE SUPPLIERNAME AND PRODUCT_NO WHERE SUPPLIERNAME HAS ‘R’
OR ‘H’AS SECOND CHARACTER.
RESULT:
THUS THE PROGRAM EXECUTED SUCCESSFULLY.
