Database-Management-System/DBMS_PRACTICAL-1.sql at main · Utsav-7/Database-Management-System · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
-- -------------------------------------------DEPOSIT TABLE-----------------------------------
CREATE TABLE DEPOSIT
(
     ACTNO VARCHAR2(5),
     CNAME VARCHAR2(18),
     BNAME VARCHAR2(18),
     AMOUNT NUMBER (8,2),
     ADATE DATE
);
INSERT INTO DEPOSIT VALUES('100','ANIL','VRCE',1000.00,'1-MAR-95');
INSERT INTO DEPOSIT VALUES('101','SUNIL','AJNI',5000.00,'4-JAN-96');
INSERT INTO DEPOSIT VALUES('102','MEHUL','KAROLBAGH',3500.00,'17-NOV-95');
INSERT INTO DEPOSIT VALUES('104','MADHURI','CHANDI',1200.00,'17-DEC-95');
INSERT INTO DEPOSIT VALUES('105','PRMOD','M.G.ROAD',3000.00,'27-MAR-96');
INSERT INTO DEPOSIT VALUES('106','SANDIP','ANDHERI',2000.00,'31-MAR-96');
INSERT INTO DEPOSIT VALUES('107','SHIVANI','VIRAR',1000.00,'5-SEP-95');
INSERT INTO DEPOSIT VALUES('108','KRANTI','NEHRU PLACE',5000.00,'2-JUL-95');
INSERT INTO DEPOSIT VALUES('109','MINU','POWAI',7000.00,'10-AUG-95');

SELECT * FROM DEPOSIT;


-- -------------------------------------------BRANCH TABLE-----------------------------------
CREATE TABLE BRANCH
(
    BNAME VARCHAR2(18),
    CITY VARCHAR2(18)
);

INSERT INTO BRANCH VALUES('VRCE','NAGPUR');
INSERT INTO BRANCH VALUES('AJNI','NAGPUR');
INSERT INTO BRANCH VALUES('KAROLBAGH','DELHI');
INSERT INTO BRANCH VALUES('CHANDI','DELHI');
INSERT INTO BRANCH VALUES('DHARAMPETH','NAGPUR');
INSERT INTO BRANCH VALUES('M.G.ROAD','BANGLORE');
INSERT INTO BRANCH VALUES('ANDHERI','BOMBAY');
INSERT INTO BRANCH VALUES('VIRAR','BOMBAY');
INSERT INTO BRANCH VALUES('NEHRU PLACE','DELHI');
INSERT INTO BRANCH VALUES('POWAI','BOMBAY');

SELECT * FROM BRANCH;

-- -------------------------------------------CUSTOMERS TABLE-----------------------------------
CREATE TABLE CUSTOMERS
(
    CNAME VARCHAR2(19),
    CITY VARCHAR2(18)
);
INSERT INTO CUSTOMERS VALUES('ANIL','CALCUTTA');
INSERT INTO CUSTOMERS VALUES('SUNIL','DELHI');
INSERT INTO CUSTOMERS VALUES('MEHUL','BARODA');
INSERT INTO CUSTOMERS VALUES('MANDAR','PATNA');
INSERT INTO CUSTOMERS VALUES('MADHURI','NAGPUR');
INSERT INTO CUSTOMERS VALUES('PRAMOD','NAGPUR');
INSERT INTO CUSTOMERS VALUES('SANDIP','SURAT');
INSERT INTO CUSTOMERS VALUES('SHIVANI','BOMBAY');
INSERT INTO CUSTOMERS VALUES('KRANTI','BOMBAY');
INSERT INTO CUSTOMERS VALUES('NAREN','BOMBAY');

SELECT * FROM CUSTOMERS;

-- -------------------------------------------BORROW TABLE-------------------------------------
CREATE TABLE BORROW
(
    LOANNO VARCHAR2(5),
    CNAME VARCHAR2(18),
    BNAME VARCHAR2(18),
    AMOUNT NUMBER (8,2)
);

INSERT INTO BORROW VALUES('201','ANIL','VRCE',1000.00);
INSERT INTO BORROW VALUES('206','MEHUL','AJNI',5000.00);
INSERT INTO BORROW VALUES('311','SUNIL','DHARAMPETH',3000.00);
INSERT INTO BORROW VALUES('321','MADHURI','ANDHERI',2000.00);
INSERT INTO BORROW VALUES('375','PRMOD','VIRAR',8000.00);
INSERT INTO BORROW VALUES('481','KRANTI','NEHRU PLACE',3000.00);

SELECT * FROM BORROW;


-- ------------------------------------------QUERIES-------------------------------------
1. DESC DEPOSIT;
   DESC BRANCH;
2. DESC CUSTOMERS;
   DESC BORROW;
3. SELECT * FROM DEPOSIT;
4. SELECT * FROM CUSTOMERS;
5. SELECT * FROM BRANCH;
6. SELECT * FROM BORROW;
7. SELECT ACTNO,AMOUNT FROM DEPOSIT;
8. SELECT CNAME,AMOUNT FROM DEPOSIT WHERE AMOUNT>4000;
9. SELECT CNAME,ADATE FROM DEPOSIT WHERE ADATE>'1-DEC-96';
10. SELECT BNAME,CITY FROM BRANCH WHERE BNAME='KAROLBAGH';
11. SELECT ACTNO,AMOUNT,ADATE FROM DEPOSIT WHERE ADATE BETWEEN '1-DEC-96' AND '1-JUN-96';
12. SELECT CNAME,BNAME FROM BORROW WHERE BNAME='VRCE';