Database Management Systems Lab

CSPC-222
Lab-Assignment 2
(Simple DML and DDL Queries)

1. Create a database named as ‘Inventory’and Create the tables described below:

Table Name: CLIENT_MASTER

Description: Used to store client information.
Column Name Data Type Size Default Attributes
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

Table Name: PRODUCT_MASTER

Description: Used to store product information.
Column Name Data Type Size Default Attributes
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

Table Name: SALESMAN_MASTER

Description: Used to store salesman information working for the company.
Column Name Data Type Size Default Attributes
SALESMANNO Varchar2 6
SALESMANNAME Varchar2 20
ADDRESS1 Varchar2 30
ADDRESS2 Varchar2 30
CITY Varchar2 20
PINCODE Number 8
STATE Varchar2 20

1
SALAMT Number 8,2
TGTTOGET Number 6,2
YTDSALES Number 6,2
REMARKS Varchar2 60

Source Code:

CREATE DATABASE Inventory;

USE Inventory;
create table CLIENT_MASTER(
CLIENT_NO varchar(6),
NAME varchar(20),
CITY varchar(15),
PINCODE int(8),
STATE varchar(15),
BALDUE float(10,2)
);
create table PRODUCT_MASTER(
PRODUCT_TNO varchar(6),
DESCRIPTION varchar(15),
PROFIT_PERCENT float(4,2),
UNIT_MEASURE varchar(10),
QTY_ON_HAND int(8),
RE_ORDER_LVL int(8),
SELL_PRICE float(8,2),
COST_PRICE float(10,2)
);
create table SALESMAN_MASTER(
SALESMANNO varchar(6),
SALESMANNAME varchar(20),
ADDRESS1 varchar(30),
ADDRESS2 varchar(30),
CITY varchar(20),

2
PINCODE int(8),
STATE varchar(20),
SALAMT float(8,2),
TGTTOGET float(6,2),
YTDSALES float(6,2),
REMARKS varchar(60)
);
SHOW tables;

Output:

1. Insert the following data into their respective tables:

a) Data for CLIENT_MASTER table:

ClientNo Name City Pincode State BalDue

C00001 Ivan Bayross Mumbai 400054 Maharashtra 15000
C00002 Mamta Muzumdar Madras 780001 Tamil Nadu 0
C00003 Chhaya Bankar Mumbai 400057 Maharashtra 5000
C00004 Ashvini Joshi Banglore 560001 Karnataka 0
C00005 Hancel Colaco Mumbai 400060 Maharashtra 2000
C00006 Deepak Sharma Mangalore 560050 Karnataka 0

b) Data for PRODUCT_MASTER table:

ProductNo Description Profit Unit QtyOn ReorderLvl SellPrice CostPrice

Percent Measure Hand
P00001 T-Shirts 5 Piece 200 50 350 250
P0345 Shirts 6 Piece 150 50 500 350
P06734 Cotton Jeans 5 Piece 100 20 600 450
P07865 Jeans 5 Piece 100 20 750 500
P07868 Trousers 2 Piece 150 50 850 550
P07885 Pull Overs 2.5 Piece 80 30 700 450
P07965 Denim Shirts 4 Piece 100 40 350 250
3
P07975 Lycra Tops 5 Piece 70 30 300 175
P08865 Skirts 5 Piece 75 30 450 300

c) Data for SALESMAN_MASTER table:

SalesmanNo Name Address1 Address2 City PinCode State

S00001 Aman A/14 Worli Mumbai 400002 Maharashtra
S00002 Omkar 65 Nariman Mumbai 400001 Maharashtra
S00003 Raj P-7 Bandra Mumbai 400032 Maharashtra
S00004 Ashish A/5 Juhu Mumbai 400044 Maharashtra

SalesmanNo SalAmt TgtToGet YtdSales Remarks

S00001 3000 100 50 Good
S00002 3000 200 100 Good
S00003 3000 200 100 Good
S00004 3500 200 150 Good

Source Code:

insert into CLIENT_MASTER

(CLIENT_NO,NAME,CITY,PINCODE,STATE,BALDUE)
values
("C00001","IVAN BAYROSS","MUMBAI",400054,"MAHARASHTRA",15000),
("C00002","MAMTA MUZUMDAR","MADRAS",780001,"TAMILNADU", 0),
("C00003","CHHAYA BANKER","MUMBAI",400057,"MAHARASHTRA",15000),
("C00004","ASHVINI JOSHI","BANGLORE",560001,"KARNATAKA",0),
("C00005","HANCEL COLACO","MUMBAI",400060,"MAHARASHTRA",2000),
("C00006","DEEPAK SHARMA","MANGLORE",560050,"KARNATAKA", 0);
SELECT * FROM CLIENT_MASTER;
insert into PRODUCT_MASTER
(PRODUCT_NO,DESCRIPTION,PROFIT_PERCENT,UNIT_MEASURE,QTY_ON_HAND,REORDER_LVL,SE
LL_PRICE,COST_PRICE)
values
("P00001","TSHIRTS",5,"PIECE",300,10,300,678),
("P0345","SHIRTS",6,"PIECE",460,13,324,698),

4
("P06734","COTTON JEANS",5,"PIECE",50,15,600,433),
("P0785","JEANS",5,"PIECE",670,20,632,100),
("P07868","TROUSERS",5,"PIECE",100,20,600,322),
("P07885","PULL OVERS",2.5,"PIECE",300, 46,400,200),
("P07965","DENIM SHIRTS",4,"PIECE",470, 50,700,430),
("P0975","LYCRA TOPS",5,"PIECE",450,67,300,500),
("P08865","SKIRTS",5,"PIECE",500,46,765,800);
SELECT * FROM PRODUCT_MASTER;

Output:

2. Exercise on retrieving records from a table

a. Find out all the tables present in the current database and other databases.
b. Find out the names of all the clients.
c. Retrieve the entire contents of the CLIENT_MASTER table.
d. Retrieve the list of names, city, and state of all the clients.
e. List the various products available from the PRODUCT_MASTER table.
f. List all the clients who are located in Mumbai.
5
g. Find the names of the salesman who have a salary equal to Rs. 3000.

Source Code:

a : show tables;
b: select name from CLIENT_MASTER;
c : select * from CLIENT_MASTER;
d : select name,city,state from CLIENT_MASTER;
e : select description from PRODUCT_MASTER;
f : select * from CLIENT_MASTER where city=Mumbai;

Output:

6
Program 4: Exercise On Updating Records from a table
a : Change the city of client no “C00005” to banglore
b : Change the balance due of client no “C00001” to Rs 1000
c : Change the costprice of trousers to 950

Source Code:

a : update CLIENT_MASTER
set City="BANGLORE"
where CLIENTNO = "C00005";
b : update CLIENT_MASTER

7
set baldue=1000
where CLIENTNO = "C00001";
c : update PRODUCT_MASTER
set COSTPRICE=950
where DESCRIPTION = "TROUSERS";

Output:

Program 5: Exercise on deleting records from the table

a : Delete all prodects from product table where qtyonhand is 3500
b : Delete from client master table where state is tamilnadu

Source code:

a : delete from PRODUCT_MASTER

8
where QTYONHAND=500;
b : delete from CLIENT_MASTER
where STATE="TAMILNADU";

Output:

Program 6: Altering the table structure

a : Add acolumn called “telephone” of data type “number” and size=”10 to client_master
b : Change the size of sellprice column in product_master to 10,2

Source Code:
9
a : alter table client_master
add column telephone int(10);
b : alter table product_master
modify sellprice float(10,2);

Output:

Program 7: Exercise on deleting the table structure along with data

a : Destroy the table content CLIENT_MASTER along with its data

Source Code:

drop table client_master

Output:

10
11
12