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

 

EMPLOYEEID	EMPLNAME	EMPFNAME	EMPTITLE	EMPSTARTDATE	EMPBRANCH	EMPSALARY	EMPSUPERVISOR
e1	Adam	Alan	CEO	11-JAN-02	b1	600000	-
e2	Bryson	Brad	branch_manager	01-FEB-03	b2	400000	e1
e3	Clay	Cedric	branch_manager	21-JUN-01	b3	450000	e1
e4	Day	Daisy	branch_manager	17-AUG-03	b4	480000	e1
e5	Engle	Eva	salesperson	01-JAN-04	b2	120000	e2
e6	Falcon	Fred	salesperson	01-JAN-02	b2	80000	e2
e7	Gandhi	Gagan	salesperson	01-JAN-03	b3	90000	e3
e8	Hee	Hwang	salesperson	01-JUN-04	b3	95000	e3
e9	Ingram	Irene	salesperson	24-SEP-02	b4	110000	e4
e10	Jerome	John	salesperson	25-AUG-02	b4	75000	e4

 Table: BRANCH

PK: branchNumber

FK: branchManager references EMPLOYEE

BRANCHNUMBER	BRANCHNAME	BRANCHSTREET	BRANCHCITY	BRANCHSTATE	BRANCHZIP	REVENUETARGET	BRANCHMANAGER
b1	branch1	9700 NW 41 St	Miami	FL	33178	800000	e1
b2	branch2	8700 SW 24 St	Miami	FL	33170	600000	e2
b3	branch3	E 200 47 St	New York	NY	11010	1000000	e3
b4	branch4	300 Park Avenue	New York	NY	10010	1200000	e4

Table: CUSTOMER

PK: customerID

CUSTOMERID	CUSTNAME	CUSTSTREET	CUSTCITY	CUSTSTATE	CUSTZIP	CUSTPHONE
c1	cust1	-	Miami	FL	33164	-
c2	cust2	-	Miami	FL	33120	-
c3	cust3	-	Miami	FL	33110	-
c4	cust4	-	Miami	FL	33178	-
c5	cust5	-	New York	NY	11021	-
c6	cust6	-	New York	NY	11001	-

Table: PRODUCT

PK: productCode

PRODUCTCODE	PRODDESCRIPTION	PRICE	STOCKLEVEL
p1	carpet	40	10000
p2	tile	20	100000
p3	pergo	50	50000

 Table: INSTALLATION

PK: installationType

INSTALLTYPE	INSTALLDESCRIPTION	RATE
i1	carpet installation	40
i2	tile installation	50
i3	pergo installation	60

Table: ORDERS

PK: orderNumber

FK: customerID references CUSTOMER;

FK: salesPerson references EMPLOYEE

 

ORDERNUMBER	ORDDATE	SALESPERSON	CUSTOMERID
o1	12-AUG-07	e5	c1
o2	14-DEC-07	e5	c2
o3	04-NOV-07	e5	c3
o4	15-AUG-07	e5	c4
o5	22-NOV-07	e10	c5
o6	01-JUL-07	e10	c6
o7	12-DEC-07	e6	c6
o8	30-NOV-07	e9	c2

  Table: PRODLINE

PK: orderNumber + prodCode

FK: orderNumber references ORDERS;

FK: prodCode references PRODUCT

ORDERNUMBER	PRODCODE	QUANTITY
o1	p1	1000
o1	p2	500
o2	p3	200
o3	p1	600
o3	p3	100
o4	p2	1000
o5	p2	800

 Table: INSTLINE

PK: orderNumber + instType

FK: orderNumber references ORDERS;

FK: instType references INSTALLATION

ORDERNUMBER	INSTTYPE	HOURS
o1	i1	20
o1	i2	30
o1	i3	10
o2	i1	10
o2	i2	20
o6	i1	20
o6	i2	10
o7	i3	10
o8	i2	20

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.