SELECT D.DEPENDENT_NAME, D.SEX
FROM Dependent D JOIN Employee E ON D.ESSN = E.SSN WHERE D.SEX = 'F'AND E.SEX = 'F'
UNION ALL(
SELECT D.DEPENDENT_NAME, D.SEX
FROM Dependent D JOIN Employee E ON D.ESSN = E.SSN WHERE D.SEX = 'M' AND E.SEX = 'M'
);

SELECT P.PNAME,SUM(W.HOURS) 
FROM Project P
LEFT JOIN Works_on W ON P.PNUMBER = W.PNO
GROUP BY P.PNAME;

SELECT *
FROM Department
WHERE DNUMBER =(
    SELECT DNO
    FROM Employee WHERE SSN = (SELECT MIN(SSN) FROM Employee)
);

SELECT D.DNAME, MAX(E.SALARY),MIN(E.SALARY),AVG(E.SALARY)
FROM department D
JOIN employee E ON D.DNUMBER = E.DNO
GROUP BY D.DNAME;

SELECT LNAME
FROM Employee
WHERE SSN IN( SELECT MGRSSN FROM Department )
AND SSN NOT IN(
    SELECT ESSN FROM Dependent
);

SELECT D.DNAME, D.DNUMBER, COUNT(E.SSN) ,AVG(E.SALARY) 
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DNUMBER = E.DNO
GROUP BY D.DNAME, D.DNUMBER
HAVING AVG(E.SALARY)< (SELECT AVG(SALARY) FROM EMPLOYEE);

SELECT CONCAT(FNAME, ' ', LNAME), D.DNAME, P.PNAME
FROM Employee E
JOIN Department D ON E.DNO =D.DNUMBER
JOIN Works_on W ON E.SSN =W.ESSN
JOIN Project P ON W.PNO =P.PNUMBER
ORDER BY D.DNAME, E.LNAME,E.FNAME;

SELECT CONCAT(FNAME, ' ', LNAME)
FROM Employee 
WHERE FNAME IN (SELECT DEPENDENT_NAME FROM Dependent);

SELECT SSN, CONCAT(FNAME, ' ', LNAME)
FROM Employee E
WHERE EXISTS(
    SELECT *
    FROM Dependent D
    WHERE D.ESSN = E.SSN
);

INSERT INTO department 
VALUES ('DEPT IT', 100, 112233, '2006-11-01');

UPDATE department
SET MGRSSN = 968574  WHERE Dnumber = 100;

UPDATE department
SET MGRSSN = 102672  WHERE Dnumber = 20;

UPDATE employee
SET SUPERSSN = 102672  WHERE SSN = 102660;

DELETE FROM dependent  WHERE Essn = 223344;

UPDATE department
SET MGRSSN = 102672
WHERE MGRSSN = 223344;

UPDATE employee
SET SUPERSSN = 102672  WHERE SUPERSSN = 223344;

DELETE FROM works_on
WHERE ESSN = 223344;


DELETE FROM employee
WHERE SSN = 223344;
