DBMS_slips
DBMS Practical Slips
Slip 1: Consider the following Entities and Relationships
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
Relation between Customer and Loan is Many to Many
Constraint: Primary key, loan_amt should be > 0.
Create table customer
(custnonumber(4) primary key,
cnamevarchar(20),dd
address varchar(20),
city varchar(20)
);
Insert into customer values(101,’bajirao’,’Maharastra chowk’,’Pimpri’);
Insert into customer values(102,’Seeta’,’Bhosari’,’Pimpri’);
Insert into customer values(103,’Geeta’,’Navi Peth’,’Nashik’);
Insert into customer values(104,’Sachin’,’Sagavi’,’Nashik’);
Create table loan
(loannonumber(4) primary key,
loanamtnumber(10) constraint loanchk check(loanamt>0)
);
Insert into loan values(201,’10000012’);
Insert into loan values(202,’32467236’);
Insert into loan values(203,’348118447’);
Insert into loan values(204,’342349223’);
Insert into loan values(205,’5697689122’);
Create table cl
(custnonumber(4) references customer(custno),
loannonumber(4) references loan(loanno));
Insert into cl values (101,201);
Insert into cl values (101,202);
Insert into cl values (102,203);
Insert into cl values (104,205);
Insert into cl values (103,201);
· Find details of all customers whose loan is greater than 10 lakhs.
Select distinct cname,address,city,loanamt
From customer,loan,cl
Where customer.custno=cl.custno and
loan.loanno=cl.loanno and
loanamt> 1000000;
· List all customers whose name starts with 'ba'.
Select distinct cname
From customer,loan,cl
Where cname like 'ba%' and
customer.custno=cl.custno and
loan.loanno=cl.loanno ;
· Display details of customer maximum loan amount.
Select distinct customer.custno,cname,address,city,loanamt
From customer,loan,cl
Where customer.custno=cl.custno and
loan.loanno=cl.loanno and
loanamt=(select max(loanamt) from loan);
· Calculate total of all loan amount
Select sum(loanamt)
from customer, loan, cl
where customer.custno=cl.custno and
loan.loanno=cl.loanno;
· List names of all customers in descending order who has taken a loan in Nasik city.me
Select distinct cname,city
From customer,loan,cl
Where customer.custno=cl.custno and
loan.loanno=cl.loanno and
city=’Nashik’ order by cnamedesc;
Slip 2 Consider the following Entities and Relationships
Department (dept_no, dept_name, location)
Employee (emp_no, emp_name, address, salary, designation)
Relation between Department and Employee is One to Many
Constraint: Primary key, salary should be > 0
Create table dept
(dnonumber(4) primary key,
Dnamevarchar(20),
location varchar(20));
insert into dept values(101,’computer’,’pune’);
insert into dept values(102,’finance’,’pimpri’);
insert into dept values(103,’computer’,’nashik’);
insert into dept values(104,’warehouse’,’pune’);
insert into dept values(105,’account’,’bhosali’);
create table emp11
(enonumber(4) primary key,
enamevarchar(20),
address varchar(20),
salary number(6) constraint salch11 check (salary>0),
designation varchar(20),
dnonumber(4) references dept(dno));
insert into emp11 values(201,’seema’,’Pimpri’,’10253’,’Manager’,’101’);
insert into emp11 values(202,’alok’,’pimpri’,’356854’,’HR’,’102’);
insert into emp11 values(203,’anil’,’nashik’,’45854’,’IT’,’103’);
insert into emp11 values(204,’rahul’,’pune’,’56253’,’HR’,’104’);
insert into emp11 values(205,’atual’,’bhosali’,’1253’,’Supervicer’,’105’);
insert into emp11 values(206,’geeta’,’Pimpri’,’10223’,’Manager’,’101’);
write queries for following.
· Find total salary of all computer department employees.
Select sum(salary)as total_salary
From emp, dept
Where emp.dno=dept.dno and
dname=’computer’;
· Find the name of department whose salary is above 10000.
Select distinct dname,salary
Fromemp, dept
wheredept.dno=emp.dno and
salary>10000;
· Count the number of employees in each department.
Select dname,count(*) as count
from emp,dept
wheredept.dno=emp.dnogroup by dname;
· Display the maximum salary of each department.
select max(salary),dname from emp,dept
where dept.dno=emp.dno group by dname;
· Display department wise employee list.
select distinctdname, ename
from emp, dept
wheredept.dno=emp.dnoorder by dname;
Slip 3 Consider the following Entities and Relationships [30 Marks]
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD)
Relation between Project and Department is Many to One
Constraint: Primary key.
Project Status Constraints: C – completed,
P-Progressive, I-Incomplete
create table dep1
(dnonumber(4)primary key,
dnamevarchar(20),
HOD varchar(10)
);
insert into dep1 values (101,'computer','Mr.Desai');
insert into dep1 values(102,'math','sanjay');
insert into dep1 values(103,'computer','rohit');
insert into dep1 values(104,'stat','pawar');
insert into dep1 values(105,'electronics','rakesh');
create table pro1
(pnonumber(4)primary key,
pnamevarchar(20),
sdate date,
budget number(5),
status varchar(25),
dnonumber(4) references dep1(dno));
insert into pro1 values(1,'mathematics',’1 mar 2013’,’500000’,'C',102);
insert into pro1 values(2,'cost',’23 feb 2014’,’3437’,'I',104);
insert into pro1 values(3,'mathematics',’3 apr 2011’,’2345096’,'P',102);
insert into pro1 values(4,'eng',’11 Nov 2013’,’23431’,’C’,105);
insert into pro1 values(5,’programming',’14 oct 2011’,’23453’, 'C',101);
Create a Database in 3NF & write queries for following.
· List the project name and department details worked in projects that are ‘Complete’.
select pname,dname,HOD,status from dep1,pro1
where dep1.dno=pro1.dno and
status=’C’;
· Display total budget of each department.
Select dname, sum(budget) from dep1,pro1
where dep1.dno=pro1.dno group by dname;
· Display incomplete project of each department
Select dname, status from dep1,pro1
Where status=’I’and
dep1.dno=pro1.dno group by dname,status;
· Find the names of departments that have budget greater than 50000
Select dname, budget from dep1,pro1
Where budget> 50000 and
dep1.dno=pro1.dno;
· Display all project working under 'Mr.Desai'.
Select pname from dep1,pro1
Where HOD='Mr.Desai' and
dep1.dno=pro1.dno;
Slip 4
Consider the following Entities and Relationships [30 Marks]
Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
Relation between Room and Guest is One to One.
Constraint: Primary key, no of days should be > 0.
create table room
(rnonumber(4)primary key,
roomtypevarchar(20),
rate varchar(10));
insert into room values(1,'ac',500);
insert into room values(2,'nonac',1500);
insert into room values(3,'ac',300);
insert into room values(4,'ac',800);
insert into room values(5,'nonac',600);
create table guest
(gnonumber(4),
gnamevarchar(10),
no_of_daysvarchar(22) constraint no_day check(no_of_days>0));
insert into guest values(1,'akshay',5);
insert into guest values(2,'sanjay',3);
insert into guest values(3,'raje',1);
insert into guest values(4,'rohit',5);
insert into guest values(5,'mane',4);
Create a Database in 3NF & write queries for following.
· Display room details according to its rates in ascending order.
select * from room order by rateasc;
· Find the names of guest who has allocated room for more than 3 days
select distinctgname from guest
where no_of_days>3;
.
· Find no. of AC rooms.
select distinct rno,roomtype from room,guest
where guest.gno=room.rno and
roomtype=’ac’;
· Display total amount for NON-AC rooms.
select roomtype, sum(rate) from room
where roomtype=’nonac’ group by roomtype;
· Find names of guest with maximum room charges.
select gnamefromguest,room
where guest.gno=room.rno and
rate=( select max(rate) from room);
Slip 5Consider the following Entities and Relationships [30 Marks]
Book (Book_no, title, author, price, year_published)
Customer (cid, cname, addr)
Relation between Book and Customer is Many to Many with quantity as
descriptive attribute.
Constraint: Primary key, price should be >0.
create table book
(bnonumber(4) primary key,
title varchar(20),
author varchar(20),
price number(5) constraint prchk check(price>0),
pu_yearnumber(4)
);
Insert into book values(101,’C programming’, ’Yashwant Kantekar’,’524’,’1995’);
Insert into book values(102,’Database’,’Korth’,’124’,’2013’);
Insert into book values(103,’Finance Accounting’,’Sachin’,’322’,’2014’);
Insert into book values(104,’OB’,’Alok’,’824’,’2015’);
Insert into book values(105,’E Commerce’,’Khan’,’254’,’2012’);
Create table cust
(cidnumber(4) primary key,
cnamevarchar(20),
addrvarchar(20)
);
Insert into cust values(201,’alok’,’pune’);
Insert into cust values(202,’atual’,’pimpri’);
Insert into cust values(203,’neetin’,’pune’);
Insert into cust values(204,’seema’,’hadapsar’);
Insert into cust values(205,’geeta’,’bhosari’);
Create table bc1
(bnonumber(4) references book(bno),
cidnumber(4) references cust(cid),
quantity number(3)
);
Insert into bc1values(101,201,3);
Insert into bc1values(102,202,2);
Insert into bc1values(103,203,4);
Insert into bc1values(104,204,1);
Insert into bc1values(105,205,30);
Create a Database in 3NF & write queries for following.
· Display customer details from 'Pune’.
select * from cust
where addr =’pune’;
· Display author wise details of book.
Select author,bno,title,price,pu_year
from book order by author;
· Display all customers who have purchased the books published in the year 2013.
Select cname,pu_year
From cust,book,bc1
wherecust.cid=bc1.cid and
book.bno=bc1.bno and
pu_year=’2013’;
· Display customer name that has purchased more than 3 books.
Select distinct cname,title, quantity fromcust,book,bc1
Where cust.cid=bc1.cid and
book.bno=bc1.bno and
quantity>3;
· Display book names having price between 100 and 200 and published in the year 2013.
select title,price
frombook
where price between 100 and 200 and
pu_year=’2013’;
Slip 6
Property (pno, desc, area, rate)
Owner (owner_name, addr, phno)
Relation between owner and Property is One to Many.
Constraint: Primary key, rate should be > 0
Create a Database in 3NF & write queries for following.
create table property1
(pnonumber(4) primary key,
desvarchar(20),
areavarchar(20),
rate number(10) constraint rtchk1 check(rate>0)
);
insert into property1 values(1,’landline’,’chinchwad’,’10000’);
insert into property1 values (2,’House’,’chinchwad’,’10450’);
insert into property1 values (3,’landline’,’pune’,’10540’);
insert into property1 values (4,’Agriculturalland’,’hdapsar’,’14500’);
insert into property1 values (5,’House’,’pune’,’10626’);
create table owner1
(ownernamevarchar(20),
addressvarchar(20),
phnonumber(10),
pnonumber(4) REFERENCES property1(pno));
insert into owner1 values('patil','chinchwad',1234567,1);
insert into owner1 values('seema','chinchwad',45342333,2);
insert into owner1 values('seeta','pune',12334237,3);
insert into owner1 values('suhas','hadapsar',123213567,4);
insert into owner1 values('patil','pune',1234567,5);
insert into owner1 values('seeta','pune',12334237,2);
· Display area wise property details
Select distinct area,ownername,des,rate from property, owner
Where property.pno=owner.pno order by area;
· Display property owned by 'Mr.Patil' having minimum rate.
Select distinct desfrom property1, owner1
Where property1.pno=owner1.pnoand
rate=(select min(rate) from property1, owner1
Where property1.pno=owner1.pno and ownername=’patil’);
· Display all properties with owner name that having highest rate of properties located in Chinchwad area.
Select distinct ownername,des from property1, owner1
Where property1.pno=owner1.pno and
rate=(select max(rate) from property1 where area=’chinchwad’ );
· Display owner name having maximum no. of properties
Select distinct ownername, count(*) from property1, owner1
Where property1.pno=owner1.pno group by ownername
Having max(owner1.pno)= (select count (*) from property1);
Slip 7
Employee (emp_no, name, skill, payrate, workdate)
Position (posting_no, skill)
Relation between Employee and Position is Many to Many with day and shift as descriptive attribute.
Constraint: Primary key, payrate should be > 0
create table employee11
(enonumber(4) primary key,
namevarchar(15),
skillvarchar(15),
payratenumber(10) constraint paychk check (payrate>0),
workdate date
);
Insert into employee11 values(1,’seema’,’manager’,25423,’1 Mar 2010’);
Insert into employee11 values(2,’sheeta’,’supervisor’,56478,’2 Feb 2012’);
Insert into employee11 values(3,’geeta’,’cleark’,65423, ’13 Dec 1999’);
Insert into employee11 values(4,’suhas’,’chef’,55423, ’21 Nov 2013’);
Insert into employee11 values(5,’sachin’,’waiter’,27423, ’11 Oct 2012’);
create table position
(pnonumber(4) primary key,
skillvarchar(15)
);
Insert into position values(201,’manager’);
Insert into positionvalues(202,’supervisor’);
Insert into position values(203,’cleark’);
Insert into positionvalues(204,’chef’);
Insert into positionvalues(205,’waiter’);
Create table ep
(enonumber(4) references employee11(eno),
pnonumber(4) references position(pno),
dayvarchar(10),
shiftvarchar(10)
);
Insert into ep values (1,201,’Monday’,’allocate’);
Insert into ep values (2,202,’Tuesday’,’notallocat’);
Insert into ep values (3,203,’Friday’,’allocate’);
Insert into epvalues (4,204,’Monday’,’allocate’);
Insert into ep values (5,205,’Friday’,’notallocat’);
Create a Database in 3NF& write queries for following.
· Find the names and rate of pay all employees who allocated a duty.
Select distinct name,payrate from position, employee11,ep
Where employee11.eno=ep.eno and
position.pno=ep.pno and
shift=’allocate’;
· Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.
Select ep.eno from position, employee11,ep
Where employee11.eno=ep.eno and
position.pno=ep.pno and
ep.pno=’201’ and
position.skill<>’waiter’;
· Display a list of names of employees who have skill of chef and who has assigned a duty.
Select name from position, employee11,ep
Where employee11.eno=ep.eno and
position.pno=ep.pno and
position.skill=’chef’ and
shift=’allocate’;
· Display emp_no and dates for all employees who are working on Tuesday and at least one other day.
Select ep.eno, workdate from position, employee11,ep
Where employee11.eno=ep.eno and
position.pno=ep.pno and
day in (’Tuesday’,’Friday’);
· Display shiftwise employee details.
Select shift,ep.eno, name,position.skill,workdate from position, employee11,ep
Where employee11.eno=ep.eno and
position.pno=ep.pno order by shift;
Slip 8
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
Relation between Bill and Menu is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
create table bill
(bnonumber(4) primary key,
day date,
tablenonumber(4),
total number(7)
);
Insert into bill values(301,’8 Jan 2013’,1,1000);
Insert into bill values(302,’1 Dec 2013’,2,1020);
Insert into bill values(303,’23 Feb 2014’,3,3424);
Insert into bill values(304,’8 Jan 2013’,2,2422);
Insert into bill values(305,’30 Nov 2013’,6,1000);
create table menu
(dnonumber(4) primary key,
ddescvarchar(20),
price number(5) constraint pchk check(price>0)
);
Insert into menu values(211,’Pav Bhaji’,600);
Insert into menu values(212,’Panner Mahkanwala’,634);
Insert into menu values(213,’Roti’,20);
Insert into menu values(214,’Kaju Masala’,345);
create table bm1
(bnonumber(4) references bill(bno),
dnonumber(4) references menu(dno),
quantity number(10));
Insert into bm1values(301,211,3);
Insert into bm1values(302,212,2);
Insert into bm1values(302,213,4);
Insert into bm1values(303,214,1);
Insert into bm1values(303,213,5);
Insert into bm1values(304,212,1);
Insert into bm1values(304,213,4);
Create a Database in 3NF & write queries for following.
· Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu.
Select bm1.bno,ddesc,price,total, quantity from bill,menu,bm1
Where bm1.bno=bill.bno and
bm1.dno=menu.dno order by ddesc;
· Find total amount collected by hotel on date 08/01/2013
Select sum (price) from bill,menu,bm1
Where bm1.bno=bill.bno and
bm1.dno=menu.dno and
day=’8 Jan 2013’;
· Count number of menus of billno 301.
Select count(bm1.dno) from bill,menu,bm1
Where bm1.bno=bill.bno and
bm1.dno=menu.dno and
bm1.bno=’301’;
· Display menu details having price between 100 and 500.
Select bm1.bno,ddesc,price,total from bill,menu,bm1
Where bm1.bno=bill.bno and
bm1.dno=menu.dno and
price between 100 and 500;
· Display total number of bills collected from each table on 01/12/2013.
Select count (bm1.bno), tableno from bill,menu,bm1
Where bm1.bno=bill.bno and
bm1.dno=menu.dno and
day=’1 Dec 2013’ group by tableno;
Slip 9
Musician (mno, mname, addr, phno)
Album (title, copy_right_date, format)
Relation between Musicians and Album is One to Many.
Constraint: Primary key.
create table musician
(mnonumber(4) primary key,
namevarchar(20),
addrvarchar(20),
phnonumber(10)
);
insert into musician values (1,'A RRehman','Pune',123456);
insert into musician values (2,'Arijit Singh’,'Bombay',2323413);
insert into musician values (3,’Nihira Joshi’,'Nashik',344553);
insert into musician values (4,’Anu Malik’,'Pune',12324231);
create table album
(titlevarchar(20),
codate date,
formatvarchar(20),
mnonumber(4) REFERENCES musician(mno));
insert into album values('track1','2 March 2010','audio',1);
insert into album values('track2','30 Nov 2012','video',2);
insert into album values('track2','22 Jan 1999','video',3);
insert into album values('track3','22 Jan 1999','audio',4);
insert into album values('track5','4 Jun 1889','video',3);
Create a Database in 3NF & write queries for following.
· Display all albums composed by ‘A R Rehman’.
Select title from musician, album
Where musician.mno=album.mno and
name='A RRehman';
· Display musician details who have composed Audio album.
Select distinct name,addr,phno,title,codate,format from musician, album
Where musician.mno=album.mno and
Format=’audio’;
· Find all musicians who have composed maximum albums.
select name, format from musician, album
Where musician.mno=album.mno and title=(select max(title)from album);
Select name,count(*) from musician,album
Where musician.mno=album.mno group by name
Having max(album.mno)= (select count (*) from album);
· Display musician wise album details.
Select name,title from musician, album
Where musician.mno=album.mno order by name;
Slip 10
Consider the following Entities and Relationships [30 Marks]
Sailor (sid, sname, age)
Boats (bid, bname, color)
Relation between Sailer and Boats is Many to Many with day as descriptive attribute.
Constraint: Primary key, age should be > 0.
Create table sailor
(sidnumber(4) primary key,
snamevarchar(10),
age number(5) constraint ack check(age>0)
);
Insert into sailor values(101,’ram’,34);
Insert into sailor values(102,’alok’,44);
Insert into sailor values(103,’surya’,42);
Insert into sailor values(104,’priti’,37);
Insert into sailor values(105,’atual’,41);
Create table boats
(bid number(4) primary key,
bnamevarchar(10),
color varchar(10)
);
Insert into boats values(201,’Hindustan’,’blue’);
Insert into boats values(202,’ABC’,’red’);
Insert into boats values(203,’XYZ’,’black’);
Insert into boats values(204,’Joshi’,’blue’);
Insert into boats values(205,’PQR’,’green’);
Create table sb
( sid number(4) references sailor(sid),
bid number(4) references boats(bid),
day varchar(10)
);
Insert into sb values(101,201,’Sunday’);
Insert into sb values(102,202,’Friday’);
Insert into sb values(103,203,’Tuesdy’);
Insert into sb values(104,204,’Monday’);
Insert into sb values(105,205,’Friday’);
Insert into sb values(105,202,’Sunday’);
Create a Database in 3NF & write queries for following.
· Display details of all boats sailed by sailor ‘Ram’.
Select sb.bid,bname,color from sb,boats,sailor
Where sb.bid=boats.bid and
sb.sid =sailor.sid and
sname=’ram’;
· Display Sailor names working on blue boat.
Select sname from sb,boats,sailor
Where sb.bid=boats.bid and
sb.sid =sailor.sid and
color=’blue’;
· Count number of boats sailed by each sailor.
Select sname,count(*) from sb,boats,sailor
Where sb.bid=boats.bid and
sb.sid =sailor.sid group by sname;
· Find the name of sailor who sailed the boat on both Tuesday & Friday.
Select sname,day from sb,boats,sailor
Where sb.bid=boats.bid and
sb.sid =sailor.sid and
day in (’Tuesdy’,’Friday’);
· Display details of the boats which is sailed maximum times on Sundays.
Select distinct bname, count(*) from sb,boats,sailor
Where sb.bid=boats.bid and
sb.sid =sailor.sidand day=’Sunday’ group by bname
having max(boats.bid)=(select count(*)from sp);
Slip 11:-
Supplier (sid, sname, addr)
Parts (pid, pname, pdesc)
Relation between Supplier and Parts is Many to Many with cost as descriptive attribute.
Constraint: Primary key, cost should be > 0.
create table supplier1
(snonumber(4) primary key,
snamevarchar(20),
addrvarchar(20)
);
insert into supplier1values(201,’Mr Pawar’,’Mumbai’);
insert into supplier1values(202,’Mr Suhas’,’Pune’);
insert into supplier1values(203,’Mr Sachin’,’Nashik’);
insert into supplier1values(204,’Miss Sonali’,’Mumbai’);
insert into supplier1values(205,’Miss Geeta’,’Pune’);
create table parts1
(pnonumber(4) primary key,
pnamevarchar(20),
desvarchar(20)
);
insert into parts1 values(301,’wheel’,’rounded’);
insert into parts1 values(302,’pencil’,’natraj’);
insert into parts1 values(303,’tire’,’plated’);
insert into parts1values(304,’break line’,’steel’);
insert into parts1 values(305,’skrew’,’rounded’);
create table sp1
(snonumber(4) references supplier1(sno),
pnonumber(4) references parts1(pno),
price number(5) constraints pck1 check(price>0)
);
insert into sp1values(201,301,500);
insert into sp1 values(202,302,40);
insert into sp1values(203,303,1200);
insert into sp1values(204,304,300);
insert into sp1values(205,305,20);
Create a Database in 3NF & write queries for following.
· Display Supplier details from 'Mumbai' city.
Select * from supplier1
Where addr=’Mumbai’;
· Update cost by 25 % for all parts supplied by supplier ‘Mr. Pawar’.
update sp1Set price=price+(0.5)
Where sp1.sno=(select sp1.sno from supplier1, sp1
Where supplier1.sno=sp1.sno and
sname=’MrPawar’);
· Display all parts supplied by each supplier.
Select pname,sname from supplier1,parts1,sp1
Where supplier1.sno=sp1.sno and
parts.pno=sp1.pno group by pname,sname;
· Display details of parts which are supplied at maximum price by each supplier.
Select distinct sname,max(price) from supplier1,parts1,sp1
Where supplier1.sno=sp1.sno group by sname;
· Display all suppliers who supply part ‘wheel’ and also display its cost.
Select sname,price from supplier1,parts1,sp1
Where supplier1.sno=sp1.sno and
parts1.pno=sp1.pno and
pname=’wheel’;
Slip 12:-
Medical_store (mno, mname, city, phno)
Drug (dno, dname, type, company, price)
Relation between Medical_store and Drug as Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
Create a Database in 3NF & write queries for following.
create table medst
(mnonumber(4) primary key,
mnamevarchar(20),
cityvarchar(20),
phnonumber(10)
);
Insert into medst values(101,’Sai medical’,’Pune’,1234567);
Insert into medst values(201,’Priti medical’,’Pimpri’,234567);
Insert into medst values(301,’Om medical’,’Hadpsar’,3456789);
Insert into medst values(401,’Surbhi medical’,’Pimpri’,4567890);
Insert into medst values(501,’Sai medical’,’Nashik’,5678912);
create table drug
(dnonumber(4) primary key,
dnamevarchar(20),
typevarchar(20),
companyvarchar(10),
price number(5) constraint pcheck1 check(price>0)
);
Insert into drug values(211,’Omini’,’Acidity’,’SunPharma’,40);
Insert into drug values(311,’Crocin’,’sardi’,’ABC’,32);
Insert into drug values(411,’Crocin’,’Flue’,’XYZ’,35);
Insert into drug values(511,’Calcium’,’Pain’,’ABC’,120);
Insert into drug values(611,’Action 500’,’Sardi’,’SunPharma’,40);
Create table md1
(mnonumber(4) references medst(mno),
dnonumber(4) references drug(dno),
quantity number(4)
);
Insert into md1values(101,211,1);
Insert into md1values(201,311,2);
Insert into md1values(301,411,1);
Insert into md1values(401,511,5);
Insert into md1values(501,611,3);
Insert into md1values(101,311,4);
Insert into md1 values(501,311,2);
· Update price of drug by 5 % of 'ABC' Company.
Update drug set price=price+(0.05)
Where drug.dno=(select md1.dno from medst,drug,md1
Where medst.mno=md1.mno and
drug.dno=md1.dno and company =’XYZ’);
· Display names of all medical store where ‘Crocin’ is available.
SelectDISTINCT company from medst,drug,md1
Where medst.mno=md1.mno and
drug.dno=md1.dno and
dname=’Crocin’;
· Count total number of drug of ‘SunPharma’ company in ‘Sai medical’ store.
Select count(md1.dno) from medst,drug,md1
Where medst.mno=md1.mno and
drug.dno=md1.dno and
mname=’Sai medical’ and
company =’SunPharma’;
· Delete all drugs supplied by ‘SunPharma‘
Delete from(select * from drug inner join md1 on drug.dno=md1.dno andcompany =’SunPharma’);
Delete from drug where company =’SunPharma’;
delete from drug
Where dno=(select md1.dno from medst,drug,md1
Where medst.mno=md1.mno and
drug.dno=md1.dno and
mname=’Om medical’ and company =’XYZ’);
· Display the details of medical store having maximum quantity of Crocin.
select mname, count(*) from medst,drug,md1
Where medst.mno=md1.mno and
drug.dno=md1.dno group by mname
having max(md1.mno)=(select count(*) from medst,drug
where dname=’Crocin’);
Slip 13:-
Account (ano, branchname, balance)
Customer (cust_no, cust_name, street, city)
Relation between Account and Customer is Many to Many.
Constraint: Primary key, balance should be > 500.
Create table account
(anonumber(4) primary key,
bnamevarchar(20),
balnumber(7) constraint bchk check(bal>0)
);
Insert into account values(101,’Chinchwad’,’1098453’);
Insert into account values(102,’Pune’,’239843’);
Insert into account values(103,’Pimpri’,’342333’);
Insert into account values(104,’Chinchwad’,’1098453’);
Insert into account values(105,’Mumbai’,’3458453’);
Insert into account values(106,’Pune’,’34’);
Create table cus
(cnonumber(4) primary key,
cnamevarchar(20),
streetvarchar(10),
cityvarchar(10)
);
Insert into cusvalues(201,’alok’,’XYZ road’,’Mumbai’);
Insert into cusvalues(202,’ram’,’PQR road’,’Pune’);
Insert into cusvalues(203,’geeta’,’Ring road’,’Pimpri’);
Insert into cusvalues(204,’raju’,’NPM road’,’Mumbai’);
Insert into cusvalues(205,’sham’,’ASD road’,’Chimchwad’);
Create table ac
(accnonumber(4) references account(accno),
cnonumber(4) references cus(cno)
);
Insert into ac values(101,201);
Insert into ac values(104,204);
Insert into ac values(102,202);
Insert into ac values(103,203);
Insert into ac values(105,205);
Insert into ac values(101,202);
Insert into ac values(101,204);
Insert into ac values(106,204);
Create a Database in 3NF & write queries for following.
· Display customer details with balance between 100000 and 200000.
Select ac.cno,cname,street,city,bal from account,cus,ac
Where ac.cno=cus.cno and
account.ano=ac.ano and
bal between 1000000 and 2000000;
· Display customers having more than two accounts in Chinchwad branch.
Select cname,count(account.ano) from account,cus,ac
Where bname=’Chinchwad’ and
ac.cno=cus.cno and
account.ano=ac.ano group by cname,bname
having count(account.ano)>=2;
· Delete account whose balance is below the balance <500.
Delete from
(select * from account inner join ac on account.accno=ac.accno where balance<500);
· Select names of all Customers whose street name include the substring “road” and whose city is ‘Mumbai’.
Select cname,street from cus
Where city=’Mumbai’ and street like '%road%';
Find number of depositor for each branch.
Select cname,count(*) from account,cus,ac
Where ac.cno=cus.cno and
account.ano=ac.ano group by cname;
Slip 14
Consider the following Entities and Relationships [30 Marks]
Branch (bname ,bcity ,assets)
Loan (loan_no, amount)
Relation between Branch and Loan is One to Many.
Constraint: Primary key, amount and assets should be > 0.
Branch (bname ,bcity ,assets)
Loan (loan_no, amount)
Create table branch
(bnonumber(4) primary key,
bnamevarchar(10),
bcityvarchar(10),
asetsnumber(10) constraints aschk check(asets>0)
);
Insert into branch values(101,’DYP’,’Mumbai’,20000);
Insert into branch values(201,’BOI’,’Pune’,23430);
Insert into branch values(301,’BOB’,’Nashik’,34200);
Insert into branch values(401,’SBI’,’Mumbai’,435210);
Insert into branch values(501,’BOM’,’Pune’,453420);
Create table loan1
(lnonumber(4) primary key,
amount number(10) constraints achk check(amount>0),
bnonumber(4) references branch(bno)
);
Insert into loan1 values(301,2341,101);
Insert into loan1 values(302,3341,201);
Insert into loan1 values(303,2312,301);
Insert into loan1 values(304,2123,401);
Insert into loan1 values(305,900,501);
Insert into loan1 values(306,500,501);
Create a Database in 3NF & write queries for following.
· Display total loan amount given by DYP branch.
Select amount,bname from branch, loan1
Where branch.bno=loan1.bno and
bname=’DYP’;
· Find total number of loans given by each branch.
Select bname,count(*) from branch, loan1
Where branch.bno=loan1.bno group by bname;
· Find the name of branch that have maximum assets located in Mumbai.
Select bname,bcity from branch, loan1
Where branch.bno=loan1.bno and
asets=(select max(asets) from branch where
bcity=’Mumbai’);
· Display loan details in descending order of their amount.
select loan1.bno,bname,bcity,asets,amount from branch,loan1
where branch.bno=loan1.bno order by amountdesc;
· Display all branches located in Mumbai, Pune and Nasik.
select bname,bcity from branch,loan1
where branch.bno=loan1.bno and
bcity in ('Mumbai','Pune','Nashik');
0 Comments