DBMS- Practical List 1

 DEPARTMENT OF COMPUTER ENGG. & INFORMATION TECHNOLOGY

PRACTICAL LIST-I

Subject: DATABASE MANGEMENT SYSTEM

1)    Create a client _master table whose structure is:

 

Column name	Data Type	Size
Client _no	Varchar2	6
Name	Varchar2	20
Address1	Varchar2	30
Address2	Varchar2	30
City	Varchar2	15
State	Varchar2	15
Pincode	Number	8
Baldue	Number	10,2

 

2)    Create a product _master table whose  structure is:

Column name	Data type	size
Product _no	Varchar2	6
Description	Varchar2	25
Profitpercent	Number	4,2
Unitmeasure	Varchar2	10
Qtyonhand	Number	8
Sellprize	Number	8,2
Costprize	Number	8,2

 

3)    Create a salesman _master table whose  structure is:

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

 

Data for Client_master Table:

Client_no	Name	City	Pincode	State	Baldue
C00001	Ivan	Mumbai	400054	Maharashtra	15000
C00002	Mamata	Madras	780001	Tamilnadu	0
C00003	Chhaya	Bharuch	392001	Gujarat	5000
C00004	Korth	Banglore	560001	Karnataka	0
C00005	Deepak	Mumbai	400060	Maharashtra	2000
C00006	Jeans	Mengalore	560050	Karnataka	0

 

Data for  Product_master:

Product_no	Description	Profitpercent	Unitmeasure	Qtyonhand	Sellprice	Costprice
P0001	T-Shirts	5	Piece	200	350	250
P0345	Shirts	6	Piece	150	500	350
P06734	Cotton Jeans	5	Piece	100	600	450
P07865	Jeans	5	Piece	100	700	500
P07868	Trousers	2	Piece	150	50	550
P07885	Pull Overs	2.5	Piece	80	700	450
P07965	Tops	5	Piece	70	300	175
P08865	Denim shirts	4	Piece	75	450	300

 

 

Data for Salesman_master Table:

Salesmanno	Name	Address1	Address2	City	Pincode	State	Salamt
S00001	Aman	A/14	Worli	Mumbai	4000002	Maharashtra	3000
S00002	Omkar	65	Nariman	Mumbai	4000001	Maharashtra	2000
S00003	Raj	P-7	Bandra	Mumbai	400032	Maharashtra	3000
S00004	Ashish	A/45	Juhu	Mumbai	400042	Maharashtra	4000

 

3. Exercise on retrieving records from a table.

a)      Find out the name of all the clients.

b)      Retrieve the entire contents of the client_master table.

c)       Retrieve the list of names, city and the state of all the clients.

d)      List the various products available from the Product_master table.

e)      List all the clients who are located in Mumbai.

f)       Find the name of salesman who have a salary equal to Rs. 3000.

 

4. Exercise on updating records in a table.

a)      Change the city of ClientNo ‘C00005’ to Bangalore.

b)      Change the Baldue of ClientNo ‘C00001’ to Rs. 1000

c)       Change the cost price of ‘Trousers’ to Rs. 950.

d)      Change the city of the salesman to Pune.

5. Exercise  on deleting records in a table.

a)      Delete all salesman_master whose salaries are equal to RS.3500.

b)      Delete all product from product_master  where the  quantity  on hand   is equal to 100.

c)       Delete from client_master where  the column state  hold the value ‘Tamil Nadu’.

6.  Exercise on altering the table structure.

a)      Add a column called ‘Telephone’ of data type  ‘number’ and size  =’10’ to the  client_master table.

b)      Change the size  of  Sellprice  column  in product master to 10,2.

         7.  Exercise on deleting the table structure along with the data

a)      Destroy the  table  client_master  along  with its data.

8.  Exercise on  renaming  the table

a)   Change the name   of the salesman_master  table  to  Sman_master.