select ename from emp1 where deptno=20 and sal> all (select sal from emp1 where deptno=30);


select ename from emp1 A where exists (select mgr from emp1 B where B.mgr=A.empno);
select ename from emp1 where empno in (select mgr from emp1);

select deptno, dname from dept1 A where not exists (select deptno from emp1 B where A.deptno=B.deptno);
select deptno, dname from dept1 where deptno <>all(select deptno from emp1);

select distinct ename, job, deptno from emp1 where deptno in (select deptno from emp1 A group by deptno having (max(sal)-min(sal))>=(select avg(sal) from emp1 B where B.deptno=A.deptno));
select distinct ename, job, deptno from emp1 where deptno in (select deptno from emp1 A group by deptno having (max(sal)-min(sal))=(select avg(sal)+25 from emp1 B where B.deptno=A.deptno));
select distinct A.ename, B.ename from emp1 A, emp1 B, salgrade1 sA, salgrade1 sB where (A.sal between sA.losal and sA.hisal) and (B.sal between sB.losal and sB.hisal) and ((sA.grade-sB.grade) in(3, 4));

/*zączenia zewnętrzne*/
select dname Departament, ename Employee from dept1 d, emp1 e where d.deptno=e.deptno(+) order by dname, ename;
select dname Departament, ename Employee from dept1 d, emp1 e where d.deptno=e.deptno(+) and e.empno is null order by dname, ename; /*departament w którym nikt nie pracuje*/
/*podać liczbę pracowników wszystkich departamentów(łącznie z tymi, w których nikt nie pracuje)*/
select dname, count(empno) from dept1 d, emp1 e where d.deptno=e.deptno(+) group by dname;
/*szefowie pracowników poziom drugi*/
select w.empno, w.ename bee_ename, w.deptno dept, '|', b.empno, nvl(b.ename, 'NIKT') bos_name, b.deptno dept from emp w, emp b where nvl(w.mgr, -9)=b.empno(+) order by w.mgr;

EMPNO BEE_ENAME DEPT '|' EMPNO BOS_NAME DEPT
---------------------- ---------- ---------------------- --- ---------------------- ---------- ----------------------
7902 FORD 20 | 7566 JONES 20
7788 HESS 20 | 7566 JONES 20
7654 MARTIN 30 | 7698 BLAKE 30
7900 JAMES 30 | 7698 BLAKE 30
7844 TURNER 30 | 7698 BLAKE 30
7521 WARD 30 | 7698 BLAKE 30
7499 ALLEN 30 | 7698 BLAKE 30
7934 MILLER 10 | 7782 CLARK 10
7876 ADAMS 20 | 7788 HESS 20
7782 CLARK 10 | 7839 KING 10
7698 BLAKE 30 | 7839 KING 10
7566 JONES 20 | 7839 KING 10
7369 SMITH 20 | 7902 FORD 20
7839 KING 10 | NIKT
1111 KOWALSKI 10 | NIKT


/*szefowie pracowników poziom trzeci*/
select w.empno, w.ename bee_ename, w.deptno dept, '|', m.empno, nvl(m.ename, 'NIKT') bos_name, m.deptno dept, '|', b.empno, nvl(b.ename, 'NIKT') bos_name, b.deptno dept from emp w, emp m, emp b where nvl(w.mgr, -9)=m.empno(+) and nvl(m.mgr, -9)=b.empno(+) order by m.mgr, w.mgr;


EMPNO BEE_ENAME DEPT '|' EMPNO BOS_NAME DEPT '|' EMPNO BOS_NAME DEPT
---------------------- ---------- ---------------------- --- ---------------------- ---------- ---------------------- --- ---------------------- ---------- ----------------------
7876 ADAMS 20 | 7788 HESS 20 | 7566 JONES 20
7369 SMITH 20 | 7902 FORD 20 | 7566 JONES 20
7788 HESS 20 | 7566 JONES 20 | 7839 KING 10
7902 FORD 20 | 7566 JONES 20 | 7839 KING 10
7900 JAMES 30 | 7698 BLAKE 30 | 7839 KING 10
7844 TURNER 30 | 7698 BLAKE 30 | 7839 KING 10
7521 WARD 30 | 7698 BLAKE 30 | 7839 KING 10
7499 ALLEN 30 | 7698 BLAKE 30 | 7839 KING 10
7654 MARTIN 30 | 7698 BLAKE 30 | 7839 KING 10
7934 MILLER 10 | 7782 CLARK 10 | 7839 KING 10
7566 JONES 20 | 7839 KING 10 | NIKT
7698 BLAKE 30 | 7839 KING 10 | NIKT
7782 CLARK 10 | 7839 KING 10 | NIKT
1111 KOWALSKI 10 | NIKT | NIKT
7839 KING 10 | NIKT | NIKT

15 rows selected

select w.empno, w.ename bee_ename, w.deptno dept, '|', m.empno, nvl(m.ename, 'NIKT') bos_name, m.deptno dept, '|', b.empno, nvl(b.ename, 'NIKT') bos_name, b.deptno dept, '|',k.empno, nvl(k.ename, 'NIKT') bos_name, k.deptno dept from emp w, emp m, emp b, emp k where nvl(w.mgr, -9)=m.empno(+) and nvl(m.mgr, -9)=k.empno(+) and nvl(k.mgr, -9)=b.empno(+) order by m.mgr, k.mgr, w.mgr;

EMPNO BEE_ENAME DEPT '|' EMPNO BOS_NAME DEPT '|' EMPNO BOS_NAME DEPT '|' EMPNO BOS_NAME DEPT
---------------------- ---------- ---------------------- --- ---------------------- ---------- ---------------------- --- ---------------------- ---------- ---------------------- --- ---------------------- ---------- ----------------------
7876 ADAMS 20 | 7788 HESS 20 | 7839 KING 10 | 7566 JONES 20
7369 SMITH 20 | 7902 FORD 20 | 7839 KING 10 | 7566 JONES 20
7902 FORD 20 | 7566 JONES 20 | NIKT | 7839 KING 10
7788 HESS 20 | 7566 JONES 20 | NIKT | 7839 KING 10
7654 MARTIN 30 | 7698 BLAKE 30 | NIKT | 7839 KING 10
7521 WARD 30 | 7698 BLAKE 30 | NIKT | 7839 KING 10
7844 TURNER 30 | 7698 BLAKE 30 | NIKT | 7839 KING 10
7900 JAMES 30 | 7698 BLAKE 30 | NIKT | 7839 KING 10
7499 ALLEN 30 | 7698 BLAKE 30 | NIKT | 7839 KING 10
7934 MILLER 10 | 7782 CLARK 10 | NIKT | 7839 KING 10
7782 CLARK 10 | 7839 KING 10 | NIKT | NIKT
7698 BLAKE 30 | 7839 KING 10 | NIKT | NIKT
7566 JONES 20 | 7839 KING 10 | NIKT | NIKT
1111 KOWALSKI 10 | NIKT | NIKT | NIKT
7839 KING 10 | NIKT | NIKT | NIKT

15 rows selected