employee lab solutions:

1. List the name and location of department 1, 3, and 5.

SELECT *
FROM Dept_Locations
WHERE DepNo IN (1, 3, 5);

2. List female employee name and project name and project number that she is working on.

SELECT FName AS FIRST_NAME, LName AS LAST_NAME, PName AS PROJECT_NAME, PNumber AS PROJECT_NUMBER
FROM employee E, project P, Works_On W
WHERE W.PNo = P.PNumber AND W.ESSN = E.SSN AND SEX IN('F', 'f');

3. List the name of male employees who has a female dependent

select E.Fname, E.LName
from employee e, dependent d
where e.ssn = d.essn and e.sex= 'M' and d.sex='F';

4. List the name of employees who has a dependent of the same gender as the employee.

SELECT E.Fname, E.LName
FROM employee E
WHERE E.SSN IN ( SELECT ESSN
FROM Dependent
WHERE ESSN = E.SSN AND Sex = E.Sex);

Or:
SELECT E.Fname, E.Lname FROM employee E, Dependent D
WHERE E.SSN=ESSN AND D.Sex = E.Sex;

5. List the name of employees who works on project controlled by department 5.

SELECT FName, LName
FROM Employee E
WHERE E.SSN IN
(SELECT ESSN
FROM Works_On W
WHERE E.SSN = W.ESSN AND W.PNo IN
(SELECT PNumber
FROM Project
WHERE DepNo = 5));

select distinct e.fname, lname 
from employee e, works_on w
where e.ssn = w.essn AND
w.pno in ( select pnumber from project where depno = 5);

6. List the name of employees who are not working on any projects.

SELECT FName, LName
FROM employee
WHERE NOT EXISTS (SELECT *
FROM Works_On
WHERE SSN=ESSN);

select FName, LName 
from employee e
where e.ssn not in (select w.essn from works_on w);

7. List the name of employees who have no dependents.

SELECT FName, LName
FROM employee
WHERE NOT EXISTS (SELECT *
FROM Dependent
WHERE SSN=ESSN);

select FName, LName 
from employee e
where e.ssn not in (select d.essn from dependent d);

8. List the name of employees who work on both project 3388 and project 1945.

SELECT FName, LName
FROM employee E, Works_On W
WHERE W.ESSN = E.SSN AND PNo = '1945' AND SSN IN 
(Select SSN from employee, Works_On WHERE ESSN = SSN AND PNo = '3388');

9. List the department number and project number that are controlled by each department, ordered by project number.

Select PNumber, DepNo from project 
ORDER by PNumber;

10. List the name of employees whose supervisor does not have a supervisor.

Select lname, fname, ssn from employee 
where superssn IN (select ssn from employee where superssn is Null);

11. List the name of employees who both lives (address) and works (department location) in the same city.

Select fname, lname, address, E.depno from employee E, Dept_locations L
Where E.depno=L.depno and address like ‘%Salt%’ and dlocation like ‘%Salt%’;

12. List the name of supervisors who starts to supervise a department before Sep 20, 1976.

Select Fname, lname, E.depno, D.mgrsdate from employee E, Department D
Where ssn=mgrssn and mgrsdate < ‘20-SEP-76’;