I need help, please if you know how to solve these problrm do it if you don\'t k
ID: 670908 • Letter: I
Question
I need help, please if you know how to solve these problrm do it if you don't knoow leave it.
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
Explanation / Answer
1. Retrieve the SSNs of employees along with the number of projects they are working on.
SELECT Essn,Count(PNumber) AS "NUMBER_OF_PROJ"
FROM WORKS_ON INNER JOIN PROJECT
ON WORKS_ON.Pno=PROJECT.Pnumber
2. For each employee working on at least two projects, list their SSN and average project hours.
SELECT Essn, Hours
FROM WORKS_ON
WHERE EXISTS
(
SELECT Count(PNumber) AS "NUMBER_OF_PROJ"
FROM WORKS_ON INNER JOIN PROJECT
ON WORKS_ON.Pno=PROJECT.Pnumber
HAVING COUNT(PNumber)>2
}
3. Retrieve the number of distinct employees that have dependents
SELECT Count(DISTINCT Essn) "NUMBER_OF_EMP_with_Dependents"
FROM WORKS_ON INNER JOIN DEPENDENT
ON WORKS_ON.Essn=DEPENDENT.Essn
4.Retrieve the names of employees who work on the project with pno=20.
SELECT CONCAT(FName,LName)
FROM WORKS_ON INNER JOIN EMPLOYEE
ON EMPLOYEE.Ssn=WORKS_ON.Essn
WHERE WORKS_ON.Pnumber=20
5. For each department with at least two locations, retrieve the department number along with its locations
SELECT DNumber , DLocation
FROM DEPT_LOCATIONS
WHERE EXISTS
(
SELECT Count(DLocation) AS "NUMBER_OF_LOCATION"
FROM DEPT_LOCATIONS
HAVING COUNT(DLocation)>2
}