• No results found

1-4

N/A
N/A
Protected

Academic year: 2021

Share "1-4"

Copied!
36
0
0

Loading.... (view fulltext now)

Full text

(1)

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),

(2)

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

(3)

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)

(4)

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

(5)

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)

(6)

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

(7)

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

(8)

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)

(9)

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

(10)

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.

(11)

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;

(12)

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.

(13)

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

(14)

--- --- --- ---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.

(15)

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

(16)

--- - --- - ---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

(17)

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 (

(18)

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

(19)

--- --- 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 );

(20)

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,

(21)

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)

(22)

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.

(23)

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

(24)

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

(25)

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,

(26)

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

(27)

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.

(28)

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

(29)

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.

(30)

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 ')

(31)

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

(32)

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

(33)

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)

(34)

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

(35)

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

(36)

ORDERN PRODUC QTYORDERED QTYDISP PRODUCTRATE --- --- --- --- ---O46866 P07975 1 0 1050 O19008 P00001 10 5 525 O19008 P07975 5 3 1050 14 rows selected. ____________xxx____________

References

Related documents

Calculate total value Enter the delta of desired net value and calculated net value into Incidental Costs field. Calculate total

Normally, the prospects of rising yields and steepening yield curves send shivers down the spine of most fixed income managers. This is because rising yields cause the price of

The interface prompts the following information (if you do want to change the default settings, press the ENTER key, else enter the desired value and then press the ENTER

The figure above shows how the system users intervene with the process of the school management system for teachers, courses and how data are sent

Moved by Director Cardenas, seconded by Director Hanks, that the board approve the following minutes of the Imperial Irrigation District Board of Directors with three minor

Assessment of Microstructure and Mechanical Behavior of Assessment of Microstructure and Mechanical Behavior of Materials and Phases Observed in Low-Enriched Uranium Materials

McKesson customers should enter the following value in this field: CLAIMSCH ZirMed customers should enter the following value in this field: ZIRMED This information

In two-variable statistical calculations, enter each data pair (the “X and Y-values”) by typing the Y-value into the display, pressing [ENTER], then type in the X-value and