Scribd
Upload
21 views15 pages

SQL Doc

The document describes how to perform various SQL operations like creating tables, inserting records, updating records, deleting records, and retrieving records from tables. It includes examples of creating tables for clients, products, salesmen and departments. It also provides examples of inserting sample data into these tables, updating and deleting records, and writing SQL queries to retrieve specific fields and records.

Uploaded by

vaibhav gaikwad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
21 views15 pages

SQL Doc

The document describes how to perform various SQL operations like creating tables, inserting records, updating records, deleting records, and retrieving records from tables. It includes examples of creating tables for clients, products, salesmen and departments. It also provides examples of inserting sample data into these tables, updating and deleting records, and writing SQL queries to retrieve specific fields and records.

Uploaded by

vaibhav gaikwad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 15

PRACTICAL NO.

2
AIM:

DESCRIPTION: 1. syntax to create table : Create table table_name


(
Column datatype(size),
Column datatype(size)
);
2. syntax to insert values : insert into table_name
Values(value1,value2,….valueN);
3. syntax to see the table : select*from table name;
4. Syntax to see column: select column_name from table_name;
5. Syntax to update table : update table_name
Set column1=value1,…..
Where condition;
6. Syntax to delete record in a table : delete from table_name
Where condition;
7. Syntax to alter table add column : alter table table_name
Add column_name datatype;
8. Syntax to delete table along data : drop table table_name;
9. Syntax to rename the table : alter table table_name
Rename to new_table_name;

1.
clientNo name city pincode state BalDue
C00001 ivanbayross mumbai 400054 maharashtra 15000
C00002 Manta muzumdar madras 780001 Tamil nadu 0
C00003 Chhaya bankar mumbai 400057 maharashta 5000
C00004 Ashwini joshi banglore 560001 karnataka 0
C00005 Hansel colaco mumbai 400060 maharashtra 2000
C00006 Deepak sharma manglore 560050 karnataka 0

create table client_master


(
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)
);
insert into client_master(clientno,name,city,pincode,state,baldue)
values('c00001','ivan bayross','mumbai',400054,'maharashta',15000);

insert into client_master(clientno,name,city,pincode,state,baldue)


values('c00002','mamta muzumder','madars',780001,'tamil_nadu',0);

insert into client_master(clientno,name,city,pincode,state,baldue)


values('c00003','chhaya bankat','mumbai',400057,'maharashta',5000);

insert into client_master(clientno,name,city,pincode,state,baldue)


values('c00004','ashwini joshi','banglore',560001,'karnataka',0);

insert into client_master(clientno,name,city,pincode,state,baldue)


values('c00005','hansel colaco','mumbai',400060,'maharashtra',2000);

insert into client_master(clientno,name,city,pincode,state,baldue)


values('c00006,'deepak sharma','manglore',560050,'karnatakaa',0);

OUTPUT:
2.
Productno description Profit Unit QntOnHand recorderLvl Sell Cost
percent measures price price
P00001 T shirts 5 piece 200 50 350 250
P0345 shirts 6 piece 150 50 500 350
P06734 cOtton 5 piece 100 20 600 450
jeans
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 Denimshirt 4 piece 100 40 350 250
P07975 Lycra tops 5 piece 70 30 300 175
P08865 skirts 5 piece 75 30 450 300

create table ‘product_master4


(
ProductNo varchar2(6),
desrciption char(15),
profitPercent number(3),
UnitMeasure char(6),
QntOnHand numbar(4),
RecorderLvl number(2),
SellPrice number(3),
costPrice number930
);

Insert into ‘product_master4


Vaues(‘P00001’,’T-shirt’,5,’piece’,200,50,350.250);

Insert into ‘product_master4


Vaues(‘P0345’,’shirt’,6,’piece’,150,50,500.350);

Insert into ‘product_master4


Vaues(‘P06734’,’cotton jeans,5,’piece’,100,20,600.450);

Insert into ‘product_master4


Vaues(‘P07865’,’jeans’,5,’piece’,100,20,750.500);

Insert into ‘product_master4


Vaues(‘P07868’,’trousers’,2,’piece’,150,50,850.550);

Insert into ‘product_master4


Vaues(‘P07885’,’pill overs’,2,5,’piece’,80,30,700.450);
Insert into ‘product_master4
Vaues(‘P07965’,’denim shirt’,4,’piece’,80,30,700.450);

Insert into ‘product_master4


Vaues(‘P07975’,’lycra tops’,5,’piece’,70,30,300.175);

Insert into ‘product_master4


Vaues(‘P08865’,’skirts’,5,’piece’,75,30,450.300);

OUTPUT
3.
salesmanNo name Address1 Address2 city pincode state
S00001 aman A/14 worli mumbai 400002 maharashta
S00002 omkar 65 nariman mumbai 400001 maharashta
S00003 raj P-7 bandra mumbai 400032 maharashta
S00004 ashish A/5 juhu mumbai 400044 maharashta
SCHEMA:salesman_master

create table salesman_master


(
SalesmanNo varchar(6),
Name char(7),
Address1 varchar(5),
Address2 char(8),
City char(7),
PinCode number(6),
Stste char(12)
);

Insert into salesman_master


Values(‘S00001’,’aman’,’A/14’,’worli’,’mumbai’,400002,,’maharashta’);

Insert into salesman_master


Values(‘S00002’,’omkar’,’65’,’nariman’,’mumbai’,400001,,’maharashta’);

Insert into salesman_master


Values(‘S00003’,’raj’,’P-7’,’bandra’,’mumbai’,400032,,’maharashta’);

Insert into salesman_master


Values(‘S00004’,’ashish’,’A/5’,’juhu’,’mumbai’,400044,,’maharashta’);
4.
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
SCHEMA:salesman_master_2
create table salesman_master_2
(
SalesmanNo varchar(6),
SalAmt number(4),
TgtToGet number(4),
YtdSales number(4),
Remarks char(5)
);

Insert into salesman_master_2


Values(‘S00001’,3000,100,50,’good’);

Insert into salesman_master_2


Values(‘S00002’,3000,200,100,’good’);

Insert into salesman_master_2


Values(‘S00003’,3000,200,100,’good’);

Insert into salesman_master_2


Values(‘S00004’,3500,200,150,’good’);

OUTPUT
Q.3 : exercise on retrieving records from a table
A. Find out name of all clients
select name from client_master;

B. Retrieve the entire content of client_master table


Select*from client_master;

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


Select name,city,state from client_master;

D. List the various products available on ‘product_master4 tablr


Select description from ‘product_master4;

E. List of clients who are located in mumbai


Select name from client_master where city=‘mumbai’;

F. Find names of all salesman who have salary=3000


Select name from salesman_master where salary=3000;

OUTPUT:

Q.4 :exercise on updating records in a table

A. change the city of clientno C00005 to banglore


Update client_master
Set city=‘banglore’,where clientno=‘C00005’;

B. change the baldue of clientno C00001to rs.1000


Update client_master
Set baldue=1000,where clientno=‘C00001’;

C. change the cost price of trousers to ts.950


Update ‘product_master4
Set costPrice=950,where ProductNo=‘P07868’;

D.change the city of salesman to pune


Update salesman_master
Set city=‘pune’;

Q.5 exercise on deleting records in a table


create table deptart
(
dept_id number(3) primary key,
dept_nm char(20) not null,
emp_count number(2) check(emp_count<=15)
);

create table employ


(
emp_no varchar(3) primary key check(emp_no like 'e%'),
dept_no number(3) constraint c1 references deptart(dept_id) ,
sal number(2) check(sal>=5000),
gender char(1) default 'm'
);

Style2
create table employ2
(
emp_no varchar(3) ,
dept_no number(3) ,
sal number(2) ,
gender char(1) default 'm',
primary key(emp_no),
check(emp_no like 'E%'),
checK (sal>=5000),
foreign key(dept_no) references deptart(dept_id)
);
insert into deptart
values(1,'marketing',3);

insert into deptart


values(2,'digitalmarketing',4);

insert into deptart


values(3,'sales',4);

fit the value in other table also

do the voilet of every statement

You might also like