1. (10 points) q1: List employees who earn ≤ the average salary of each department. For example, if the average of dept1 is 260000, average of dept2 is 15000, you should list employees who earn less then 15000. 2. (10 points) q2: List the number of male employees in each department where the average salary of employees (male and female) of that department is < 35000. Result has schema (Dno, NumberMales) 3. (10 points) q3: Assume that employees who have no supervisors (denoted by a NULL value for the Super ssn attribute) are the highest ranked employees - rank 1. Employees supervised by rank 1 have rank 2, and so on. List the number of employees who are supervised by rank 2 employees. You should not hardcode the rank 2 employees since the TA will be using a different database instance. Result has schema (Fname, Lname, NumberSupervised) 4. (10 points) q4: List the employees along with a count of the number of their dependents. Employees with no dependents should have count of 0. Order by number of dependents descending. Result has schema (Fname, Lname, NumberofDependents) 5. (15 points) q5: For projects with more than two employees, list a count of the number of employees working on each project grouped by department. Also list summary counts. 6. (15 points) q6: Assume that employees who have no supervisors (denoted by a NULL value for the Super ssn attribute) are the highest ranked employees - rank 1). Employees super- vised by rank 1 have rank 2, and so on. Assume that there are at most three ranks: 1, 2, 3. (If the number of ranks are unbounded, then you can only solve this query using recursion.) List each employees along with their rank and the count of their supervisees.

 

EMPLOYEE Fname Minit Lname John B Smith Franklin T Wong Alicia J Zelaya Jennifer S Bdate Ssn 123456789 1965-01-09 731 Fondren, Houston, TX | M 333445555 1955-12-08 638 Voss, Houston, TX M Address Sex Salary Super_ssn Dno 999887777 1968-01-19 3321 Castle, Spring, TX Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX James E Borg 888665555 1937-11-10 450 Stone, Houston, TX F 30000 333445555 40000 888665555 5 25000 987654321 4 5 F 43000 888665555 4 38000 333445555 F 25000 333445555 55 M 25000 987654321 4 M 55000 NULL 1 DEPARTMENT DEPT_LOCATIONS Dname Dnumber Mgr_ssn Mgr_start_date Dnumber Dlocation Research 5 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 1 888665555 1981-06-19 5 Bellaire 5 Sugarland 5 Houston WORKS ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 1 32.5 ProductX 1 Bellaire 5 123456789 2 7.5 ProductY 2 Sugarland 5 666884444 3 40.0 ProductZ 3 Houston 5 453453453 1 20.0 Computerization 10 Stafford 4 453453453 2 20.0 Reorganization 20 Houston 1 333445555 2 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent_name Sex Bdate Relationship 999887777 30 30.0 333445555 999887777 10 10.0 333445555 Alice Theodore F 1986-04-05 Daughter M 1983-10-25 Son 987987987 10 35.0 333445555 Joy F 1958-05-03 Spouse 987987987 30 5.0 987654321 Abner M 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth F 1967-05-05 Spouse