You are to write, and run, SQL queries to answer a series of questions about a bank database. The database is an SQL Server DB on the Cypress server (CYPRESS.csil.sfu.ca) and you can access it in SRYE 4013 or by using remote desktop. The database is (imaginatively) named bank354.

Bank Schema

The schema for the bank database is as follows. Primary key attributes are underlined and foreign keys are noted in superscript.

• Customer = {customerID, firstName, lastName, income, birthDate }
• Account = {accNumber, type, balance, branchNumberBranch}
• Owns = {customerIDCustomer, accNumberAccount}
• Transactions = {transNumber, accNumberAccount, amount}
• Employee = {sin, firstName, lastName, salary, branchNumberBranch}
• Branch = {branchNumber, branchName, managerSINEmployee, 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, and should equal the sum of the account's transaction amounts
• The type (Account) attribute represents the type an account: chequing (CHQ), saving (SAV), or business (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 identifies a transaction
• The branchNumber attribute (Branch) uniquely identifies a branch
• The managerSIN attribute (Branch) represents the SIN of the branch manager

Questions

Write SQL queries to return the data specified in questions 1 to 20.

Query Requirements

• Rows must be ordered as shown in the question (noted in green), order is ascending unless specified otherwise
• Columns must be printed in the order shown in the question (from left to right)
• Query results should be sent to text (not a grid)
• 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 customer ID of customers who own accounts)

Queries

1. Last name, first name and salary of employees who work in branch number 3, order by last name, then first name.
2. Account number, account type, transaction number and transaction amount of accounts with balances over $120,000, order by account number, then transaction number.
3. Last name, first name, and birth dates of customers who were born before any customer named Carol Alexander, order by last name then first name.
4. Customer ID, last name, income and account numbers of customers with income over $60,000 who own an account with at least one transaction greater than (+)$110,000, 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 does not contain any large transactions.
5. Owner customer ID, types, account numbers and balances of Berlin and London business (type bus) and savings (type sav) accounts owned by customers who own at least one business and at least one savings account in the Berlin and London branches, order by customer ID, then type, then account number.That is, only include accounts owned by customers with: sav in Berlin and bus in London; bus in Berlin and sav in London; bus and sav in Berlin; or bus and sav in London.
6. Transaction number, transaction amount and the transaction amount as a percentage of the account balance (i.e. three columns), for account number 42, order by descending transaction amount.
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 own an account with another customer with the same first or last name, order by customer ID. The result should not contain duplicate customer IDs.
8. Customer ID, last name, first name and income of customers who have incomes greater than $60,000, if they have the same first and last name as an employee show their salary in a fifth column (which should be NULL for most customers), order by last name and first name. You must use an outer join in your solution (which is the easiest way to do it).
9. Exactly as question eight, except that your query cannot include any join operation.
10. Customer ID, last name and first name of customers who own accounts of all types, order by customer ID. The result should not contain duplicate customer IDs.
11. Highest salary (a single number) of a branch manager
12. SIN, first name, last name and salary of the highest paid employee (or employees) of the London branch, order by sin.
13. Count of the number of different first and last names of customers and the number of different first and last names of employees, four - sensibly named - numbers in a single row.
14. Branch name, and minimum, maximum and average salary of the employees at each branch, order by branch name.
15. Customer ID, last name and birth dates of customers who own accounts at a minimum of three different branches, order by customer ID.
16. The date of birth of the oldest customer with an income over 90,000, the date of birth of the oldest customer with an income less than or equal to 90,000 and the difference in days between the two dates of birth; the result must have three named columns, with one row, in one result set (hint: look up T-SQL time and date functions).
17. Customer ID, last name, first name, income, and average account balance of customers who have at least four accounts, and whose last names second letter is an 'r' (e.g. Grey) or whose first names have a vowel as the first letter, but do not have a vowel as the last letter (e.g. Amy), order by customer ID. Note that this will be much easier if you look up LIKE wildcards in the MSDN T-SQL documentation. Also note - to appear in the result customers must have at least four accounts and satisfy one (or both) of the name conditions.
18. Branch number, branch name, budget, sum of account balances and sum of account balance / budget (in a named column) of each branch , order by branch name.
19. Account number, minimum transaction amount, average transaction amount and maximum transaction amountof accounts with more than ten transactions that are held at a branch which holds accounts of the customer with the second highest income, order by account number.
20. Customer ID, last name, account number, type and balance of accounts of customers where the customer's average account balance is less than half of the average overall account balance. For example, if the average account balance (of all accounts) is $10,000 then return customers whose average account balances is less than $5,000. Order by customer ID, then account number. Note that all accounts of qualifying customers should be returned even if their balances are more than half the average balance.