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