Question

An organization ABC maintains a database EMPDEPENDENT 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

1. Find the names of employees with their dependent names.
2. Find employee details working in a department, say, ‘PRODUCTION’.
3. Find employee names having no dependent.
4. Find names of employees working in a department, say, ‘SALES’ and having exactly two dependents.

Answer 1

a) 

SELECT E.Name, D.DependentName
FROM EMPLOYEE E
JOIN DEPENDENT D
ON E.EmpID = D.EmpID;

b) 

SELECT *
FROM EMPLOYEE
WHERE Department = 'PRODUCTION';

c) 

SELECT Name
FROM EMPLOYEE
WHERE EmpID NOT IN (
    SELECT DISTINCT EmpID
    FROM DEPENDENT
);

d) 

SELECT E.Name
FROM EMPLOYEE E
JOIN DEPENDENT D
ON E.EmpID = D.EmpID
WHERE E.Department = 'SALES'
GROUP BY E.EmpID, E.Name
HAVING COUNT(D.DependentName) = 2;