Employee EMPLOYEEID EMPLNAME EMPFNAME EMPTITLE EMPSTARTDATE EMPBRANCH EMPSALARY
ID: 3573309 • Letter: E
Question
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
Branch
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
Customer
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
-
Product
INSTALLTYPE
INSTALLDESCRIPTION
RATE
i1
carpet installation
40
i2
tile installation
50
i3
pergo installation
60
Installation
PRODUCTCODE
PRODDESCRIPTION
PRICE
STOCKLEVEL
p1
carpet
40
10000
p2
tile
20
100000
p3
pergo
50
50000
Orders
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
Prodline
ORDERNUMBER
PRODCODE
QUANTITY
o1
p1
1000
o1
p2
500
o2
p3
200
o3
p1
600
o3
p3
100
o4
p2
1000
o5
p2
800
Instline
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
Question 2: SQL [2 ´ 10 = 20 Points]
Formulate SQL queries for the following (a and b) with reference to the specified database. Ensure that your SQL queries will produce a correct response for every instance of the database.
[10 points]
a. List the branch number of branches that meet both the following criteria:
1. The branch has received at least one order, AND
2. None of the orders received by the branch are for installation services placed by customers who reside in the same state as the state in which the branch is located.
Orders received by salespersons working at a branch are counted as that branch’s order. Each qualifying branch number should appear exactly once
[10 points]
b. List the last name of supervisors whose salary exceed half the total installation revenue generated from orders received collectively by all salespersons directly supervised by the supervisor.
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
Explanation / Answer
#1
SELECT B.BRANCHNUMBER
FROM ORDERS AS O INNER JOIN EMPLOYEE AS E
ON O.SALESPERSON = E.EMPLOYEEID
INNER JOIN BRANCH AS B
ON E.EMPBRANCH = B.BRANCHNUMBER
INNER JOIN CUSTOMERS AS C
ON O.CUSTOMERID = C.CUSTOMERID
WHERE B.BRANCHSTATE != C.CUSTSTATE;
#2
SELECT T2.EMPSUPERVISOR
(SELECT T1.EMPSUPERVISOR, T1.QUANTITY*P.PRICE AS REVENUE
(SELECT E.EMPSUPERVISOR, PL.PRODUCTCODE, SUM(QUANTITY) AS QUANTITY
FROM ORDERS AS O INNER JOIN PRODUCTLINE AS PL
ON O.ORDERNUMBER = PL.ORDERNUMBER
INNER JOIN EMPLOYEES AS E
ON E.EMPLOYEEID = O.SALESPERSON
GROUP BY E.EMPSUPERVISOR, PL.PRODUCTCODE) AS T1
INNER JOIN PRODUCT AS P
ON P.PRODUCTCODE = T1.PRODUCTCODE) AS T2
INNER JOIN
(SELECT E2.EMPLOYEEID AS SUPERVISOR, E2.EMPSALARY
FROM EMPLOYEES AS E1 JOIN EMPLOYEES AS E2
ON E1.EMPSUPERVISOR = E.EMPLOYEEID) AS T3
ON T2.EMPSUPERVISOR = T3.SUPERVISOR
WHERE T3.EMPSALARY > T2.REVENUE/2
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.