$30
HW3. More SQL
HW3. More SQL
Objectives
In this assignment, you will write more complex SQL queries to query a database.
How to use Order By to sort data
How to use Set Operators to union/intersect multiple tables
How to use Join Opeartor to join multiple tables
How to use Aggregations and Group By to aggregate data
How to write subqueries in SQL
Background
Suppose you work at a bank as a data analyst. Your main job is to analyze the data stored in their database. Please download the database at this link.
The database has five tables. The following shows their schemas. Primary key attributes are underlined and foreign keys are noted in superscript.
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumberFK-Branch}
Owns = {customerIDFK-Customer, accNumberFK-Account}
Transactions = {transNumber, accNumberFK-Account, amount}
Employee = {sin, firstName, lastName, salary, branchNumberFK-Branch}
Branch = {branchNumber, branchName, managerSINFK-Employee, budget}
Notes
The customerID attribute (Customer) is a unique number that represents a customer, it is not a customer's SIN
The accNumber attribute (Account) represents the account number
The balance (Account) attribute represents the total amount in an account
The type (Account) attribute represents the type an account: chequing, saving, or business
The Owns relation represents a many-to-many relationship (between Customer and Account)
The transNumber attribute (Transactions) represents a transaction number, combined with account number it uniquely identify a transaction
The branchNumber attribute (Customer) uniquely identifies a branch
The managerSIN attribute (Customer) represents the SIN of the branch manager
Questions （1 point per question)
Write SQL queries to return the data specified in questions 1 to 20.
Query Requirement
The answer to each question should be a single SQL query
You must order each query as described in the question, order is always ascending unless specified otherwise
Every column in the result should be named, so if the query asks you to return something like income minus salary make sure that you include an AS statement to name the column
While your queries will not be assessed on their efficiency, marks may be deducted if unnecessary tables are included in the query (for example including Owns and Customer when you only require the customerID of customers who own accounts)
Execute the next two cells
%load_ext sql
%sql sqlite:///bank.db
'Connected: @bank.db'
Queries
1. First name, last name, income of customers whose income is within [60,000, 70,000], order by income (desc), lastName, firstName.
%%sql
SELECT firstName, lastName, Income
FROM Customer
WHERE Income >= 60000 AND Income <= 70000
ORDER BY Income DESC, lastName, firstName
* sqlite:///bank.db
Done.
firstName lastName income
Steven Johnson 69842
Bonnie Johnson 69198
Larry Murphy 69037
Evelyn Scott 68832
Jeffrey Griffin 68812
Randy Mitchell 67895
Anna Cooper 67275
Kimberly Powell 65555
Mildred Reed 64499
Helen Sanchez 63333
Martha Young 63192
Willie Jones 61312
Andrew Jones 61289
Harold Young 60731
Cheryl Parker 60586
Henry Ward 60404
2. SIN, branch name, salary and manager’s salary - salary (that is, the salary of the employee’s manager minus salary of the employee) of all employees in New York, London or Berlin, order by ascending (manager salary - salary).
%%sql
SELECT E.sin, B.branchName, E.salary, M.salary-E.salary as 'manager salary - salary'
FROM Employee E, Branch B, Employee M
WHERE E.branchNumber = B.branchNumber AND B.managerSIN = M.sin AND
(B.branchName = "New York" or B.branchName = "London" or B.branchName = "Berlin")
ORDER BY M.salary - E.salary;
* sqlite:///bank.db
Done.
sin branchName salary manager salary - salary
23528 New York 94974 -4491
11285 New York 93779 -3296
31964 New York 90483 0
55700 London 99289 0
99537 Berlin 90211 0
38351 New York 86093 4390
97216 London 89746 9543
40900 New York 77533 12950
58707 London 85934 13355
57796 New York 75896 14587
79510 London 84199 15090
33743 Berlin 70396 19815
30513 London 78839 20450
27004 New York 69842 20641
81263 New York 67275 23208
28453 London 75146 24143
68383 Berlin 65722 24489
68006 London 73264 26025
42182 New York 60059 30424
95429 Berlin 51003 39208
29474 London 59360 39929
59653 New York 49066 41417
82333 Berlin 45443 44768
92400 New York 44853 45630
48264 London 52031 47258
14209 New York 36784 53699
55146 London 42893 56396
86032 London 42301 56988
15153 Berlin 32204 58007
24901 New York 32470 58013
45676 London 41201 58088
58844 Berlin 30403 59808
81302 Berlin 29426 60785
85587 London 38385 60904
11499 Berlin 27769 62442
79162 New York 27531 62952
82495 London 35868 63421
70163 Berlin 25389 64822
82076 New York 25328 65155
76576 Berlin 23540 66671
77100 Berlin 23477 66734
97976 London 32400 66889
96443 Berlin 19971 70240
82244 London 29009 70280
82464 London 28953 70336
90667 New York 19534 70949
90368 New York 19403 71080
24065 London 25870 73419
25902 New York 14334 76149
37490 London 23082 76207
71076 New York 13393 77090
93942 New York 12065 78418
78993 New York 10953 79530
41545 Berlin 9534 80677
49069 Berlin 7600 82611
85925 London 15370 83919
24469 London 13950 85339
49024 Berlin 3349 86862
3. First name, last name, and income of customers whose income is at least twice the income of any customer whose lastName is Butler, order by last name then first name.
%%sql
SELECT firstName, lastName, Income
FROM Customer
WHERE income > (SELECT Butler.income*2
FROM Customer Butler
WHERE Butler.lastName = "Butler")
ORDER BY lastName, firstName
* sqlite:///bank.db
Done.
firstName lastName income
Ernest Adams 75896
William Adams 77570
Carol Alexander 56145
Anthony Bailey 72328
Ruby Barnes 84562
Ronald Bell 91166
Philip Brooks 83907
Clarence Brown 95879
Jason Brown 53770
Carlos Clark 77423
Shawn Clark 58007
Billy Coleman 77001
Mark Coleman 72382
Dennis Collins 89746
Sharon Collins 99531
Jerry Cook 91174
Anna Cooper 67275
Jimmy Cooper 79613
Victor Doom 97412
Phillip Edwards 99339
Andrew Evans 59137
Dennis Flores 84560
Diana Gonzales 94777
Joe Gonzalez 89692
Brian Gray 71601
Christine Gray 95821
Benjamin Green 54011
Edward Green 73689
Jeffrey Griffin 68812
Johnny Hall 78168
Sandra Hayes 83395
Antonio Henderson 84174
Deborah Hernandez 90211
Roy Hernandez 59788
Charles Hill 92397
Deborah Hill 70455
Bonnie Johnson 69198
Steven Johnson 69842
Andrew Jones 61289
Arthur Jones 57935
Robin Jones 54320
Willie Jones 61312
Albert Kelly 70028
Phyllis Lopez 92919
Judith Martinez 59593
Randy Mitchell 67895
Helen Morgan 98442
Ralph Morgan 59759
Katherine Morris 79080
Larry Murphy 69037
Sean Nelson 96216
Cheryl Parker 60586
Ernest Perez 59757
Victor Perez 86093
Kimberly Powell 65555
Mary Powell 74194
Mildred Reed 64499
Cynthia Ross 53639
Helen Sanchez 63333
Donna Sanders 76164
Joe Sanders 95144
Annie Scott 59925
Evelyn Scott 68832
Norma Simmons 99902
Susan Smith 86045
Jeremy Stewart 73556
Phillip Taylor 58767
Eugene Thomas 79481
Roger Turner 84919
Henry Ward 60404
Juan Ward 84262
Steve Ward 78495
Gerald Watson 55740
Amanda White 59360
Margaret White 75146
Ryan Williams 95170
Jack Wilson 70247
Louise Wilson 96214
Harold Young 60731
Martha Young 63192
4. Customer ID, income, account numbers and branch numbers of customers with income greater than 90,000 who own an account at both London and Latveria branches, order by customer ID then account number. The result should contain all the account numbers of customers who meet the criteria, even if the account itself is not held at London or Latveria.
%%sql
SELECT C.customerID, C.Income, O.accNumber, A.branchNumber
FROM Customer C, Owns O, Account A, Branch B
WHERE C.customerID = O.customerID AND O.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND C.Income >90000
AND C.customerID in
(SELECT O.customerID
FROM Owns O, Account A, Branch B
WHERE O.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND B.branchName = 'London')
AND C.customerID in
(SELECT O.customerID
FROM Owns O, Account A, Branch B
WHERE O.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND B.branchName = 'Latveria')
ORDER BY C.customerID, O.accNumber
* sqlite:///bank.db
Done.
customerID income accNumber branchNumber
27954 94777 10 1
27954 94777 68 3
27954 94777 239 2
51850 97412 35 1
51850 97412 129 1
51850 97412 161 3
51850 97412 182 2
62312 92919 61 3
62312 92919 116 1
62312 92919 219 2
62312 92919 261 5
5. Customer ID, types, account numbers and balances of business (type BUS) and savings (type SAV) accounts owned by customers who own at least one business account or at least one savings account, order by customer ID, then type, then account number.
%%sql
SELECT O.customerID, A.type, A.accNumber, A.balance
FROM Owns O, Account A
WHERE O.accNumber = A.accNumber AND (A.type="BUS" OR A.type ="SAV")
ORDER BY O.customerID, A.type, A.accNumber
* sqlite:///bank.db
Done.
customerID type accNumber balance
11790 BUS 150 77477.04
11790 SAV 1 118231.13
11799 BUS 174 23535.33
13230 SAV 137 76535.96
13697 SAV 251 33140.3
13874 SAV 82 29525.31
14295 BUS 106 102297.76
14295 BUS 273 65213.27
14295 SAV 245 95413.18
16837 BUS 197 19495.5
16837 BUS 212 54950.8
19308 BUS 234 35041.68
19308 SAV 82 29525.31
19308 SAV 260 55607.43
19973 SAV 140 99233.93
20287 SAV 222 81498.87
22050 BUS 221 105068.53
22050 SAV 19 83432.52
23010 SAV 250 28400.79
25052 BUS 290 87236.94
25052 BUS 297 77378.21
25052 SAV 171 94194.62
25159 BUS 242 31705.31
25159 SAV 270 24148.47
27004 BUS 271 43452.71
27004 SAV 70 33716.29
27004 SAV 96 37055.15
27954 BUS 10 72667.44
27954 SAV 68 37748.82
28453 BUS 173 15923.34
28453 SAV 89 97457.14
29474 SAV 60 53485.04
29474 SAV 85 69476.72
30525 SAV 125 44498.65
30525 SAV 270 24148.47
30807 BUS 91 62531.41
30807 SAV 156 41520.57
32422 BUS 254 63839.93
32422 SAV 282 101063.84
33133 SAV 216 74211.19
33133 SAV 263 22682.38
33726 SAV 243 49766.04
33850 SAV 256 72686.41
33913 SAV 260 55607.43
34069 BUS 27 81162.08
34069 BUS 177 52032.61
34069 BUS 218 103650.37
35059 BUS 264 56998.05
35059 SAV 213 41508.56
35780 SAV 217 50874.79
36238 BUS 240 96635.34
37716 SAV 142 86931.71
37716 SAV 186 46559.63
38351 SAV 95 22741.92
38351 SAV 189 67788
38602 BUS 74 70301.55
38861 BUS 294 36864.46
38861 SAV 228 77031.07
38861 SAV 248 65919.35
40351 BUS 123 80993.9
40351 BUS 139 101394.11
41545 BUS 51 45793.34
41545 SAV 32 83408.19
41648 SAV 135 105420.87
42612 BUS 225 18954.6
43705 BUS 236 23084.55
43705 SAV 192 19162.66
44065 SAV 193 20098.57
44637 SAV 69 18172.88
44922 BUS 133 86457.17
44922 BUS 279 88794.87
46058 SAV 110 36235.58
46058 SAV 245 95413.18
46630 BUS 255 29913.6
46630 SAV 160 87925.09
46937 SAV 9 132271.23
47953 BUS 115 102857.55
47953 SAV 48 63416.35
49747 SAV 142 86931.71
50742 BUS 107 102366.95
51850 BUS 129 35668.54
51850 SAV 35 77214.48
52189 BUS 249 83863.1
52189 BUS 290 87236.94
52189 SAV 53 49101.06
52622 SAV 95 22741.92
52622 SAV 257 69711.29
55194 BUS 93 79642.98
57796 SAV 99 17951.51
59366 BUS 36 65482.68
59366 SAV 26 112046.36
59366 SAV 64 87815.69
59366 SAV 152 31858.67
60959 SAV 205 49952.82
61379 BUS 151 82207.06
61976 BUS 167 20965.26
61976 SAV 235 44741.9
62312 BUS 116 34798.47
62312 BUS 219 29672.22
62312 SAV 261 55402.81
63772 SAV 134 37690.5
63859 SAV 291 101504.47
64063 BUS 167 20965.26
64063 BUS 200 100035.01
65044 BUS 179 42494.67
65044 SAV 117 14203.1
65441 BUS 124 11732.87
65441 BUS 269 79912.41
65441 SAV 181 24453.37
66418 BUS 8 121267.54
66418 SAV 278 48434.8
66744 SAV 60 53485.04
67384 BUS 234 35041.68
67384 SAV 37 9421.53
67384 SAV 223 41345.93
69101 BUS 55 49713.83
69101 BUS 63 57035.26
69101 BUS 273 65213.27
69256 BUS 123 80993.9
69256 BUS 151 82207.06
73386 SAV 94 74260.98
73386 SAV 253 74761.19
73925 BUS 63 57035.26
73925 SAV 143 27480.19
75671 BUS 63 57035.26
75671 SAV 194 92152.03
76786 SAV 170 69580.12
77100 SAV 101 17004.14
77100 SAV 230 63379.26
77100 SAV 253 74761.19
78477 SAV 9 132271.23
78477 SAV 49 87557.84
79601 SAV 26 112046.36
79601 SAV 110 36235.58
80315 BUS 16 75390.64
80315 BUS 199 87161.89
80315 SAV 128 73129.43
80321 SAV 121 103512.78
80321 SAV 146 95876.24
81108 BUS 92 71552.54
81108 SAV 121 103512.78
81263 BUS 4 106503.6
81263 SAV 98 69297.68
82333 BUS 178 16105.24
82333 SAV 103 90491.84
82464 SAV 86 50837.08
83038 BUS 237 91951.43
83620 BUS 138 104044.22
84873 BUS 18 103579.69
84873 BUS 141 93073.14
85981 BUS 209 88574.32
86357 SAV 86 50837.08
86858 SAV 284 85756.7
87013 SAV 145 34588.13
87822 BUS 175 70997.62
87822 SAV 275 95955.98
88164 SAV 120 27253.21
88375 BUS 22 78928.42
88375 BUS 43 82028.05
89902 BUS 4 106503.6
89902 SAV 48 63416.35
89902 SAV 78 72742.21
90649 BUS 247 72957.44
90667 BUS 267 24588.13
90667 SAV 97 11797.34
90798 SAV 146 95876.24
91672 BUS 8 121267.54
91672 BUS 215 83349.17
92389 SAV 72 59597.18
92389 SAV 193 20098.57
92389 SAV 268 91951.04
92389 SAV 280 45824.72
93300 BUS 80 13574.85
93300 BUS 166 65453.56
93300 SAV 224 61398.87
93300 SAV 298 92525.41
93791 BUS 238 46680.5
93791 SAV 44 69658.25
93791 SAV 155 55474.05
93995 BUS 159 44147.62
93995 BUS 180 40624.03
96475 SAV 25 105997.07
97216 BUS 88 4563.37
98826 BUS 114 67973.27
98826 SAV 12 77626.76
98923 SAV 40 72419.68
99537 SAV 243 49766.04
6. Branch name, account number and balance of accounts with balances greater than $110,000 held at the branch managed by Phillip Edwards, order by account number.
%%sql
SELECT B.branchName, A.accNumber, A.balance
FROM Account A, Branch B, Employee E
WHERE A.branchNumber = B.branchNumber AND B.managerSIN = E.SIN AND
A.balance > 110000 AND E.firstName = "Phillip" AND E.lastName = "Edwards"
ORDER BY A.accNumber
* sqlite:///bank.db
Done.
branchName accNumber balance
London 1 118231.13
London 8 121267.54
London 9 132271.23
London 13 112505.84
London 26 112046.36
London 28 112617.97
London 31 111209.89
London 119 113473.16
7. Customer ID of customers who have an account at the New York branch, who do not own an account at the London branch and who do not co-own an account with another customer who owns an account at the London branch, order by customer ID. The result should not contain duplicate customer IDs.
%%sql
SELECT DISTINCT O.customerID
FROM Owns O, Account A, Branch B
WHERE O.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND
B.branchName = "New York" AND O.customerID NOT IN
(SELECT O1.customerid
FROM Owns O1, owns O2
WHERE O1.accNumber = O2.accNumber AND O2.customerid IN
(SELECT O3.customerID
FROM Owns O3, Account A, Branch B
WHERE O3.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND
b.branchName = "London"))
ORDER BY O.customerID
* sqlite:///bank.db
Done.
customerID
11696
13874
16837
38602
44637
46630
57796
61976
64063
87013
87822
90534
93300
93791
93995
96712
97121
8. SIN, first name, last name, and salary of employees who earn more than $70,000, if they are managers show the branch name of their branch in a fifth column (which should be NULL/NONE for most employees), order by branch name. You must use an outer join in your solution (which is the easiest way to do it).
%%sql
SELECT E.SIN, E.firstName, E.lastName, E.salary, B.branchName
FROM employee E LEFT OUTER JOIN Branch B
ON E.sin = B.managersin
WHERE E.salary > 70000
ORDER BY B.branchName
* sqlite:///bank.db
Done.
sin firstName lastName salary branchName
11285 Rebecca Simmons 93779 None
23528 Lisa Russell 94974 None
28453 Margaret White 75146 None
30513 Timothy Perez 78839 None
33743 Jacqueline Scott 70396 None
38351 Victor Perez 86093 None
40900 Chris Garcia 77533 None
57796 Ernest Adams 75896 None
58707 Clarence Watson 85934 None
63772 Mary Powell 74194 None
68006 Stephen Ross 73264 None
79510 Nicholas Hernandez 84199 None
81126 Steve Campbell 71185 None
86213 Beverly Martinez 85853 None
95246 David Garcia 98773 None
97216 Dennis Collins 89746 None
99537 Deborah Hernandez 90211 Berlin
51850 Victor Doom 87242 Latveria
55700 Phillip Edwards 99289 London
63963 Cheryl Thompson 71284 Moscow
31964 Victor Doom 90483 New York
9. Exactly as question eight, except that your query cannot include any join operation.
%%sql
SELECT E.SIN, E.firstName, E.lastName, E.salary, B.branchName
FROM employee E, Branch B
WHERE E.sin = B.managerSIN AND E.salary > 70000
UNION
SELECT E.SIN, E.firstName, E.lastName, E.salary, NULL
FROM employee E, Branch B
WHERE E.branchNumber = B.branchNumber AND E.salary > 70000 AND E.SIN <> B.managerSIN
ORDER BY B.branchName
* sqlite:///bank.db
Done.
sin firstName lastName salary branchName
11285 Rebecca Simmons 93779 None
23528 Lisa Russell 94974 None
28453 Margaret White 75146 None
30513 Timothy Perez 78839 None
33743 Jacqueline Scott 70396 None
38351 Victor Perez 86093 None
40900 Chris Garcia 77533 None
57796 Ernest Adams 75896 None
58707 Clarence Watson 85934 None
63772 Mary Powell 74194 None
68006 Stephen Ross 73264 None
79510 Nicholas Hernandez 84199 None
81126 Steve Campbell 71185 None
86213 Beverly Martinez 85853 None
95246 David Garcia 98773 None
97216 Dennis Collins 89746 None
99537 Deborah Hernandez 90211 Berlin
51850 Victor Doom 87242 Latveria
55700 Phillip Edwards 99289 London
63963 Cheryl Thompson 71284 Moscow
31964 Victor Doom 90483 New York
10. Customer ID, first name, last name and income of customers who have income greater than 5000 and own accounts in all of the branches that Helen Morgan owns accounts in, order by income in descreasing order.
%%sql
SELECT C.customerID, C.firstName, C.lastName, C.Income
FROM customer C
WHERE NOT EXISTS(SELECT A2.branchNumber
FROM customer C2, owns O2, account A2
WHERE C2.customerID = O2.customerID AND O2.accNumber = A2.accNumber
AND C2.firstName = "Helen" AND C2.lastName = "Morgan"
EXCEPT
SELECT A1.branchNumber
FROM owns O1, account A1
WHERE C.customerID = O1.customerID AND O1.accNumber = A1.accNumber
)
AND C.Income>5000
ORDER BY C.Income DESC
* sqlite:///bank.db
Done.
customerID firstName lastName income
90649 Helen Morgan 98442
99537 Deborah Hernandez 90211
65441 Arthur Thompson 36915
11. SIN, first name, last name and salary of the lowest paid employee (or employees) of the London branch, order by sin.
%%sql
SELECT E.sin, E.firstName, E.lastName, E.salary
FROM employee E, branch B
WHERE E.branchNumber = B.branchNumber AND B.branchName = "London" AND E.sin NOT IN
(SELECT E1.sin
FROM employee E1, employee E2, branch B
WHERE E1.salary > E2.salary AND E1.branchNumber = B.branchNumber AND E2.branchNumber = B.branchNumber AND
B.branchName = "London")
ORDER BY E.SIN
* sqlite:///bank.db
Done.
sin firstName lastName salary
24469 Frank Rodriguez 13950
12. Branch name, and the difference of maximum and minimum (salary gap) and average salary of the employees at each branch, order by branch name.
%%sql
SELECT B.branchName, max(E.salary) - min(E.salary) as "salary gap", avg(E.salary) as "average salary"
FROM employee E, branch B
WHERE E.branchNumber = B.branchNumber
GROUP BY E.branchNumber
ORDER BY B.branchName
* sqlite:///bank.db
Done.
branchName salary gap average salary
Berlin 86862 34714.8125
Latveria 89282 56143.46153846154
London 85339 50813.80952380953
Moscow 58759 49065.71428571428
New York 84021 48649.90476190476
13. Count of the number of employees working at the New York branch and Count of the number of different last names of employees working at the New York branch (two numbers in a single row).
%%sql
SELECT count(E.sin) AS "Number of Employees" ,count(DISTINCT E.lastName) as "Number of last names"
FROM employee E, branch B
WHERE E.branchnumber = B.branchNumber AND B.branchName = 'New York'
* sqlite:///bank.db
Done.
Number of Employees Number of last names
21 20
14. Sum of the employee salaries (a single number) at the New York branch.
%%sql
SELECT sum(E.salary) as "New York Employee Salary Sum"
FROM employee E, branch B
WHERE E.branchNumber = B.branchNumber AND B.branchName = "New York"
* sqlite:///bank.db
Done.
New York Employee Salary Sum
1021648
15. Customer ID, first name and last name of customers who own accounts at a max of four different branches, order by Last Name and first Name.
%%sql
SELECT DISTINCT C.customerID, C.firstName, C.lastName
FROM customer C, owns O, account A
WHERE C.customerID = O.customerID AND O.accNumber = A.accNumber AND C.customerID IN
(SELECT C1.customerID
FROM customer C1, owns O1, account A1
WHERE C1.customerID = O1.customerID AND O1.accNumber = A1.accNumber
GROUP BY C1.customerID
HAVING count(DISTINCT A1.branchNumber) <= 4)
ORDER BY C.lastName, C.firstName
* sqlite:///bank.db
Done.
customerID firstName lastName
57796 Ernest Adams
66418 Stephanie Adams
98826 William Adams
86858 Carol Alexander
77100 Laura Alexander
25052 Jack Anderson
89197 Lawrence Anderson
41545 Terry Bailey
33133 Henry Barnes
64055 Laura Barnes
18166 Ruby Barnes
43705 Louis Bell
33913 Ronald Bell
67384 Lawrence Brooks
13230 Marie Brooks
49747 Philip Brooks
25159 Shirley Brooks
72583 Clarence Brown
30525 Helen Carter
44637 Susan Carter
83620 Carlos Clark
35380 Harold Clark
41648 Shawn Clark
46630 Billy Coleman
64063 Mark Coleman
97216 Dennis Collins
85981 Sharon Collins
33726 Jerry Cook
28505 Joe Cook
81263 Anna Cooper
13874 Jimmy Cooper
51850 Victor Doom
66744 Rachel Edwards
86357 Andrew Evans
44922 Dennis Flores
27954 Diana Gonzales
87978 Christopher Gonzalez
89902 Joe Gonzalez
78477 Brian Gray
32422 Christine Gray
11790 Benjamin Green
91672 Edward Green
87416 Jeffrey Griffin
38003 Joshua Griffin
10839 Amy Hayes
40351 Sandra Hayes
82464 Susan Hayes
61969 Antonio Henderson
66386 Chris Henderson
99537 Deborah Hernandez
76786 Roy Hernandez
13697 Charles Hill
65044 Deborah Hill
16837 Stephen Hughes
55146 Mark Jackson
38861 Gerald James
93300 Bonnie Johnson
27004 Steven Johnson
60959 Andrew Jones
73386 Arthur Jones
75671 Billy Jones
38602 Robin Jones
81108 Willie Jones
19973 Albert Kelly
42612 Joe Kelly
80321 Kimberly Kelly
34069 Earl Lee
87822 Dennis Long
45960 Jacqueline Long
11696 Denise Lopez
62312 Phyllis Lopez
47953 Frank Martinez
11799 Judith Martinez
97121 Justin Martinez
85587 Justin Mitchell
88375 Randy Mitchell
90649 Helen Morgan
52622 Maria Morgan
36238 Ralph Morgan
55194 Kathleen Morris
93995 Kevin Morris
30807 Roy Morris
90667 Carl Murphy
30622 Harry Murphy
90534 Joseph Murphy
35059 Larry Murphy
96475 Sean Nelson
84873 Cheryl Parker
88164 Jimmy Parker
61976 Wanda Parker
69101 Ernest Perez
38351 Victor Perez
87013 Patrick Peterson
73925 Doris Powell
96712 Kimberly Powell
63772 Mary Powell
14295 Anne Ramirez
44065 Benjamin Ramirez
98923 Dennis Reed
19308 Mildred Reed
46058 Adam Rivera
91520 Keith Rivera
92389 Amy Ross
22050 Helen Sanchez
91349 Donna Sanders
79601 Joe Sanders
52189 Shawn Sanders
37716 Annie Scott
93791 Evelyn Scott
13423 Norma Simmons
82333 Charles Smith
59366 Susan Smith
73562 Jeremy Stewart
83038 Phillip Taylor
20287 Eugene Thomas
69256 Jacqueline Thomas
65441 Arthur Thompson
80315 Roger Turner
33850 Henry Ward
46937 Juan Ward
44459 Gerald Watson
29474 Amanda White
28453 Margaret White
61379 Ryan Williams
90798 Aaron Wilson
81495 Jack Wilson
50742 Louise Wilson
82244 Douglas Wright
35780 Harold Young
63859 Maria Young
23010 Martha Young
16. Average income of customers older than 60 and average income of customers younger than 20, the result must have two named columns, with one row, in one result set (hint: look up SQLite time and date functions).
%%sql
SELECT
(SELECT AVG(Customer.Income)
FROM Customer
WHERE (date('now') - Customer.birthDate) > 60)
as 'avg Income older than 60',
(SELECT AVG(Customer.Income)
FROM Customer
WHERE (date('now') - Customer.birthDate) < 20)
as 'avg Income younger than 20'
* sqlite:///bank.db
Done.
avg Income older than 60 avg Income younger than 20
54878.392857142855 41888.333333333336
17. Customer ID, last name, first name, income, and average account balance of customers who have at least three accounts, and whose last names begin with S and contain an e (e.g. Steve) or whose first names begin with A and have the letter n just before the last 2 letters (e.g. Anne), order by customer ID. Note that to appear in the result customers must have at least 2 accounts and satisfy one (or both) of the name conditions.
%%sql
SELECT C.customerID, C.lastName, C.firstName, C.income, AVG(A.balance) as "average account balance"
FROM Customer C, Owns O, Account A
WHERE C.CustomerID = O.CustomerID AND O.accNumber = A.accNumber AND (C.lastName LIKE 'S%e%' OR C.firstName LIKE 'A%n__')
AND C.customerID IN
(SELECT O1.customerID
FROM Owns O1
GROUP BY O1.customerID
HAVING count(O1.accNumber)>=3)
GROUP BY C.customerID
ORDER BY C.customerID
* sqlite:///bank.db
Done.
customerID lastName firstName income average account balance
14295 Ramirez Anne 44495 87641.40333333334
29474 White Amanda 59360 68591.57333333335
52189 Sanders Shawn 13615 68936.21166666666
79601 Sanders Joe 95144 58843.438
81263 Cooper Anna 67275 68895.66333333333
18. Account number, balance, sum of transaction amounts, and balance - transaction sum for accounts in the London branch that have at least 15 transactions, order by transaction sum.
%%sql
SELECT T.accNumber, A.balance, sum(t.amount) as "transaction sum", A.balance-sum(t.amount) as "balance - transaction sum"
FROM transactions T, Account A, branch B
WHERE T.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND B.branchName = "London"
GROUP BY A.accNumber
HAVING count(transNumber) >= 15
ORDER BY sum(T.amount)
* sqlite:///bank.db
Done.
accNumber balance transaction sum balance - transaction sum
113 82792.58 82792.58 0.0
9 132271.23 132271.22999999998 2.9103830456733704e-11
19. Branch name, account type, and average transaction amount of each account type for each branch for branches that have at least 50 accounts of any type, order by branch name, then account type.
%%sql
SELECT B.branchName, A.type, AVG(T.amount) as "average transaction amount"
FROM transactions T, account A, branch B
WHERE T.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND B.branchNumber IN
(SELECT A1.branchNumber
FROM Account A1
GROUP BY A1.branchNumber
HAVING count(a1.branchNumber) >= 50)
GROUP BY A.branchNumber, A.type
ORDER BY B.branchName, A.type
* sqlite:///bank.db
Done.
branchName type average transaction amount
Latveria BUS 6323.264077253221
Latveria CHQ 6950.850576923073
Latveria SAV 6925.2736708860775
London BUS 9334.790548780491
London CHQ 8947.788654970751
London SAV 8281.66272727273
New York BUS 7533.197088607597
New York CHQ 7541.038226950345
New York SAV 5932.801875000004
20. Branch name, account type, account number, transaction number and amount of transactions of accounts where the average transaction amount is greater than three times the (overall) average transaction amount of accounts of that type. For example, if the average transaction amount of all business accounts is 2,000 then return transactions from business accounts where the average transaction amount for that account is greater than 6,000. Order by branch name, then account type, account number and finally transaction number. Note that all transactions of qualifying accounts should be returned even if they are less than the average amount of the account type.
%%sql
SELECT B.branchName, A.type, A.accNumber, T.transNumber, T.amount
FROM transactions T, account A, branch B
WHERE T.accNumber = A.accNumber AND A.branchNumber = B.branchNumber AND T.accNumber IN
(SELECT T1.accNumber
FROM transactions T1, account A1
WHERE T1.accNumber = A1.accNumber
GROUP BY T1.accNumber
HAVING AVG(T1.amount)> (SELECT AVG(T2.amount)*3
FROM transactions T2, account A2
WHERE T2.accNumber = A2.accNumber AND A2.type = A1.type
))
ORDER BY B.branchName, A.type
* sqlite:///bank.db
Done.
branchName type accNumber transNumber amount
Latveria CHQ 206 1 80371.46
Latveria CHQ 206 2 3639.13
Latveria CHQ 206 3 -196.5
London BUS 18 1 103802.18
London BUS 18 2 1588.38
London BUS 18 3 -1161.43
London BUS 18 4 -649.44
London CHQ 13 1 108440.2
London CHQ 13 2 1770.56
London CHQ 13 3 2587.99
London CHQ 13 4 -292.91
London SAV 121 1 98101.36
London SAV 121 2 -524.42
London SAV 121 3 3372.65
London SAV 121 4 3304.11
London SAV 121 5 -740.92
New York BUS 151 1 84601.1
New York BUS 151 2 -1603.48
New York BUS 151 3 -790.56
New York CHQ 158 1 84961.78
New York CHQ 158 2 232.45
New York CHQ 158 3 -1212.29
Submission
Complete the code in this notebook hw3.ipynb. Put hw3.ipynb and bank.db into hw3.zip and submit it to the Canvas activity Assignment 3.
