Experiment-3

ProgramName: Application of Creation, Deletion, Insertion, Updation, Alter, Destroy,

Rename Commands:
a) Create Table CLIENT_MASTER, PRODUCT_MASTER, SALESMAN_MASTER
b) Insert relevant data into the tables
c) Retrieve data from table CLIENT_MASTER, PRODUCT_MASTER,
SALESMAN_MASTER
d) Update records in the tables CLIENT_MASTER, PRODUCT_MASTER,
SALESMAN_MASTER
e) Delete records from tables CLIENT_MASTER, PRODUCT_MASTER,
SALESMAN_MASTER
f) Create a new table with already existing table
g) Insert data into a new table from already existing table
h) Alter structure of the tables CLIENT_MASTER, PRODUCT_MASTER,
SALESMAN_MASTER
i) Destroy a table along with its data
j) Rename SALESMAN_MASTER
k) Show the structure of the table product_master

Theory Concept: This program intends to demonstrate application of various commands

used for data definition and data manipulation language.

Implementation:

Q-a) Create the tables described below

Table Name : CLIENT_MASTER

Description : Used to store the client information

Column Name Data Type Size

Client_no Varchar2 6
Name Varchar2 20
Address1 Varchar2 30
Address2 Varchar2 30
City Varchar2 15
Pincode Number 6
State Varchar2 15

Table Name : PRODUCT_MASTER

Description : Used to store the product information

Column Name Data Type Size

Product_no Varchar2 6
Description Varchar2 20
Quantity_on_hand Number 8
Reorder_level Number 8
Cost_price Number 8,2
Selling_Price Number 8,2

Table Name : SALESMAN_MASTER

Description : Used to store the salesman information working for the Company

Column Name Data Type Size

Salesman_no Varchar2 6
Name Varchar2 20
Address1 Varchar2 30
Address2 Varchar2 30
City Varchar2 15
Pincode Number 6
State Varchar2 15
Date_of_joining Date
Salary Number 8,2

Ans:create table client_master(client_no. varchar(6), name varchar(20), city varchar(15),

pincode number(6), state varchar(5));
create table product_master(product_no. varchar(6), description varchar(20),
quantity_on_headnumber(8), cost price number(8,2), selling price number(8,2));
create table SALESMAN_MASTER(salesman_novarchar(6), name varchar(20),address1
varchar(30), address2 varchar(30), city varchar(15), pincode number(6), state varchar(15),
date_of_joining date, salary number(8,2));
Output: Table created

Q-b) Insert data items into the tables created above

Ans : insert into client_master(client_no. , name , city , pincode , state) values(‘&client_no.’ ,
‘&name’ , ‘&city’ , ‘&pincode’ , ‘&state’);
Client_no Name City Pincode State
3 Akshita Ghaziabad 23456 UP
4 Dhawal Ghaziabad 24364 UP
5 Akansha Dhampur 246761 UP
6 Divya Hapur 35498 UP

Output: 4 rows created

insert into product_master

(product_no.,description,quantity_on_hand,cost_price,selling_price)
values(‘&product_no’,’&description’,’&quantity_on_hand’,’&cost_price’,’&selling_price’);
Product no. Description quantity_on_hand cost_price selling_price
1 Chair 5 1000 1250
2 Table 5 5000 6000

Output: 2 rows created

Q-c) Retrieve records from the above tables as follows

a) Find out the names of all the clients

b) Retrieve the entire contents of the client_master table
c) Retrieve description, cost_price and selling_price from product master
d) Retrieve clients from client_master table who live in ’Dhampur’

e) Retrieve distinct city from client_master table

f) Retieveproduct_no., description and cost_price from product_masterwhich are
ordered by cost_price.

Ans:
a) select name from client_master ;
Output:
NAME
--------
Akshita
Dhawal
Akansha
Divya

4 rows selected

b) select * from client_master ;

Output:
Client_no Name City Pincode State
3 Akshita Ghaziabad 23456 UP
4 Dhawal Ghaziabad 24364 UP
5 Akansha Dhampur 246761 UP
6 Divya Hapur 35498 UP

4 rows selected

c) select description, cost_price, selling_price from product master ;

Output:
DESCRIPTION COST_PRICE SELLING_PRICE
------------------------------------------------------------------
Chair 1000 1250
Table 5000 6000

2 rows selected

d) select name from client_master where city = ’Dhampur’;

Output:
NAME
--------
Akansha
1 row selected

e) select distinct city from client_master ;

Output:
CITY
------------
Ghaziabad
Dhampur
Hapur

3 row selected

f) select product_no., description, cost_price from product_master order by cost_price ;

Output:
Product No. Description Cost Price
4 Mirror 250
1 Chair 1000

2 rows selected

Q-d) Update the records in the tables above as follows

a) Change the city of client_no ‘C1’ to dhampur

b) Change the cost price from 250 to 500 in product_master;

Ans :
a) Update client-master set city=’Noida’ where city=’Dhampur’;

Output:1 row selected

b) Update product_master
set cost _price=500 where cost _price=250;

Output: 1 row updated

Q-e) Delete records in the table above as follows

Delete all records for the product_master table
Ans:
Delete from product_master;

Output: 5 rows deleted

Q-f) Create table New_client from client_master with the fields Client_no, name
Ans :
create table new-client(client_no,name) as (select client_no,name from
client_master);
Output:Table created

Q-g) Insert into table new_client data from table client_master where city = ‘Hapur’
Ans: insert into new_client select client_no.,name from client-master where city=’Hapur’;
Output:
Client_no. Name
6 Divya

1 row created

Q-h) Alter the table structures as instructed

a) Add a column called Telephone_no of data type number and size = 10 to the
client_master table
b) Change the size of the description column in product_master to 25
c) Drop the column Telephone_no from the table client_master
Ans:
a) alter table client_master add(telephone_number(10));

Output:Table created
b) alter table product_master modify(description varchar(25));

Output:Table created
c) alter table client_master drop column telephone_no;

Output:Table created

Q-i) Destroy the table new_client along with its data

Ans:
Drop table new_client;
Output:Table dropped

Q-j) Change the name of the product_master to products

Ans:
renameproduct_master to products;
Output:Table renamed

Q-k) Show the structure of the table product_master

Ans :
Describe product1
Output:
Column Description
Product_no. Varchar(6)
Description Varchar(20)