1 Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project.
-------------------------------------------------------------

select e.FNAME,e.MINIT,e.LNAME
from   employee e, works_on w, project p
where  e.DNO = 5 and
       e.ssn = w.essn and w.pno = p.pnumber and
       p.pname = 'ProductX' and w.hours > 10;
------------------------------------
FNAME                     M LNAME
------------------------- - -------------------------
John                      B Smith
Joyce                     A English

-----------------------------------------------
2 List the names of all employees who have a dependent with the same first name as themselves.
------------------------------------
select e.fname, e.minit, e.lname
from   employee e, dependent d
where  e.ssn = d.essn and e.fname = d.dependent_name;

FNAME                     M LNAME
------------------------- - -------------------------
Alec                      C Best

--------------------------------------
3 Find the names of employees who are directly supervised by ‘Franklin Wong’.
--------------------------------------
select e.fname,e.minit,e.lname
from   employee e, employee e1
where  e.superssn = e1.ssn
and    e1.fname = 'Franklin' and e1.lname = 'Wong';

FNAME                     M LNAME
------------------------- - -------------------------
John                      B Smith
Ramesh                    K Narayan
Joyce                     A English


-----------------------------------------------
4 Retrieve the names of employees who work on every project.
------------------------------------------
select e.fname, e.minit, e.lname
from   employee e
where  not exists(
         select p.pnumber 
         from   project p
         where  not exists(
                  select w1.essn 
                  from   works_on w1
                  where  w1.pno = p.pnumber and w1.essn = e.ssn));
---------------------------------------------------------
Alternate query
---------------------------------------------------------
SELECT fname,lname
FROM employee
WHERE NOT EXISTS (SELECT pnumber
           FROM project
           MINUS (SELECT pno
		 FROM works_on
		 WHERE ssn=essn));


				  
FNAME                     M LNAME
------------------------- - -------------------------
Franklin                  T Wong

		
-----------------------------------------------
5 Retrieve the names of employees who do not work on any project.
----------------------------------------------------
select fname,minit,lname
from   employee 
where  not exists (
          select essn 
          from   works_on 
          where  ssn = essn);

		  FNAME                     M LNAME
------------------------- - -------------------------
Kate                      W King
Bob                       B Bender


----------------------------------------------------
6 Retrieve the names and addresses of all employees who work on at least one project located 
in Houston but whose department has no location in Houston.
-------------------------------------------------------
select e.fname, e.minit, e.lname, e.address
from   employee e, project p, works_on w
where  e.ssn = w.essn and p.pnumber = w.pno and p.plocation = 'Houston' and 
       not exists (
          select d.dnumber 
          from   dept_locations d
          where  e.dno = d.dnumber and d.dlocation = 'Houston');
		  
FNAME                     M LNAME
------------------------- - -------------------------
ADDRESS
--------------------------------------------------
Jennifer                  S Wallace
291 Berry, Bellaire, TX

		
------------------------------------------------
7 Retrieve the last names of all department managers who have no dependents.
----------------------------------------------------

select e.lname
from   employee e, department d 
where  e.ssn = d.mgrssn and 
         not exists(
             select essn 
             from   dependent 
             where  d.mgrssn = essn);
			 
LNAME
-------------------------
James
James
Borg

-----------------------------------------		
8. List the names of employees whose supervisor's supervisor has a social security number of 888665555. 
-----------------------------------------

Select e1.lname 
from employee e1, employee e2 
where e1.superssn = e2.ssn and e2.superssn = '888665555';

LNAME
-------------------------
Smith
Zelaya
Narayan
English
Jabbar
----------------------------------------------------------
9. List the name(s) of employee(s) with the lowest salary.
-----------------------------------------------------------

Select e.fname, e.lname 
from employee e 
where e.salary <= All(Select salary from employee);

FNAME                     LNAME
------------------------- -------------------------
Ahmad                     Jabbar
Alicia                    Zelaya
Joyce                     English

---------------------------------------------------------
10.List the names of employees who work in the department that has an employee with the highest salary among all employees. 
---------------------------------------------------------

Select e.fname, e.lname 
from employee e, employee e1 
where e.dno = e1.dno and 
	e1.salary >= All(Select e2.salary from employee e2);
	
Alternative query
----------------------
select fname, lname
from employee 
where dno = (
	select dno
	from employee
	where salary = (
		select max(salary)
		from employee
		)
	)
;
------------------
FNAME                     LNAME
------------------------- -------------------------
Lyle                      Leslie
Kate                      King
Jill                      Jarvis
Bob                       Bender
Billie                    King
Jon                       Kramer
Ray                       King
Gerald                    Small
Arnold                    Head
Helga                     Pataki
Naveen                    Drew

FNAME                     LNAME
------------------------- -------------------------
John                      James
Sammy                     Hall
Red                       Bacher
Carl                      Reedy

--------------------------------------------------------
11.For each project, list the project name and the total hours per week (by all employees) spent on the project. 
---------------------------------------------------------

select p.pnumber, p.pname, SUM (hours) 
from project p, works_on w
where p.pnumber = w.pno group by p.pnumber,p.pname;
-------------------------
PNUMBER PNAME                     SUM(HOURS)
---------- ------------------------- ----------
	20 Reorganization                    25
	 1 ProductX                        62.5
	61 OperatingSystems                 360
	92 LaserPrinters                    134
	62 DatabaseSystems                  308
	63 Middleware                       146
	10 Computerization                   55
	30 Newbenefits                       65
	 2 ProductY                        37.5
	91 InkjetPrinters                   330
	 3 ProductZ                          50

---------------------------------------------------------
12. For each department, retrieve the department name and the average salary of all employees working in that department. 
---------------------------------------------------------

select d.dnumber, d.dname, AVG (salary) 
from department d, employee e
where d.dnumber = e.dno group by dnumber, dname;
--------------------------
   DNUMBER DNAME                     AVG(SALARY)
---------- ------------------------- -----------
	 5 Research                        33250
	 8 Sales                           43500
	 1 Headquarters                    55000
	 6 Software                        57000
	 4 Administration                  31000
	 7 Hardware                        63450

---------------------------------------------------
13. Retrieve the average salary of all female employees.
---------------------------------------------------

select AVG(salary) AVG_Female_Salary from employee where sex = 'F';