HW2. SQL Basics

HW2. SQL Basics
Objectives
In this homework assignment, you will use SQL to store and query a database. You will practice the followings:

How to use patterns to find matching information
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
This assignment has a total of 10 points.

HomeWork
Suppose you work at a bank as a data analyst. Your main job is to analyze the data stored in their database to find out information that can help the business. 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}
Please run the next two cells after downloading the database, before you start.

%load_ext sql
%sql sqlite:///bank.db
'Connected: @bank.db'
(1 point) Suppose you talked with a customer, you remember their name started with 'M', inluded an 'r' and finished with an 'a', but you are not sure about the complete spelling. Please write a SQL query to show the first name and last name of the customers with such first name.
%%sql

SELECT firstName, lastName 
FROM Customer 
WHERE firstName LIKE 'M%r%a'
 * sqlite:///bank.db
Done.
firstName    lastName
Martha    Young
Martha    Butler
Maria    Morgan
Maria    Young
(1 point) Please write a SQL query to show names of the branches and first name and last name of their managers.
%%sql

SELECT branchName, firstName, lastName 
FROM Branch, Employee
WHERE sin = managerSIN
 * sqlite:///bank.db
Done.
branchName    firstName    lastName
London    Phillip    Edwards
Latveria    Victor    Doom
New York    Victor    Doom
Berlin    Deborah    Hernandez
Moscow    Cheryl    Thompson
(2 points) Please write a SQL query to find out employees who are also customers (based on their matching first name and last names).
%%sql

SELECT E.*
FROM Customer C, Employee E
WHERE E.firstName = C.firstName AND E.lastName = C.lastName
ORDER BY E.firstName, E.lastName
 * sqlite:///bank.db
Done.
sin    firstName    lastName    salary    branchNumber
29474    Amanda    White    59360    1
92389    Amy    Ross    12525    5
81263    Anna    Cooper    67275    3
14295    Anne    Ramirez    44495    5
73386    Arthur    Jones    57935    2
90667    Carl    Murphy    19534    3
82333    Charles    Smith    45443    4
99537    Deborah    Hernandez    90211    4
97216    Dennis    Collins    89746    1
82244    Douglas    Wright    29009    1
57796    Ernest    Adams    75896    3
96443    Ernest    Perez    19971    4
44459    Gerald    Watson    55740    5
85587    Justin    Mitchell    38385    1
55194    Kathleen    Morris    38549    2
77100    Laura    Alexander    23477    4
28453    Margaret    White    75146    1
55146    Mark    Jackson    42893    1
63772    Mary    Powell    74194    2
55700    Phillip    Edwards    99289    1
30807    Roy    Morris    40753    2
27004    Steven    Johnson    69842    3
82464    Susan    Hayes    28953    1
41545    Terry    Bailey    9534    4
31964    Victor    Doom    90483    3
51850    Victor    Doom    87242    2
91712    Victor    Doom    9491    2
38351    Victor    Perez    86093    3
81108    Willie    Jones    61312    2
(3 points) Please write a SQL query to show account number, account type, account balance, and transaction amount of the accounts with balance higher than 100,000 and transaction amouns higher than 15000, starting by the accounts with the highest transaction amount and highest account balance.
%%sql

SELECT A.accNumber, A.type, A.balance, T.amount
FROM Account A, Transactions T
WHERE A.accNumber = T.accNumber AND 
      T.amount > 15000 AND A.balance > 100000
ORDER BY amount DESC, balance DESC
 * sqlite:///bank.db
Done.
accNumber    type    balance    amount
9    SAV    132271.23    114869.79
8    BUS    121267.54    114680.63
31    CHQ    111209.89    110249.28
1    SAV    118231.13    109587.15
25    SAV    105997.07    109068.54
13    CHQ    112505.84    108440.2
20    CHQ    107270.59    108278.46
4    BUS    106503.6    104550.76
26    SAV    112046.36    104346.46
6    CHQ    107309.23    104247.4
18    BUS    103579.69    103802.18
17    CHQ    103356.07    103431.57
28    CHQ    112617.97    102680.84
5    CHQ    105696.04    101945.4
2    CHQ    100808.03    100002.19
81    CHQ    107129.47    99712.38
227    CHQ    109916.78    98987.65
218    BUS    103650.37    98757.79
119    CHQ    113473.16    98480.27
147    CHQ    114094.94    98155.28
121    SAV    103512.78    98101.36
187    CHQ    103328.66    97629.4
165    CHQ    108042.83    96796.3
138    BUS    104044.22    96658.35
176    CHQ    113048.79    96473.74
282    SAV    101063.84    96430.68
135    SAV    105420.87    95889.6
198    CHQ    102686.76    95539.37
272    CHQ    101739.44    95431.93
164    CHQ    101336.25    94145.63
115    BUS    102857.55    93549.92
139    BUS    101394.11    92397.65
59    CHQ    112534.31    91590.92
221    BUS    105068.53    90728.42
291    SAV    101504.47    90181.92
200    BUS    100035.01    87660.19
130    CHQ    102776.09    87542.82
107    BUS    102366.95    86853.53
106    BUS    102297.76    85329.15
148    CHQ    100187.85    83528.49
(3 points) Please write a SQL query to find the customer ID, first name, and last name of customers who own accounts at London and Berlin branches, order by last name and first name.
%%sql

SELECT C.customerID, C.firstName, C.lastName
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
      B.branchName = 'London'
    
INTERSECT

SELECT C.customerID, C.firstName, C.lastName
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
      B.branchName = 'Berlin'
    
ORDER BY lastName, firstName
 * sqlite:///bank.db
Done.
customerID    firstName    lastName
66418    Stephanie    Adams
89197    Lawrence    Anderson
41545    Terry    Bailey
33726    Jerry    Cook
86357    Andrew    Evans
44922    Dennis    Flores
87978    Christopher    Gonzalez
10839    Amy    Hayes
99537    Deborah    Hernandez
13697    Charles    Hill
38861    Gerald    James
73386    Arthur    Jones
47953    Frank    Martinez
88375    Randy    Mitchell
90649    Helen    Morgan
46058    Adam    Rivera
52189    Shawn    Sanders
13423    Norma    Simmons
65441    Arthur    Thompson
29474    Amanda    White
63859    Maria    Young
Submission
Complete the code in this notebook hw2.ipynb, and submit it to through Canvas system to your HW2 activity. You can also include a pdf file where you can add your comments, thoughts, explanations about any of the questions.