Program 5 (QUERIES)

Queries (Program 5)

1. Make a list of all project numbers for projects that involve an employee whose last name is ‘Scott’, either as a worker or as a manager of the department that controls the project.

(SELECT DISTINCT P. Pno

FROM PROJECT P,WORKS_ON  W, EMPLOYEE E

WHERE P.Pno=W.Pno ANDW.Ssn=E.Ssn AND E.Name like '%Scott')

UNION

(SELECTP.Pno                                                                 

FROM PROJECT P,EMPLOYEE E, DEPARTMENT D    

WHEREP.Dno=D.DnoANDD.Mgrssn=E.Ssn AND E.Name like '%Scott');

OR

(SELECT DISTINCT W. Pno

FROM WORKS_ON  W, EMPLOYEE E

WHERE W.Ssn=E.Ssn AND E.Name like '%Scott')

UNION

(SELECTP.Pno                                                                 

FROM PROJECT P,EMPLOYEE E, DEPARTMENT D    

WHEREP.Dno=D.DnoANDD.Mgrssn=E.Ssn AND E.Name like '%Scott');

2.   Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.

SELECT Name, Salary*1.1 as New_Salary

FROM EMPLOYEE E, WORKS_ON W,PROJECT P

where E.Ssn=W.Ssn AND W.Pno=P.Pno AND P.Pname='IoT';

3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department. 

SELECT SUM(Salary),MAX(Salary),MIN(Salary),AVG(Salary)

FROM EMPLOYEE E,DEPARTMENT D

WHERE E.Dno=D.Dno AND D.Dname='Accounts';

4. Retrieve the name of each employee who works on all the projects controlled by department number 5 (use NOT EXISTS operator).

SELECT E.Name

FROM EMPLOYEE E

WHERE NOT EXISTS (( SELECT  P.Pno

                                           FROM PROJECT P

                          WHERE P.Dno=5)

MINUS(SELECT W.Pno

                                         FROMWORKS_ON W

                                    WHERE E.Ssn=W.Ssn));

OR(Alternate Query)

SELECT E.Name

FROM EMPLOYEE E

WHERE   NOT EXISTS ( SELECT  *

                                    FROM WORKS_ON B

                                           WHERE (B.Pno IN (  SELECT  P.Pno

                                                                        FROM PROJECT P

                                                                        WHERE P.Dno=5) AND

NOT EXISTS( SELECT *

                                                                                    FROM WORKS_ON C

                                                                        WHERE C.Ssn=E.Ssn ANDC.Pno=B.Pno)));

5. For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than Rs. 6,00,000.

SELECT D.Dno,COUNT(*) as NO_OF_EMP

FROM DEPARTMENT D,EMPLOYEE E

WHERE D.Dno=E.Dno AND E.Salary>600000 AND E.Dno IN(SELECTE.Dno

                                                                                    FROM EMPLOYEE E

                                                                                    GROUP BY E.Dno

                                                                                    HAVING COUNT(*)>5)

GROUP BY D.Dno;  

OR

SELECT Dno,COUNT(*) as NO_OF_EMP

FROM EMPLOYEE E

WHERE Salary>600000 AND Dno IN(SELECT Dno

                                                    FROM EMPLOYEE

                                                    GROUP BY Dno

                                                     HAVING COUNT(*)>5)

GROUP BY Dno;