Practical 1: AIM:-Write All The Queries For Data Definition

PRACTICAL 1
AIM:-Write all the queries for Data Definition

languauge(DDL) in RDBMS.
DDL (Data definition language)- It is used to define a
database,modify its structures after it has been created and destroy
it after it is no longer in use or needed.DDL commands are listed
below:
CREATE TABLE This command is used to create a new table .
CREATE TABLE <table name>

(<column specification>,<column specification>,
..)
create table Client_Master(Client_No varchar(6),Name

varchar(20),City varchar(15),Pincode integer(8),State
varchar(15),Bal_due integer(10));
create table Product_Master(Product_No varchar(6),Description
varchar(15),Profit_Percent varchar(4),Unit_Measure
varchar(10),Qty_On_Hand integer(8),Reorder_Lvl integer(8),Sell_Price
integer(8),Cost_Price integer(8));
create table Salesman_Master(Salesman_No varchar(6),Name
varchar(20),Address_1 varchar(30),Address_2 varchar(30),City
varchar(20),Pincode integer(8),State varchar(20),Sal_Amt
integer(8),Tgt_To_Get integer(6),Ydt_Sales integer(6),Remarks
varchar(60));
CREATE VIEW Views are logical windows for users to see the
results of operation on one or more base tables.
CREATE VIEW <view name>
[<view column names>]
As<query specification>
ALTER TABLE It is used to provide commands to add column to

tables. They also provide commands to modify the column of
tables.
1. To add new column
ALTER TABLE<table name>
ADD<column specification>
2. To modify a existing column

ALTER TABLE<table name>
MODIFY<COLUMN name>
<new details for the column>
insert into Client_master values('C00001','Ivan

Bayross','Mumbai',400054,'Maharashtra',15000);
insert into Client_master values('C00002','Mamta
Muzumdar','Madras',780001,'Tamil Nadu',0);
insert into Client_master values('C00003','Chhaya
Bankar','Mumbai',400057,'Maharashtra',5000);
insert into Client_master values('C00004','Ashwini
Joshi','Banglore',560050,'Karnataka',0);
insert into Client_master values('C00005','Hansel
Colaco','Mumbai',400060,'Maharashtra',2000);
insert into Client_master values('C00006','Deepak
Sharma','Manglore',560050,'Karnataka',0);
insert into Product_Master values('P00001','TShirts',5,'Piece',200,50,350,250);
insert into Product_Master values('P06734','Cotton

Jeans',5,'Piece',100,20,600,450);
insert into Product_Master
values('P07865','Jeans',5,'Piece',100,20,750,500);
values('P07868','Trousers',2,'Piece',150,50,850,550);
insert into Product_Master values('P07885','Pull
Overs',2.5,'Piece',80,30,700,450);
insert into Product_Master values('P07965','Denim

Shirts',4,'Piece',100,40,350,250);
insert into Product_Master values('P07975','Lycra
Tops',5,'Piece',70,30,300,175);
values('P08865','Skirts',5,'Piece',75,30,450,300);
insert into Salesman_Master
values('S00001','Aman','A/14','Worli','Mumbai',400002,'Maharashtra',
3000,100,50,'Good');
values('S00002','Omkar','65','Nariman','Mumbai',400001,'Maharashtr
a',3000,200,100,'Good');
insert into Salesman_Master values('S00003','Raj','P7','Bandra','Mumbai',400032,'Maharashtra',3000,200,100,'Good');
values('S00004','Ashish','A/5','Juhu','Mumbai',400044,'Maharashtra',3
500,200,150,'Good');
PRACTICAL 2
AIM:-Write all the queries for Data Manipulation language
(DML) in RDBMS.
DML (Data manipulation language )-DML is used to
manipulate data available in a database system .It means to
add, delete, retrieve or update data available in database .
Typical commands are listed below:
INSERT An insert commands adds a new record with
all columns in the table.
INSERT INTO<table name>
VALUES(<values>)
QUERY: - To retrieve the names of all the clients.

select name from Client_master;
QUERY: - To retrieve the entire content of the client_master table.

select *from Client_Master;
QUERY: - To retrieve the list of names ,cities and states of all the
clients.
select name,city,state from Client_Master;
QUERY: - To retrieve the various products available from the

product_master table.
select description from Product_Master;
QUERY: - To retrieve the clients who are located in Mumbai.

select *from Client_Master where city='Mumbai';
QUERY: - To change the city of salesman to Pune.

update Salesman_Master set city='Pune';
select *from salesman_master;
QUERY: - To change the city of client_no C00005 to Banglore.

update Client_Master set city='Banglore' where client_no='C00005';
update client_master set state='Karnataka' where

client_no='C00005';
select *from cleint_master where client_no='C00005';
QUERY: - To change the bal_due of client_no C00001 to Rs.1000.

update client_master set Bal_due='1000' where client_no='C00001';
select *from client_master where client_no='C00001';
QUERY: - To change the cost_price of Trousers to Rs.950.

update product_master set cost_price='950' where
description='Trousers';
select *from product_master where description='Trousers';
QUERY: -To delete all the salesman from salesman_master whose

salaries are equal to Rs.3500.
delete from salesman_master where sal_amt=3500;

select *from product_master;
QUERY: - To delete all product from product_master where quantity

on hand is equal to Rs.100.
delete from product_master where Qty_on_hand=100;
select *from product_master;
QUERY: - To delete from client_master where the column state holds

the value Tamil Nadu.
delete from client_master where state='Tamil Nadu';
select *from client_master;
QUERY: - To add a column called Telephone to the client_master

table;
Alter table client_master add(Telephone integer);
select *from client_master;
QUERY: -To destroy the table client_master with its data.
drop table client_master;

Practical 1: AIM:-Write All The Queries For Data Definition

Uploaded by

Copyright:

Available Formats

Practical 1: AIM:-Write All The Queries For Data Definition

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Practical 1: AIM:-Write All The Queries For Data Definition

Uploaded by

Copyright:

Available Formats

PRACTICAL 1

AIM:-Write all the queries for Data Definition

CREATE TABLE <table name>

create table Client_Master(Client_No varchar(6),Name

ALTER TABLE It is used to provide commands to add column to

2. To modify a existing column

insert into Client_master values('C00001','Ivan

insert into Product_Master values('P06734','Cotton

insert into Product_Master values('P07965','Denim

QUERY: - To retrieve the names of all the clients.

QUERY: - To retrieve the entire content of the client_master table.

QUERY: - To retrieve the various products available from the

QUERY: - To retrieve the clients who are located in Mumbai.

QUERY: - To change the city of salesman to Pune.

QUERY: - To change the city of client_no C00005 to Banglore.

update client_master set state='Karnataka' where

QUERY: - To change the bal_due of client_no C00001 to Rs.1000.

QUERY: - To change the cost_price of Trousers to Rs.950.

QUERY: -To delete all the salesman from salesman_master whose

delete from salesman_master where sal_amt=3500;

QUERY: - To delete all product from product_master where quantity

QUERY: - To delete from client_master where the column state holds

QUERY: - To add a column called Telephone to the client_master

QUERY: -To destroy the table client_master with its data.

drop table client_master;

You might also like