-- List the name of each employee who worked on EVERY project.

select firstname, lastname
from   Emps E
where  not exists (select *
                   from   Projects P
                   where  P.pno not in (select pno
                                        from   WorksOn W
                                        where  W.eid = E.eid));

select firstname, lastname
from   Emps E
where  not exists (select *
                   from   Projects P
                   where  not exists (select *
                                      from   WorksOn W
                                      where  W.eid = E.eid
                                             and W.pno = P.pno));

-- For each employee, list his/her name and count how many (distinct) 
-- projects he/she has/had worked on.

-- select firstname, lastname, count(pno) 

select firstname, lastname, count(distinct pno) 
  from Emps natural join WorksOn
group by eid, firstname, lastname;

-- What about those employees who never worked on any project?

select firstname, lastname, count(distinct pno) 
  from Emps E left join WorksOn W on E.eid = W.eid
group by E.eid, firstname, lastname;

-- For each department, list the department’s name and the highest 
-- salary earned by its employees.

select dname, max(salary)
  from departments left join emps on did = workdept
group by dname;
-- having avg(salary) > 60000;

-- For each department, list the department’s name and the average
-- and total salary of the employees working in it.

-- select dname, count(eid) as numEmps, avg(salary) as averageSal,
select dname, count(*) as numEmps, avg(salary) as averageSal,
       sum(salary) as totalSal, sum(salary)/count(eid) as calcAVG
  from departments left join emps on did = workdept
group by dname;

-- List the name(s) of the employee(s) who has the highest salary. 
-- (Note that query needs to return ALL qualified data.)

select firstname, lastname, salary
  from (select firstname, lastname, salary
          from emps
         where salary is not null
        order by salary desc)
where rownum = 1; -- where rownum <= 2;

-- won't work because the null salary in emps table
select firstname, lastname, salary
  from emps
 where salary >= all (select salary
                        from emps);

-- won't work
select firstname, lastname, max(salary)
  from emps
group by firstname, lastname;

-- works in sqlite, but not in Oracle
select firstname, lastname, max(salary)
  from emps;

-- works
select firstname, lastname, salary
  from emps
 where salary >= all (select salary
                        from emps
                       where salary is not null);

select firstname, lastname, salary
  from emps
 where salary = (select max(salary)
                   from emps
                  where salary is not null);

-- List the name(s) of the departments that have more than 2 employees
select dname, count(eid)
  from departments left join emps on did = workdept
group by dname
having count(eid) > 2;

-- For each employee, list his/her name and count how many (distinct) 
-- projects he/she has/had worked on. Result should include those
-- never worked on any project.

select firstname, lastname, count(distinct pno) as ProjCount
  from Emps natural join WorksOn
group by eid, firstname, lastname
union all
select firstname, lastname, 0 as ProjCount
  from Emps
 where eid NOT IN (select eid
                     from workson);

-- list the name of each employee who has never worked on any project
select firstname, lastname
  from Emps
minus
select firstname, lastname
  from Emps natural join WorksOn;

-- list the name of each member who has certificate in
-- both 'Saws' and 'Nail Guns'

select distinct fname, lname
  from members M, Certificates C1, Certificates C2
 where M.mno = C1.mno and C1.category = 'Saws'
       and M.mno = C2.Mno and C2.category = 'Nail Guns';

select fname, lname
  from members
 where mno IN (select mno
                 from Certificates
                where category = 'Saws')
       and mno IN (select mno
                     from Certificates
                    where category = 'Nail Guns');

select fname, lname
  from members natural join Certificates
 where category = 'Saws'
Intersect
select fname, lname
  from members natural join Certificates
 where category = 'Nail Guns';