Using the tables below: Provide the SQL statements to: 12) For each employee, sh
ID: 3698592 • Letter: U
Question
Using the tables below:
Provide the SQL statements to:
12) For each employee, show how many employee(s) each supervises.
14) Retrieve the name of employee whose salary is greater than the average salary of his or her corresponding department.
19) List the name of each department, and the total number of employees with salaries greater than $30,000, and only the department with more than one employee.
20. List the name of department that has more than one employee who earns more than $30,000.
TABLE Employee
NAME SSN BDATE SEX SALARY SUPERSSN DNO
------------------- --------- --------- --- ---------- --------- --------
John B Smith 123456789 09-JAN-55 M 30000 333445555 5
Franklin T Wong 333445555 08-DEC-45 M 40000 888665555 5
Alicia J Zelaya 999887777 19-JUL-85 F 25000 987654321 4
Jennifer S Wallace 987654321 20-JUN-31 F 43000 888665555 4
Ramesh K Narayan 666884444 15-SEP-52 M 38000 333445555 5
Joyce A English 453453453 31-JUL-62 F 25000 333445555 5
Ahmad V Jabbar 987987987 29-MAR-59 M 25000 987654321 4
James E Borg 888665555 10-NOV-27 M 55000 1
TABLE Department;
DNAME DNUMBER MGRSSN MGRSTARTD
--------------- -------- --------- ---------
Research 5 333445555 22-MAY-78
Administration 4 987654321 01-JAN-85
Headquarters 1 888665555 19-JUN-71
TABLE Project
PNAME PNUMBER PLOCATION DNUM
--------------- -------- --------------- --------
ProductX 1 Bellaire 5
ProductY 2 Sugarland 5
ProductZ 3 Houston 5
Computerization 10 Stafford 4
Reorganization 20 Houston 1
Newbenefits 30 Stafford 4
TABLE Deptlocation
DNUMBER DLOCATION
-------- ---------------
1 Houston
4 Stafford
5 Bellaire
5 Houston
5 Sugarland
TABLE workson
ESSN PNO HOURS
--------- -------- ----------
123456789 1 32.5
123456789 2 7.5
666884444 3 40
453453453 1 20
453453453 2 20
333445555 2 10
333445555 3 10
333445555 10 10
333445555 20 10
999887777 30 30
999887777 10 10
ESSN PNO HOURS
--------- -------- ----------
987987987 10 35
987987987 30 5
987654321 30 20
987654321 20 15
888665555 20
Explanation / Answer
12) Aggregate function COUNT() is used along with group by to get the employee count under each supervisor.
SELECT SUPERSSN, COUNT(SSN)
FROM Employee
GROUP BY SUPERSSN;
14) Subquery is used in WHERE clause to check salary of employee greater than the average salary of their department.
SELECT SSN, NAME,
FROM Employee e
WHERE SALARY > (SELECT AVG(SALARY) FROM Employee em WHERE em.DNO = e.DNO);
19) 2 tables namely Department, Employee and joined. Aggreagte function COUNT() is used along with group by clause to get the number of employees in each department. Where clause is used to filter the result with salary greater than 30,000.
SELECT DNAME, DNUMBER, COUNT(SSN)
FROM Department d
INNER JOIN Employee e
ON e.DNO = d.DNUMBER
WHERE e.SALARY > 30,000
GROUP BY DNAME, DNUMBER
HAVING COUNT(SSN) > 1;
20)
SELECT DNAME, COUNT(SSN)
FROM Department d
INNER JOIN Employee e
ON e.DNO = d.DNUMBER
WHERE e.SALARY > 30,000
GROUP BY DNAME
HAVING COUNT(SSN) > 1;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.