MMIS 630: Homework 2

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

Formulate SQL queries for the following (1 –6) with reference to this 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 $100,000, list the employee’s id (employeeid), first name (empfname), 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 “total_salary”.

(3)    List the order number, order date (orddate), and customer id (customerid) for all orders placed by customers located in the state of Florida (where custstate = ‘FL’).

(4)    For each order, list the ordernumber and the total revenue generated from sale of products as “product_revenue”. Product_revenue for an order is computed as the sum of the quantity times the price of all the products sold as part of that order.

(5)    For each State list the “total_ product_revenue_from_state”.  

Total_ product_revenue_from_state for a state is defined as the sum of the product_revenue (as defined in query 4 above) from all orders placed by customers located in that state (custstate).

(6)  Under a column header “installation_only_orders”, list the order numbers for orders that require installation services, but do not include the sale of any products.