ROLL NO.

: 23BCE277
NAME: RATHVA DIPESHKUMAR B.
SEMESTER: 3
DIVISION: E
BATCH: E-2

PRACTICAL: 1

CODES:

1. a)Create table client (used to store client information) having

following attributes:

Column Name Data Type Size

clientno varchar2 6

name varchar2 20

city varchar2 15

pincode number 8

state varchar2 15

baldue number 10, 5

#OUTPUT:
SQL> CREATE TABLE client (
b)
c)
d) clientno VARCHAR2(6),
e) name VARCHAR2(20),
f) city VARCHAR2(15),
g) pincode NUMBER(8),
h) state VARCHAR2(15),
i) baldue NUMBER(10, 5)
j) );
 Table created.

k) Create table product (used to store product information) having

following attributes:

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

#OUTPUT:

SQL> CREATE TABLE product (

2 productno VARCHAR2(6),
3 description VARCHAR2(15),
4 profitpercent NUMBER(4, 2),
5 unitmeasure VARCHAR2(10),
6 qtyonhand NUMBER(8),
7 reorderlvl NUMBER(8),
8 sellprice NUMBER(8, 2), 9
9 costprice NUMBER(8, 2) );
Table created.

l) Create table salesman (used to store salesman information working

for the company) having following attributes:

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

salamt number 8, 2

tgttoget number 6, 2

ytdsales number 6, 2

remarks varchar2 60

#OUTPUT:
SQL> CREATE TABLE salesman (
2 salesmanno VARCHAR2(6),
3 salesmanname VARCHAR2(20),
4 address1 VARCHAR2(30),
5 address2 VARCHAR2(30),
6 city VARCHAR2(20),
7 pincode NUMBER(8),
8 state VARCHAR2(20),
9 salamt NUMBER(8, 2),
10 tgttoget NUMBER(6, 2),
11 ytdsales NUMBER(6, 2),
12 remarks VARCHAR2(60)
13 );

Table created.
m) Create table sales_order (used to store client’s orders) having
following attributes:

Column Name Data Type Size

orderno varchar2 6

clientno varchar2 6

orderdate date

salesmanno varchar2 6

delaytype char 1

Billyn char 1

delaydate date

orderstatus varchar2 10

#OUTPUT:
SQL> CREATE TABLE sales_order (
2 orderno
VARCHAR2(6), 3 clientno
VARCHAR2(6),
4 orderdate DATE,
5 salesmanno VARCHAR2(6),
6 delaytype CHAR(1),
7 billyn CHAR(1),
8 delaydate DATE,
9 orderstatus VARCHAR2(10)
10 );

Table created.
n) Create table sales_order_details (used to store client’s orders with
details of each product ordered ) having following attributes:

Column Name Data Type Size

Orderno varchar2 6

productno varchar2 6

qtyordered number 8

productrate number 10, 2

#OUTPUT:
SQL> CREATE TABLE sales_order_details (
2 orderno VARCHAR2(6),
3 productno VARCHAR2(6),
4 qtyordered NUMBER(8),
5 productrate NUMBER(10, 2)
6 );

2.a) Retrieve the structure of the tables.

SQL> DESCRIBE client;
Name Null? Type
----------------------------------------- -------- ----------------------------
CLIENTNO VARCHAR2(6)
NAME VARCHAR2(20)
CITY VARCHAR2(15)
PINCODE NUMBER(8)
STATE VARCHAR2(15)
BALDUE NUMBER(10,5)

SQL> DESCRIBE product;

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)
SQL> DESCRIBE salesman;
Name Null? Type
----------------------------------------- -------- ----------------------------
SALESMANNO VARCHAR2(6)
SALESMANNAME VARCHAR2(20)
ADDRESS1 VARCHAR2(30)
ADDRESS2 VARCHAR2(30)
CITY VARCHAR2(20)
PINCODE NUMBER(8)
STATE VARCHAR2(20)
SALAMT NUMBER(8,2)
TGTTOGET NUMBER(6,2)
YTDSALES NUMBER(6,2)
REMARKS VARCHAR2(60)

SQL> DESCRIBE sales_order;

Name Null? Type
----------------------------------------- -------- ----------------------------
ORDERNO VARCHAR2(6)
CLIENTNO VARCHAR2(6)
ORDERDATE DATE
SALESMANNO VARCHAR2(6)
DELAYTYPE CHAR(1)
BILLYN CHAR(1)
DELAYDATE DATE
ORDERSTATUS VARCHAR2(10)
SQL> DESCRIBE sales_order_details;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDERNO VARCHAR2(6)
PRODUCTNO VARCHAR2(6
)
QTYORDERED NUMBER(8)
PRODUCTRATE NUMBER(10,2)
SQL> commit;

Commit complete.
3. a) Insert the following data into client table :

ClientNo Name City Pincode State BalDue

C01 Ivan Mumbai 400054 Maharasht 15000

Bayross ra

C02 Mamta Chennai 780001 Tamil 0

Shah Nadu

C03 Chhaya Mumbai 400057 Maharasht 5000

Patel ra

C04 Ashni Bangalore 560001 Karnataka 0

Joshi

C05 Harsh Mumbai 400060 Maharasht 2000

Desai ra
0
C06 Deepak Mangalore 560050 Karnataka
Sharma

SQL> INSERT INTO client VALUES ('C01', 'Ivan Bayross', 'Mumbai', 400054, 'Maharashtra',
15000);
 SQL> INSERT ALL
b)
c)
d) INTO client VALUES ('C02', 'Mamta Shah', 'Chennai', 780001, 'Tamil Nadu', 0)
e) INTO client VALUES ('C03', 'Chhaya Patel', 'Mumbai', 400057, 'Maharashtra', 5000)
f) INTO client VALUES ('C04', 'Ashni Joshi', 'Bangalore', 560001, 'Karnataka', 0)
g) INTO client VALUES ('C05', 'Harsh Desai', 'Mumbai', 400060, 'Maharashtra', 2000)
h) INTO client VALUES ('C06', 'Deepak Sharma', 'Mangalore', 560050, 'Karnataka', 0) 7
SELECT * FROM dual;

5 rows created.

i) Insert the following data into product table :

Produc Descriptio Profit Unit Qty Reord Sell Cost

t No n Percen Measu on er Price Price
t re hand Level
 Piece
P00001 1.44 5 100 20 525 500
Floppies
Piece
P03453 Monitors 6 10 3 12000 1120
0

P06734 Mouse 5 Piece 20 5 1050 500

Piece
P07865 1.22 5 100 20 525 500
Floppies
Piece
P07868 Keyboard 2 10 3 3150 3050
s

SQL> INSERT ALL

j) INTO product VALUES ('P00001', '1.44 Floppies', 5, 'Piece', 100, 20, 525, 500)
k) INTO product VALUES ('P03453', 'Monitors', 6, 'Piece', 10, 3, 12000, 11200)
l) INTO product VALUES ('P06734', 'Mouse', 5, 'Piece', 20, 5, 1050, 500)
m) INTO product VALUES ('P07865', '1.22 Floppies', 5, 'Piece', 100, 20, 525, 500)
n) INTO product VALUES ('P07868', 'Keyboards', 2, 'Piece', 10, 3, 3150, 3050)
o) SELECT * FROM dual;

5 rows created.
p) Insert the following data into salesman table :
Salesman SalesmanNa Addres Addres City PinCo State
No me s1 s2 de

S01 Aman A/14 Worli Mumb 40000 Maharas

ai 2 htra

S02 Omkar 65 Narima Mumb 40000 Maharas

n ai 1 htra

S03 Raj P-7 Bandra Mumb 40003 Maharas

ai 2 htra

S04 Ashish A/5 Juhu Mumb 40004 Maharas

ai 4 htra
Salesma
nNo SalAmt TgtToGet YtdSales Remarks

S01 3000 100 50 Good

S02 3000 200 100 Good

S03 3000 200 100 Good

S04 3500 200 150 Good

SQL> INSERT ALL

2 INTO salesman VALUES ('S01', 'Aman', 'A/14', 'Worli', 'Mumbai', 400002, 'Maharashtra',
3000, 100, 50, 'Good')
3 INTO salesman VALUES ('S02', 'Omkar', '65', 'Nariman', 'Mumbai', 400001,
'Maharashtra',
3000, 200, 100, 'Good')
4 INTO salesman VALUES ('S03', 'Raj', 'P-7', 'Bandra', 'Mumbai', 400032, 'Maharashtra',
3000, 200, 100, 'Good')
5 INTO salesman VALUES ('S04', 'Ashish', 'A/5', 'Juhu', 'Mumbai', 400044, 'Maharashtra',
3500, 200, 150, 'Good')
6 SELECT * FROM dual;

4 rows created.
q) Insert the following data into sales_order table :

Order Client OrderD Salesma DeliveryT Bill Delivery OrderSta

No No ate nNo ype yn Date tus
S01
O1900 C01 12-01-1 F N 20-01-16 In
1 6 Process
 S02
O1900 C02 25-01-1 P N 27-01-17 Cancelle
2 7 d
S03
O4686 C03 18-02-1 F Y 20-02-17 Fulfilled
5 7
S01
O1900 C01 03-04-1 F Y 07-04-16 Fulfilled
3 6
S02
O4686 C04 20-05-1 P N 22-05-16 Cancelle
6 6 d
S04
O1900 C05 24-05-1 F N 26-05-16 In
8 6 Process

SQL> INSERT ALL

2 INTO sales_order VALUES ('O19001', 'C01', TO_DATE('12-01-16', 'DD-MM-YY'), 'S01',
'F', 'N', TO_DATE('20-01-16', 'DD-MM-YY'), 'In Process')
3 INTO sales_order VALUES ('O19002', 'C02', TO_DATE('25-01-17', 'DD-MM-YY'), 'S02',
'P', 'N', TO_DATE('27-01-17', 'DD-MM-YY'), 'Cancelled')
4 INTO sales_order VALUES ('O46865', 'C03', TO_DATE('18-02-17', 'DD-MM-YY'), 'S03',
'F', 'Y', TO_DATE('20-02-17', 'DD-MM-YY'), 'Fulfilled')
5 INTO sales_order VALUES ('O19003', 'C01', TO_DATE('03-04-16', 'DD-MM-YY'), 'S01',
'F', 'Y', TO_DATE('07-04-16', 'DD-MM-YY'), 'Fulfilled')
6 INTO sales_order VALUES ('O46866', 'C04', TO_DATE('20-05-16', 'DD-MM-YY'), 'S02',
'P', 'N', TO_DATE('22-05-16', 'DD-MM-YY'), 'Cancelled')
7 INTO sales_order VALUES ('O19008', 'C05', TO_DATE('24-05-16', 'DD-MM-YY'), 'S04',
'F', 'N', TO_DATE('26-05-16', 'DD-MM-YY'), 'In Process')
8 SELECT * FROM dual;

6 rows created.
r) Insert the following data into sales_order_details table :

OrderNo ProductNo QtyOrdered ProductRate

O19001 P00001 4 525

O19001 P07965 2 8400

O19001 P07885 2 5250

O19002 P00001 10 525

O46865 P07868 3 3150

SQL> INSERT ALL

2 INTO sales_order_details VALUES ('O19001', 'P00001', 4, 525)
3 INTO sales_order_details VALUES ('O19001', 'P07965', 2, 8400)
4 INTO sales_order_details VALUES ('O19001', 'P07885', 2, 5250)
5 INTO sales_order_details VALUES ('O19002', 'P00001', 10, 525)
6 INTO sales_order_details VALUES ('O46865', 'P07868', 3, 3150) 7 SELECT *
FROM dual;

5 rows created.

4.Do as directed:
a)Change the city of Harsh to Bangalore.

SQL> UPDATE client

SET CITY = 'Bangalore' 3 WHERE
NAME = 'Harsh Desai'; 1 row
updated. SQL> select * from client;
CLIENT NAME CITY PINCODE STATE BALD
------ -------------------- --------------- --------- --------------- --------- UE
C02 Mamta Shah Chennai 780001 Tamil Nadu 0
C03 Chhaya Patel Mumbai 400057 Maharashtra 5000
C04 Ashni Joshi Bangalore 560001 Karnataka 0
C05 Harsh Desai Bangalore 400060 Maharashtra 2000
C06 Deepak Sharma Mangalore 560050 Karnataka 0
C01 Ivan Bayross Mumbai 400054 Maharashtra 1500
0
6 rows selected.

b)Change the city of salesman to Pune. What is wrong in doing this

type of query?
SQL> UPDATE salesman
SET city = 'Pune'; 4

rows updated.

c)Delete all the salesmen whose salaries are equal to Rs. 3500.
SQL> DELETE FROM salesman
WHERE salamt = 3500;

1 row deleted.
d)Delete all the clients who live in “Tamil Nadu”.
SQL> DELETE FROM client

WHERE state = 'TamilNadu';

1 row deleted.

SQL> select * from client;

CLIENT NAME CITY PINCODE STATE BALD
------ -------------------- --------------- --------- --------------- --------- UE
 C03 Chhaya Patel Mumbai 400057 Maharashtra 5000
C04 Ashni Joshi Bangalore 560001 Karnataka 0
C05 Harsh Desai Bangalore 400060 Maharashtra 2000
C06 Deepak Sharma Mangalore 560050 Karnataka 0
C01 Ivan Bayross Mumbai 400054 Maharashtra 1500
0
SQL>

e)Add a column called “Telephone” of data type “Number” and size

“10” to the client table.
SQL> ALTER TABLE client
ADD telephone NUMBER(10);

Table altered.

SQL> select * from client;

CLIENT NAME CITY PINCODE STATE BALDUE TELEPHONE

------ -------------------- --------------- --------- --------------- ---------
---------
C03 Chhaya Patel Mumbai 400057 Maharashtra 5000
C04 Ashni Joshi Bangalore 560001 Karnataka 0
C05 Harsh Desai Bangalore 400060 Maharashtra 2000
C06 Deepak Sharma Mangalore 560050 Karnataka 0
C01 Ivan Bayross Mumbai 400054 Maharashtra 150
00
SQL>
f)Change the size of sell price to 6, 2. Observe the behavior.SQL> ALTER
TABLE product
MODIFY sellprice NUMBER(6, 2); MODIFY
sellprice NUMBER(6, 2)
*
ERROR at line 2:
ORA-01440: column to be modified must be empty to decrease precision or scale

SQL>

g)Change the name of salesman table to sman.

SQL> RENAME salesman TO sman;

Table renamed.

h)Create a new table sales_order_new from sales_order containing

data as well as structure
SQL> CREATE TABLE sales_order_new AS
2 SELECT * FROM sales_order;
 Table created.
SQL> select * from sales_order_new;

ORDERN CLIENT ORDERDATE SALESM D B DELAYDATE ORDERSTATU

------ ------ --------- ------ - - --------- ----------
O19001 C01 12-JAN-16 F N 20-JAN-16 In
S01 Process
O19002 C02 25-JAN-17 P N 27-JAN-17
S02 Cancelled
O46865 C03 18-FEB-17 F Y 20-FEB-17 Fulfilled
S03
O19003 C01 03-APR-16 F Y 07-APR-16 Fulfilled
S01
O46866 C04 20-MAY-16 P N 22-MAY-16
S02 Cancelled
O19008 C05 24-MAY-16 F N 26-MAY-16 In
S04 Process
6 rows selected.

SQL>

i)Create a new table order_details from sales_order_details containing

only the structure (no data).
SQL> CREATE TABLE order_details AS
2 SELECT * FROM sales_order_details WHERE 1 = 0;

Table created.
 SQL> select * from

order_details; no rows selected

SQL> describe order_details;

Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ORDERNO VARCHAR2(6)
PRODUCTNO VARCHAR2(6)
QTYORDERED NUMBER(8)
PRODUCTRATE NUMBER(10
,2)
SQL>