Anjuman Engineering College, Bhatkal Database Applications Laboratory
1. Consider the Insurance database given below. The primary keys are underlined and the data types are specified: PERSON (driver-id : string, name: string, address: string) CAR (Regno : string, model : string,year: int) ACCIDENT (report-number : int, date : date, locati on : string) OWNS (driver-id :string,regno: string) PARTICIPATED (driver-id : string, regno : string,report-number : int, damage- amount : int) i) create the above tables by properly specifying the primary keys and the foreign keys ii) Enter atleast ive tuples for each relation iii) Demonstrate how you a. Update the damage amount for the car with a speciic regno in accident with report number 12 to 25000 b. Add a new accident to the database iv) Find the total number of people who owned cars that were involved in accidents in 2006. v) Find the number of accidents in which cars belonging to a speciic model were involved. vi) Generation of suitable reports vii) Create suitable front end for querying and display the results create table person ( driverid varchar(5) primary key, name varchar(30), address varchar(50) ); create table car ( regno varchar(5) primary key, model varchar(25), year int ); create table accident ( reportno int primary key, datel date, location varchar(30) ); create table owns ( driverid references person, regno references car
);
1
Anjuman Engineering College, Bhatkal Database Applications Laboratory
create table participated ( driverid references person, regno references car, reportno references accident, damages int ); insert into person values (1,'abc','hkbkce,nagawara'); insert into person values (2,'bcd','atria,yelahanka'); insert into person values (3,'ade','ramahiah,near iisc'); insert into person values (4,'cfg','mvjce,whiteield'); insert into person values (5,'rte','pesit,somewhere'); insert into car values ('al','merc',2007); insert into car values ('a2','porsche',2005); insert into car values ('b2','rolls royce',2006); insert into car values (b4','ferrari',1999); insert into car values ('cl','bentley',2000); insert into car values ('d2','maybach',2007); insert into accident values (1,'1-jan-2006','domlur'); insert into accident values (2,'28-mar-2006','m.g.road'); insert into accident values (3,'2-dec-2006','r.t.nagar'); insert into accident values (4,'5-jan-20071;yelahanka'); insert into accident values (5,'26-jan-2007','koramangla'); insert into accident values (6,'4-feb-2007','majestic'); insert into owns values (1,'al'); insert into owns values (2,'a2'); insert into owns values (2,'cl'); insert into owns values (3,'b2'); insert into owns values (4,'b4'); insert into owns values (5,'d2'); insert into participated values (1,'al',1,500); insert into participated values (2,'a2',1,2000); insert into participated values (3,'b2',1,1000); insert into participated values (1,'a1',2,1500); insert into participated values (4,1c1',2,800); insert into participated values (5,'d2',3,750); insert into participated values (2,'c1',3,600); insert into participated values (1,'al',3,200); insert into participated values (5,'d2',4,1000); insert into participated values (2,'a2',5,1200); insert into participated values (3,'b2',6,10000); insert into participated values (2,1a1',6,5000);
2
Anjuinan Engineering College, Bhatkal I 3 Database Applications Laboratory I
select * from person; DRIVERID NAME 1 2 3 4 5
abc bcd ade cfg rte
ADDRESS hkbkce,nagawara atria,yelahanka ramahiah,near iisc mvjce,whiteield pesit,somewhere
select * from car; REGNO MODEL YEAR merc porsche rolls royce ferrari bentley maybach
al a2 b2 b4 cl d2
select * from accident; REPORTNO DATE1 1
2 3 4 5 6
01-JAN-06 28-MAR-06 02-DEC-06 05-JAN-07 26-JAN-07 04-FEB-07
2007 2005 2006 1999 2000 2007
LOCATION domlur m.g.road r.t.nagar yelahanka koramangla majestic
select * from owns; DRIVERID REGNO 1 2 2 3 4 5
al a2 cl b2 b4 d2
select * from participated; DRIVERID REGNO REPORTNO DAMAGES 1 2 3 1 4 5 2 1
al a2 b2 al cl d2 cl al
1 1 1 2 2 3 3 3
500 2000 1000 1500 800 750 600 200
Anjuman Engineering College, Bhatkal I 4 Database Applications Laboratory I 5 2 3 2
d2 a2 b2 al
4 5 6 6
1000 1200 10000 5000
iii.a Update the damage amount for the car with a specific regno in accident with report number 12 to 25000 update participated set damages=25000 where reportno=4 and regno=V21; 1 row updated select * from participated; DRIVERID REGNO REPORTNO 1 2 3 1 4 5 2 1 5 2 3 2
al a2 b2 al cl d2 cl al d2 a2 b2 al
DAMAGES
1 1 1 2 2 3 3 3 4 5 6 6
500 2000 1000 1500 800 750 600 200 25000 1200 10000 5000
iii.b Add a new accident to the database insert into accident values (7,'16-mar-2007','nagawara'); 1 row updated insert into participated values (2,'al',7,15000); 1 row updated select * from accident; REPORTNO DATE1 1 2 3 4 5 6 7
01-JAN-06 28-MAR-06 02-DEC-06 05-JAN-07 26-JAN-07 04-FEB-07 16-MAR-07
LOCATION domlur m.g.road r.t.nagar yelahanka koramangla majestic nagawara
Anjuman Enginccring Collcgc, Bhatkal Database Applications Laboratory select * from participated; DRIVERLD REGNO REPORTNO DAMAGES 1 2 3 1 4 5 2 1 5 2 3 2 2
al a2 b2 al cl d2 cl a1 d2 a2 b2 al al
1 1 1 2 2 3 3 3 4 5 6 6 7
500 2000 1000 1500 800 750 600 200 25000 1200 10000 5000 15000
iv. Find the total number of people who owned cars that were involved in accidents in 2006 select count(distinct p.driverid) from accident a,owns o,participated p where datel like `%06' and a.reportno=p.reportno and p.regno=o.regno and o.driverid=p.driverid; COUNT(DISTENCTP.DRIVERID) ----------- ----------- ------------4 v. Find the number of accidents in which cars belonging to a specific model were involved. select count(*) from car c,participated p where model='porsche' and c.regno=p.regno; COUNT(*) 2
5
Anjuman Engineering College, Bhatkal Database Applications Laboratory 2. Consider the following relations for an order processing database application in a company. CUSTOMER (Cust #: int, Cname: string, City: string) ORDER (Order #: int, Odate: date, Cust #: int, Ord-Amt: int) ORDER-ITEM (Order #: int, Item #: int, qty: int) ITEM (Item #: int, Unit Price: int) SHIPMENT (Order #: int, Warehouse #: int, Ship-Date: date) WAREHOUSE (Warehouse #: int, City: string) i) iii)
iv) v) vi) vii)
Create the above tables by properly specifying the primary keys and the foreign keys. ii) Enter at least ive tuples for each relation. Produce a listing: CUSTNAME, NO_OF_ORDERS, AVG_ORDER_AMT, where the middle column is the total number of orders by the customer and the last column is the average order amount for that customer. List the Order# for the orders that were shipped from all the warehouses that the company has in a specific city. Demonstrate how you delete Item# 10 from the ITEM table and make that field null in the ORDER-ITEM table. Generation of suitable reports. Create a suitable front end for querying and displaying the results.
create table customer ( cno int primary key, cname varchar( 1 0), city varchar( 1 5) ); create table orderl ( ono int primary key, odate date, cno references customer, oamt int
); create table item ( ino int primary key, uprice int ); create table oitem ( ono references orderl, ino references item on delete set null, qty int );
6
Anjuman Engineering College, Bhatkal Database Applications Laboratory
create table warehouse ( wno int primary key, city varchar(15) ); create table shipment ( ono references orderl, wno references warehouse, sdate date ); insert into customer values(L'abcVbangalore'); insert into customer values(2,'cdeVmysores); insert into customer values(3,'def,'chennai'); insert into customer values(4,'efg','mumbai'); insert into customer values(5,'adf,'kolkata'); insert into orderl values(1,'1-jan-2006',1,20000); insert into orderl values(2,'26-mar-2006',2,10000); insert into orderl values(3,'12-jun-2006',1,5000); insert into orderl values(4,'15-sep-2006',3,9000); insert into orderl values(5,'5-jan-2007',4,2500); insert into orderl values(6,'10-jan-2007',4,2400); insert into orderl values(7,'3-mar-2007',5,3500); insert into item values(1,500); insert into item values(2,300); insert into item values(3,2500); insert into item values(4,800); insert into item values(5,700); insert into oitem values(1,1,40); insert into oitem values(2,1,20); insert into oitem values(3,3,2); insert into oitem values(5,3,1); insert into oitem values(4,2,30); insert into oitem values(6,4,3); insert into oitem values(7,5,5); insert into warehouse values(100,1bangalore'); insert into warehouse values(101,'chennai'); insert into warehouse values(102,'mumbai'); insert into warehouse values(103,'kolkata'); insert into warehouse values(104,'mysore'); insert into shipment values(1,100,'3-jan-2006'); insert into shipment values(2,100,'28-mar-2006'); insert into shipment values(3,101,'13-jun-2006');
7
Anjuman Engineering College, Bhatkal Database Applications Laboratory insert into shipment values(4,102,'18-sep-2006'); insert into shipment values(5,103,'11-jan-2007'); insert into shipment values(6,104,'13-jan-2007'); insert into shipment values(7,103,'3-mar-2007');
select * from customer; CNO CNAME CITY -------- --- ----- ---------1 abc bangalore 2 cde mysore 3 def chennai 4 efg mumbai 5 adf kolkata
select * from order 1; ONO ODATE 1 01-JAN-06 2 26-MAR-06 3 12-JUN-06 4 15- SEP-06 5 05-JAN-07 6 10-JAN-07 7 03-MAR-07
select * from item; NO UPRICE 1 2 3 4 5
500 300 2500 800 700
select * from oitem; ONO INO QTY 1 2 3 5 4 6 7
1 1 3 3 2 4 5
40 20 2 1 30 3 5
CNO OAMT 1 2 1 3 4 4 5
20000 10000 5000 9000 2500 2400 3500
8
Anjuman Engineering College, Bhatkal Database Applications Laboratory
select * from warehouse; WNO CITY 100 101 102 103 104
bangal ore chennai mumbai kolkata mysore
select * from shipment; ONO WNO SDATE 1 2 3 4 5 6 7
100 100 101 102 103 104 103
03-JAN-06 28-MAR-06 13-JUN-06 18-SEP-06 11-JAN-07 13-JAN-07 03-MAR-07
iii. Produce a listing: CUSTNAME, NO_OF_ORDERS, AVG_ORDER_AMT, where the middle column is the total number of orders by the customer and the last column is the average order amount for that customer. select c.cname,count(q.qty),avg(oamt) from customer c, orderl o, oitem q where q.ono=o.ono and c.cno=o.cno group by cname; CNAME COUNT(Q.QTY) AVG(OAMT) abc adf cde def efg
2 1 1 1 2
12500 3500 10000 9000 2450
iv. List the Order# for the orders that were shipped from all the warehouses that the company has in a specific city. select w.city, s.wno, s.ono from warehouse w, shipment s where w.wno=s.wno and w.city='mumbai'; CITY mumbai
WNO ONO 102
4
9
Anjuman Engineering College, Bhatkal I 10 Database Applications Laboratory I v. Demonstrate how you delete Item# 10 from the ITEM table and make that field null in the ORDER-ITEM table
delete from item where ino=5; 1 row deleted select * from item; INO UPRICE 1 2 3 4
500 300 2500 800
Anjuman Engineering College, Bhatkal I Database Applications Laboratory I
3. Consider the following database of student enrollement in courses and books adopted for each course . STUDENT (regno :string , name : string , major : string , bdate : int) COURSE (course# : int , cname : string , dept : string) ENROLL ( regno : string , course#: int , sem : int , marks : int ) BOOK ADAPTION ( course#: int , sem : int , book_isbn :int) TEXT( book isbn : int , book-title : string , publisher : string , author : string). i) Create the above tables by properly specifying the primary keys and the foreign key . ii) Enter atleast ive tuples for each relation . iii) Demonstrate how you add a new text book to the database and make this book be adopted by some department. iv) Produce a list of text books( include course # ,book_isbn,book-title) in the alphabetical order for courses ofered by the cs department that use more than 2 books. v) List any department that has all its adopted books published by specific publisher. vi) Generation of suitable reports. vii) Create suitable front end for querying and display the results create table student ( regno varchar(5) primary key, name varchar( 1 0), major varchar( 1 0), bdate date ); create table course ( cno int primary key, cname varchar(10), dept varchar(10) ); create table enroll ( regno references student, cno references course, sem int primary key, marks int ); create table text ( isbn int primary key, title varchar(15), publisher varchar(10), author varchar(10) );
11
Anjuman Engineering College, Bhatkal
Database Applications Laboratory create table bad ( cno references course, sem references enroll, isbn references text ); insert into student values('cs421,'InikhilVcseV17-dec-1986'); insert into student values(cs48VmujeebVcse','02-sep-1986'); insert into student values(ec261,1pradeepVcse',116-aug-1987'); insert into student values('ee37VmajidViseY28-may-1986'); insert into student values('is481,'wajidVise',128-may-1986'); insert into course values(1,'.netVcomputer'); insert into course values(2,1j2eeVcomputer'); insert into course values(3,'mis','infosci'); insert into course values(4,'fs','infosci'); insert into course values(5,'oracle','computer'); insert into enroll values('cs42',1,6,98); insert into enroll values('cs48',2,3,97); insert into enroll values('ec26',5,5,50); insert into enroll values('is48',3,7,90); insert into enroll values('ee37',4,4,80); insert into text values(101,11et us c','lpe','fahad'); insert into text values(102,'c++','abc','mujeeb'); insert into text values(103,'oracleYdef,'othman'); insert into text values(104,'.net','Ipe','naushad'); insert into text values(105,j2ee','pearsonVmikhil'); insert into bad values(1,6,101); insert into bad values(1,6,103); insert into bad values(1,6,102); insert into bad values(4,4,104); insert into bad values(5,7,105); select * from student; REGNO NAME MAJOR cs42 cs48 ec26 ee37 is48
mikhil mujeeb pradeep majid wajid
BDATE cse cse cse ise ise
17-DEC-86 02-SEP-86 16-AUG-87 28-MAY-86 28-MAY-86
12
Anjuman Engineering College, Bhatkal Database Applications Laboratory
select * from course; CNO CNAME DEPT 1 2 3 4 5
.net j2ee mis fs oracle
computer computer infosci infosci computer
select * from enroll; REGNO CNO SEM MARKS cs42 cs48 ec26 is48 ee37
101 102 103 104 105
1 2 5 3 4
6 3 5 7 4
let us c c++ oracle net j2ee
98 97 50 90 80
1pe abc def 1pe pearson
fahad mujeeb othman naushad mikhil
select * from bad; CNO SEM ISBN 1 1 1 4 5
6 6 6 4
101 103 102 104
7
105
iii.Demonstrate how you add a new text book to the database and make this book be adopted by some department. insert into text values(1063AVA','pearson','Avril.); insert into bad values(2,7,106); select * from text; ISBN TITLE PUBLISHER AUTHOR 101 102 103 104 105 106
let us c c++ oracle net j2ee JAVA
1pe abc def 1pe pearson pearson
fahad mujeeb othman naushad mikhil Avril
13
Anjuman Engineering College, Bhatkal I
Database Applications Laboratory I select * from bad; CNO SEM ISBN 1 1 1 4 5 2
101 103 102 104 105 106
6 6 6 4 7 7
iv. Produce a list of text books( include course # ,book isbn,book-title) in the alphabetical order for courses offered by the cs department that use more than 2 books. SELECT s.cno,t.title,b.isbn FROM course s,bad b,text t where s.cno=b.cno AND b.isbn=t.isbn AND s.dept='computer' AND b.cno in (select b.cno from bad b,course s where b.cno=s.cno group by b.cno having count(*)>2) order by t.title; CNO TITLE 1 1 1
ISBN
c++ let us c oracle
102 101 103
v. List any department that has all its adopted books published by specific publisher. select s.dept, t.publisher from course s, bad b, text t where s.cno=b.cno and t.isbn=b.isbn and publisher='lpe'; DEPT
PUBLISHER
computer infosci
1pe 1pe
14
Anjuman Engineering College, Bhatkal I Database Applications Laboratory I
4. Consider the following relations for the details maintained by a book dealer. AUTHOR (Author-id: int, Name: string, City: string, Country: string) PUBLISHER (Publisher-id: int, Name: string, City: string, Country: string) CATALOG (Book-id: int, title: string, author-id: int, Publisher-id: int, Category-id: int, Year: int, Price: int) CATEGORY (Category-id: int, Description: string) ORDER-DETAILS (Order-no : int, Book-id: int, Quantity: int) i. Create the above tables by properly specifying the primary keys and the foreign keys. ii. Enter at least five tuples for each relation. iii. Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is ater 2000. iv. Find the author of the book which has maximum sales. v. Demonstrate how you increase the price of books published by a specific publisher by 10%. vi. Generation of suitable reports. vii. Create a suitable front end for querying and displaying the results. create table author ( aid int primary key, name varchar(15), city varchar(15), country varchar(15) ); create table publisher ( pid int primary key, name varchar(15) , city varchar(15) , country varchar(15) ); create table category ( cid int primary key, description varchar(15) ); create table catalog ( bid int primary key, title varchar(15), aid references author, pid references publisher, cid references category, year int, price int );
15
Anjuman Engineering College, Bhatkal Database Applications Laboratory
create table odetails ( ono int, bid references catalog, qty int ); insert into author values (1,'mik','bang','ind'); insert into author values (2,'muj','bang','ind'); insert into author values (3,'prad','tri','aus'); insert into author values (4,'maj','anan','ame'); insert into author values (5,'waj','anan','euro'); insert into publisher values (101,'pearson','bang','ind'); insert into publisher values (102,'tata','mumbai','aus'); insert into publisher values (103,'sapna','che','euro'); insert into publisher values (104,'abc','tri','ame'); insert into publisher values (105,'xyz','anan','ind'); insert into category values (1001,'computer'); insert into category values (1002,'electronics'); insert into category values (1003,'maths'); insert into category values (1004,'science'); insert into category values (1005,'electrical'); insert into catalog values (111,'Iibl',1,101,1001,2002,500); insert into catalog values (112,'lib2',2,102,1002,2000,800); insert into catalog values (113,'Iib3',3,103,1003,2003,200); insert into catalog values (114,'lib4',4,104,1001,2006,350); insert into catalog values (115,'Iib5',5,105,1004,2007,100); insert into catalog values (116,'lib6',2,103,1005,2007,600); insert into catalog values (117,'Iib7',2,105,1002,2007,450); insert into catalog values (118,'Iib8',1,101,1001,2002,500); insert into odetails values (1,111,2); insert into odetails values (2,112,3); insert into odetails values (3,111,5); insert into odetails values (4,113,1); insert into odetails values (5,114,2); insert into odetails values (6,115,1); insert into odetails values (1,114,2); insert into odetails values (2,113,2); select * from author; AID NAME 1 2 3 4 5
mik muj prad maj waj
CITY
COUNTRY
bang bang tri anan anan
ind ind aus ame euro
16
Anjuman Engineering College, Bhatkal Database Applications Laboratory select * from publisher; P) NAME CITY COUNTRY 101 102 103 104 105
pearson tata sapna abc xyz
bang mumbai the tri anan
ind aus euro ame ind
select * from category; CID DESCRIPTION 1001 1002 1003 1004 1005
computer electronics maths science electrical
select * from catalog; BID TITLE AID PID CID YEAR PRICE 111 112 113 114 115 116 117 118
lib 1 lib2 lib3 lib4 lib5 lib6 lib? lib8
1 2 3 4 5 2 2 1
101 102 103 104 105 103 105 101
1001 1002 1003 1001 1004 1005 1002 1001
2002 2000 2003 2006 2007 2007 2007 2002
500 800 200 350 100 600 450 500
select * from odetails; ONO BID QTY ------------------1 111 2 2 112 3 3 111 5 4 1 13 1 5 114 2 6 115 1 1 114 2 2 113 2 iii. Give the detail of the author who have 2 or more books in the catalog and the price of the book is greater than the average price of the book in the catalog and the year of publication after 2000 select name,city,country from author where aid in (select aid from catalog where year>2000 and price>(select avg(price) from catalog)
17
Anjuman Engineering College, Bhatkal Database Applications Laboratory
group by aid having count(*)>1); NAME CITY COUNTRY mik muj
bang bang
ind ind
iv. Find the author of the book which has maximum sales select a. aid, name from author a, catalog c where a.aid=c.aid and c.bid=(select bid from odetails group by bid having sum(qty)=(select max(sum(qty)) from odetails group by bid)); AD NAME 1
mik
v. Demonstrate how to increase the price of books published by a speciic publisher by 10%
update catalog set price=1.1*price where pid in (select pid from publisher where name='abc'); select * from catalog; BID TITLE AD PID CID YEAR PRICE 111 112 113 114 115 1 16 117 118
lib 1 lib2 lib3 lib4 lib5 lib6 lib? ib8
1 2 3 4 5 2 2 1
101 102 103 104 105 103 105 101
1001 1002 1003 1001 1004 1005 1002 1001
2002 2000 2003 2006 2007 2007 2007 2002
500 800 200 385 100 600 450 500
18
Anjuman Engineering College, Bhatkal I Database Applications Laboratory I 5. Consider the folowing database for a banking enterprise
BRANCH (branch_name: string, branch_city: string, assets: real) ACCOUNT (accno: int, branch_name: string, balance: real) CUSTOMER (customer name: string, customer_street: string, city:string) DEPOSITOR (customer name: string, accno: int) LOAN (loan_number: int, branch_name: string, amount: real) BORROWER (customer name: string, loan number: int) i) Create the above tables by properly specifying the primary keys and the foreign keys. ii) Enter atleast ive tuples for each relation. iii) Find all the customers who atleast two accounts at the MAIN branch. iv) Find all the customers who have an account at all branches located in a speciic city. v) Demonstrate how you delete all account tuples at every branch located in a speciic city. vi) Generation of suitable reports. vii) Create suitable front end for querying and displaying the results. create table branch ( bname varchar(10) primary key, city varchar(10), assets real ); create table account ( accno int primary key, bname varchar(10), balance real, foreign key(bname) references branch(bname)
); create table cust ( cname varchar(10) primary key, cstreet varchar(10) not null, city varchar(10) not null
); create table depositor ( cname varchar(10), accno int, primary key(cname,accno), foreign key(accno) references account(accno) on delete cascade, foreign key(cname) references cust(cname) on delete cascade
);
19
Anjumn Engineering College, Bhatkal I 20 Database Applications Laboratory I
create table loan ( Ino int primary key, bname varchar(10) not null, amt real, foreign key(bname) references branch(bname) on delete cascade ); create table borrower ( cname varchar(10), lno int, primary key(cname,lno), foreign key(cname) references cust(cname) on delete cascade, foreign key(Ino) references loan(lno) on delete cascade ); insert into branch values('abc','bang',1200000); insert into branch values('def,'che',2000000); insert into branch values('abn','mum',330000); insert into branch values('xyz','hyd',555555); insert into branch values('mno','bang',9999999); insert into account values(1,'abc',25000); insert into account values(2,'def,12000); insert into account values(3,'def,1000); insert into account values(4,'abn',10000); insert into account values(5,'mno',600000); insert into account values(6,1xyz1,50000); insert into cust values('mik1,1ab1,1bang'); insert into cust values('muj','cd','bang'); insert into cust values('maj','ef,'che'); insert into cust values('waj','xy','del'); insert into cust values('prad','lm','mum'); insert into cust values('now','op','hyd'); insert into depositor values('mik',2); insert into depositor values('muj',1); insert into depositor values('muj',5); insert into depositor values('prad',4); insert into depositor values('maj',3); insert into depositor values('waj',6); insert into depositor values('mik',3); insert into loan values(1,'abc',5000); insert into loan values(2,'def,1500); insert into loan values(3,'abn',10000); insert into loan values(4,'xyz',3500); insert into loan values(5,'mno',20000);
Anjuman Engineering College, Bhatkal 121 Database Applications Laboratory
insert into borrower values('mik',2); insert into borrower values('muj',1); insert into borrower values('prad',3); insert into borrower values('maj',4); insert into borrower values('waj',5);
select *from branch; BNAME CITY ------abc bang def che mum abn hyd xYz mno bang
select *from account; ACCNO BNAME 1 2 3 4 5 6
abc def def abn mno
xyz
ASSETS 1200000 2000000 330000 555555 9999999
BALANCE 25000 12000 1000 10000 600000 50000
select *from cust; CNAME CSTREET CITY mik muj maj waj prad now
ab cd of xy lm op
select *from depositor; CNAME ACCNO mik muj muj prad maj waj mik
2 1 5 4 3 6 3
bang bang che del mum hyd
Anjuman Engineering College, Bhatkal Database Applications Laboratory
select *from loan; LNO BNAME AMT 1 2 3 4 5
abc def abn xyz mno
5000 1500 10000 3500 20000
select *from borrower; CNAME LNO mik muj prad maj waj
2 1 3 4 5
iii. Find all the customers who atleast two accounts at the MAIN branch. select cname from account a,depositor d where a.accno=d.accno and bname='def roup by cname having count(*)>1; CNAME mik iv. Find all the customers who have an account at all branches located in a specific city select cname from cust c where not exists (select bname from branch where city='bang' minus select bname from depositor d,account a where d.accno=a.accno and d.cname=c.cname) and exists (select bname from branch where city—bang); CNAME muj v. Demonstrate how you delete all account tuples at every branch located in a speciic city. delete from account where bname in (select bname from branch where city='che'); 2 rows deleted. select *from account; ACCNO BNAME BALANCE 1 4 5 6
abc abn mno xyz
25000 10000 600000 50000
**************
22
