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)
