Assignment 1.
Student Name: Bcapro Roll No:000
Room000(room_no, description, rate)
Guest000(guest_no ,guest_name ,no_of_days )
SQL> create table Room000(room_no number(3)primary key, description varchar2(15), rate number(5));
Table created.
SQL> select * from Room000;
ROOM_NO DESCRIPTION RATE GUEST_NO
---------------- ------------------ ------------- ---------------
11 AC 700 103
12 Non_AC 500 102
13 1st class 800 104
14 AC 750 101
15 Non_AC 600 105
SQL> create table Guest000(guest_no number(3)primary key, guest_name varchar2(15),
no_of_days number constraints chk check (no_of_days>0));
Table created.
SQL> select * from Guest000;
GUEST_NO GUEST_NAME NO_OF_DAYS
---------- - ------------------ ------------------------
101 sachin 5
102 Ramdev 3
103 babalu 2
104 nitin 8
105 vaibhav 6
Create a Database in 3NF & write queries for following.
1)Display room details according to its rates in ascending order.
SQL> select room_no, description, rate from Room000
order by rate asc;
ROOM_NO DESCRIPTION RATE
--------------- ----------------- --------------
12 Non_AC 500
15 Non_AC 550
11 AC 700
14 AC 750
13 1st class 800
2) Find the names of guest who has allocated room for more than 3 days
SQL> select guest_name from Guest000
where no_of_days >3;
GUEST_NAME
---------------
sachin
nitin
vaibhav
3) Find no. of AC rooms.
SQL> select count(description) from Room000
where description='AC'
COUNT(DESCRIPTION)
----------------------------
3
4) Display total amount for NON-AC rooms
SQL> select sum(rate) from Room000
where description='Non_AC';
SUM(RATE)
----------------
1050
5) Find names of guest with maximum room charges.
SQL> select guest_name from Guest000,Room000
where Guest000.guest_no=Room000.guest_no
and rate=(select max(rate) from Room000);
GUEST_NAME
-------------------
Nitin
Assignment 02
Student Name:Bcapro. Roll No:000
Department000(dept_no, dept_name, location)
Employee000(emp_no, emp_name, address, salary, designation, dept_no)
SQL>create table Department000(dept_nonumber(3)primarykey,dept_name varchar2(15), location varchar2(15));
Table created.
SQL> select * from Department000;
DEPT_NO DEPT_NAME LOCATION
--------------- ------------------- ---------------------
11 Mca Baramati
12 Bca svpm coll
13 Computer V.P coll
14 B.com Supe
15 Machanical Indapur
16 civil A.Nagar
17 Bcs Pune
SQL> create table Employee000(emp_no number(3)primary key, emp_name varchar2(15),
address varchar2(10), salary number(5), designation varchar2(15),dept_no number(3),
constraints fkdept_no foreign key(dept_no) references Department000(dept_no),
constraints salchk check (salary>0));
Table created.
SQL> select * from Employee000;
EMP_NO EMP_NAME ADDRESS SALARY DESIGNATION DEPT_NO
------------- ------------------- ------------- --------------- ---------------------- ----------------
101 mr.pravin satara 8500 worker 16
102 mr.sachin nira 9700 security 15
103 Mr. Sawant Supe 10500 Prof 11
104 mr.pande mumbai 45000 principle 12
105 mr.kate malegoan 11500 Homegard 15
106 mr.wagh phaltan 25000 supervisor 16
107 mr.ladkat baramati 10500 worker 15
108 mr.darekar daund 8000 prof 11
109 mr.jadhav solapur 22000 vice.principle 12
110 mr.pawar kedgoan 12000 manager 14
111 mr.shinde jejuri 25000 assistent 13
112 mr.shelke khandaj 20000 secretary 13
Create a Database in 3NF & write queries for following.
1)Find total salary of all computer department employees.
SQL> select sum(salary) from Department000,Employee000
where Department000.dept_no=Employee000.dept_no and
dept_name='Computer';
SUM(SALARY)
----------------------
45000
2)Find the name of department whose salary is above 10000.
SQL> select distinct dept_name from Department000,Employee000
where Department000.dept_no=Employee000.dept_no and
salary>10000
DEPT_NAME
--------------------
B.com
Bca
Computer
Machanical
Mca
civil
3)Count the number of employees in each department.
SQL> select dept_name count(emp_no) from Department000,Employee000
where Department000.dept_no=Employee000.dept_no
group by dept_name;
DEPT_NAME COUNT(EMP_NO)
-------------------- ---------------------------
B.com 1
Bca 2
Computer 2
Machanical 3
Mca 2
civil 2
4)Display the maximum salary of each department.
SQL> select dept_name,max(salary) from Department000,Employee000
where Department000.dept_no=Employee000.dept_no
group by dept_name;
DEPT_NAME MAX(SALARY)
------------------ ------------------------
B.com 12000
Bca 45000
Computer 25000
Machanical 11500
Mca 10500
civil 25000
5) Display department wise employee list.
SQL> select dept_name,emp_name from Department000,Employee000
where Department000.dept_no=Employee000.dept_no
group by dept_name,emp_name;
DEPT_NAME EMP_NAME
--------------- ---------------------
B.com mr.pawar
Bca mr.jadhav
Bca mr.pande
Computer mr.shelke
Computer mr.shinde
Machanical mr.kate
Machanical mr.ladkat
Machanical mr.sachin
Mca Mr. Sawant
Mca mr.darekar
civil mr.pravin
civil mr.wagh
6) Display the name of employee who has minimum salary
SQL> select emp_name,min(salary) from Employee000;
EMP_NAME MIN(SALARY)
------------------- -----------------------
mr.darekar 8000
7) Display all the details of employee id 102
SQL>select emp_no,emp_name,address,salary,designation from Employee000
where emp_no=102;
EMP_NO EMP_NAME ADDRESS SALARY DESIGNATION
--------------- --------------- ----------------- -------------- --------------------------
102 mr.sachin nira 9700 security
8) Display all the details of Mr. Sawant.
SQL> select emp_no,emp_name,address,salary,designation from Employee000
where emp_name='Mr. Sawant';
EMP_NO EMP_NAME ADDRESS SALARY DESIGNATION
--------------- ------------------- ---------------- -------------- ------------------------
103 Mr. Sawant Supe 10500 Prof
Assignment 03
Student Name:Bcapro Roll No:000
Route000(rout_no, source, destination, no_of_stations)
Bus000(bus_no, capacity, depot_no, rout_no)
SQL> create table Route000(rout_no number(3)primary key, source varchar2(10),
destination varchar2(10), no_of_stations number(3));
Table created.
SQL> select * from Route000;
ROUT_NO SOURCE DESTINATION NO_OF_STATIONS
---------------- -------------------- ---------------------- ------------------------------
1 supe baramati 7
2 Chinchwad Katraj 6
3 Swargate Hadapsar 7
4 Nigadi Kothrud 3
5 Dund Baramati 8
6 malegoan nira 5
7 baramati sangvi 4
8 phaltan baramati 8
9 pandare malegoan 4
10 saswad veer 9
SQL> create table Bus000(bus_no number(3)primary key, capacity number(3),
depot_no number(3),rout_no number(3),
constraints fkrout_no foreign key(rout_no) references Route000(rout_no ));
Table created.
SQL> select * from Bus000;
BUS_NO CAPACITY DEPOT_NO ROUT_NO
-------------- ---------------- ---------------- ---------------
523 45 56 5
641 25 1 2
221 35 401 1
241 20 43 2
302 27 51 3
602 35 201 4
753 30 311 3
820 26 140 4
220 10 131 3
369 18 159 10
852 96 129 9
600 25 378 3
Create a Database in 3NF & write queries for following.
1) Find out the route details on which buses whose capacity is 20 runs.
SQL> select source, destination, no_of_stations from Bus000,Route000
where Bus000.rout_no=Route000.rout_no and capacity=20;
SOURCE DESTINATION NO_OF_STATIONS
------------------ ------------------- -----------------------------
Chinchwad Katraj 6
2) Display number of stations from 'Chinchwad' to ' Katraj'.
SQL> select no_of_stations from Route000
where source='Chinchwad' and destination='Katraj';
NO_OF_STATION
---------------------------
6
3) Display the route on which more than 3 buses runs.
SQL>select Route000.rout_no,source, destination from Bus000,Route000
where Route000.rout_no=Bus000.rout_no
group by Route000.rout_no,source, destination
having count (Bus000.rout_no)>3
ROUT_NO SOURCE DESTINATION
---------------- --------------- --------------------------
3 Swargate Hadapsar
4) Display number of buses of route ‘Swargate’ to ‘Hadapsar’.
SQL>select count(bus_no) from Route000,Bus000
where Route000.rout_no=Bus000.rout_no and
source='Swargate' and destination='Hadapsar';
COUNT(BUS_NO)
---------------------------
4
5) Find the bus having maximum capacity from ‘Nigadi’ to 'Kothrud'.
SQL> select bus_no,capacity from Bus000,Route000
where Route000.rout_no=Bus000.rout_no
and source='Nigadi' and destination='Kothrud'
group by bus_no,capacity
having capacity=(select max(capacity)) from Bus000,Route000;
BUS_NO CAPACITY
-------------- -------------------
602 35
Assignment 04
Student Name:Bcapro Roll No:000
Branch000(bno, bname, bcity, assets)
Account000(acc_no ,balance, bno)
SQL> create table Branch000(bno number(3)primary key,bname varchar2(15),
Bcity varchar2(15),assets number(9),constraints assetschk check( assets>0));
Table created.
SQL> select * from Branch000;
BNO BNAME BCITY ASSETS
---------- ----------------- --------------- -------------
101 Maha Bank A.Nagar 25000
102 SBI Baramati 22000
103 HDFC Pune 48000
104 Dena Indapur 100000
105 PDC Supe 80000
106 ICICI Pune 28000
SQL> create table Account000(acc_no number(8)primary key,balance number(6),
bno number(3), constraints fkbno foreign key(bno) references Branch000(bno));
Table created
SQL> select * from Account000;
ACC_NO BALANCE BNO
-------------- ----------- ----------
1000001 120000 101
1000002 100000 104
1000003 80000 102
1000004 40000 101
1000005 200000 105
1000006 350000 106
1000007 25000 105
1000008 38000 103
1000009 90000 106
1000010 35000 101
Create a Database in 3NF & write queries for following.
1)Find the maximum account balance of each branch.
SQL> select bname,max(balance) from Branch000,Account000
where Branch000.bno=Account000.bno
group by bname;
BNAME MAX(BALANCE)
----------- ----------------------
Dena 100000
HDFC 350000
Maha Bank 120000
PDC 200000
SBI 80000
2)Find branches where average account balance is more than 30000.
SQL>select bname from Branch000,Account000
where Branch000.bno=Account000.bno
group by bname
having avg(balance)>30000
BNAME
------------
Dena
HDFC
Maha Bank
PDC
SBI
3)Find names of all branches that have assets value greater than that of each branch in ‘pune’.
SQL>select bname, max(assets) from Branch000
Where bcity = ‘pune’
Group by bname;
BNAME MAX(ASSETS)
---------- -------------------
HDFC 48000
ICICI 28000
4) Decrease 3% balance on account whose balance is greater than 100000.
3 row updated;
SQL> select * from Account000;
ACC_NO BALANCE BNO
----------- ------------- ---------
1000001 120000 101
1000002 100000 104
1000003 80000 102
1000004 40000 101
1000005 200000 105
1000006 350000 103
1000007 25000 105
1000008 38000 103
1000009 90000 105
1000010 35000 101
5) Display details of branch whose city starts from 'A'.
SQL> select* from Branch000
where bcity like 'A%'
BNO BNAME BCITY ASSETS
----------- --------------- ----------- -------------
101 Maha Bank A.Nagar 25000
Assignment 05
Student Name:Bcapro Roll No:000
Donor000(donor_no, donor_name, city)
Blood_Donation000(bid,blood_group,quantity,date_of_collection, donor_no)
SQL> create table Donor000(donor_no number(5)primary key, donor_name varchar2(15),
city varchar2(10));
Table created.
SQL> select * from Donor000;
DONOR_NO DONOR_NAME CITY
------------------ ------------------------ ---------------
101 omkar malegoan
102 kale pune
103 jawalkar jejuri
104 pravin indapur
105 siddharth Mumbai
106 pramod shirur
107 vijay shrirampur
SQL> create table Blood_Donation000(bid number(5)primary key,
blood_group varchar2(8)NOT NULL,
quantity varchar2(10),date_of_collection date,donor_no number(5),
constraints fkdonor_no foreign key(donor_no) references Donor000(donor_no));
Table created.
SQL> select * from Blood_Donation000;
BID BLOOD_GR QUANTITY DATE_OF_C DONOR_NO
---------- ------------------ ----------------- ------------------ ------------------
1 A+ve 5 02-APR-13 103
2 B+ve 3 13-MAY-10 102
3 AB+ve 6 06-NOV-12 101
4 A-ve 2 15-DEC-13 105
5 O+ve 8 29-FEB-12 104
6 AB-ve 4 16-JAN-14 103
7 A+ve 6 04-JUL-13 105
8 AB-ve 6 06-AUG-13 103
9 B+ve 2 18-MAR-12 104
10 O-ve 15 25-FEB-14 102
11 AB+ve 5 25-DEC-13 101
Create a Database in 3NF & write queries for following.
1) Display total blood quantity collected on 25th December 2013.
SQL>select sum(quantity) from Blood_Donation000
where date_of_collection='25- Dec-2013';
SUM(QUANTITY)
--------------------------
5
2) Display total blood donated by each donor.
SQL>select donor_name,sum(quantity) from Blood_Donation000,Donor000
where Blood_Donation000.donor_no=Donor000.donor_no
group by donor_name;
DONOR_NAME SUM(QUANTITY)
----------------------- -------------------------
jawalkar 15
kale 18
omkar 11
pravin 10
siddharth 8
3) Display Donor details having blood group 'A+ve'.
SQL>select donor_name,city from Blood_Donation000,Donor000
where Blood_Donation000.donor_no=Donor000.donor_no
and blood_group='A+ve'
DONOR_NAME CITY
----------------------- -----------
jawalkar jejuri
siddharth mumbai
4) Display the donor who has donated blood more than two times.
SQL> select donor_name from Blood_Donation000,Donor000
where Blood_Donation000.donor_no=Donor000.donor_no
and quantity>2;
DONOR_NAME
-----------------------
jawalkar
kale
omkar
pravin
jawalkar
siddharth
jawalkar
kale
omkar
5) Display the donor information with blood group whose city name contains “sh” in it.
SQL> select donor_name from Donor000,Blood_Donation000
where Donor000.donor_no=Blood_Donation000.donor_no
and city like 'sh%';
DONOR_NAME
-----------------------
pramod
vijay
Assignment 06
Student Name:Bcapro. Roll No:000
Area000(area_name, area_type)
Person000(pno, person_name, birthdate, income)
SQL> create table area000(area_name varchar(15)primary key,area_type varchar(15),constraints chkat check(area_type in('Urban','Rural')));
Table created;
SQL> select * from area000;
AREA_NAME AREA_TYPE
------------------ -------------------
Shivajinagar Urban
Sambhajinagar Rural
Vidyanagar Rural
Gitanagar Rural
PCMC Urban
Supe Rural
Bhikobanagar Rural
Pune Urban
SQL> Create table person000(pno number(5)primary key,pname varchar(15),
Birthdate date,income number(7),constraints chkin check(income>0)
Constraints fkarea_name foreign key(ara_name) references area000(area_name));
Table created;
SQL> select * from person000;
PNO PNAME BIRTHDATE INCOME AREA_NAME
----------- --------------- ------------------- ------------- -------------------
101 Sachin 01-MAR-95 25000 Shivajinagar
102 omkar 22-DEC-92 25000 Shivajinagar
103 Akshay 16-MAY-90 20000 Gitanagar
104 Chetan 14-NOV-89 15000 Vidyanagar
105 Pramod 20-JUN-94 50000 Supe
107 Amol 22-NOV-93 8500 Pune
108 Sanjay 15-JAN-95 9000 PCMC
Create a Database in 3NF & write queries for following.
1)Display persons having income less than 1 lakhs in PCMC Area.
SQL> select pname from area000,person000
where area000.area_name=person000.area_name
and (income<100000) and area000.area_name='PCMC';
PNAME
-----------
Sanjay
2)Display population of each area.
SQL>select area000.area_name,count(pname)from area000,person000
where area000.area_name=person000.area_name
group by area000.area_name
AREA_NAME COUNT(PNAME)
------------------- -----------------------
Gitanagar 1
Supe 1
PCMC 1
Pune 1
Shivajinagar 2
Vidyanagar 1
3)Display persons details from 'Urban' area.
SQL> select pno,pname,birthdate,income from area000,person000
where area000.area_name=person000.area_name
and area_type='Urban';
PNO PNAME BIRTHDATE INCOME
-------- -------------- ------------------- --------------
101 Sachin 01-MAR-95 25000
102 omkar 22-DEC-92 25000
107 Amol 22-NOV-93 8500
108 Sanjay 15-JAN-95 9000
4)Display details of person from each area having minimum income.
SQL> select area000.area_name, pno,pname,birthdate,min(income)from person000,area000
where area000.area_name=person000.area_name
group by area000.area_name,pno,pname,birthdate;
AREA_NAME PNO PNAME BIRTHDATE MIN(INCOME)
------------------- -------------------- ------------------- --------------------
Gitanagar 103 Akshay 16-MAY-90 20000
Supe 105 Pramod 20-JUN-94 50000
PCMC 108 Sanjay 15-JAN-95 9000
Pune 107 Amol 22-NOV-93 8500
Shivajinagar 101 Sachin 01-MAR-95 25000
Shivajinagar 102 omkar 22-DEC-92 25000
Vidyanagar 104 Chetan 14-NOV-89 15000
Assignment 07
Student Name:Bcapro. Roll No:000
Plan000(plan_no, plan_name,nooffreecalls,freecalltime,fix_amt)
Customer000(cust_no, cust_name, mobile_no, plan_no)
SQL> create table Plan000(plan_no number(4)primary key,plan_name varchar2(15),
no_of_free_calls number(5),freecalltime number(5),fix_amt number(4));
Table created.
SQL> select * from Plan000;
PLAN_NO PLAN_NAME NO_OF_FREE_CALLS FREECALLTIME FIX_AMT
---------------- -------------------- -------------------------------- -------------------------- ---------------
1 SMS 25 10 53
2 Internet 15 20 109
3 3G 41 100 60
4 GPRS 25 10 25
5 Night Pack 25 10 20
6 Free Call 300 2 150
7 Go Max 35 5 50
8 Internet 45 12 6000
9 lets_rock 10 2 50
10 go min 8 2 40
SQL> create table Customer000(cust_no number(3),cust_name varchar2(10),
mobile_no number(11),plan_no number(4),
constraints fkplan_no foreign key(plan_no) references Plan000(plan_no));
Table created.
SQL> select * from Customer000;
CUST_NO CUST_NAME MOBILE_NO PLAN_NO
--------------- ------------------- ---------- -------- -------------
101 sachin 8379015521 2
102 Mahesh 9825501100 1
103 chetan 9879015521 3
104 ranjit 9579125521 2
105 umesh 9879015528 5
106 omkar 7379015525 6
107 shubham 8379015531 8
108 sharad 8079015593 7
109 sagar 9850015529 8
109 pramod 9527015529 4
110 John 8380015529 1
111 John 8798015529 7
112 akshay 8379832971 10
Create a Database in 3NF & write queries for following.
1)Display the plan having minimum response.
SQL> select plan_no,plan_name,min(freecalltime) from plan000
group by plan_no,plan_name
PLAN_NO PLAN_NAME MIN(FREECALLTIME)
----------- -------------------- ------------------------------
1 SMS 10
2 Internet 20
3 3G 100
4 GPRS 10
5 Night Pack 10
6 Free Call 2
7 Go Max 5
8 Internet 12
2)Display customer details starting their mobile number with 98
SQL> select * from customer000
where mobile_no like'98%'
CUST_NO CUST_NAME MOBILE_NO PLAN_NO
---------------- ------------------ ------------------ -------------
102 Mahesh 9825501100 1
103 chetan 9879015521 3
105 umesh 9879015528 5
109 sagar 9850015529 8
3)Display the customer details that are getting less number of free calls than that of the plan ‘Let’s Rock’.
SQL>select cust_no,cust_name,mobile_no from Customer000,Plan000
where Customer000.plan_no=Plan000.plan_no and
no_of_free_calls<10
CUST_NO CUST_NAME MOBILE_NO
--------- ------------------ -----------------
111 akshay 8379832971
4)Delete the details of ‘John’ who has stopped ‘Go Max’ plan.
1 row deleted.
SQL> select * from Customer000;
CUST_NO CUST_NAME MOBILE_NO PLAN_NO
---------------- ----------------- ----------------------- -------------
101 sachin 8379015521 2
102 Mahesh 9825501100 1
103 chetan 9879015521 3
104 ranjit 9579125521 2
105 umesh 9879015528 5
106 omkar 7379015525 6
107 shubham 8379015531 8
108 sharad 8079015593 7
109 sagar 9850015529 8
109 pramod 9527015529 4
111 akshay 8379832971 10
5)Find the plan whose fixed amount is greater than 5000.
SQL> select plan_name from Plan000
where fix_amt>5000;
PLAN_NAME
---------------
Internet
Assignment 08
Student Name:Bcapro. Roll no:000
Book000(book_no, book_name, price)
Publisher000(pno, pname, city)
Book_Pub000(book_no, pno, quantity)
SQL> create table Book000(book_no number(5)primary key,book_name varchar2(10),price number(3),constraints chkprice check (price>0));
Table created.
SQL> select * from Book000;
BOOK_NO BOOK_NAME PRICE
--------------- ------------------- ------------
10001 DBMS 150
10102 C Prog. 140
10203 O.B. 120
10305 E-com 80
10406 Account 180
10806 Java 150
10702 .Net 140
SQL>create table Publisher000(pno number(3)primary key,pname varchar2(10),
city varchar2(10));
Table created.
SQL> select * from Publisher000;
PNO PNAME CITY
---------- ------------- ------------
101 BPV Mumbai
102 Vision Pune
103 Nirali Delhi
104 Thakur Haidrabad
105 Techmax Baramati
106 Iindu Kolhapur
SQL> create table Book_Pub000(book_no number(5),pno number(3), quantity number(4),
constraints fkbook_no foreign key(book_no) references Book000(book_no),
constraints fkpno foreign key(pno) references Publisher000(pno));
Table created.
SQL> select * from Book_Pub000;
BOOK_NO PNO QUANTITY
------------ --------- --------------
10001 101 5
10102 103 4
10203 104 11
10305 105 15
10406 101 10
10305 106 20
10806 102 25
10702 104 35
10806 101 25
Create a Database in 3NF & write queries for following.
1)Display total quantity of each book .
SQL> select book_name,count(quantity) from Book000,Publisher000,Book_Pub000
where Book000.book_no=Book_Pub000.book_no
and Publisher000.pno=Book_Pub000.pno
group by book_name;
BOOK_NAME COUNT(QUANTITY)
----------------- ----------------------------
.Net 1
Account 1
C Prog. 1
DBMS 1
E-com 2
Java 1
O.B. 1
2)Display Publisher names from 'Pune'.
SQL> select pname from Publisher000
where city='Pune';
PNAME
----------
Vision
3)Display all publisher publishing more than two books.
SQL>select pname from Book000,Publisher000,Book_Pub000
where Book000.book_no=Book_Pub000.book_no
and Publisher000.pno=Book_Pub000.pno
group by pname having count(pname)>2;
PNAME
----------
BPV
Iindu
Nirali
Techmax
Thakur
Vision
4)Display publisher having average books price less than average books price of ‘BPV Publications’
SQL>select pname from Book000,Publisher000,Book_Pub000
Where Book000.book_no=Book_Pub000.book_no and
Publisher000.pno=Book_Pub000.pno and
Pname=‘BPV’
having avg< price;
PNAME
----------
Vision
Nirali
Thakur
Techmax
Iindu
5)Display publisher wise book details.
SQL> select pname,Book000.book_no book_name,price from Book000,Publisher000,Book_Pub000
Where Book000.book_no=Book_Pub000.book_no and
Publisher000.pno=Book_Pub000.pno
group by pname,Book000.book_no,book_name,price;
PNAME BOOK_NAME PRICE
---------- ----------------- ----------
BPV 10001 150
BPV 10406 180
Iindu 10305 80
Nirali 10102 140
Techmax 10305 80
Thakur 10203 120
Thakur 10702 140
Vision 10806 150
Assignment 09
Student Name:Bcapro . Roll No:000
Item000(item_no, item_name, quantity)
Suppliers000(sup_no, sup_name, address, city, phone_no)
SQL>create table item000(it_no number(3)primary key,it_name varchar(15),
quantity number(3));
Table Created.
SQL> select * from item000;
IT_NO IT_NAME QUANTITY
------------ -------------------- -------------------
1 Washing Machine 10
2 Refrigerator 15
3 TV 25
4 Laptop 5
5 Tablet 51
6 AC 5
7 Fan 65
8 Radio 100
9 Car 10
10 Bike 15
11 DVD 1
SQL>create table suppliers000(sup_no number(5)primary key,sup_name varchar(15),address varchar(15),city varchar(15),ph_no number(15));
Table Created.
SQL> select * from suppliers000;
SUP_NO SUP_NAME ADDRESS CITY PH_NO
------------- -------------------- -------------------- --------------- ---------------------
101 Mr.Tambe Supe Baramati 9922999945
102 Mr.Pavan Asu Phaltan 8975495097
103 Mr.Yogesh Pandare Baramati 9730623868
104 Mr.Garud Pandare Baramati 8605070848
105 Mr.Chavan Chavan-Vasti Malegaon 9960320796
107 Mr.Jadhav Baramati Baramati 9970565402
108 Mr.Gorde Pune Pune 8554831382
109 Mr.Phalke Asu Phaltan 9960456578
110 Mr.Jagdale Patas Baramati 9923074242
SQL>create table item_sup000(it_no number(5),sup_no number(5),rate number(5),
discount number(5),constraints fk2it foreign key(it_no)references item000(it_no),
constraints fk2sp foreign key(sup_no)references suppliers000(sup_no));
Table Created.
SQL> select * from item_sup000;
IT_NO SUP_NO RATE DISCOUNT
------------- -------------- --------------- -------------------
1 101 15000 20
2 102 20000 15
1 103 14000 25
3 103 12000 5
4 104 5600 3
5 105 25000 25
6 103 45000 45
1 107 16000 20
Create a Database in 3NF & write queries for following.
1)Delete items having quantity less than 2.
SQL> delete from item000
where quantity<2;
1 row deleted.
SQL> select * from item000;
IT_NO IT_NAME QUANTITY
------------- ---------------------- ----------------
1 Washing Machine 10
2 Refrigerator 15
3 TV 25
4 Laptop 5
5 Tablet 51
6 AC 5
7 Fan 65
8 Radio 100
9 Car 10
10 Bike 15
2)Display total number of suppliers who are supplying ‘Refrigerator’.
SQL>select count(sup_name)from item000,suppliers000,item_sup000
where item000.it_no=item_sup000.it_no and suppliers000.sup_no=item_sup000.sup_no
and it_name='Refrigerator'
COUNT(SUP_NAME)
-------------------------------
1
3)Display all suppliers supplying ‘Washing Machine’ with minimum cost.
SQL>select sup_name,min(rate) from item000,suppliers000,item_sup000
where item000.it_no=item_sup000.it_no and suppliers000.sup_no=item_sup000.sup_no and it_name='Washing Machine'group by sup_name;
SUP_NAME MIN(RATE)
-----------------------------------------
Mr.Jadhav 16000
Mr.Tambe 15000
Mr.Yogesh 14000
4)Give supplier details who give maximum discount on each item.
SQL>select sup_name,address,city,ph_no,it_name,max(discount)from item000,suppliers000,item_sup000 where item000.it_no=item_sup000.it_no
and suppliers000.sup_no=item_sup000.sup_no
group by sup_name,address,city,ph_no,it_name;
SUP_NAME ADDRESS CITY PH_NO IT_NAME MAX(DISCOUNT)
------------------ --------------- ------------- ------------------ ----------------- -------------------
Mr.Chavan Chavan-Vasti Malegaon 9960320796 Tablet 25
Mr.Garud Pandare Baramati 8605070848 Laptop 3
Mr.Jadhav Baramati Baramati 9970565402 Washing Machine 20
Mr.Pavan Asu Phaltan 8975495097 Refrigerator 15
Mr.Tambe Supe Baramati 9922999945 Washing Machine 20
Mr.Yogesh Pandare Baramati 9730623868 AC 45
Mr.Yogesh Pandare Baramati 9730623868 TV 5
Mr.Yogesh Pandare Baramati 9730623868 Washing Machine 25
5)List suppliers supplying maximum number of item.
SQL>select sup_name,max(it_no) from item000,suppliers000,item_sup000
where item000.it_no=item_sup000.it_no and
suppliers000.sup_no=item_sup000.sup_no
group by sup_name;
SUP_NAME MAX(IT_NO)
----------------- -------------------
Mr.Yogesh 3
Assignment 10
Student Name:Bcapro Roll No:000
Wholesaler000(w_no, w_name, address, city)
Product000(product_no, product_name, rate)
SQL>create table wholesaler000(w_no number(4),w_name varchar(15),address varchar(15),city varchar(15),constraints pk2w primary key(w_no));
Table Created.
SQL> select * from wholesaler000;
W_NO W_NAME ADDRESS CITY
---------- --------------- ---------------- -------------
1 Sanjay Supe Baramati
2 Omkar Baramati Baramati
3 Sharad Bhigwan Indapur
4 Pramod Sherewadi Baramati
5 Sanket Pandare Baramati
6 Shubham Phaltan Solapur
7 Pranil Phaltan Baramati
8 Chetan Malegoan Pune
9 Mr.Khabia Pune Pune
10 mr.shah Delhi mumbai
SQL>create table product000(p_no int primary key,p_name varchar(15),rate number(3));
Table Created.
SQL> select * from product000;
P_NO P_NAME RATE
---------- --------------- ------------------------
101 Moniter 2500
102 CPU 15000
103 Mouse 150
104 keyboard 700
105 DVD 3000
106 Mobile 5000
107 laptop 40000
108 headset 700
109 tv 40000
110 printer 5000
SQL> create table w_prod000(w_no int,p_no int,quantity number(3),
constraint fk2p foreign key(w_no)references wholesaler000(w_no),
constraint fk2e foreign key(p_no) references product000(p_no));
Table Created.
select * from w_prod000;
W_NO P_NO QUANTITY
----------- ---------- ----------------
1 101 50
2 102 100
3 103 150
9 101 200
4 104 60
5 105 70
6 106 80
7 104 90
8 104 100
10 104 120
Create a Database in 3NF & write queries for following.
1.Display total number of wholesaler of each product.
SQL>select p_name,count(w_name) from wholesaler000,product000,w_prod000
where wholesaler000.w_no=w_prod000.w_no and product000.p_no=w_prod000.p_no
group by p_name
P_NAME COUNT(W_NAME)
------------- ----------------------------
CPU 1
DVD 1
Mobile 1
Moniter 4
Mouse 1
keyboard 4
2.Display total quantity of each product sold by ‘Mr. Khabia’.
SQL>select p_name,sum(quantity)from wholesaler000,product000,w_prod000
where wholesaler000.w_no=w_prod000.w_no and product000.p_no=w_prod000.p_no
and w_name='Mr.Khabia'
group by p_name
P_NAME SUM(QUANTITY)
--------------- ----------------------------
Moniter 200
3)Decrement rate of all products by 5% supplied by wholesaler from 'Pune ' city.
SQL>update wholesaler000,product000,w_prod000 SET
where wholesaler000.w_no=w_prod000.w_no and product000.p_no=w_prod000.p_no
and rate=rate-0.05 and city='pune'
3 rows updated
select * from wholesaler000;
W_NO W_NAME ADDRESS CITY
---------- --------------- ------------------ ---------------
1 Sanjay Supe Baramati
2 Omkar Baramati Baramati
3 Sharad Bhigwan Indapur
4 Pramod Sherewadi Baramati
5 Sanket Pandare Baramati
6 Shubham Phaltan Solapur
7 Pranil Phaltan Baramati
8 Chetan Malegoan Pune
9 Mr.Khabia Pune Pune
10 mr.shah Delhi mumbai
11 mayur Kedgoan Dund
select * from product000;
P_NO P_NAME RATE
--------- --------------- ----------------
101 Moniter 2500
102 CPU 15000
103 Mouse 150
104 keyboard 700
105 DVD 300
106 Mobile 5000
107 laptop 36100
108 headset 665
109 tv 36100
110 printer 5000
111 Moniter 2375
4)Display wholesaler from 'Pune' city and supplying 'Monitor'.
SQL>select w_name from wholesaler000,product000,w_prod000
where wholesaler000.w_no=w_prod000.w_no and product000.p_no=w_prod000.p_no
and p_name='Moniter'and city='pune';
W_NO W_NAME ADDRESS CITY
--------- --------------- --------------- -----------
11 Mayur Kedgoan Dund
