					More programs at www.pranavthegreat.cjb.net


CREATE TABLE BRANCH
(BNAME VARCHAR2(20),
BCITY VARCHAR2(20),
ASSETS DECIMAL(10,2) DEFAULT 50000.00,
CONSTRAINT TAB5PK1 PRIMARY KEY (BNAME));

CREATE TABLE PACCOUNT
(ACCNO INT,
BNAME VARCHAR2(20),
BALANCE DECIMAL(10,2) DEFAULT 50000.00,
CONSTRAINT TAB5PK2 PRIMARY KEY (ACCNO),
CONSTRAINT TAB5FK1 FOREIGN KEY (BNAME) REFERENCES BRANCH(BNAME));

CREATE TABLE CUSTOMER
(CNAME VARCHAR2(30),
CSTREET VARCHAR2(30),
CCITY VARCHAR2(30) DEFAULT 'BANGALORE',
CONSTRAINT TAB2PK3 PRIMARY KEY(CNAME));

CREATE TABLE DEPOSITOR
(CNAME VARCHAR2(30),
ACCNO INT NOT NULL,
CONSTRAINT TAB5PK4 PRIMARY KEY(CNAME),
CONSTRAINT TAB5FK2 FOREIGN KEY(CNAME) REFERENCES CUSTOMER(CNAME),
CONSTRAINT TAB5FK3 FOREIGN KEY(ACCNO) REFERENCES PACCOUNT(ACCNO));

CREATE TABLE LOAN
(LOAN_NO INT,
BNAME VARCHAR2(20) NOT NULL,
AMOUNT DECIMAL(10,2) DEFAULT 100.00,
CONSTRAINT TAB5PK5 PRIMARY KEY(LOAN_NO),
CONSTRAINT TAB5FK4 FOREIGN KEY (BNAME) REFERENCES BRANCH(BNAME));

CREATE TABLE BORROWER
(CNAME VARCHAR2(30),
LOAN_NO INT NOT NULL,
CONSTRAINT TAB5PK6 PRIMARY KEY(CNAME),
CONSTRAINT TAB5FK5 FOREIGN KEY(CNAME) REFERENCES CUSTOMER(CNAME),
CONSTRAINT TAB5FK6 FOREIGN KEY(LOAN_NO) REFERENCES LOAN(LOAN_NO));

======================================================================
======================================================================

INSERT INTO BRANCH
VALUES('&BNAME','&BCITY',&ASSETS);

INSERT INTO PACCOUNT
VALUES(&ACCNO,'&BNAME',&BALANCE);

INSERT INTO CUSTOMER
VALUES('&CNAME','&CSTREET','&CCITY');

INSERT INTO DEPOSITOR
VALUES('&CNAME',&ACCNO);

INSERT INTO LOAN
VALUES(&LOAN_NO,'&BNAME',&AMOUNT);

INSERT INTO BORROWER
VALUES('&CNAME',&LOAN_NO);

======================================================================
======================================================================

SELECT D.CNAME
FROM DEPOSITOR D,PACCOUNT A,BRANCH B
WHERE D.ACCNO=A.ACCNO AND A.BNAME=B.BNAME
AND A.BNAME='MAIN'
GROUP BY D.CNAME
HAVING COUNT(CNAME)>=2;

SELECT D.CNAME,B.BNAME,B.BCITY
FROM BRANCH B,PACCOUNT A,DEPOSITOR D
WHERE D.ACCNO=A.ACCNO AND A.BNAME=B.BNAME
AND D.ACCNO=ALL(SELECT A.ACCNO
FROM BRANCH B,PACCOUNT L
WHERE B.BCITY='&BCITY' AND
L.BNAME=B.BNAME) AND B.BNAME IN(SELECT BNAME
FROM BRANCH
WHERE BCITY='&BCITY');

DELETE FROM PACCOUNT
WHERE BNAME IN (SELECT B.BNAME
FROM BRANCH B
WHERE B.BCITY='BANGALORE');

======================================================================
======================================================================

ALTER TABLE BORROWER
ADD CONSTRAINT TAB5U1 UNIQUE(LOAN_NO);

ALTER TABLE DEPOSITOR
ADD CONSTRAINT TAB5FK3 FOREIGN KEY(ACCNO)
REFERENCES PACCOUNT(ACCNO)
ON DELETE CASCADE;

======================================================================
======================================================================

5A>SELECT CNAME 
FROM CUSTOMER
WHERE CNAME IN(SELECT CNAME
FROM DEPOSITOR D,PACCOUNT A
WHERE A.ACCNO=D.ACCNO) AND
CNAME NOT IN(SELECT C.CNAME
FROM CUSTOMER C,BORROWER B,LOAN L
WHERE C.CNAME=B.CNAME AND B.LOAN_NO=L.LOAN_NO);

5B>SELECT L.BNAME
FROM LOAN L,BORROWER B
WHERE L.LOAN_NO=B.LOAN_NO
GROUP BY L.BNAME
HAVING COUNT(*)=(SELECT(MAX(COUNT(*)))
FROM BORROWER GROUP BY CNAME);

5C> OH MY GOSHH...

5D>SELECT B.BNAME
FROM BRANCH B,LOAN L
WHERE B.BNAME=L.BNAME
GROUP BY B.BNAME
HAVING SUM(AMOUNT)<=SUM(B.ASSETS);

5E>HA HA...LOVE SUCH QUERIES THAT CAN'T BE ANSWERED....

SELECT * FROM PACCOUNT;
SELECT * FROM BRANCH;
SELECT * FROM DEPOSITOR;


					More programs at www.pranavthegreat.cjb.net
