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.