· 4 years ago · Jun 26, 2021, 09:26 PM
1use Bank
2go
3
4/*
5logical shcema:
6
7Branch(bname, bcity, assets)
8Customer(cid, cname, cstreet, ccity)
9Account(accid, #from_branch, amount)
10Loan(loanid, #from_branch, amount)
11Depositor(#customer, #account)
12Borrower(#customer, #loan)
13*/
14
15
16--CREATING THE TABLES (WITHOUT CONSTRAINTS)
17
18
19CREATE TABLE Branch(
20 bname varchar(20) not null,
21 bcity varchar(20) null,
22 assets int null
23)
24
25CREATE TABLE Customer(
26 cid int not null,
27 cname varchar(20) null,
28 ccity varchar(20) null
29)
30
31CREATE TABLE Account(
32 accid int not null,
33 from_branch varchar(20) not null,
34 amount int null
35)
36
37CREATE TABLE Loan(
38 loanid int not null,
39 from_branch varchar(20) not null,
40 amount int null
41)
42
43CREATE TABLE Depositor(
44 customer int not null,
45 account int not null,
46)
47
48CREATE TABLE Borrower(
49 customer int not null,
50 loan int not null,
51)
52
53--Creating tables ended.
54
55
56--Add the primary and foreign keys as constraints
57
58alter table Branch
59 add primary key(bname)
60
61alter table Customer
62 add primary key(cid)
63
64alter table Account
65 add primary key(accid), foreign key(from_branch) references Branch(bname)
66
67alter table loan
68 add primary key(loanid), foreign key(from_branch) references Branch(bname)
69
70alter table Depositor
71 add primary key(customer,account), foreign key(customer) references Customer(cid),
72 foreign key(account) references Account(accid)
73
74alter table Borrower
75 add primary key(customer,loan ), foreign key(customer) references Customer(cid),
76 foreign key(loan) references Loan(loanid)
77
78
79--Insert into the Bank database
80
81/* In order to insert instances into a table, we have some variations all involving
82the keyword insert into, let us see a basic exmaple: */
83
84--manually insert some tuples into the branch table:
85
86INSERT INTO Branch --bname, bcity, assets... Make sure you insert compatible datatypes
87 values ('QNB','Beirut', NULL),
88 ('Broadway', 'Saida', NULL),
89 ('Trust', 'Beirut', NULL),
90 ('Kings', 'Sour', NULL),
91 ('The Bankers', 'Sour', NULL),
92 ('Deposit', 'Saida', NULL),
93 ('MSN', 'Nabateih', NULL);
94
95INSERT INTO Customer
96 values (22, 'Mohammad','Beirut'),
97 (10, 'fatima', 'Beirut'),
98 (98, 'Ali', 'Saida'),
99 (31, 'Hasan', 'Sour'),
100 (120, 'Mahdi', 'Saida'),
101 (8, 'Mustafa', 'Sour'),
102 (18, 'Murtada', 'Nabateih'),
103 (52, 'Nour', 'Nabateih'),
104 (44, 'Lina', 'Beirut');
105
106INSERT INTO Account
107 values (1, 'QNB', 2000),
108 (2, 'Trust', 300),
109 (3, 'MSN', 2000),
110 (4, 'Deposit', 300),
111 (5, 'QNB', 170),
112 (6, 'The Bankers', 5500);
113
114INSERT INTO Loan
115 values (99, 'QNB', 1000),
116 (92, 'Kings', 2200),
117 (87, 'Trust', 600),
118 (80, 'Deposit', 8000);
119
120INSERT INTO DEPOSITOR
121 values (22, 1),
122 (10, 2),
123 (98, 6),
124 (31, 5),
125 (120, 4),
126 (52, 3);
127
128INSERT INTO Borrower
129 values (52, 87),
130 (120, 99),
131 (10, 80),
132 (8, 92);
133
134
135--Give the following Queries:
136
137--All branches' names
138SELECT bname
139FROM Branch
140
141--All customer names with a loan over 2000
142SELECT
143cname
144FROM Customer c, Borrower b, Loan l
145WHERE c.cid = b.customer and b.loan = l.loanid and l.amount > 2000
146--sol 2:
147SELECT
148cname FROM Customer WHERE cid in
149(SELECT customer FROM Borrower WHERE loan in
150 (SELECT loanid FROM Loan WHERE amount > 2000)
151)
152
153--All customers' ids having an account without having a loan
154SELECT
155customer
156FROM Depositor
157WHERE customer not in (
158 SELECT customer
159 FROM Borrower
160 )
161
162--All the customers' names who have accounts in branches from Beirut or Saida
163SELECT
164cname
165FROM Customer as c
166WHERE c.cid in (SELECT customer
167 FROM Depositor as d
168 WHERE d.account in (SELECT accid
169 FROM Account as a
170 WHERE a.from_branch in (SELECT bname FROM Branch WHERE bcity in ('Beirut', 'Saida'))
171 )
172 )
173--sol2:
174SELECT cname
175FROM Customer c, Depositor d
176WHERE c.cid = d.customer and d.account in
177( SELECT accid FROM Account a, Branch b WHERE a.from_branch = b.bname and b.bcity in ('Beirut', 'Saida') )
178
179--All customers' names with 'i' in them
180SELECT
181cname
182FROM Customer
183WHERE cname like '%i%'
184
185--All customers' names ending with 'a' unless they have an account from a branch in Beirut
186SELECT
187cname
188FROM Customer
189WHERE cname like '%a'
190EXCEPT
191(SELECT cname
192FROM Customer
193WHERE cid in (SELECT customer
194 FROM Depositor
195 WHERE account in (SELECT accid
196 FROM Account
197 WHERE from_branch in (SELECT bname
198 FROM Branch
199 WHERE bcity = 'Beirut'
200 )
201 )
202 )
203)
204
205--The number of accounts in each of the Beirut branches
206SELECT from_branch, count(*) as quantity
207FROM Account as A
208WHERE A.from_branch in
209 (SELECT bname
210 FROM Branch
211 WHERE bcity = 'Beirut')
212GROUP BY from_branch
213
214--the number of total accounts in beirut
215SELECT count(*)
216FROM Account a, Branch b
217WHERE a.from_branch = b.bname and b.bcity = 'Beirut'
218
219
220
221go
222
223--VIEWS
224
225CREATE VIEW customers_city as (SELECT cname, ccity FROM Customer) --What does it mean?
226go
227
228-- a view is a virtual table, the data will not be stored in a new table but we will treat it as so
229-- the above view means that now we have a virtual table formed of all the customer's name and cities (2 columns)
230-- When we select from a view, the dbms needs to go to the actual source data and select from it, since a view is virtual
231-- then why do we use it?
232
233CREATE VIEW customers_loan as(
234 SELECT c.cname, a.amount
235 FROM Customer c, Depositor d, Account a
236 WHERE c.cid = d.customer and d.account = a.accid
237)
238go
239SELECT * FROM customers_loan
240
241--for example now I can have access to the amount each customer have instantly without needing to write the same code over and over
242--Good database programmers create views instead of creating a table from the existing data to save space(as above)
243
244--we have the (not) EXISTS keyword that checks if a table has data in it (ex on slide)
245--the (not) UNIQUE keyword checks if a table has duplicates
246
247
248--Variations of insert:
249
250 --Create accounts with 1000$ for all people who have loan over 2000$ (the loanid will serve as the accid):
251 INSERT INTO Account
252 SELECT loanid , from_branch, 1000
253 FROM Loan
254 WHERE Loan.amount > 2000
255 SELECT * FROM Account
256
257 INSERT INTO Depositor
258 SELECT cid, loanid
259 FROM Customer c, Loan, Borrower
260 WHERE c.cid = Borrower.customer and Loan.amount>2000
261
262
263go
264
265-- Fill in the assets of the each bank (assets being how much total money there is in it's accounts)
266--Sol 1:
267CREATE VIEW res as
268SELECT bname, sum(a.amount) as sum
269FROM Account as a, Branch as b
270WHERE a.from_branch = b.bname
271GROUP BY b.bname
272
273go
274
275UPDATE Branch
276SET assets = (SELECT sum FROM res WHERE Branch.bname = res.bname)
277
278--Sol 2:
279
280UPDATE Branch
281SET assets = (SELECT sum(amount) FROM ACCOUNT WHERE from_branch = bname)
282
283--Delete all useless branches
284--Sol 1:
285DELETE FROM Branch
286WHERE bname not in
287(SELECT a.from_branch FROM Account a) and bname not in
288(SELECT l.from_branch FROM Loan l)
289
290--Sol 2:
291DELETE FROM Branch
292WHERE bname in
293(SELECT bname FROM Branch except
294 (
295 SELECT from_branch FROM Loan
296 UNION
297 SELECT from_branch FROM Account
298 )
299)
300
301-- Give cust ids of people that have accounts in all branches located in Beirut
302
303-- Add 500$ to the assets of branches having more than 1 accounts in them
304
305
306
307
308
309
310
311
312