Bright Tutorials Bright Tutorials
CBSE Class 11 Informatics Practices Question 62 of 87

Structured Query Language (SQL) — Question 23

Back to all questions
23
Question

Question 17

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:

(i) Find the names of the employees with their dependents' names.

(ii) Find employee details working in a department, say, 'PRODUCTION'.

(iii) Find employee names having no dependents.

(iv) Find the names of employees working in a department, say, 'SALES' and having exactly two dependents.

Answer

(i)

SELECT e.Name, d.DependentName
FROM EMPLOYEE e, DEPENDENT d 
WHERE e.EmpID = d.EmpID;

(ii)

SELECT *
FROM EMPLOYEE
WHERE Department = 'PRODUCTION';

(iii)

SELECT e.Name
FROM EMPLOYEE e, DEPENDENT d 
WHERE e.EmpID = d.EmpID AND d.DependentName IS NULL;

(iv)

SELECT Name
FROM EMPLOYEE
WHERE Department = 'SALES' AND EmpID IN (
    SELECT EmpID
    FROM DEPENDENT
    GROUP BY EmpID
    HAVING COUNT(*) = 2
);