Ć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 e.ename, d.dname from emp e, dept d where e.deptno=d.deptno order by dname;

ENAME DNAME

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

KING ACCOUNTING

CLARK ACCOUNTING

MILLER ACCOUNTING

JONES RESEARCH

SCOTT RESEARCH

ADAMS RESEARCH

SMITH RESEARCH

FORD RESEARCH

BLAKE SALES

MARTIN SALES

ALLEN SALES

TURNER SALES

JAMES SALES

WARD SALES

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

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

DEPTNO DNAME

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

20 RESEARCH

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

DNAME ENAME LOC

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

SALES TURNER CHICAGO

4. Wybierz pracowników zatrudnionych w Dallas

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC

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

7566 JONES MANAGER 7839 81/04/02 2975 20 20 RESEARCH DALLAS

7902 FORD ANALYST 7566 81/12/03 3000 20 20 RESEARCH DALLAS

7369 SMITH CLERK 7902 80/12/17 800 20 20 RESEARCH DALLAS

7788 SCOTT ANALYST 7566 82/12/09 3000 20 20 RESEARCH DALLAS

7876 ADAMS CLERK 7788 83/01/12 1100 20 20 RESEARCH 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;

EMPNO ENAME K_EMPNO K_ENAME

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

7698 BLAKE 7839 KING

7782 CLARK 7839 KING

7566 JONES 7839 KING

7654 MARTIN 7698 BLAKE

7499 ALLEN 7698 BLAKE

7844 TURNER 7698 BLAKE

7900 JAMES 7698 BLAKE

7521 WARD 7698 BLAKE

7902 FORD 7566 JONES

7369 SMITH 7902 FORD

7788 SCOTT 7566 JONES

7876 ADAMS 7788 SCOTT

7934 MILLER 7782 CLARK

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

EMPNO ENAME K_EMPNO K_ENAM

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

7839 KING

7698 BLAKE 7839 KING

7782 CLARK 7839 KING

7566 JONES 7839 KING

7654 MARTIN 7698 BLAKE

7499 ALLEN 7698 BLAKE

7844 TURNER 7698 BLAKE

7900 JAMES 7698 BLAKE

7521 WARD 7698 BLAKE

7902 FORD 7566 JONES

7369 SMITH 7902 FORD

7788 SCOTT 7566 JONES

7876 ADAMS 7788 SCOTT

7934 MILLER 7782 CLARK

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 || ' pracuje dla ' || ek.ename from emp e, emp ek where e.mgr=ek.empno;

E.ENAME||'PRACUJEDLA'||EK.ENAME

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

BLAKE pracuje dla KING

CLARK pracuje dla KING

JONES pracuje dla KING

MARTIN pracuje dla BLAKE

ALLEN pracuje dla BLAKE

TURNER pracuje dla BLAKE

JAMES pracuje dla BLAKE

WARD pracuje dla BLAKE

FORD pracuje dla JONES

SMITH pracuje dla FORD

SCOTT pracuje dla JONES

ADAMS pracuje dla SCOTT

MILLER pracuje dla CLARK

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 ) order by hiredate;

ENAME HIREDATE

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

SMITH 80/12/17

ALLEN 81/02/20

CLARK 81/06/09

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

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

ENAME GRADE

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

JAMES 1

SMITH 1

ADAMS 1

MARTIN 2

WARD 2

MILLER 2

ALLEN 3

TURNER 3

BLAKE 4

CLARK 4

JONES 4

FORD 4

SCOTT 4

KING 5

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

ENAME GRADE

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

ALLEN 3

TURNER 3

SQL Ćwiczenia Strona 3

---