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

3.9 Exactly as question eight, except that your query cannot include any join operation.

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

3.11 SIN, first name, last name and salary of the lowest paid employee (or employees) of the London branch, order by sin.

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

3.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 Yorkbranch (two numbers in a single row).

3.14 Sum of the employee salaries (a single number) at the New York branch.

Answer 1

3.8

Select SIN,firstName,lastName,Salary , branchName from Employee left outer join Branch on Employee.SIN = Branch.ManagerSIN where Salary > 70000 order by branchName;

3.9

Select SIN,firstName, lastName, Salary , branchName from Employee ,Branch where Salary > 70000 and Employee.SIN = Branch.ManagerSIN order by branchName;

3.10

Select Customer.CustomerId, firstName, lastName, income from customer inner join Owns on Customer. CustomerId = Owns.CustomerId inner join Account on Owns.accNumber = Account.accNumber where income > 5000 and firstName = 'Helen' and lastName = 'Morgan' order by income desc;

3.11

Select SIN,firstName,lastName,Salary from Employee inner join Branch on Employee.branchNumber = Branch.branchNumber where branchName = 'London' and Salary = (Select min(Salary) from Employee) order by SIN;

3.12

Select branchName, max(Salary)-min(Salary) as 'SalaryGap', avg(Salary) from Employee inner join Branch on Employee.branchNumber = Branch.branchNumber group by branchName order by branchName;

3.13

Select count(Employee.SIN), count(distinct lastName) from Employee inner join Branch on Employee.branchNumber = Branch.branchNumber where branchName = 'New York'

3.14

Select sum(Salary) from Employee inner join Branch on Employee.branchNumber = Branch.branchNumber where branchName = 'New York';

Do ask if any doubt. Please upvote.