An organization ABC maintains a database EMP-DEPENDENT to record the following details about its employees and their dependents.


EMPLOYEE (AadhaarNo, Name, Address, Department, EmpID)
DEPENDENT (EmpID, DependentName, Relationship)
Use the EMP-DEPENDENT database to answer the following SQL queries:

(a) Find the names of employees with their dependent names.
Ans:         
Select E.Name, D.DependentName FROM EMPLOYEE E, DEPENDENT D
WHERE E.EmpID = D.EmpID;


(b) Find employee details working in a department, say, ‘PRODUCTION’.
Ans:         
SELECT * FROM EMPLOYEE WHERE Department = ‘PRODUCTION’;


(c) Find employee names having no dependent
Ans:         
SELECT Name FROM EMPLOYEE
WHERE EmpID NOT IN (SELECT EMPID FROM DEPENDENT);


(d) Find names of employees working in a department, say, ‘SALES’ and having exactly two dependents.
Ans:         
SELECT E.Name FROM EMPLO.YEE E, DEPENDENT D
WHERE E.EmpID = D.EmpID AND Department = ‘SALES’
GROUP BY E.EmpID
HAVING COUNT (D.EmpID) = 2;