Reference no: EM13850659

Consider the following instance of this database. The Primary key (PK) and Foreign Keys (FK) are identified for each table.

Table: EMPLOYEE

PK: employeeID;

FK: empBranch references BRANCH;

FK: empSupervisor references EMPLOYEE

 Table: BRANCH

PK: branchNumber

FK: branchManager references EMPLOYEE

Table: CUSTOMER

PK: customerID

Table: PRODUCT

PK: productCode

 Table: INSTALLATION

PK: installationType

Table: ORDERS

PK: orderNumber

FK: customerID references CUSTOMER;

FK: salesPerson references EMPLOYEE

  Table: PRODLINE

PK: orderNumber + prodCode

FK: orderNumber references ORDERS;

FK: prodCode references PRODUCT

 Table: INSTLINE

PK: orderNumber + instType

FK: orderNumber references ORDERS;

FK: instType references INSTALLATION

Formulate SQL queries for the following (1 -6) with reference to this database. Present your SQL query and the results returned for the specified instance of the database. Your queries should work for EVERY instance of the database (and not just for the instance specified in this document).

(1) For each employee with a salary greater than $120,000, list the employee's id (employeeid), last name (emplname), and salary (empsalary) as "high_salary". Records should appear in descending order of salary.

(2) For each branch list the branchnumber and the sum total of salary paid to all employees working for that branch as "branch_payroll". Records should appear in descending order of branchnumber.

(3) List the order number, order date (orddate), and customer id (customerid) for all orders placed by customers located in the state of New York ( 'NY'). Records should appear in descending order of order number.

(4) For each order, list the ordernumber and the total revenue generated from installation services as "installation_revenue". Installation revenue for an order is computed as the sum of the hours times the rate of all installation services sold as part of that order.

(5) For each State list the "State_install_revenue" computed as the sum of the installation revenue (as defined in query 4 above) from all orders placed by customers located in that state (custstate).

(6) Under a column header "install_only_orders", list the order numbers for orders that include installation services but do not include any products.