set pause on
set pagesize 24
set linesize 79
prompt here are the solutions for the homework

/* Question 1 */
prompt Question 1
select fname, minit, lname, dno, hours, pname from employee, project, works_on where ssn=essn and pno=pnumber and dno=5 and hours >=10 and pname='ProductX';

/* Question 2 */
prompt Question 2
select fname, lname from employee, dependent where ssn=essn and fname=dependent_name;

/* Question 3 */
prompt Question 3
select e.fname, e.minit, e.lname from employee e, employee s where e.superssn=s.ssn and s.lname='Wong' and s.fname='Franklin';

/* Question 4 */
prompt Question 4
select p.pname, sum(w.hours) from project p, works_on w where w.pno=p.pnumber group by pname;

/* Question 5 */
prompt Question 5 (after adding to more rows for Franklin Wong)
select fname, lname from employee where ssn in (select essn from works_on group by essn having count (*) in (select count(*) from project));

/* or */ 
prompt another solution for question 5
SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE NOT EXISTS (SELECT P.PNUMBER      FROM PROJECT P WHERE NOT EXISTS (SELECT * FROM WORKS_ON W WHERE W.ESSN = E.SSN AND W.PNO = P.PNUMBER));

/* Question 6 */
prompt Question 6
select fname, lname from employee where ssn not in (select essn from works_on group by essn having count (*) >0);

/* Question 7 */
prompt Question 7
select d.dname, avg(e.salary) from department d, employee e where e.dno=d.dnumber group by d.dname;

/* Question 8 */
prompt Question 8
select avg(salary) from employee where sex='F' group by sex;

/* Question 9 */
prompt Question 9
SELECT E.FNAME, E.LNAME, E.ADDRESS
  FROM   EMPLOYEE E
  WHERE  EXISTS (SELECT *
                 FROM WORKS_ON W, PROJECT P
                 WHERE E.SSN=W.ESSN AND P.PLOCATION ='HOUSTON'
                       AND P.PNUMBER = W.PNO
         AND NOT EXISTS  (SELECT *
                         FROM  DEPT_LOCATION D, DEPARTMENT T
                         WHERE T.DNUMBER =E.DNO AND T.DNUMBER =  D.DNUMBER AND
                               D.DLOCATION = 'HOUSTON') );
/* Question 10 */
prompt Question 10
select fname, lname, ssn from employee e where ssn in (select distinct superssn from employee) and not exists (select distinct essn from dependent where ssn=essn);
set pause off