Assignment 2. SQL Advanced¶

Objectives¶

In this assignment, you will be trained to 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, 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}

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

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

Submission¶

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.