CAPITOLUL 7
EXTRAGEREA DATELOR DIN MAI MULT DE O TABELA
Join
Join-ul este folosit cind o cerere SQL necesita date din mai multe
tabele din baza de date.
Liniile dintr-o tabela pot fi unite cu liniile din alta tabela in
functie de valorile comune existente in coloanele corespunzatoare.
Sint doua tipuri de conditie de join :
1. Equi-join
2. Non-equi-join
Equi-join
Pentru a determina angajatii din departamente, vom compara valorile
din coloana DEPTNO a angajatului cu aceleasi valori din DEPTNO din
tabela DEPT. Relatia dintre tabela EMP si DEPT este un equi-join, in
care valorile din coloana DEPTNO din ambele tabele sint egale.
(Operatorul de comparatie folosit este =.)
O conditie de join este specificata in clauza WHERE:
SELECT column(s)
FROM tables
WHERE join condition is ...
Pentru a face join pe cele doua tabele EMP si DEPT, introducem:
SELECT ENAME, JOB, DNAME
FROM EMP, DEPT
WHERE EMP>DEPTNO + DEPT>DEPTNO;
ENAME JOB DNAME
------- ------ -------
CLARK MANAGER ACCOUNTING
MILLER CLERK ACCOUNTING
KING PRESIDENT ACCOUNTING
SMITH CLERK RESEARCH
SCOTT ANALYST RESEARCH
JONES MANAGER RESEARCH
ADAMS CLERK RESEARCH
FORD ANALYST RESEARCH
ALLEN SALESMAN SALES
BLAKE MANAGER SALES
TURNER SALESMAN SALES
JAMES CLERK SALES
MARTIN SALESMAN SALES
WARD SALESMAN SALES
Vom observa ca acum fiecare angajat are listat numele departamentului
lui.
Liniile din EMP sint combinate cu liniile din DEPT si sint intoarse
doar liniile pentru care valorile EMP.DEPTNO si DEPT.DEPTNO sint eale.
Observati ca, conditia de join specifica numele coloanei precedat de
numele tabelei.Aceasta este o necesitate cind numele coloanelor sint
aceleasi in ambele tabele.Este necesar sa specificam exact ce coloane
sint referite.
Aceasta necesitate este de asemenea aplicata coloanelor care pot fi
ambigue in clauzele SELECT sau ORDER BY.
Pentru a recunoaste diferentele dintre coloana DEPTNO din EMP si
coloana DEPTNO din DEPT, introducem:
SELECT DEPT.DEPTNO, ENAME, JOB, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY DEPT.DEPTNO;
DEPTNO ENAME JOB DNAME
-------- ------- ------ -------
10 CLARK MANAGER ACCOUNTING
10 MILLER CLERK ACCOUNTING
10 KING PRESIDENT ACCOUNTING
20 SMITH CLERK RESEARCH
20 SCOTT ANALYST RESEARCH
20 JONES MANAGER RESEARCH
20 ADAMS CLERK RESEARCH
20 FORD ANALYST RESEARCH
30 ALLEN SALESMAN SALES
30 BLAKE MANAGER SALES
30 TURNER SALESMAN SALES
30 JAMES CLERK SALES
30 MARTIN SALESMAN SALES
30 WARD SALESMAN SALES
Observati ca fiecarui numar de departament din tabela DEPT i se face
join pentru a se potrivi cu numerele de departament din tabela EMP.De
exemplu, trei angajati lucreaza in departamentul 10 - Accounting -
deci exista trei potri- viri.Prin urmare ACCOUNTING este afisat pentru
fiecare angajat din acel depar- tament.
Folosirea alias-urilor de tabela
Poate fi foarte plictisitor sa tiparesti numele tabelelor
repetat.Etichete temporare (sau alias-uri) pot fi folosite in clauza
FROM.Aceste nume temporare sint valide doar in instructiunea SELECT
curenta.Alias-urile de tabele trebuie de asemenea sa fie specificate
in clauza SELECT.Aceasta creste efectiv viteza cererii, in care
contextul contine informatii foarte exacte.
Alias-urile de tabela sint folosite in urmatorul context:
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO;
Alias-urile de tabele pot fi de lungime de maxim 30 de caractere, dar
mai scurte sint mai bune.De asemenea incercati sa le faceti cit mai
explicite.
Daca un alias de tabela este folosit pentru un nume particular de
tabela in clauza FROM, atunci acel alias trebuie sa fie substituit
pentru numele tabelei in contextul din SELECT.
Produs
-----------------------------------------------------------------------
| EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO |
-----------------------------------------------------------------------
|=======================================================================|
-----------------------------------------------------------------------
| 7788 SCOTT ANALYST 7566 05-MAR-84 3000 20 |---
----------------------------------------------------------------------- |
|=======================================================================| |
----------------------------------------------------------------------- |
|
|
|
|
---------------------------------------------------------------------|
|
|
|
|
| -------------------------------
| | DEPTNO DNAME LOC |
| -------------------------------
|--------| 10 ACCOUNTING NEW YORK |
|--------| 20 RESEARCH DALLAS |
|--------| 30 SALES CHICAO |
|--------| 40 OPERATIONS BOSTON |
-------------------------------
In absenta unei conditii WHERE, fiecare linie din EMP este unita in
ordine cu fiecare linie din DEPT.
Se vor afisa 53 de linii.
Produs
Cind o conditie de join este invalida sau este omisa, rezultatul este
omis si toate combinatiile de linii vor fi listate.
Un produs tinde sa genereze un numar mare de linii si rezultatul sau
este rar folosit.Trebuie intotdeauna inclusa o conditie de join intr-o
clauza WHERE, in afara de cazul in care este necesara combinarea
tuturor liniilor din toate tabelele.
Non-Equi-Join
Relatia dintre tabelele EMP si SALGRADE este un non-equi-join, in care
nici o coloana din EMP nu corespunde direct cu o coloana din
SALGRADE.Relatia este obtinuta folosind un operator, altul decit
operatorul de egalitate (=).Pentru a evalua gradatia unui anajat,
salariul lui trebuie sa fie intre salariul minim si salariul maxim.
Operatorul BETWEEN este folosit pentru a construi conditia,
introducem:
SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
ENAME SAL GRADE
--------- ------- ---------
SMITH 800.00 1
ADAMS 1,100.00 1
JAMES 950.00 1
WARD 1,250.00 2
MARTIN 1,250.00 2
MILLER 1,300.00 2
ALLEN 1,600.00 3
TURNER 1,500.00 3
JONES 2,975.00 4
BLAKE 2,850.00 4
CLARK 2,450.00 4
SCOTT 3,000.00 4
FORD 3,000.00 4
KING 5,000.00 5
Alti operatori cum ar fi = pot fi folositi, oricum BETWEEN este cel
mai simplu.Nu uitati sa specificati mai intii valoarea minima si
ultima va- loarea maxima cind folositi BETWEEN.Din nou sint folosite
alias-uri de tabele, nu din cauza posibilelor ambiguitati, dar din
motive de performanta.
Reguli pentru join-ul tabelelor
Pentru a face join pe cele trei tabele este necesar sa construim doua
condi- tii de join.Pentru a face join pe patru tabele sint necesare
minim trei condi- tii de join.
O regula simpla este:
numarul minim de conditii de join = numarul de tabele - 1
Aceasta regula nu poate fi aplicata daca tabela are o cheie primara,
care identifica in mod unic fiecare linie (cheile primare sint
explicate mai tirziu in manual).
Sintaxa
SELECT [DISTINCT] {[tabela].* | expresie [alias], ...}
FROM tabela [alias], ...
WHERE [conditie de join] ...
AND [conditie de linie] ...
OR [alta conditie de linie]
GROUP BY {expresie | coloana}
HAVING {conditie de grup}
ORDER BY {expresie | coloana} [ASC | DESC]
Observatii
* Se pot specifica conditii de join impreuna cu alte conditii (non
join);
* De asemenea trebuie sa fiti atenti la precedenta operatorilor cind
folo- siti predicatul OR.
Exercitii - Join-uri simple
Aceste exxercitii au intentia de a va capata experienta practica in
extrage- rea datelor din mai mult decit o tabela si includ teme
discutate in capitolele anterioare.
Tema
1. Afisati numele tuturor angajatilor si numele departamentului lor,
in ordi- nea numelui departamentelor.
ENAME DNAME
-------- ---------
CLARK ACCOUNTING
MILLER ACCOUNTING
KING ACCOUNTING
SMITH RESEARCH
SCOTT RESEARCH
JONES RESEARCH
ADAMS RESEARCH
FORD RESEARCH
ALLEN SALES
BLAKE SALES
TURNER SALES
JAMES SALES
MARTIN SALES
WARD SALES
Vor fi selectate 14 inregistrari.
2. Afisati numele tuturor angajatilor, numarul si numele
departamentului.
ENAME DEPTNO DNAME
-------- --------- ---------
CLARK 10 ACCOUNTING
MILLER 10 ACCOUNTING
KING 10 ACCOUNTING
SMITH 20 RESEARCH
SCOTT 20 RESEARCH
JONES 20 RESEARCH
ADAMS 20 RESEARCH
FORD 20 RESEARCH
ALLEN 30 SALES
BLAKE 30 SALES
TURNER 30 SALES
JAMES 30 SALES
MARTIN 30 SALES
WARD 30 SALES
Vor fi selectate 14 inregistrari.
3. Afisati numele, localitatea si departamentul angajatilor al caror
salariu lunar este mai mare ca 1500.
ENAME LOCATION DNAME
-------- ---------- ---------
CLARK NEW YORK ACCOUNTING
KING NEW YORK ACCOUNTING
JONES DALLAS RESEARCH
FORD DALLAS RESEARCH
SCOTT DALLAS RESEARCH
ALLEN CHICAGO SALES
BLAKE CHICAGO SALES
Vor fi selectate 7 inregistrari.
4. Afisati lista salariilor, gradatiilor angajatilor.
ENAME JOB SAL GRADE
---------- ------- ------- ----------
SMITH CLERK 800.00 1
ADAMS CLERK 1,100.00 1
JAMES CLERK 950.00 1
WARD SALESMAN 1,250.00 2
MARTIN SALESMAN 1,250.00 2
MILLER CLERK 1,300.00 2
ALLEN SALESMAN 1,600.00 3
TURNER SALESMAN 1,500.00 3
JONES MANAGER 2,975.00 4
BLAKE MANAGER 2,850.00 4
CLARK MANAGER 2,450.00 4
SCOTT ANALYST 3,000.00 4
FORD ANALYST 3,000.00 4
KING PRESIDENT 5,000.00 5
5. Listati doar angajatii cu gradatia 3.
ENAME JOB SAL GRADE
---------- ------- ------- ----------
ALLEN SALESMAN 1,600.00 3
TURNER SALESMAN 1,500.00 3
6. Listati toti angajatii din Dallas.
ENAME SAL LOCATION
---------- ------- ----------
SMITH 800.00 DALLAS
SCOTT 3,000.00 DALLAS
JONES 2,975.00 DALLAS
ADAMS 1,100.00 DALLAS
FORD 3,000.00 DALLAS
Alte exercitii daca aveti timp:
7. Afisati numele angajatilor, functia, salariul, gradatia si numele
departa- mentului pentru toti angajatii din companie in afara de
functionari.Sortati dupa salariu, afisind mai intii salariul cel
mai mare.
ENAME JOB SAL GRADE DNAME
-------- --------- -------- --------- ---------
KING PRESIDENT 5,000.00 5 ACCOUNTING
FORD ANALYST 3,000.00 4 RESEARCH
SCOTT ANALYST 3,000.00 4 RESEARCH
JONES MANAGER 2,975.00 4 RESEARCH
BLAKE MANAGER 2,850.00 4 SALES
CLARK MANAGER 2,450.00 4 ACCOUNTING
ALLEN SALESMAN 1,600.00 3 SALES
TURNER SALESMAN 1,500.00 3 SALES
MARTIN SALESMAN 1,250.00 2 SALES
WARD SALESMAN 1,250.00 2 SALES
Vor fi selectate 10 inregistrari.
8. Afisati urmatoarele detalii pentru angajatii care cistiga 36000$
pe an sau care sint functionari.
ENAME JOB ANNUAL_SAL DEPTNO DNAME GRADE
--------- ------- -------------- --------- --------- --------
FORD ANALYST 36000 20 RESEARCH 4
SCOTT ANALYST 36000 20 RESEARCH 4
MILLER CLERK 15600 10 ACCOUNTING 2
JAMES CLERK 11400 30 SALES 1
ADAMS CLERK 13200 20 RESEARCH 1
SMITH CLERK 9600 20 RESEARCH 1
Vor fi selectate 6 inregistrari.
Solutii
1.
SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
2.
SELECT ENAME, E.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
3.
SELECT ENAME, LOC LOCATION, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND SAL > 1500;
4.
SELECT ENAME, JOB, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
5.
SELECT ENAME, JOB, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
AND GRADE = 3;
6.
SELECT ENAME, SAL, LOC LOCATION
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND LOC = 'DALLAS';
7.
SELECT ENAME, JOB, SAL, GRADE, DNAME
FROM EMP, SALGRADE, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND SAL BETWEEN LOSAL AND HISAL
AND JOB != 'CLERK'
ORDER BY SAL DESC;
8.
SELECT ENAME, JOB, SAL * 12 ANNUAL_SAL, D.DEPTNO, DNAME, GRADE
FROM EMP E, SALGRADE, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND SAL BETWEEN LOSAL AND HISAL
AND (SAL * 12 + NVL(COMM, 0) = 3600 OR E.JOB = 'CLERK')
ORDER BY E.JOB;
BogSoft 1999