dba3b

Query for given questions:

1) SELECT DISTINCT P.PNo
FROM PROJECT P
LEFT JOIN WORKS_ON W ON P.PNo = W.PNo
LEFT JOIN EMPLOYEE E1 ON W.SSN = E1.SSN
LEFT JOIN DEPARTMENT D ON P.DNo = D.DNo
LEFT JOIN EMPLOYEE E2 ON D.MgrSSN = E2.SSN
WHERE E1.Name LIKE '%Hegde%' OR E2.Name LIKE '%Hegde%';

2) SELECT E.SSN, E.Name, E.Salary * 1.10 AS NewSalary
FROM EMPLOYEE E
JOIN WORKS_ON W ON E.SSN = W.SSN
JOIN PROJECT P ON W.PNo = P.PNo
WHERE P.PName = 'AI';

3) SELECT SUM(E.Salary) AS TotalSalary,
 MAX(E.Salary) AS MaxSalary,
 MIN(E.Salary) AS MinSalary,
 AVG(E.Salary) AS AvgSalary
FROM EMPLOYEE E
JOIN DEPARTMENT D ON E.DNo = D.DNo
WHERE D.DName = 'Accounts';

4) SELECT E.Name
FROM EMPLOYEE E
WHERE NOT EXISTS (
 SELECT P.PNo
 FROM PROJECT P
 WHERE P.DNo = 5
 AND NOT EXISTS (
 SELECT W.SSN
 FROM WORKS_ON W
 WHERE W.PNo = P.PNo AND W.SSN = E.SSN
 )
);

5) SELECT D.DNo, COUNT(E.SSN) AS NumEmployees
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.DNo = E.DNo
WHERE E.Salary > 60000
GROUP BY D.DNo
HAVING COUNT(E.SSN) >= 1;