In this assignment, you will be trained to write more complex SQL queries to query a database.
Order By
to sort dataSet Operators
to union/intersect multiple tablesJoin Opeartor
to join multiple tablesAggregations
and Group By
to aggregate datasubqueries
in SQL 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.
Notes
Write SQL queries to return the data specified in questions 1 to 20.
Query Requirement
Execute the next two cells
%load_ext sql
%sql sqlite:///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.
#REPLACE WITH YOUR CODE
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).
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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).
#REPLACE WITH YOUR CODE
9. Exactly as question eight, except that your query cannot include any join operation.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
11. SIN, first name, last name and salary of the lowest paid employee (or employees) of the London branch, order by sin.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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).
#REPLACE WITH YOUR CODE
14. Sum of the employee salaries (a single number) at the New York branch.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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).
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
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.
#REPLACE WITH YOUR CODE
Complete the code in this notebook A2.ipynb. Put A2.ipynb
and bank.db
into A2.zip and submit it to the CourSys activity Assignment 2.