school / college project on sql (oracle) (customer, branch,,, calcutta university)

customer.sql

CREATE TABLE CUSTOMER(
CUSTOMER_ID NUMBER(5),
CUSTOMER_NAME VARCHAR2(25),
CUSTOMER_STREET VARCHAR2(20),
CUSTOMER_CITY VARCHAR2(20),
PRIMARY KEY(CUSTOMER_ID)
);


CREATE TABLE BRANCH(
BRANCH_ID NUMBER(5) PRIMARY KEY,
BRANCH_NAME VARCHAR2(20),
BRANCH_CITY VARCHAR2(20),
ASSETS NUMBER(10)
);

CREATE TABLE ACCOUNT(
ACCOUNT_NUMBER CHAR(5) PRIMARY KEY,
BRANCH_ID NUMBER(5),
BALANCE NUMBER(10),
FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH(BRANCH_ID)
);

CREATE TABLE DEPOSITOR(
CUSTOMER_ID NUMBER(5),
ACCOUNT_NUMBER CHAR(5),
FOREIGN KEY (CUSTOMER_ID ) REFERENCES CUSTOMER(CUSTOMER_ID),
FOREIGN KEY (ACCOUNT_NUMBER) REFERENCES ACCOUNT(ACCOUNT_NUMBER)
);

CREATE TABLE LOAN(
LOAN_NUMBER CHAR(4) PRIMARY KEY,
BRANCH_ID NUMBER(5),
AMOUNT NUMBER(4),
FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH(BRANCH_ID)
);

CREATE TABLE BORROWER(
CUSTOMER_ID NUMBER(5),
LOAN_NUMBER CHAR(4),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID),
FOREIGN KEY (LOAN_NUMBER) REFERENCES LOAN(LOAN_NUMBER)
);

CREATE TABLE PAYMENT(
LOAN_NUMBER CHAR(4),
PAYMENT_NUMBER NUMBER(3),
PAYMENT_DATE DATE,
PAYMENT_AMOUNT NUMBER(4),
FOREIGN KEY (LOAN_NUMBER) REFERENCES LOAN(LOAN_NUMBER)
);



INSERT INTO CUSTOMER VALUES(1,'CHANDAN DUTTA','JADAVPUR','KOLKATA;');
INSERT INTO CUSTOMER VALUES(2,'ARNAB DEY','TOLLYGUNGE','KOLKATA');
INSERT INTO CUSTOMER VALUES(3,'SUBHADIP ROY','GARIYA','KOLKATA');
INSERT INTO CUSTOMER VALUES(4,'ANAMIKA GUHA','DHAKURIA','KOLKATA');
INSERT INTO CUSTOMER VALUES(5,'KRISHNENDU GANGULI','AC HOWRAH','HOWRAH');
INSERT INTO CUSTOMER VALUES(6,'SHARMILA PAUL','BHOWANIPORE','KOLKATA');
INSERT INTO CUSTOMER VALUES(7,'SOUMYAJIT SAMANTA','BENUBAN','KOLKATA');
INSERT INTO CUSTOMER VALUES(8,'SANDHYA SAH','SILIGURI','SILIGURI');
INSERT INTO CUSTOMER VALUES(9,'SALMAN SALIM','B.GARDEN','HOWRAH');
INSERT INTO CUSTOMER VALUES(10,'SUMAN ROUTH','TOLLYGAUNGE','KOLKATA');



INSERT INTO BRANCH VALUES(01,'BHOWANIPORE','KOLKATA',90000);
INSERT INTO BRANCH VALUES(02,'EXIDE','KOLKATA',67000);
INSERT INTO BRANCH VALUES(03,'ALIPORE','KOLKATA',298000);
INSERT INTO BRANCH VALUES(04,'DUMDUM','KOLKATA',30000);

INSERT INTO ACCOUNT VALUES('A-101',1,400);
INSERT INTO ACCOUNT VALUES('A-108',2,95000);
INSERT INTO ACCOUNT VALUES('A-456',3,64000);
INSERT INTO ACCOUNT VALUES('A-105',4,200);
INSERT INTO ACCOUNT VALUES('A-111',2,4000);
INSERT INTO ACCOUNT VALUES('A-767',3,9150);
INSERT INTO ACCOUNT VALUES('A-451',3,364);
INSERT INTO ACCOUNT VALUES('A-005',1,500);
INSERT INTO ACCOUNT VALUES('A-002',4,11400);
INSERT INTO ACCOUNT VALUES('A-009',4,5000);

INSERT INTO DEPOSITOR VALUES(1,'A-009');
INSERT INTO DEPOSITOR VALUES(2,'A-002');
INSERT INTO DEPOSITOR VALUES(3,'A-005');
INSERT INTO DEPOSITOR VALUES(4,'A-451');
INSERT INTO DEPOSITOR VALUES(5,'A-108');
INSERT INTO DEPOSITOR VALUES(6,'A-767');
INSERT INTO DEPOSITOR VALUES(7,'A-101');
INSERT INTO DEPOSITOR VALUES(8,'A-456');
INSERT INTO DEPOSITOR VALUES(9,'A-108');
INSERT INTO DEPOSITOR VALUES(10,'A-105');

INSERT INTO LOAN VALUES('L-01',1,9089);
INSERT INTO LOAN VALUES('L-02',2,908);
INSERT INTO LOAN VALUES('L-03',4,2000);
INSERT INTO LOAN VALUES('L-04',4,9000);
INSERT INTO LOAN VALUES('L-05',3,3500);

INSERT INTO BORROWER VALUES(1,'L-01');
INSERT INTO BORROWER VALUES(8,'L-02');
INSERT INTO BORROWER VALUES(5,'L-03');
INSERT INTO BORROWER VALUES(3,'L-04');
INSERT INTO BORROWER VALUES(9,'L-05');

INSERT INTO PAYMENT VALUES('L-01',001,'12-JUL-97',2000);
INSERT INTO PAYMENT VALUES('L-02',002,'19-JUL-97',9000);
INSERT INTO PAYMENT VALUES('L-03',004,'12-JUL-97',8000);
INSERT INTO PAYMENT VALUES('L-04',003,'21-JUN-97',100);
INSERT INTO PAYMENT VALUES('L-05',005,'17-FEB-99',2050);


select * from customer
where customer_id in(select customer_id from depositor
where account_number in(select account_number from account
where balance>500)
);


select * from customer 
where customer_id not in(select customer_id from borrower); 



select * from customer
where customer_id in(select customer_id from borrower);

(SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME , C.CUSTOMER_CITY, C.CUSTOMER_STREET, B.BRANCH_NAME, A.ACCOUNT_NUMBER
    FROM CUSTOMER C, BRANCH B, ACCOUNT A, DEPOSITOR D 
    WHERE((C.CUSTOMER_ID = D.CUSTOMER_ID) AND 
          (D.ACCOUNT_NUMBER = A.ACCOUNT_NUMBER) AND 
          (A.BRANCH_ID = B.BRANCH_ID)
         )
    ) ;


(select a.account_number,a.branch_id,a.balance
from account a,depositor d,customer c
where ((a.account_number=d.account_number)and (d.customer_id=c.customer_id)and (c.customer_id=1))
union
(select l.loan_number,l.branch_id,l.amount
from loan l, borrower b,customer c
where ((b.loan_number = l.loan_number)and (b.customer_id=c.customer_id)and (c.customer_id=1))));



SELECT   branch_name ,  assets
  FROM BRANCH b 
   where assets >=  ALL (select assets from branch c);


select * from customer
where customer_id in(select customer_id from borrower);