Ćwiczenie 5

Podzapytania

Podzapytania zwracające jeden wiersz

Podzapytania zwracające wiele wierszy

Podzapytanie to wewnętrzny SELECT wykonywany w pierwszej kolejności, po to, aby jego wynik został użyty do wykonania zapytania zewnętrznego.

Podzapytania zwracające pojedynczą wartość można traktować jak zwykłe wyrażenie

.

SELECT kolumna_1, kolumna_2, …

FROM tabela

WHERE kolumna = (SELECT kolumna

FROM tabela

WHERE warunek)

ORDER BY kolumna;

Warunki porównawcze dzielimy na operatory jednowierszowe (>, =, >=, <, <=) i operatory wielowierszowe (IN, ANY,ALL)

Podzapytania mogą występować również w klauzuli HAVING (przypominamy — klauzula HAVING odnosi się do grup wierszy). Nie istnieje limit na liczbę poziomów zagnieżdżania podzapytań

Operatory ALL i ANY można stosować w podzapytaniach zwracających więcej niż jeden wiersz. Podaje się je w klauzulach WHERE i HAVING razem z operatorami porównywania.

Operator ANY — wiersz zostanie wybrany, jeśli wyrażenie jest zgodne co najmniej z jedną wartością wybraną w podzapytaniu

Operator ALL — warunek musi być spełniony przez wszystkie wartości wybrane w podzapytaniu.

Wybierzmy wszystkich pracowników, którzy zarabiają więcej niż ktokolwiek w departamencie 30:

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE SAL > ALL (SELECT DISTINCT SAL

FROM EMP

WHERE DEPTNO=30)

W podzapytaniu nie może występować klauzula ORDER BY. Klauzula ORDER BY może wystąpić tylko raz dla całego zapytania i wtedy musi pojawić się jako ostatnia.

Zadania do zapytań SQL:

1. Wybierz urzędników (CLERK) mających pensję poniżej średniej dla wszystkich

select ename, sal from emp where job='CLERK' and sal<(select avg(sal) from emp);

ENAME SAL

---------- ----------

JAMES 950

SMITH 800

ADAMS 1100

MILLER 1300

2. Wybierz pracowników dostających najniższą pensję.

select ename, sal, s.grade from emp, salgrade s where sal between losal and hisal and s.grade=1;

ENAME SAL GRADE

---------- ---------- ----------

JAMES 950 1

SMITH 800 1

ADAMS 1100 1

3. Wybierz pracowników, których pensja jest najwyższa w departamentach

select deptno, ename, sal from emp where sal in(select max(sal) from emp group by deptno);

DEPTNO ENAME SAL

---------- ---------- ----------

30 BLAKE 2850

20 FORD 3000

20 SCOTT 3000

10 KING 5000

4. Wybierz zawody, w których średnia płaca jest wyższa niż średnia płaca w zawodzie 'MANAGER'

select job from emp having avg(sal)>(select avg(sal) from emp where job='MANAGER') group by job;

JOB

---------

ANALYST

PRESIDENT

5. Wybierz wszystkich zatrudnionych na tym samym stanowisku co SMITH

select ename from emp where job=(select job from emp where ename='SMITH');

JAMES

SMITH

ADAMS

MILLER

6. Wyświetl nazwiska i daty zatrudnienia każdego pracownika zatrudnionego w tym samym departamencie co pracownik BLAKE, ale bez pracownika BLAKE.

select ename, hiredate from emp where ename!='BLAKE' and job=(select job from emp where ename='BLAKE');

select ename, hiredate from emp where not ename='BLAKE' and job=(select job from emp where ename='BLAKE');

ENAME HIREDATE

---------- --------

CLARK 81/06/09

JONES 81/04/02

7. Wyświetl nazwisko i pensję każdego pracownika, który podlega pracownikowi KING

select ename, sal from emp where mgr=(select empno from emp where ename='KING');

ENAME SAL

---------- ----------

BLAKE 2850

CLARK 2450

JONES 2975

8. Wyświetl numer departamentu, nazwisko i stanowisko wszystkich pracowników w departamencie RESEARCH

select deptno, ename, job from emp where deptno=(select deptno from dept where dname='RESEARCH');

DEPTNO ENAME JOB

---------- ---------- ---------

20 JONES MANAGER

20 FORD ANALYST

20 SMITH CLERK

20 SCOTT ANALYST

20 ADAMS CLERK

9. Znajdź stanowisko o najniższych średnich zarobkach

select job from emp having avg(sal)=(select min(avg(sal)) from emp group by job) group by job;

JOB

---------

CLERK

10. Znajdź tych pracowników, którzy zostali zatrudnieniu przed przyjęciem do pracy ich kierowników.

select e.empno, e.ename, ek.empno as k_empno, ek.ename as k_ename from emp e, emp ek where e.mgr=ek.empno and e.hiredate < ek.hiredate;

EMPNO ENAME K_EMPNO K_ENAME

---------- ---------- ---------- ----------

7698 BLAKE 7839 KING

7782 CLARK 7839 KING

7566 JONES 7839 KING

7499 ALLEN 7698 BLAKE

7521 WARD 7698 BLAKE

7369 SMITH 7902 FORD

11. Znajdź pracowników zarabiających maksymalna pensję na ich stanowiskach pracy. Uporządkuj ich według malejących zarobków

select ename, job, sal from emp where sal in (select max(sal) from emp group by job) order by sal desc;

ENAME JOB SAL

---------- --------- ----------

KING PRESIDENT 5000

FORD ANALYST 3000

SCOTT ANALYST 3000

JONES MANAGER 2975

ALLEN SALESMAN 1600

MILLER CLERK 1300

12. Wskaż dla każdego departamentu najwcześniej zatrudnionych pracowników. Uporządkuj według dat zatrudnienia

select ename, deptno, hiredate from emp where hiredate in(select min(hiredate) from emp group by deptno) order by hiredate;

ENAME DEPTNO HIREDATE

---------- ---------- --------

SMITH 20 80/12/17

ALLEN 30 81/02/20

CLARK 10 81/06/09

Warunek, w którym porównujemy wiele wartości

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE (SAL,DEPTNO) IN (SELECT MAX(SAL), DEPTNO

FROM EMP

GROUP BY DEPTNO);

SQL Ćwiczenia Strona 3

Podzapytanie to polecenie SELECT zagnieżdżone w innym poleceniu SELECT

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ------- --------- ----- -------- ------ ------ ------

7839 KING PRESIDENT 81/11/17 5000 10

7698 BLAKE MANAGER 7839 81/05/01 2850 30

7782 CLARK MANAGER 7839 81/06/09 2450 10

7566 JONES MANAGER 7839 81/04/02 2975 20

7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30

7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30

7844 TURNER SALESMAN 7698 81/09/08 1500 0 30

7900 JAMES CLERK 7698 81/12/03 950 30

7521 WARD SALESMAN 7698 81/02/22 1250 500 30

7902 FORD ANALYST 7566 81/12/03 3000 20

7369 SMITH CLERK 7902 80/12/17 800 20

7788 SCOTT ANALYST 7566 82/12/09 3000 20

7876 ADAMS CLERK 7788 83/01/12 1100 20

7934 MILLER CLERK 7782 82/01/23 1300 10

DEPTNO DNAME LOC

----- ------------ --------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

---