SQL Assignment 2

1. Retrieve the SSNs of employees along with the number of projects they are working on.

2. For each employee working on at least two projects, list their SSN and average project hours.

3. Retrieve the number of distinct employees that have dependents.

4. Retrieve the names of employees who work on the project with pno=20.

5. (extra credit) For each department with at least two locations, retrieve the department number along with its locations.

Figure 3 One possible database state for the COMPANY relational database schema EMPLOYEE Bdate B Smith 123456789 1965-01-09 231 Fondren, Houston, TX M 30000 John Frankin T Wong 333445555 1955-12-08 Alicia rS Wallace 987654321 1941 06-20 291 Benry BelaeX43000 890665555 455555 638 Voss. Houston, TX M 40000 888665555 JZelaya 999887777 1968-01-19 3321 Castle TXF25000 RKNorayan 000884444 1002091e HmT M 38000 20445598 loyceA Engish 453453453 1972-0731 5631 Rice Hoason. TXF 25000 AhmadV Jabbar982987987 1969-03-29 980 Dutas Houstion TXM 25000 9876543 amo E IBorg 888665555 1932-11-10, 450st nMouton. TX M 55000 NULL 445555E DE PARTMENT DEPT LOCATIONS Drame Dumber 338455 9826543