NOT allow to use JOIN keyword or subqueries, this do not need the data

Background

Suppose you work at a bank as a data analyst. Your main job is to analyze the data stored in their database. The database has five tables, whose schema is shown as below. Primary keys attributes are underlined and foreign keys are noted in the superscript.

Customer = {customerID, firstName, lastName, income, birthDate} Account = {accNumber, type, balance, branchNumberFK-Branch} Owns = {customerIDFK-Customer, accNumberFK-Account} Transaction = {transNumber, accNumberFK-Account, amount}

Employee = {ssn, firstName, lastName, salary, branchNumberFK-Branch} Branch = {branchNumber, branchName, managerSSNFK-Employee, budget}

Notes.

• The customerID attribute (Customer) is a unique number that represents a customer, it is not a cus- tomers SSN

• 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: checking, saving, or business

• The transNumber attribute (Transactions) represents a transaction number, combined with account

  number it uniquely identify a transaction

• The branchNumber attribute (Branch) uniquely identifies a branch

• The managerSSN attribute (Branch) represents the SSN of the branch manager

Questions:

1. Return SSN, branch name, salary, and (managers salary - salary) (that is, the salary of a employees manager minus salary of that employee) of all employees in New York, London or Berlin, order by ascending (managers salary - salary).

2. Return first name, last name, and income of customers whose income are at least twice the that of any customer whose lastName is Smith, order by last name then first name. Note that no duplicate customer information should be provided in the final results.