# Q12
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE E.SSN IN
(SELECT	ESSN
FROM DEPENDENT
WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME);

SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS
(SELECT	*
FROM DEPENDENT
WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME);

# Q8
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE as E, EMPLOYEE as S WHERE E.SUPERSSN=S.SSN;
# Q8
SELECT	E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM (EMPLOYEE as E LEFT OUTER JOIN EMPLOYEE as S ON E.SUPERSSN=S.SSN);

# Q2
SELECT 	PNUMBER, DNUM, LNAME, BDATE, ADDRESS 
FROM ((PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) 
     JOIN EMPLOYEE ON MGRSSN=SSN)
WHERE PLOCATION='Stafford';

# Q15
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) 
FROM EMPLOYEE;

# Q16: 
SELECT 	MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM	EMPLOYEE, DEPARTMENT
WHERE	DNO=DNUMBER AND DNAME='Research';

# Q17
SELECT COUNT(*) FROM EMPLOYEE;

# Q18
SELECT COUNT(*) FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research';

# Q20:
SELECT DNO, COUNT(*), AVG(SALARY)
FROM	EMPLOYEE
GROUP BY DNO;

# Q21:	
SELECT	PNUMBER, PNAME, COUNT(*)
FROM	PROJECT, WORKS_ON
WHERE	PNUMBER=PNO
GROUP BY PNUMBER, PNAME;

# Q22
SELECT	PNUMBER, PNAME, COUNT(*)
FROM	PROJECT, WORKS_ON
WHERE	PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT(*) > 2;

# Q25
SELECT	FNAME, LNAME
FROM	EMPLOYEE
WHERE	ADDRESS LIKE '%Houston,TX%';

# Q26
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE LIKE '_______5_';

# Q27
SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX';

# Q28
SELECT  DNAME, LNAME, FNAME, PNAME
FROM 	DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE	DNUMBER=DNO AND SSN=ESSN   AND  PNO=PNUMBER  
ORDER BY DNAME, LNAME;

# U1c
INSERT INTO DEPT_LOCATIONS (DLOCATION) VALUES ('Houston');

# U4A: 
DELETE FROM  EMPLOYEE  WHERE  LNAME='Brown';

# U4B: 
DELETE FROM  EMPLOYEE  WHERE  SSN='123456789';

# U4C: 
DELETE FROM  EMPLOYEE
WHERE  DNO  IN 
(SELECT DNUMBER   FROM DEPARTMENT   WHERE DNAME='Research');

# U4D: 
DELETE FROM  EMPLOYEE;

# U5:	
UPDATE PROJECT 
SET PLOCATION = 'Bellaire', DNUM = 5 
WHERE PNUMBER=10;

# U6: 
UPDATE EMPLOYEE 
SET  SALARY = SALARY *1.1 
WHERE DNO IN 
  (SELECT DNUMBER       
   FROM DEPARTMENT
   WHERE DNAME='Research');