Database Management Systems (DBMS) Lab Program 5

Database Management Systems (DBMS) Lab Program



Question 5

CREATE TABLE branch(
name VARCHAR(30),
city VARCHAR(30),
assets NUMBER(10,3),
PRIMARY KEY(name)
);

CREATE TABLE account (
accno NUMBER(10),
branchname VARCHAR(30),
balance NUMBER(10,3),
PRIMARY KEY(accno),
FOREIGN KEY (branchname) REFERENCES branch(name) ON DELETE CASCADE,
CHECK(balance>0)
);

CREATE TABLE cust(
name VARCHAR(30),
street VARCHAR(30),
city VARCHAR(30),
PRIMARY KEY(name)
);

CREATE TABLE depositor (
custname VARCHAR(30),
accno NUMBER(10),
PRIMARY KEY(custname,accno),
FOREIGN KEY (custname) REFERENCES cust(name) ON DELETE CASCADE,
FOREIGN KEY (accno) REFERENCES account(accno) ON DELETE CASCADE
);

CREATE TABLE loan (
no NUMBER(10),
branchname VARCHAR(30),
amount NUMBER(10,3),
PRIMARY KEY(no),
FOREIGN KEY (branchname) REFERENCES branch(name) ON DELETE CASCADE
);

CREATE TABLE borrower (
custname varchar(30),
loanno NUMBER(10),
PRIMARY KEY (custname,loanno),
FOREIGN KEY (custname) REFERENCES cust(name) ON DELETE CASCADE,
FOREIGN KEY (loanno) REFERENCES loan(no) ON DELETE CASCADE
);


DESC  branch;
DESC account;
DESC depositor;
DESC customer;
DESC loan;
DESC borrower;


insert into branch values('abc','bang',1200000.01);
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.2);

insert into  account values(1,'abc',25000);
insert into  account values(2,'def',12000.14);
insert into  account values(3,'def',1000);
insert into  account values(4,'abn',10000);
insert into  account values(5,'mno',600000.66);
insert into  account values(6,'xyz',50000);

insert into cust values('mik','ab','bang');
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);

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;
SELECT * FROM account;
SELECT * FROM depositor;
SELECT * FROM customer;
SELECT * FROM loan;
SELECT * FROM borrower;




-----------------------------------------------------------------------------------------------------------
3 - at least 2 accounts at the main branch.
-----------------------------------------------------------------------------------------------------------
select d.custname,count(d.custname)
from account a,depositor d
where a.accno=d.accno AND a.branchname='def'
group by d.custname
having count(d.custname)>1;


-----------------------------------------------------------------------------------------------------------
4 - all the customers who have an account at ALL the banches located in a specified 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)
 


-----------------------------------------------------------------------------------------------------------
5 - demonstrate how you delete a tuple in account relation at every branch locatedin a specified city.
-----------------------------------------------------------------------------------------------------------

DELETE FROM account
WHERE branchname IN
(SELECT name FROM branch WHERE city LIKE 'che');