Q.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:

The EMP-DEPENDENT database links the two tables through the common column EmpID.

-- a) employee names with their dependent names
SELECT E.Name, D.DependentName
FROM EMPLOYEE E JOIN DEPENDENT D ON E.EmpID = D.EmpID;

-- b) full details of employees of the PRODUCTION department
SELECT * FROM EMPLOYEE WHERE Department = 'PRODUCTION';

-- c) employees having no dependent
SELECT Name FROM EMPLOYEE
WHERE EmpID NOT IN (SELECT EmpID FROM DEPENDENT);

-- d) SALES employees having exactly two dependents
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(*) = 2; …

Reading the schema first

EMPLOYEE(AadhaarNo, Name, Address, Department, EmpID)
DEPENDENT(EmpID, DependentName, Relationship)

EmpID identifies an employee in EMPLOYEE and re-appears in DEPENDENT as the link (a foreign key): one employee can have many dependent rows. Whenever a question needs information from both tables, we join them on EmpID.

a) Names of employees with their dependent names

The name lives in EMPLOYEE, the dependent's name in DEPENDENT — so join:

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

Why these lines: E and D are table aliases that keep the query readable; the ON E.EmpID = D.EmpID condition pairs each dependent row with its own employee. The result has two columns — Name | DependentName — one row per (employee, dependent) pair. An employee with three dependents appears three times; an employee with none does not appear at all (an inner join keeps only matching rows).

b) Details of employees working in 'PRODUCTION'

Everything asked for is inside EMPLOYEE, so no join is needed:

SELECT *
FROM EMPLOYEE
WHERE Department = 'PRODUCTION';

Why: SELECT * returns all five attributes ("employee details"), and the WHERE clause keeps only rows whose Department equals the string 'PRODUCTION'. The result is a table with columns AadhaarNo | Name | Address | Department | EmpID containing only PRODUCTION employees.

c) Employee names having no dependent

"No dependent" means the employee's EmpID never occurs in DEPENDENT. A join cannot express absence, but a subquery can:

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

Why: the inner query builds the set of all EmpIDs that do have dependents; NOT IN keeps every employee outside that set. The result is a single Name column.

d) 'SALES' employees having exactly two dependents

This needs a count per employee, which is what GROUP BY + HAVING do:

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(*) = 2;

Why each clause works:

• JOIN … ON E.EmpID = D.EmpID — one row per dependent of each employee. …