HW2. SQL and Relational Algebra

HW2. SQL and Relational Algebra
Objectives¶
In this assignment, you will write more complex SQL queries. You will practice the following:

How to use Set Operators to union/intersect multiple tables
How to use Join Operator to join multiple tables
How to use Aggregations and Group By to aggregate data
How to write Subqueries in SQL
How to use Relational Algebra to describe the SQL queries you have previously written
Background
We will use the same database bank.db that we used in homework assignment (1). 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 (type CHQ), saving (type SAV), or business (type BUS)
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
Important Note
Please note that you are not allowed to use anything other than what you have learned in class. As an example, you can use INNER JOIN and LEFT OUTER JOIN, but you should not use CROSS JOIN or NATURAL JOIN because we have not discussed them in class yet. You can also use subqueries in the FROM or WHERE clasuses, but cannot use WITH. You will be penalized (by -2 points) for each violation.

Part 1 (25 points): SQL Questions
Write SQL queries to return the data specified in questions 1 to 19.

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 have indicative names, so make sure that you include required 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. (1 point) First name, last name, income of customers whose income is within [60,000, 70,000], order by income (desc), lastName, firstName.

%%sql
/*
Customer = {customerID, firstName, lastName, income, birthDate}
*/
SELECT firstName, lastName, income
FROM Customer
WHERE income<=70000 AND income>=60000
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. (1 point) 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

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}

*/

SELECT E.sin, B.branchName, E.salary, M.salary - E.salary AS manager_salary_difference
FROM Employee E
JOIN Branch B ON E.branchNumber = B.branchNumber
JOIN Employee M ON B.managerSIN = M.sin
WHERE B.branchName IN ('New York', 'London', 'Berlin')
ORDER BY manager_salary_difference ASC;
 * sqlite:///bank.db
Done.
sin    branchName    salary    manager_salary_difference
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. (1 point) 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

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT firstName, lastName, income
FROM Customer
WHERE income >= 2*(SELECT income FROM Customer WHERE lastName = 'Butler')
ORDER BY lastName ASC, firstName ASC;
 * 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. (1 point) 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

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT C.customerID, C.Income, O.accNumber, A.branchNumber, B.branchName
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    branchName
27954    94777    10    1    London
27954    94777    68    3    New York
27954    94777    239    2    Latveria
51850    97412    35    1    London
51850    97412    129    1    London
51850    97412    161    3    New York
51850    97412    182    2    Latveria
62312    92919    61    3    New York
62312    92919    116    1    London
62312    92919    219    2    Latveria
62312    92919    261    5    Moscow
5. (1 point) 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
/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT O.customerID, A.type, A.accNumber, A.balance
FROM Owns O
JOIN Account A ON A.accNumber = O.accNumber AND A.type IN ('BUS','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. (1 point) 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

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT b.branchName, A.accNumber, A.balance
FROM Account A
JOIN Branch b ON b.branchNumber = A.branchNumber
JOIN Employee e ON b.managerSIN = e.sin AND e.firstName = 'Phillip' AND e.lastName = 'Edwards'
WHERE (A.balance >= 110000) 
ORDER BY A.accNumber ASC;
 * 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. (1 point) 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

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT DISTINCT o.customerID
FROM Owns o
JOIN Account A ON A.accNumber = o.accNumber
JOIN Branch B ON B.branchNumber = A.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. (1 point) 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
/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

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. (1 point) Exactly as question eight, except that your query cannot include any join operation.

%%sql

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT E.sin, E.firstName, E.lastName, E.salary,
  (SELECT B.branchName FROM Branch B WHERE B.managerSIN = E.sin) AS branchName
FROM Employee E
WHERE E.salary > 70000
ORDER BY 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. (1 point) SIN, first name, last name and salary of the lowest paid employee (or employees) of the London branch, order by sin.

%%sql
/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT e.sin, e.firstName, e.lastName, e.salary
FROM Employee e
JOIN Branch b ON b.branchName = 'London' AND e.branchNumber = b.branchNumber
WHERE e.salary =  (
    SELECT MIN(salary)
    FROM Employee
    WHERE branchNumber = b.branchNumber
);
 * sqlite:///bank.db
Done.
sin    firstName    lastName    salary
24469    Frank    Rodriguez    13950
11. (1 point) 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

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT B.branchName, (MAX(E.salary) - MIN(E.salary)) AS salaryGap, AVG(E.salary) AS averageSalary
FROM Employee E
JOIN Branch B ON E.branchNumber = B.branchNumber
GROUP BY B.branchName
ORDER BY B.branchName;
 * sqlite:///bank.db
Done.
branchName    salaryGap    averageSalary
Berlin    86862    34714.8125
Latveria    89282    56143.46153846154
London    85339    50813.80952380953
Moscow    58759    49065.71428571428
New York    84021    48649.90476190476
12. (1 point) 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

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT COUNT(*) AS EmployeeCount, COUNT(DISTINCT lastName) AS UniqueLastNames
FROM Employee
WHERE branchNumber = (
    SELECT branchNumber
    FROM Branch
    WHERE branchName = 'New York'
  );
 * sqlite:///bank.db
Done.
EmployeeCount    UniqueLastNames
21    20
13. (1 point) Sum of the employee salaries (a single number) at the New York branch.

%%sql

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT SUM(e.salary) AS EmployeeSalariesNewYorkTotal
FROM Employee e, Branch b
WHERE e.branchNumber = b.branchNumber AND b.branchName = 'New York';
 * sqlite:///bank.db
Done.
EmployeeSalariesNewYorkTotal
1021648
14. (1 point) Customer ID, first name and last name of customers who own accounts at four different branches, order by Last Name and first Name.

%%sql
/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT c.customerID, c.firstName, c.lastName
FROM Customer c
JOIN Owns o ON c.customerID = o.customerID
JOIN Account a ON o.accNumber = a.accNumber
GROUP BY c.customerID, c.firstName, c.lastName
HAVING COUNT(DISTINCT a.branchNumber) = 4
ORDER BY c.lastName, c.firstName;
 * sqlite:///bank.db
Done.
customerID    firstName    lastName
44922    Dennis    Flores
73386    Arthur    Jones
62312    Phyllis    Lopez
90667    Carl    Murphy
92389    Amy    Ross
65441    Arthur    Thompson
15. (2 points) Average income of customers older than 60 on Jun 12,2023 and average income of customers younger than 20 on Jun 12,2023, the result must have two named columns, with one row, in one result set (hint: look up SQLite time and date functions).

%%sql

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT 
(SELECT AVG(Customer.Income)  
    FROM Customer 
    WHERE (date('now') - Customer.birthDate) > 60) 
    AS 'Older then 60 Income (AVG)', 
(SELECT AVG(Customer.Income) 
    FROM Customer 
    WHERE (date('now') - Customer.birthDate) < 20)
    AS 'Younger than 20 Income (AVG)'
;
 * sqlite:///bank.db
Done.
Older then 60 Income (AVG)    Younger than 20 Income (AVG)
55367.76315789474    56570.0
16. (2 points) 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 three accounts and satisfy one (or both) of the name conditions.

%%sql

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT c.customerID, c.lastName, c.firstName, c.income, AVG(a.balance) AS AverageAccountBalance
FROM Customer c
JOIN Owns o ON c.customerID = o.customerID
JOIN Account a ON o.accNumber = a.accNumber
WHERE (c.lastName LIKE 'S%e%' OR c.firstName LIKE 'A%n__')
GROUP BY c.customerID, c.lastName, c.firstName, c.income
HAVING COUNT(DISTINCT o.accNumber) >= 3
ORDER BY c.customerID;
 * sqlite:///bank.db
Done.
customerID    lastName    firstName    income    AverageAccountBalance
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
17. (2 points) 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 

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT A.accNumber, A.balance, SUM(t.amount) AS TransactionSum,
    (A.balance - SUM(t.amount)) AS BalanceMinusTransactionSum
    
FROM Account A
JOIN Transactions t ON A.accNumber = t.accNumber
JOIN Branch B ON B.branchNumber = A.branchNumber
WHERE B.branchName = 'London'
GROUP BY A.accNumber, A.balance
HAVING COUNT(t.transNumber) >= 15
ORDER BY TransactionSum;
 * sqlite:///bank.db
Done.
accNumber    balance    TransactionSum    BalanceMinusTransactionSum
113    82792.58    82792.58    0.0
9    132271.23    132271.22999999998    2.9103830456733704e-11
18. (2 points) Branch name, account type, and average transaction amount of each account type for each branch for branches that have at least 50 accounts combined, order by branch name, then account type.

%%sql

/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT B.branchName, A.type, AVG(T.amount) AS "avg_transaction_amount"
FROM Transactions T
JOIN Account A ON T.accNumber = A.accNumber
JOIN Branch B ON A.branchNumber = B.branchNumber
WHERE B.branchNumber IN 
      (SELECT a2.branchNumber
       FROM Account a2
       GROUP BY A2.branchNumber
       HAVING count(a2.branchNumber) >= 50)
GROUP BY A.branchNumber, A.type;
UsageError: Cell magic `%%sql` not found.
19. (3 points) 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
/*
Customer = {customerID, firstName, lastName, income, birthDate}
Account = {accNumber, type, balance, branchNumber^(FK-Branch)}
Owns = {customerID^(FK-Customer), accNumber^(FK-Account)}
Transactions = {transNumber, accNumber^(FK-Account, amount)}
Employee = {sin, firstName, lastName, salary, branchNumber^(FK-Branch)}
Branch = {branchNumber, branchName, managerSIN^(FK-Employee), budget}
*/

SELECT b.branchname, a.type, a.accnumber, t.transnumber, t.amount
FROM account a
JOIN transactions t ON t.accnumber = a.accnumber
JOIN branch b ON a.branchnumber = b.branchnumber
WHERE a.accnumber IN (
    SELECT t1.accnumber
    FROM transactions t1
    JOIN account a1 ON t1.accnumber = a1.accnumber
    GROUP BY t1.accnumber
    HAVING AVG(t1.amount) > (
        SELECT AVG(t2.amount) * 3
        FROM transactions t2
        JOIN account a2 ON t2.accnumber = a2.accnumber
        WHERE a2.type = a1.type
    )
);
 * sqlite:///bank.db
Done.
branchName    type    accNumber    transNumber    amount
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    BUS    18    1    103802.18
London    BUS    18    2    1588.38
London    BUS    18    3    -1161.43
London    BUS    18    4    -649.44
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
Latveria    CHQ    206    1    80371.46
Latveria    CHQ    206    2    3639.13
Latveria    CHQ    206    3    -196.5
Part 2 (5 points): Relational Algebra Questions
Preparation
To write a relational algebra query in a cell, the cell should be a Markdown cell. You can use LaTeX equations in a markdown cell for required algebraic notation. Double click on this cell to see the souce code for each operator. Here is a list of the main operators:

Selection ( σ
 )
Projection ( π
 )
Union ( ∪
 )
Intersect ( ∩
 )
Set Difference ( −
 )
Cross Product ( ×
 )
Rename ( ρ
 )
Join ( ⋈
 )
Conjunction ( ∧
 )
Disjunction ( ∨
 )
Greater Than or Equal To ( ≥
 )
Less Than or Equal To ( ≤
 )
Semijin ( ⋉
 )
Antijoin ( ⋉¯
 )
You may also need  Subscript
  and  Superscript
  in the notations you use.

Consider the same bank database you have used before.

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}
In each question below, please write down the relational algebraic presentations for the described query. For this question, we use relational algebra on set.

20.1 (1 point) Find out names of the bank branches and first name and last name of their managers.

πB.branchName,E.firstName,E.lastName(E⋈E.managerSIN=B.managerSINB)
 
20.2 (1 point) Show account number, account type, account balance, and transaction amount of the accounts with balance higher than 100,000 and transaction amounts higher than 15000.

πA.accNumber,A.type,A.balance,T.amount(σA.balance>100,000∧T.amount>15,000(A⋈A.accNumber=T.accNumberT))
 
20.3 (1 point) Show first name, last name, and income of customers whose income is at least twice the income of any customer whose lastName is Butler.

πC1.firstName,C1.lastName,C1.income(σC1.income≥2×C2.income∧C2.lastName=′Butler′(C1×C2))
 
20.4 (2 points) Show 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. 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.

πC.customerID,C.income,A.accNumber,A.branchNumber(σC.income>90,000(C)⋈σA.branchNumber=′London′(A))⋈σA.branchNumber=′Latveria′(A)
 
Submission
Complete the code and markdown cells in this notebook and submit it to the Canvas activity Homework 2.