--oef1
select c.name,sum(o.total)
from customer c, ord o
where c.custid=o.custid
and c.custid IN(select c2.custid
              from customer c2, item i, product p, ord o2
              where o2.ordid=i.ordid
              and p.prodid=i.prodid
              and o2.custid=c.custid
              and p.descrip='ACE TENNIS RACKET I'
              )
group by c.name
MINUS
select c.name,sum(o.total)
from customer c, ord o
where c.custid=o.custid
and c.custid IN(select c2.custid
              from customer c2, item i, product p, ord o2
              where o2.ordid=i.ordid
              and p.prodid=i.prodid
              and o2.custid=c.custid
              and p.descrip='ACE TENNIS NET'
              )
group by c.name;

--oef2
select d.dname,e.ename,count(t.projno)
from emp e, dept d, team t
where e.empno=t.empno
and d.deptno=e.deptno
and e.sal = (select max(e2.sal)
             from emp e2
             where e2.deptno=e.deptno
             group by e2.deptno
             )
group by d.dname,e.ename;

--oef3
break on departement
select departement,rownum as "volgorde",naam
from (select deptno departement,ename naam
      from emp
      order by sal DESC)
order by departement;

--oef4
select e1.ename, e2.ename,e1.sal,d.dname,TO_CHAR(e1.hiredate, 'DD-MM-YY')
from emp e1, emp e2, dept d
where e1.mgr=e2.empno
and e1.deptno=d.deptno
and 1 <= (select count(e1.empno)
          from emp e3
          where e1.job=e3.job
          and e1.hiredate < e3.hiredate
          and e1.sal < e3.sal
          )
and (e1.job!='SALESMAN' or (e1.job='SALESMAN' and e1.sal > (select avg(sal)
                                                             from emp
                                                             where job='SALESMAN'
                                                              )));

--oef5
select c.city,sum(o.total)
from customer c, ord o
where c.custid=o.custid
group by c.city
having sum(o.total) > (select avg(sum(o2.total))
                       from ord o2, customer c2
                       where c2.custid=o2.custid
                       group by c2.city
                       )
UNION
select d.loc,sum(e.sal)
from dept d, emp e
where e.deptno=d.deptno
group by d.loc
having sum(e.sal) > (select avg(sum(e2.sal))
                     from emp e2, dept d2
                     where d2.deptno=e2.deptno
                     group by d2.loc);

--oef7
select count(e.empno),'Verkopers'
from emp e
where e.empno in (select e2.empno
                  from emp e2, customer c2, ord o2
                  where e2.empno=c2.repid
                  and o2.custid=c2.custid
                  and e2.job='SALESMAN'
                  group by e2.empno
                  having sum(o2.total) > (select avg(sum(o3.total))
                                          from ord o3, customer c3, emp e3
                                          where e3.empno=c3.repid
                                          and c3.custid=o3.custid                                         
                                          group by e3.empno
                                          ))
UNION
select count(e.empno),'Niet Verkopers'
from emp e
where e.empno in (select e2.empno
                 from emp e2, team t2
                 where e2.empno=t2.empno
                 and e2.job!='SALESMAN'
                 and 1 <= (select count(t3.projno)
                           from emp e3, team t3
                           where e3.empno=t3.empno
                           group by e3.empno));