Assignment 1 Day 1
1.Create the tables described below: a)Table name: Client_master.
Column Name Data Type Size
clientno varchar2 6 name varchar2 20 address1 varchar2 30 address2 varchar2 30 city varchar2 15 pincode number 8 state varchar 15 baldue number 10,2 Source code:
SQL> create table Client_master 2 ( 3 clientno varchar2(6), 4 name varchar2(20), 5 address1 varchar2(30), 6 address2 varchar2(30), 7 city varchar2(15), 8 pincode number(8), 9 state varchar2(15), 10 baldue number(10,2) 11 ); Table created SQL> desc Client_master; Name Null? Type
--- --- CLIENTNO VARCHAR2(6) NAME VARCHAR2(20) ADDRESS1 VARCHAR2(30) ADDRESS2 VARCHAR2(30) CITY VARCHAR2(15) PINCODE NUMBER(8) STATE VARCHAR2(15) BALDUE NUMBER(10,2)
b)Table name: Product_master.
Column Name Data Type Size
productno varchar2 6 description varchar2 15 profitpercent number 4,2 unitmeasure varchar2 10 qtyonhand number 8 reorderLvl number 8 sellprice number 8,2 costprice number 8,2 Source code:
SQL> create table Product_master 2 (
3 productno varchar2(6), 4 description varchar2(15),
5 profitpercent number(4,2), 6 unitmeasure varchar2(10), 7 qtyonhand number(8), 8 reorderLvl number(8), 9 sellprice number(8,2), 10 costprice number(8,2) 11 ); Table created. SQL> desc Product_master; Name Null? Type
--- --- PRODUCTNO VARCHAR2(6) DESCRIPTION VARCHAR2(15) PROFITPERCENT NUMBER(4,2) UNITMEASURE VARCHAR2(10) QTYONHAND NUMBER(8) REORDERLVL NUMBER(8) SELLPRICE NUMBER(8,2) COSTPRICE NUMBER(8,2)
2.Insert the following data into their respective tables: a)Data for Client_master table:
ClientNo Name City Pincode State BalDue
C001 Ivan Bayross Bombay 400054 Maharashtra 15000 C002 Vandana Saitwal Madras 780001 Tamil Nadu 0 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Delhi 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 Source code:
SQL> insert into Client_master values
2 ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', 3 &baldue);
Enter value for clientno: C001 Enter value for name: Ivan Bayross Enter value for address1:
Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400054 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C001','Ivan Bayross',' ',' ','Bombay',400054,'Maharashtra',
Enter value for baldue: 15000 old 3: &baldue)
new 3: 15000) 1 row created. SQL> /
Enter value for clientno: C002
Enter value for name: Vandana Saitwal Enter value for address1:
Enter value for address2: Enter value for city: Madras
Enter value for pincode: 780001 Enter value for state: Tamil Nadu
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C002','Vandana Saitwal',' ',' ','Madras',780001,'Tamil Nadu',
Enter value for baldue: 0 old 3: &baldue)
new 3: 0) 1 row created. SQL> /
Enter value for clientno: C003
Enter value for name: Pramada Jaguste Enter value for address1:
Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400057 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C003','Pramada Jaguste','','','Bombay',400057,'Maharashtra',
Enter value for baldue: 5000 old 3: &baldue)
new 3: 5000) 1 row created. SQL> /
Enter value for clientno: C004 Enter value for name: Basu Navindgi Enter value for address1:
Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400056 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C004','Basu Navindgi','','','Bombay',400056,'Maharashtra',
Enter value for baldue: 0 old 3: &baldue)
new 3: 0) 1 row created. SQL> /
Enter value for clientno: C005
Enter value for name: Ravi Sreedharan Enter value for address1:
Enter value for address2: Enter value for city: Delhi Enter value for pincode: 100001 Enter value for state: Delhi
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C005','Ravi Sreedharan','','','Delhi',100001,'Delhi',
Enter value for baldue: 2000 old 3: &baldue)
1 row created. SQL> /
Enter value for clientno: C006 Enter value for name: Rukmini Enter value for address1: Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400050 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C006','Rukmini','','','Bombay',400050,'Maharashtra',
Enter value for baldue: 0 old 3: &baldue)
new 3: 0) 1 row created. SQL> commit; Commit complete
SQL> select * from client_master; CLIENT NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BALDUE
--- --- --- --- - --- -
---C001 Ivan Bayross
Bombay 400054 Maharashtra 15000 C002 Vandana Saitwal
Madras 780001 Tamil Nadu 0 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Delhi 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 6 rows selected.
b)Data for Product_master table:
Product No Description Profit Percent Unit
Hand Reorder Lvl Sellprice Costprice P00001 1.44 Floppies 5 Piece 100 20 525 500 P03453 Monitors 6 Piece 10 3 1200 11280 P06734 Mouse 5 Piece 20 5 1050 1000 P07865 1.22 Floppies 5 Piece 100 20 525 500 P07868 Keyboard 2 Piece 10 3 3150 3050 P07885 CD Drive 2.5 Piece 10 3 5250 5100 P07965 540 HDD 4 Piece 10 3 8400 8000 P07975 1.44 Drive 5 Piece 10 3 1050 1000 P08865 1.22 Drive 5 Piece 2 3 1050 1000 Source code:
SQL> insert into Product_master values
2 ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, 3 &reorderLvl,&sellprice,&costprice);
Enter value for productno: P00001
Enter value for description: 1.44 Floppies Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 100
old 2: ('&product','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P00001','1.44 Floppies',5,'Piece',100,
Enter value for reorderlvl: 20 Enter value for sellprice: 525 Enter value for costprice: 500
old 3: &reorderLvl,&sellprice,&costprice) new 3: 20,525,500)
1 row created. SQL> /
Enter value for productno: P03453 Enter value for description: Monitors Enter value for profitpercent: 6 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&product','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P03453','Monitors',6,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 12000 Enter value for costprice: 11280
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,12000,11280)
1 row created. SQL> /
Enter value for productno: P06734 Enter value for description: Mouse Enter value for profitpercent: 5 Enter value for unitmeasure: Piece Enter value for qtyonhand: 20
old 2: ('&product','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P06734','Mouse',5,'Piece',20,
Enter value for reorderlvl: 5 Enter value for sellprice: 1050 Enter value for costprice: 1000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 5,1050,1000)
1 row created.
SQL> /
Enter value for productno: P07865
Enter value for description: 1.22 Floppies Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 100
old 2: ('&product','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07865','1.22 Floppies',5,'Piece',100,
Enter value for reorderlvl: 20 Enter value for sellprice: 525 Enter value for costprice: 500
old 3: &reorderLvl,&sellprice,&costprice) new 3: 20,525,500)
1 row created. SQL> /
Enter value for productno: P07868 Enter value for description: Keyboard Enter value for profitpercent: 2 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07868','Keyboard',2,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 3150 Enter value for costprice: 3050
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,3150,3050)
1 row created. SQL> /
Enter value for productno: P07885 Enter value for description: CD Drive Enter value for profitpercent: 2.5 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07885','CD Drive',2.5,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 5250 Enter value for costprice: 5100
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,5250,5100)
1 row created. SQL> /
Enter value for productno: P07965 Enter value for description: 540 HDD Enter value for profitpercent: 4 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
new 2: ('P07965','540 HDD',4,'Piece',10, Enter value for reorderlvl: 3
Enter value for sellprice: 8400 Enter value for costprice: 8000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,8400,8000)
1 row created. SQL> /
Enter value for productno: P07975
Enter value for description: 1.44 Drive Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07975','1.44 Drive',5,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 1050 Enter value for costprice: 1000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,1050,1000)
1 row created. SQL> /
Enter value for productno: P08865
Enter value for description: 1.22 Drive Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 2
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P08865','1.22 Drive',5,'Piece',2,
Enter value for reorderlvl: 3 Enter value for sellprice: 1050 Enter value for costprice: 1000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,1050,1000)
1 row created. SQL> commit; Commit complete.
SQL> select * from Product_master;
PRODUC DESCRIPTION PROFITPERCENT UNITMEASUR QTYONHAND REORDERLVL SELLPRICE COSTPRICE --- --- --- ---P00001 1.44 Floppies 5 Piece 100 20 525 500 P03453 Monitors 6 Piece 10 3 12000 11280 P06734 Mouse 5 Piece 20 5 1050 1000 P07865 1.22 Floppies 5 Piece 100 20 525 500 P07868 Keyboard 2 Piece 10 3 3150 3050
P07885 CD Drive 2.5 Piece 10 3 5250 5100 P07965 540 HDD 4 Piece 10 3 8400 8000 P07975 1.44 Drive 5 Piece 10 3 1050 1000 P08865 1.22 Drive 5 Piece 2 3 1050 1000 9 rows selected. Day 2
Create the table described below: 1.c)Table name: Salesman_master. Column Name Data Type Size
Salesmanno varchar2 6 Salesmanname varchar2 20 Address1 varchar2 30 Address2 varchar2 30 City varchar2 20 PinCode number 8 State varchar2 20 Sal_Amt number 8,2 TgtToGet number 6,2 YtdSales number 6,2 Remarks varchar2 60 Source code:
SQL> create table Salesman_master 2 ( 3 Salesmanno varchar2(6), 4 Salesmanname varchar2(20), 5 Address1 varchar2(30), 6 Address2 varchar2(30), 7 City varchar2(20), 8 PinCode number(8), 9 State varchar2(20), 10 Sal_Amt number(8,2), 11 TgtToGet number(6,2), 12 YtdSales number(6,2), 13 Remarks varchar2(60) 14 ); Table created. SQL> desc Salesman_master; Name Null? Type
--- --- SALESMANNO VARCHAR2(6) SALESMANNAME VARCHAR2(20) ADDRESS1 VARCHAR2(30) ADDRESS2 VARCHAR2(30) CITY VARCHAR2(20) PINCODE NUMBER(8)
STATE VARCHAR2(20) SAL_AMT NUMBER(8,2) TGTTOGET NUMBER(6,2) YTDSALES NUMBER(6,2) REMARKS VARCHAR2(60)
2.c)Data for Salesman_master table: Salesman
No Salesman Name Add ress1 Add
ress2 City Pin Code State Sal
Amt Tgt
To
Get Ytd
Sales Rem arks
S001 Kiran A/14 Worli Bombay 400002 Maha rash
tra 3000 100 50 Good
S002 Manish 65 Nariman Bombay 400001 Maha rash
tra 3000 200 100 Good
S003 Ravi P 7 Bandra Bombay 400032 Maha rash
tra 3000 200 100 Good
S004 Ashish A/5 Juhu Bombay 400044 Maha rash
tra 3000 200 150 Good
Source code:
SQL> insert into Salesman_master values
2 ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode, 3 '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks');
Enter value for salesmanno: S001 Enter value for salesmanname: Kiran Enter value for address1: A/14 Enter value for address2: Worli Enter value for city: Bombay Enter value for pincode: 400002
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S001','Kiran','A/14','Worli','Bombay',400002, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 100 Enter value for ytdsales: 50 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,100,50,'Good')
1 row created. SQL> /
Enter value for salesmanno: S002 Enter value for salesmanname: Manish Enter value for address1: 65
Enter value for address2: Nariman Enter value for city: Bombay
Enter value for pincode: 400001
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S002','Manish','65','Nariman','Bombay',400001, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 200 Enter value for ytdsales: 100 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,200,100,'Good')
1 row created. SQL> /
Enter value for salesmanno: S003 Enter value for salesmanname: Ravi Enter value for address1: P - 7 Enter value for address2: Bandra Enter value for city: Bombay Enter value for pincode: 400032
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S003','Ravi','P - 7','Bandra','Bombay',400032, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 200 Enter value for ytdsales: 100 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,200,100,'Good')
1 row created. SQL> /
Enter value for salesmanno: S004 Enter value for salesmanname: Ashish Enter value for address1: A/5
Enter value for address2: Juhu Enter value for city: Bombay Enter value for pincode: 400044
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S004','Ashish','A/5','Juhu','Bombay',400044, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 200 Enter value for ytdsales: 150 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,200,150,'Good')
1 row created. SQL> Commit; Commit complete.
SALESM SALESMANNAME ADDRESS1 ADDRESS2 CITY PINCODE STATE SAL_AMT TGTT OGET YTDSALES REMARKS
--- --- --- -- - -- - --- --- --- ---S001 Kiran A/14 Worli
Bombay 400002 Maharashtra 3000 100 50 Good S002 Manish 65 Nariman Bombay 400001 Maharashtra 3000 200 100 Good S003 Ravi P - 7 Bandra Bombay 400032 Maharashtra 3000 200 100 Good
S004 Ashish A/5 Juhu
Bombay 400044 Maharashtra 3000 200 150 Good
3.Exercise on retrieving records from a table: i)Find the names of all clients.
Ans) SQL> select name from Client_master; NAME ---Ivan Bayross Vandana Saitwal Pramada Jaguste Basu Navindgi Ravi Sreedharan Rukmini 6 rows selected.
ii)Retrieve the entire contents of Client_master table. Ans) SQL> select * from Client_master;
CLIENT NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BALDUE
--- --- --- --- - --- -
---C001 Ivan Bayross
Bombay 400054 Maharashtra 15000 C002 Vandana Saitwal
Madras 780001 Tamil Nadu 0 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Delhi 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 6 rows selected.
iii)Retrieve the list of names,city and state of all clients. Ans) SQL> select name,city,state from Client_master;
NAME CITY STATE
--- ---Ivan Bayross Bombay Maharashtra
Vandana Saitwal Madras Tamil Nadu Pramada Jaguste Bombay Maharashtra Basu Navindgi Bombay Maharashtra Ravi Sreedharan Delhi Delhi Rukmini Bombay Maharashtra 6 rows selected.
iv)List the various products available from the product_master table. Ans) SQL> select description from Product_master;
DESCRIPTION ---1.44 Floppies Monitors Mouse 1.22 Floppies Keyboard CD Drive 540 HDD 1.44 Drive 1.22 Drive 9 rows selected.
v)List all the clients who are located in Mumbai
Ans) SQL> select clientno,name from Client_master where city='Bombay'; CLIENT NAME --- ---C001 Ivan Bayross C003 Pramada Jaguste C004 Basu Navindgi C006 Rukmini
vi)Find the names of salesman who have a salary equl to RS.3000.
Ans) SQL> select Salesmanname from Salesman_master where Sal_Amt=3000;
SALESMANNAME ---Kiran Manish Ravi Ashish
vii)Change the city of ClientNo C005 to Bangalore.
Ans) SQL> update Client_master set city='Bangalore' where clientno='C005'; 1 row updated.
CLIENT NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BALDUE
--- --- --- --- - --- -
---C001 Ivan Bayross
Bombay 400054 Maharashtra 15000 C002 Vandana Saitwal
Madras 780001 Tamil Nadu 0 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Bangalore 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 6 rows selected.
viii)Change the BalDue of ClientNo C001 to Rs. 1000.
Ans) SQL> update Client_master set baldue=1000 where clientno='C001'; 1 row updated.
SQL> select * from Client_master;
CLIENT NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BALDUE
--- --- --- --- - --- -
---C001 Ivan Bayross
Bombay 400054 Maharashtra 1000 C002 Vandana Saitwal
Madras 780001 Tamil Nadu 0 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Bangalore 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 6 rows selected.
ix)Change the cost price of 1.44 Drive to Rs.950.00.
Ans) SQL> update Product_master set costprice=950.00 where description='1.44 Dri ve';
1 row updated.
SQL> select * from Product_master;
PRODUC DESCRIPTION PROFITPERCENT UNITMEASUR QTYONHAND REORDERLVL SELLPRICE COSTPRICE
--- --- --- ---P00001 1.44 Floppies 5 Piece 100 20 525 500 P03453 Monitors 6 Piece 10 3 12000 11280 P06734 Mouse 5 Piece 20 5 1050 1000 P07865 1.22 Floppies 5 Piece 100 20 525 500 P07868 Keyboard 2 Piece 10 3 3150 3050 P07885 CD Drive 2.5 Piece 10 3 5250 5100 P07965 540 HDD 4 Piece 10 3 8400 8000 P07975 1.44 Drive 5 Piece 10 3 1050 950 P08865 1.22 Drive 5 Piece 2 3 1050 1000 9 rows selected.
x)Change the city of the Salesman to Pune.
Ans) SQL> update Salesman_master set City='Pune'; 4 rows updated.
SQL> select * from Salesman_master; SALESM SALESMANNAME ADDRESS1 ADDRESS2 CITY PINCODE STATE SAL_AMT TGTT OGET YTDSALES REMARKS
--- --- --- -- - -- - --- --- --- ---S001 Kiran A/14 Worli
Pune 400002 Maharashtra 3000 100 50 Good S002 Manish 65 Nariman Pune 400001 Maharashtra 3000 200 100 Good S003 Ravi P - 7 Bandra Pune 400032 Maharashtra 3000 200 100 Good
S004 Ashish A/5 Juhu
Pune 400044 Maharashtra 3000 200 150 Good
xi)Delete all salesman from the Salesman_master whose salaries are equl to Rs. 3 500.
Ans) SQL> delete from Salesman_master where Sal_Amt=3500; 0 rows deleted.
SALESM SALESMANNAME ADDRESS1 ADDRESS2 CITY PINCODE STATE SAL_AMT TGTT OGET YTDSALES REMARKS
--- --- --- -- - -- - --- --- --- ---S001 Kiran A/14 Worli
Pune 400002 Maharashtra 3000 100 50 Good S002 Manish 65 Nariman Pune 400001 Maharashtra 3000 200 100 Good S003 Ravi P - 7 Bandra Pune 400032 Maharashtra 3000 200 100 Good
S004 Ashish A/5 Juhu
Pune 400044 Maharashtra 3000 200 150 Good
xii)Delete all products from Product_master where the quantity on hand is equl t o 100.
Ans) SQL> delete from Product_master where qtyonhand=100; 2 rows deleted.
SQL> select * from Product_master;
PRODUC DESCRIPTION PROFITPERCENT UNITMEASUR QTYONHAND REORDERLVL SELLPRICE COSTPRICE --- --- --- ---P03453 Monitors 6 Piece 10 3 12000 11280 P06734 Mouse 5 Piece 20 5 1050 1000 P07868 Keyboard 2 Piece 10 3 3150 3050 P07885 CD Drive 2.5 Piece 10 3 5250 5100 P07965 540 HDD 4 Piece 10 3 8400 8000 P07975 1.44 Drive 5 Piece 10 3 1050 950 P08865 1.22 Drive 5 Piece 2 3 1050 1000 7 rows selected.
xiii)Delete from Client_master where the column state holds the value Tamil Nadu . Ans) SQL> delete from Client_master where state='Tamil Nadu';
1 row deleted.
SQL> select * from Client_master; CLIENT NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BALDUE
--- - --- - ---C001 Ivan Bayross Bombay 400054 Maharashtra 1000 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Bangalore 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 5 rows selected.
xiv)Add a column called Telephone of data type number and size= 10 to the Client_mast er table.
Ans) SQL> alter table Client_master add(telephone number(10)); Table altered.
SQL> desc client_master; Name Null? Type
--- --- CLIENTNO VARCHAR2(6) NAME VARCHAR2(20) ADDRESS1 VARCHAR2(30) ADDRESS2 VARCHAR2(30) CITY VARCHAR2(15) PINCODE NUMBER(8) STATE VARCHAR2(15) BALDUE NUMBER(10,2) TELEPHONE NUMBER(10)
xv)Destroy the Client_master table along with its data. Ans) SQL> drop table Client_master;
Table dropped.
SQL> desc client_master; ERROR:
ORA-04043: object client_master does not exist.
xvi)Change the name of the Salesman_master to sman_master. Ans) SQL> rename Salesman_master to sman_mast;
Table renamed.
SQL> select * from sman_mast; SALESM SALESMANNAME ADDRESS1
--- --- ---ADDRESS2 CITY PINCODE
---STATE SAL_AMT TGTTOGET YTDSALES
---REMARKS
---S001 Kiran A/14
Maharashtra 3000 100 50 Good
SALESM SALESMANNAME ADDRESS1
--- --- ---ADDRESS2 CITY PINCODE
---STATE SAL_AMT TGTTOGET YTDSALES
---REMARKS ---S002 Manish 65 Nariman Pune 400001 Maharashtra 3000 200 100 Good
SALESM SALESMANNAME ADDRESS1
--- --- ---ADDRESS2 CITY PINCODE
---STATE SAL_AMT TGTTOGET YTDSALES
---REMARKS ---S003 Ravi P - 7 Bandra Pune 400032 Maharashtra 3000 200 100 Good
SALESM SALESMANNAME ADDRESS1
--- --- ---ADDRESS2 CITY PINCODE
---STATE SAL_AMT TGTTOGET YTDSALES
---REMARKS ---S004 Ashish A/5 Juhu Pune 400044 Maharashtra 3000 200 150 Good Day 3
Create the tables described below: 4.a)Table Name: Client_master.
Column Name Data Type Size Attributes
clientno varchar2 6 Primary key, first letter must start with C
name varchar2 20 Not null
address1 varchar2 30 address2 varchar2 30 city varchar2 15 pincode number 8 state varchar2 15 baldue number 10,2 Source code:
SQL> create table Client_master 2 (
3 clientno varchar2(6) primary key, 4 name varchar2(20) not null,
5 address1 varchar2(30), 6 address2 varchar2(30), 7 city varchar2(15), 8 pincode number(8), 9 state varchar2(15), 10 baldue number(10,2),
11 constraint arg_client check(clientno like 'C%') 12 );
Table created.
SQL> desc Client_master; Name Null? Type
--- --- CLIENTNO NOT NULL VARCHAR2(6)
NAME NOT NULL VARCHAR2(20) ADDRESS1 VARCHAR2(30) ADDRESS2 VARCHAR2(30) CITY VARCHAR2(15) PINCODE NUMBER(8) STATE VARCHAR2(15) BALDUE NUMBER(10,2)
b)Table Name: Product_master
Column Name Data Type Size Attributes
productno varchar2 6 Primary key, first letter must start with P .
description varchar2 15 Not null
profitpercent number 4,2 Not null
unitmeasure varchar2 10 Not null
qtyonhand number 8 Not null reorderLvl number 8 Not null
sellprice number 8,2 Not null, cannot be 0 costprice number 8,2 Not null, cannot be 0
Source code:
SQL> create table Product_master 2 (
3 productno varchar2(6) primary key, 4 description varchar2(15) not null, 5 profitpercent number(4,2) not null, 6 unitmeasure varchar2(10) not null, 7 qtyonhand number(8) not null, 8 reorderLvl number(8) not null, 9 sellprice number(8,2) not null, 10 costprice number(8,2) not null,
11 constraint arg_product check(productno like 'P%'), 12 constraint arg_sell check(sellprice<>0),
13 constraint arg_cost check(costprice<>0) 14 );
Table created.
SQL> desc Product_master; Name Null? Type
--- --- PRODUCTNO NOT NULL VARCHAR2(6)
DESCRIPTION NOT NULL VARCHAR2(15) PROFITPERCENT NOT NULL NUMBER(4,2) UNITMEASURE NOT NULL VARCHAR2(10) QTYONHAND NOT NULL NUMBER(8) REORDERLVL NOT NULL NUMBER(8) SELLPRICE NOT NULL NUMBER(8,2) COSTPRICE NOT NULL NUMBER(8,2)
c)Table name: Salesman_master.
Column Name Data Type Size Attributes
Salesmanno varchar2 6 Primary key/ first letter must start with S
Salesmanname varchar2 20 Not null
Address1 varchar2 30 Not null
Address2 varchar2 30
City varchar2 20
PinCode number 8
State varchar2 20
Sal_Amt number 8,2 Not null, cannot be 0
TgtToGet number 6,2 Not null, cannot be 0 YtdSales number 6,2 Not null
Remarks varchar2 60 Source code:
SQL> create table Salesman_master 2 (
3 Salesmanno varchar2(6) primary key, 4 Salesmanname varchar2(20) not null, 5 Address1 varchar2(30) not null, 6 Address2 varchar2(30),
7 City varchar2(20), 8 PinCode number(8), 9 State varchar2(20),
10 Sal_Amt number(8,2) not null, 11 TgtToGet number(6,2) not null, 12 YtdSales number(6,2) not null, 13 Remarks varchar2(60),
14 constraint arg_sales check(Salesmanno like 'S%'), 15 constraint arg_sal check(Sal_Amt<>0),
16 constraint arg_tgt check(TgtToGet<>0) 17 );
SQL> desc Salesman_master;
Name Null? Type
--- --- SALESMANNO NOT NULL VARCHAR2(6)
SALESMANNAME NOT NULL VARCHAR2(20) ADDRESS1 NOT NULL VARCHAR2(30) ADDRESS2 VARCHAR2(30)
CITY VARCHAR2(20) PINCODE NUMBER(8) STATE VARCHAR2(20)
SAL_AMT NOT NULL NUMBER(8,2) TGTTOGET NOT NULL NUMBER(6,2) YTDSALES NOT NULL NUMBER(6,2) REMARKS VARCHAR2(60)
d)Table name: Sales_order.
Column Name Data type Size Arrtibutes
Orderno varchar2 6 Primary key, first letter must start with O Orderdate date
Clientno varchar2 6 Foreign key references client_no of client_master table
DelyAddr varchar2 25
SalesmanNo varchar2 6 Foreign key references Salesman_no of sa lesman_master table
Delytype char 1 Delivery part(P), full(F) default F
Billedyn char 1
Delydate date Cannot be less than order date
Orderstatus varchar2 10 Values( In Process , Fullfilled , BackOrder , Cance lled )
Source code:
SQL> create table Sales_order 2 (
3 Orderno varchar2(6) primary key, 4 Orderdate date,
5 Clientno varchar2(6) references Client_master(clientno), 6 DelyAddr varchar2(25),
7 SalesmanNo varchar2(6) references Salesman_master(Salesmanno), 8 Delytype char(1) default 'F',
9 Billedyn char(1), 10 Delydate date,
12 constraint arg_saleordr check(Orderno like 'O%'), 13 constraint arg_saledely check(Delytype in('P','F')), 14 constraint arg_saledate check(Delydate>=Orderdate),
15 constraint arg_saleordrsts check(Orderstatus in('In Process','Fullfilled',' BackOrder','cancelled'))
16 );
Table created.
SQL> desc Sales_order; Name Null? Type
--- --- ORDERNO NOT NULL VARCHAR2(6)
ORDERDATE DATE CLIENTNO VARCHAR2(6) DELYADDR VARCHAR2(25) SALESMANNO VARCHAR2(6) DELYTYPE CHAR(1) BILLEDYN CHAR(1) DELYDATE DATE ORDERSTATUS VARCHAR2(10)
e)Table name: Sales_order_details.
Column Name Data type Size Attributes
Orderno varchar2 6 Primary key/Foreign key references order_no of t he sales_order table.
Productno varchar2 6 Primary key/Foreign key references produ ct_no of the product_master table.
Qtyordered number 8 Qtydisp number 8
Productrate number 10,2 Source code:
SQL> create table Sales_order_details 2 (
3 Orderno varchar2(6) references Sales_order(Orderno),
4 Productno varchar2(6) references Product_master(productno), 5 Qtyordered number(8), 6 Qtydisp number(8), 7 Productrate number(10,2), 8 Primary key(Orderno,Productno) 8 ); Table created. SQL> desc Sales_order_details; Name Null? Type
--- --- ORDERNO NOT NULL VARCHAR2(6)
PRODUCTNO NOT NULL VARCHAR2(6) QTYORDERED NUMBER(8)
QTYDISP NUMBER(8)
PRODUCTRATE NUMBER(10,2)
Day 4
Insert the following data into their respective tables: 5 a)Data for Client_master table:
ClientNo Name City Pincode State BalDue
C001 Ivan Bayross Bombay 400054 Maharashtra 15000 C002 Vandana Saitwal Madras 780001 Tamil Nadu 0 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Delhi 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 Source code:
SQL> insert into Client_master values
2 ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', 3 &baldue);
Enter value for clientno: C001 Enter value for name: Ivan Bayross Enter value for address1:
Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400054 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C001','Ivan Bayross',' ',' ','Bombay',400054,'Maharashtra',
Enter value for baldue: 15000 old 3: &baldue)
new 3: 15000) 1 row created. SQL> /
Enter value for clientno: C002
Enter value for name: Vandana Saitwal Enter value for address1:
Enter value for address2: Enter value for city: Madras Enter value for pincode: 780001 Enter value for state: Tamil Nadu
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C002','Vandana Saitwal',' ',' ','Madras',780001,'Tamil Nadu',
Enter value for baldue: 0 old 3: &baldue)
new 3: 0) 1 row created.
SQL> /
Enter value for clientno: C003
Enter value for name: Pramada Jaguste Enter value for address1:
Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400057 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C003','Pramada Jaguste',' ',' ','Bombay',400057,'Maharashtra',
Enter value for baldue: 5000 old 3: &baldue)
new 3: 5000) 1 row created. SQL> /
Enter value for clientno: C004 Enter value for name: Basu Navindgi Enter value for address1:
Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400056 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C004','Basu Navindgi',' ',' ','Bombay',400056,'Maharashtra',
Enter value for baldue: 0 old 3: &baldue)
new 3: 0) 1 row created. SQL> /
Enter value for clientno: C005
Enter value for name: Ravi Sreedharan Enter value for address1:
Enter value for address2: Enter value for city: Delhi Enter value for pincode: 100001 Enter value for state: Delhi
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C005','Ravi Sreedharan',' ',' ','Delhi',100001,'Delhi',
Enter value for baldue: 2000 old 3: &baldue)
new 3: 2000) 1 row created. SQL> /
Enter value for clientno: C006 Enter value for name: Rukmini Enter value for address1: Enter value for address2: Enter value for city: Bombay Enter value for pincode: 400050 Enter value for state: Maharashtra
old 2: ('&clientno','&name','&address1','&address2','&city',&pincode,'&state', new 2: ('C006','Rukmini',' ',' ','Bombay',400050,'Maharashtra',
Enter value for baldue: 0 old 3: &baldue)
new 3: 0) 1 row created. SQL> commit; Commit complete.
SQL> select * from client_master; CLIENT NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BALDUE
--- --- --- --- - --- -
---C001 Ivan Bayross
Bombay 400054 Maharashtra 15000 C002 Vandana Saitwal
Madras 780001 Tamil Nadu 0 C003 Pramada Jaguste Bombay 400057 Maharashtra 5000 C004 Basu Navindgi Bombay 400056 Maharashtra 0 C005 Ravi Sreedharan Delhi 100001 Delhi 2000 C006 Rukmini Bombay 400050 Maharashtra 0 6 rows selected.
b)Data for Product_master table:
Product No Description Profit Percent Unit Measure QtyOn Hand Reorder Lvl Sellprice Costprice P00001 1.44 Floppies 5 Piece 100 20 525 500 P03453 Monitors 6 Piece 10 3 1200 11280 P06734 Mouse 5 Piece 20 5 1050 1000 P07865 1.22 Floppies 5 Piece 100 20 525 500 P07868 Keyboard 2 Piece 10 3 3150 3050 P07885 CD Drive 2.5 Piece 10 3 5250 5100 P07965 540 HDD 4 Piece 10 3 8400 8000 P07975 1.44 Drive 5 Piece 10 3 1050 1000 P08865 1.22 Drive 5 Piece 2 3 1050 1000
Source code:
SQL> insert into Product_master values
2 ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, 3 &reorderLvl,&sellprice,&costprice);
Enter value for productno: P00001
Enter value for description: 1.44 Floppies Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 100
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P00001','1.44 Floppies',5,'Piece',100,
Enter value for reorderlvl: 20 Enter value for sellprice: 525 Enter value for costprice: 500
old 3: &reorderLvl,&sellprice,&costprice) new 3: 20,525,500)
1 row created. SQL> /
Enter value for productno: P03453 Enter value for description: Monitors Enter value for profitpercent: 6 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P03453','Monitors',6,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 12000 Enter value for costprice: 11280
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,12000,11280)
1 row created. SQL> /
Enter value for productno: P06734 Enter value for description: Mouse Enter value for profitpercent: 5 Enter value for unitmeasure: Piece Enter value for qtyonhand: 20
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P06734','Mouse',5,'Piece',20,
Enter value for reorderlvl: 5 Enter value for sellprice: 1050 Enter value for costprice: 1000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 5,1050,1000)
1 row created. SQL> /
Enter value for productno: P07865
Enter value for description: 1.22 Floppies Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 100
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07865','1.22 Floppies',5,'Piece',100,
Enter value for reorderlvl: 20 Enter value for sellprice: 525 Enter value for costprice: 500
old 3: &reorderLvl,&sellprice,&costprice) new 3: 20,525,500)
1 row created. SQL> /
Enter value for productno: P07868 Enter value for description: Keyboard Enter value for profitpercent: 2 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07868','Keyboard',2,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 3150 Enter value for costprice: 3050
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,3150,3050)
1 row created. SQL> /
Enter value for productno: P07885 Enter value for description: CD Drive Enter value for profitpercent: 2.5 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07885','CD Drive',2.5,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 5250 Enter value for costprice: 5100
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,5250,5100)
1 row created. SQL> /
Enter value for productno: P07965 Enter value for description: 540 HDD Enter value for profitpercent: 4 Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07965','540 HDD',4,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 8400 Enter value for costprice: 8000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,8400,8000)
1 row created. SQL> /
Enter value for productno: P07975
Enter value for description: 1.44 Drive Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 10
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P07975','1.44 Drive',5,'Piece',10,
Enter value for reorderlvl: 3 Enter value for sellprice: 1050 Enter value for costprice: 1000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,1050,1000)
1 row created. SQL> /
Enter value for productno: P08865
Enter value for description: 1.22 Drive Enter value for profitpercent: 5
Enter value for unitmeasure: Piece Enter value for qtyonhand: 2
old 2: ('&productno','&description',&profitpercent,'&unitmeasure',&qtyonhand, new 2: ('P08865','1.22 Drive',5,'Piece',2,
Enter value for reorderlvl: 3 Enter value for sellprice: 1050 Enter value for costprice: 1000
old 3: &reorderLvl,&sellprice,&costprice) new 3: 3,1050,1000)
1 row created. SQL> commit; Commit complete.
SQL> select * from Product_master;
PRODUC DESCRIPTION PROFITPERCENT UNITMEASUR QTYONHAND REORDERLVL SELLPRICE COSTPRICE --- --- --- ---P00001 1.44 Floppies 5 Piece 100 20 525 500 P03453 Monitors 6 Piece 10 3 12000 11280 P06734 Mouse 5 Piece 20 5 1050 1000 P07865 1.22 Floppies 5 Piece 100 20 525 500 P07868 Keyboard 2 Piece 10 3 3150 3050 P07885 CD Drive 2.5 Piece 10 3 5250 5100 P07965 540 HDD 4 Piece 10 3 8400 8000 P07975 1.44 Drive 5 Piece 10 3 1050 1000 P08865 1.22 Drive 5 Piece 2 3 1050 1000 9 rows selected.
c)Data for Salesman_master table: Salesman
No Salesman Name Add ress1 Add
ress2 City Pin Code State Sal
Amt Tgt
To
Get Ytd
Sales Rem arks
S001 Kiran A/14 Worli Bombay 400002 Maha rash
tra 3000 100 50 Good
S002 Manish 65 Nariman Bombay 400001 Maha rash
tra 3000 200 100 Good
S003 Ravi P 7 Bandra Bombay 400032 Maha rash
tra 3000 200 100 Good
S004 Ashish A/5 Juhu Bombay 400044 Maha rash
tra 3000 200 150 Good
Source code:
SQL> insert into Salesman_master values
2 ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode, 3 '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks');
Enter value for salesmanno: S001 Enter value for salesmanname: Kiran Enter value for address1: A/14 Enter value for address2: Worli Enter value for city: Bombay Enter value for pincode: 400002
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S001','Kiran','A/14','Worli','Bombay',400002, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 100 Enter value for ytdsales: 50 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,100,50,'Good')
1 row created. SQL> /
Enter value for salesmanno: S002 Enter value for salesmanname: Manish Enter value for address1: 65
Enter value for address2: Nariman Enter value for city: Bombay
Enter value for pincode: 400001
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S002','Manish','65','Nariman','Bombay',400001, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 200 Enter value for ytdsales: 100 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,200,100,'Good')
1 row created. SQL> /
Enter value for salesmanno: S003 Enter value for salesmanname: Ravi Enter value for address1: P - 7 Enter value for address2: Bandra Enter value for city: Bombay Enter value for pincode: 400032
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S003','Ravi','P - 7','Bandra','Bombay',400032, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 200 Enter value for ytdsales: 100 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,200,100,'Good')
1 row created. SQL> /
Enter value for salesmanno: S004 Enter value for salesmanname: Ashish Enter value for address1: A/5
Enter value for address2: Juhu Enter value for city: Bombay Enter value for pincode: 400044
old 2: ('&Salesmanno','&Salesmanname','&Address1','&Address2','&City',&PinCode ,
new 2: ('S004','Ashish','A/5','Juhu','Bombay',400044, Enter value for state: Maharashtra
Enter value for sal_amt: 3000 Enter value for tgttoget: 200 Enter value for ytdsales: 150 Enter value for remarks: Good
old 3: '&State',&Sal_Amt,&TgtToGet,&YtdSales,'&Remarks') new 3: 'Maharashtra',3000,200,150,'Good')
1 row created. SQL> commit; Commit complete.
SALESM SALESMANNAME ADDRESS1 ADDRESS2 CITY PINCODE STATE SAL_AMT TGTT OGET YTDSALES REMARKS
--- --- --- -- - -- - --- --- --- ---S001 Kiran A/14 Worli
Bombay 400002 Maharashtra 3000 100 50 Good S002 Manish 65 Nariman Bombay 400001 Maharashtra 3000 200 100 Good S003 Ravi P - 7 Bandra Bombay 400032 Maharashtra 3000 200 100 Good
S004 Ashish A/5 Juhu
Bombay 400044 Maharashtra 3000 200 150 Good
d)Data for Sales_order table:
Orderno Orderdate Clientno Delytype Billyn Salesmanno Delydate Orderstatus
O19001 12-Jan-96 C00001 F N S00001 20-Jan-96 In Proce
ss
O19002 25-Jan-96 C00002 P N S00002 27-Jan-96 Cancelle
d
O46865 18-Feb-96 C00003 F Y S00003 20-Feb-96 Fulfille
d
O19003 03-Apr-96 C00001 F Y S00001 07-Apr-96 Fulfille
d
O46866 20-May-96 C00004 P N S00002 22-May-96 Cancelle
d
O19008 24-May-96 C00005 F N S00004 26-May-96 In Proce
ss
Source code:
SQL> insert into Sales_order values
2 ('&Orderno','&Orderdate','&Clientno','&DelyAddr','&SalesmanNo','&Delytype', 3 '&Billedyn','&Delydate','&Orderstatus');
Enter value for orderno: O19001 Enter value for orderdate: 12-jan-96 Enter value for clientno: C001
Enter value for delyaddr:
Enter value for salesmanno: S001 Enter value for delytype: F Enter value for billedyn: N
Enter value for delydate: 20-jan-96 Enter value for orderstatus: In Process
old 2: ('&Orderno','&Orderdate','&Clientno','&DelyAddr','&SalesmanNo','&Delyty pe','&Billedyn','&Delydate','&Orderstatus')
new 2: ('O19001','12-jan-96','C001',' ','S001','F','N','20-jan-96','In Process ')
1 row created. SQL> /
Enter value for orderno: O19002 Enter value for orderdate: 25-jan-96 Enter value for clientno: C002
Enter value for delyaddr:
Enter value for salesmanno: S002 Enter value for delytype: P Enter value for billedyn: N
Enter value for delydate: 27-jan-96 Enter value for orderstatus: cancelled
old 2: ('&Orderno','&Orderdate','&Clientno','&DelyAddr','&SalesmanNo','&Delyty pe','&Billedyn','&Delydate','&Orderstatus')
new 2: ('O19002','25-jan-96','C002',' ','S002','P','N','27-jan-96','cancelled' )
1 row created. SQL> /
Enter value for orderno: O46865 Enter value for orderdate: 18-feb-96 Enter value for clientno: C003
Enter value for delyaddr:
Enter value for salesmanno: S003 Enter value for delytype: F Enter value for billedyn: Y
Enter value for delydate: 20-feb-96 Enter value for orderstatus: Fullfilled
old 2: ('&Orderno','&Orderdate','&Clientno','&DelyAddr','&SalesmanNo','&Delyty pe','&Billedyn','&Delydate','&Orderstatus')
new 2: ('O46865','18-feb-96','C003',' ','S003','F','Y','20-feb-96','Fullfilled ')
1 row created. SQL> /
Enter value for orderno: O19003 Enter value for orderdate: 03-apr-96 Enter value for clientno: C001
Enter value for delyaddr:
Enter value for salesmanno: S001 Enter value for delytype: F Enter value for billedyn: Y
Enter value for delydate: 07-apr-96 Enter value for orderstatus: Fullfilled
old 2: ('&Orderno','&Orderdate','&Clientno','&DelyAddr','&SalesmanNo','&Delyty pe','&Billedyn','&Delydate','&Orderstatus')
new 2: ('O19003','03-apr-96','C001',' ','S001','F','Y','07-apr-96','Fullfilled ')
1 row created. SQL> /
Enter value for orderno: O46866 Enter value for orderdate: 20-may-96 Enter value for clientno: C004
Enter value for delyaddr:
Enter value for salesmanno: S002 Enter value for delytype: P
Enter value for billedyn: N
Enter value for delydate: 22-may-96 Enter value for orderstatus: cancelled
old 2: ('&Orderno','&Orderdate','&Clientno','&DelyAddr','&SalesmanNo','&Delyty pe','&Billedyn','&Delydate','&Orderstatus')
new 2: ('O46866','20-may-96','C004',' ','S002','P','N','22-may-96','cancelled' )
1 row created. SQL> /
Enter value for orderno: O19008 Enter value for orderdate: 24-may-96 Enter value for clientno: C005
Enter value for delyaddr:
Enter value for salesmanno: S004 Enter value for delytype: F Enter value for billedyn: N
Enter value for delydate: 26-may-96 Enter value for orderstatus: In Process
old 2: ('&Orderno','&Orderdate','&Clientno','&DelyAddr','&SalesmanNo','&Delyty pe','&Billedyn','&Delydate','&Orderstatus')
new 2: ('O19008','24-may-96','C005',' ','S004','F','N','26-may-96','In Process ')
1 row created.
SQL> select * from Sales_order;
ORDERN ORDERDATE CLIENT DELYADDR SALESM D B DELYDATE ORDERSTAT U
--- --- --- --- - -
-O19001 12-JAN-96 C001 S001 F N 20-JAN-96 In Proces s
O19002 25-JAN-96 C002 S002 P N 27-JAN-96 cancelled O46865 18-FEB-96 C003 S003 F Y 20-FEB-96 Fullfille d
O19003 03-APR-96 C001 S001 F Y 07-APR-96 Fullfille d
O46866 20-MAY-96 C004 S002 P N 22-MAY-96 cancelled O19008 24-MAY-96 C005 S004 F N 26-MAY-96 In Proces s
6 rows selected.
e)Data for Sales_order_details.
Orderno Productno Qtyordered Qtydisp Productrate
O19001 P00001 4 4 525 O19001 P07965 2 1 8400 O19001 P07885 2 1 5250 O19002 P00001 10 0 525 O46865 P07868 3 3 3150 O46865 P07885 3 1 5250 O46865 P00001 10 10 525 O46865 P03453 4 4 1050 O19003 P03453 2 2 1050 O19003 P06734 1 1 12000 O46866 P07965 1 0 8400
O46866 P07975 1 0 1050
O19008 P00001 10 5 525
O19008 P07975 5 3 1050
Source code:
SQL> insert into Sales_order_details values
2 ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate); Enter value for orderno: O19001
Enter value for productno: P00001 Enter value for qtyordered: 4 Enter value for qtydisp: 4
Enter value for productrate: 525
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19001','P00001',4,4,525)
1 row created. SQL> /
Enter value for orderno: O19001 Enter value for productno: P07965 Enter value for qtyordered: 2 Enter value for qtydisp: 1
Enter value for productrate: 8400
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19001','P07965',2,1,8400)
1 row created. SQL> /
Enter value for orderno: O19001 Enter value for productno: P07885 Enter value for qtyordered: 2 Enter value for qtydisp: 1
Enter value for productrate: 5250
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19001','P07885',2,1,5250)
1 row created. SQL> /
Enter value for orderno: O19002 Enter value for productno: P00001 Enter value for qtyordered: 10 Enter value for qtydisp: 0
Enter value for productrate: 525
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19002','P00001',10,0,525)
1 row created. SQL> /
Enter value for orderno: O46865 Enter value for productno: P07868 Enter value for qtyordered: 3 Enter value for qtydisp: 3
Enter value for productrate: 3150
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O46865','P07868',3,3,3150)
SQL> /
Enter value for orderno: O46865 Enter value for productno: P07885 Enter value for qtyordered: 3 Enter value for qtydisp: 1
Enter value for productrate: 5250
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O46865','P07885',3,1,5250)
1 row created. SQL> /
Enter value for orderno: O46865 Enter value for productno: P00001 Enter value for qtyordered: 10 Enter value for qtydisp: 10 Enter value for productrate: 525
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O46865','P00001',10,10,525)
1 row created. SQL> /
Enter value for orderno: O46865 Enter value for productno: P03453 Enter value for qtyordered: 4 Enter value for qtydisp: 4
Enter value for productrate: 1050
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O46865','P03453',4,4,1050)
1 row created. SQL> /
Enter value for orderno: O19003 Enter value for productno: P03453 Enter value for qtyordered: 2 Enter value for qtydisp: 2
Enter value for productrate: 1050
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19003','P03453',2,2,1050)
1 row created. SQL> /
Enter value for orderno: O19003 Enter value for productno: P06734 Enter value for qtyordered: 1 Enter value for qtydisp: 1
Enter value for productrate: 12000
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19003','P06734',1,1,12000)
1 row created. SQL> /
Enter value for orderno: O46866 Enter value for productno: P07965 Enter value for qtyordered: 1
Enter value for qtydisp: 0
Enter value for productrate: 8400
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O46866','P07965',1,0,8400)
1 row created. SQL> /
Enter value for orderno: O46866 Enter value for productno: P07975 Enter value for qtyordered: 1 Enter value for qtydisp: 0
Enter value for productrate: 1050
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O46866','P07975',1,0,1050)
1 row created. SQL> /
Enter value for orderno: O19008 Enter value for productno: P00001 Enter value for qtyordered: 10 Enter value for qtydisp: 5
Enter value for productrate: 525
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19008','P00001',10,5,525)
1 row created. SQL> /
Enter value for orderno: O19008 Enter value for productno: P07975 Enter value for qtyordered: 5 Enter value for qtydisp: 3
Enter value for productrate: 1050
old 2: ('&Orderno','&Productno',&Qtyordered,&Qtydisp,&Productrate) new 2: ('O19008','P07975',5,3,1050)
1 row created. SQL> commit; Commit complete.
SQL> select * from Sales_order_details; ORDERN PRODUC QTYORDERED QTYDISP PRODUCTRATE --- --- --- --- ---O19001 P00001 4 4 525 O19001 P07965 2 1 8400 O19001 P07885 2 1 5250 O19002 P00001 10 0 525 O46865 P07868 3 3 3150 O46865 P07885 3 1 5250 O46865 P00001 10 10 525 O46865 P03453 4 4 1050 O19003 P03453 2 2 1050 O19003 P06734 1 1 12000 O46866 P07965 1 0 8400
ORDERN PRODUC QTYORDERED QTYDISP PRODUCTRATE --- --- --- --- ---O46866 P07975 1 0 1050 O19008 P00001 10 5 525 O19008 P07975 5 3 1050 14 rows selected. ____________xxx____________