Ćwiczenie 4

Wybieranie danych z wielu tabel

Złączenie równościowe

Chcemy uzyskać następującą informację: dla każdego pracownika znaleźć jego nazwisko, zawód (tabela EMP) i nazwę departamentu, w którym pracuje (tabela DEPT).

W obu tych tabelach występuje kolumna DEPTNO, określająca numer departamentu i zawierająca takie same wartości, mówiące o związku pomiędzy tymi tabelami. Związek oparty o relację równości nazywamy związkiem równościowym (equi-join).

Warunek równości zapisuje się jak zwykły warunek, z tym, że wartości do porównania są pobierane z różnych tabel.

Aby powiązać ze sobą logicznie tabele EMP i DEPT, napiszemy:

SELECT ENAME, JOB, DNAMEFROM EMP, DEPTWHERE EMP.DEPTNO=DEPT.DEPTNO

Gdy warunek łączenia jest niepoprawny lub zupełnie pominięty, wynikiem będzie
Produkt Kartezjański, w którym wyświetlane są wszystkie kombinacje wierszy.

Złączenia nierównościowe

Złączenia nierównościowe (non-equi-join) nie są oparte o relację równości. Związek pomiędzy wierszami dwóch tabel określa się poprzez zastosowanie innego operatora niż równość.

Na przykład związek pomiędzy tabelami EMP i SALGRADE jest oparty na następujących zasadach: określenie stawki zaszeregowania pracownika polega na wskazaniu do jakiego przedziału (LOSAL, HISAL) należą jego zarobki.

Do utworzenia tego warunku zastosujemy operator BETWEEN…AND.

SELECT ENAME, SAL, S.GRADEFROM EMP E, SALGRADE SWHERE SAL BETWEEN LOSAL AND HISAL;

Aliasy

Aliasy definiuje się w klauzuli FROM. Obowiązują one jedynie w zapytaniu, w którym są zdefiniowane. Należy używać aliasów także w klauzuli SELECT, mimo iż tekstowo występuje ona wcześniej niż klauzula FROM.

SELECT D.DEPTNO, E.ENAME, D.DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO

ORDER BY D.DEPTNO;

Połączenie tabeli samej ze sobą

Dzięki aliasom możemy połączyć tabelę samą ze sobą np ... FROM EMP A, EMP B .. ..

Złączenia zewnętrzne

Podczas łączenia tabel wiersz w tabeli nie mający swojego odpowiednika w drugiej tabeli nie zostanie wybrany. Np. w przykładzie łączenia tabeli EMP i DEPT poprzez kolumnę DEPNO nie został wybrany wiersz tabeli DEPT, gdzie DEPTNO=40 (departament OPERATIONS), ponieważ nikt nie pracuje w tym departamencie.

Aby został również wybrany departament 40 należy użyć złączenia zewnętrznego (outer-join), gdzie wiersz w tabeli DEPT, który nie ma swojego odpowiednika w tabeli EMP zostanie połączony z wierszem tabeli EMP zawierającym same wartości NULL (mimo, że wiersz taki w rzeczywistości nie istnieje).

Złączenie zewnętrzne oznaczamy znakiem (+) po tej stronie równości, która dotyczy tabeli z niepełną informacją.

SELECT ENAME, D.DEPTNO, DNAMEFROM EMP E, DEPT DWHERE E.DEPTNO(+)=D.DEPTNO;

DEPT		EMP
DEPTNO		EMPNO
DNAME		ENAME
LOC		JOB
		MGR
		HIREDATE
		SAL
		COMM
		DEPTNO

Zadania do zapytań SQL:

1. Wybierz nazwiska oraz nazwy departamentów wszystkich pracowników w kolejności alfabetycznej nazw departamentów

select d.deptno, e.ename, d.dname from emp e, dept d where e.deptno(+)=d.deptno order by d.deptno;

select ename, job, dname from emp, dept where emp.deptno=dept.deptno;

2. Wybierz numer (DEPTNO) i nazwę (DNAME) departamentu pracownika o nazwisku FORD.
   (dodatkowy warunek AND w klauzuli WHERE)

select d.deptno, d.dname from emp e, dept d where e.deptno(+)=d.deptno and e.ename LIKE 'FORD';

3. Dla pracowników o miesięcznej pensji 1500 podaj ich nazwiska, miejsca usytuowania ich departamentów oraz nazwy tych departamentów.

select dname, ename, loc from emp, dept where emp.deptno=dept.deptno and sal='1500';

4. Wybierz pracowników zatrudnionych w Dallas

select * from emp, dept where emp.deptno=dept.deptno and loc='DALLAS';

5. Obok numeru i nazwiska pracownika podaj numer i nazwisko jego kierownika
   (łączenie tabeli z tą samą tabelą)

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;

select a.empno, a.ename from emp a, emp b where a.ename=b.ename;

6. Zmodyfikuj rozwiązanie poprzedniego zadania w ten sposób, aby wyświetlić także informacje o pracowniku KING, który nie posiada szefa

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 (+);

7. Znajdź nazwiska szefa każdego pracownika i utwórz kolumnę z tekstem
   'nazwisko pracownika pracuje dla nazwisko szefa'

select e.ename `nazwisko pracownika, || ' pracuje dla ' ||, ek.ename nazwisko szefa' from emp e, emp ek where e.mgr=ek.empno;

select e.ename `nazwisko_pracownika || ' pracuje dla ' || ek.ename nazwisko szefa' from emp e, emp ek where e.mgr=ek.empno;

? Utwórz listę pracowników i zaszereguj ich do klas zarobkowych

select ename, sal, s.grade from emp e, salgrade s where sal between LOSAL and HISAL;

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

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

9. Utwórz listę pracowników z zaszeregowaniem ich do klas zarobkowych.

select ename, grade from emp, salgrade where sal between losal and hisal;

10. Wybierz informację o pracownikach, których zarobki odpowiadają klasie 3

select ename, grade from emp, salgrade where sal between losal and hisal and grade='3';

Wybierz nazwiska i pensje wszystkich zarabiających ponizej średniej

select ename, sal from emp where sal < ANY (select AVG(SAL) from emp) order by sal;

Wybierz nazwiska i pensje wszystkich zarabiających najnizszą pensję

select ename, sal from emp where sal = ANY (select MIN(SAL) from emp) order by sal;

Wybierz urzędników (CLERK) mających pensję poniżej średniej dla pracujących na stanowisku Salesman

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

Wybierz zawody których średnia apensja jest wyzsza niż średnia płaca w zawodzie Manager

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

Wybierz wszystkich pracowników, którzy zarabiają więcej niż jakikolwiek SALESMAN

select job, ename from emp where sal>ANY (select MAX(sal) from emp where job='SALESMAN') order by job;

Wybierz nazwiska i daty zatrudnienia kazdego pracownika zatrudnionego w tym samym departamencie co pracownik BLAKE ale bez pracownika KING

select ename, hiredate from emp where mgr IS NOT NULL and deptno=(select deptno from emp where ename='BLAKE' );

Wybierz nazwiska i daty zatrudnienia pracownika który podlega pracownikowi KING

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

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');

Wyświetl numer departamentu nazwisko i stanowisko wszystkich pracowników w departamencie RESERCH

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

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;

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;

SQL Ćwiczenia Strona 3

---