Question

Write following queries given these schemas:
(FK stands for Foreign Key)

• 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}

Focus should be on using:

• Order By to sort data
• Set Operators to union/intersect multiple tables
• Join Operator to join multiple tables
• Aggregations and Group By to aggregate data
• Subqueries

3.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.

3.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).

3.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. S**teve) **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.

3.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.

3.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.

3.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.

Answer 1

(FK stands for Foreign Key)

• 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}

Focus should be on using:

• Order By to sort data
• Set Operators to union/intersect multiple tables
• Join Operator to join multiple tables
• Aggregations and Group By to aggregate data
• Subqueries

3.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.

3.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).

3.17 Customer ID, last name, first name, income, and average account balanceof customers who have at least three accounts, and whose last names begin with S* and contain an *e (e.g. S**teve) **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.

3.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.

3.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.

^Answer:

^Query-1

^{select a.branchName as Branch,b.FirstName as Manager_FirstName,b.lastName as Manager_LastName
from branch as a innerjoin employee as b
ON a.managerSIN = b.sin;}

^Query-2

^{select a.sin as Employee_SIN,CONCAT(a.FIRSTNAME, ' ', a.LASTNAME) as EMPLOYEE_NAME
from employee as a innerjoin customer as b
ON a.firstname = b.firstname AND a.lastname = b.lastname;}

^Query-3

^{select a.accNumber as AccountNumber,a.type as AccountType,a.balance as AccountBalance,b.b.amount as TransactionAmount
from account as a innerjoin transaction as b
on a.accNumber = b.accNumber
where a.balance>100000 AND b.amounnt>15000
order by b.amount DESC,a.balance DESC;}

^Query-4

^{select a.customerID as CustomerID,a.firstname as Customer_FirstName,a.lastname as Customer_LastName
from customer as a innerjoin owns as b ON a.customerID = b.customerID
innerjoin Account as c ON b.accNumber = c.accNumber
innerjoin Branch as d ON c.branchNumber = d.branchNumber
where d.branchName = 'london' OR d.branchName = 'Berlin'
order by a.lastname,a.firstname;}