Baze de date-Anul 2

Laborator 3 SQL

Interogări multi-relaţie. Operaţia de join. Operatori pe mulţimi.

Subcereri necorelate.

I. [Obiective]
În acest laborator vom continua lucrul cu interogări multi-relaţie (acestea sunt
cele care regăsesc date din mai multe tabele). Am introdus deja diferite tipuri de join.
Vom relua aceste operaţii, vom analiza şi o altă metodă de implementare a lor şi de
asemenea vom utiliza operatori pe mulţimi şi subcereri necorelate (fără
sincronizare).
Foarte utile în rezolvarea exerciţiilor propuse vor fi funcţiile SQL, prezentate în
laboratorul 2.

II. [Join]
Am implementat deja operaţia de join (compunere a tabelelor) în cadrul unor
exemple relative la modelul luat în considerare (HR).
Join-ul este operaţia de regăsire a datelor din două sau mai multe tabele, pe
baza valorilor comune ale unor coloane. De obicei, aceste coloane reprezintă cheia
primară, respectiv cheia externă a tabelelor. Reamintim că pentru a realiza un join între
n tabele, va fi nevoie de cel puţin n – 1 condiţii de join.
Tipuri de join :
 Inner join (equijoin, join simplu) – corespunde situaţiei în care valorile de pe
coloanele ce apar în condiţia de join trebuie să fie egale.
 Nonequijoin - condiţia de join conţine alţi operatori decât operatorul egalitate.
 Left | Right Outer join – un outer join este utilizat pentru a obţine în rezultat şi
înregistrările care nu satisfac condiţia de join. Operatorul pentru outer join este
semnul plus inclus între paranteze (+), care se plasează în acea parte a condiţiei
de join care este deficientă în informaţie. Efectul acestui operator este de a uni
liniile tabelului care nu este deficient în informaţie şi cărora nu le corespunde nici
o linie în celălalt tabel cu o linie cu valori null. Operatorul (+) poate fi plasat în
orice parte a condiţiei de join, dar nu în ambele părţi.
Obs: O condiţie care presupune un outer join nu poate utiliza operatorul IN şi nu poate fi
legată de altă condiţie prin operatorul OR.
 Full outer join – left outer join + right outer join
 Self join – join-ul unui tabel cu el însuşi. În ce situaţie concretă (relativ la modelul
nostru) apărea această operaţie?

Join în standardul SQL3 (SQL:1999):

Pentru join, sistemul Oracle9i oferă şi o sintaxă specifică, în conformitate cu standardul
SQL3 (SQL:1999). Această sintaxă nu aduce beneficii în privinţa performanţei faţă de
join-urile care folosesc sintaxa specifică Oracle. Tipurile de join conforme cu SQL3 sunt
definite prin cuvintele cheie CROSS JOIN (pentru produs cartezian), NATURAL JOIN,
FULL OUTER JOIN, clauzele USING şi ON.
Sintaxa corespunzătoare standardului SQL3 este următoarea:
SELECT tabel_1.nume_coloană, tabel_2.nume_coloană
FROM tabel_1
[CROSS JOIN tabel_2]
 | [NATURAL JOIN tabel_2]
| [JOIN tabel_2 USING (nume_coloană) ]
| [JOIN tabel_2 ON (tabel_1.nume_coloană = tabel_2.nume_coloană) ]
| [LEFT | RIGHT | FULL OUTER JOIN tabel_2
ON (tabel_1.nume_coloană = tabel_2.nume_coloană) ];
 NATURAL JOIN presupune existenţa unor coloane având acelaşi nume în ambele
tabele. Clauza determină selectarea liniilor din cele două tabele, care au valori egale
în aceste coloane. Dacă tipurile de date ale coloanelor cu nume identice sunt diferite,
va fi returnată o eroare.
În versiunile precedente ale lui Oracle Server nu era permisă realizarea unui join
fără a specifica explicit coloanele care intervin în operaţie. Oracle9i oferă posibilitatea
completării automate a operaţiei de join. Coloanele având acelaşi nume în cele două
tabele trebuie să nu fie precedate de numele sau alias-ul tabelului corespunzător.
 JOIN tabel_2 USING nume_coloană efectuează un equijoin pe baza coloanei cu
numele specificat în sintaxă. Această clauză este utilă dacă există coloane având
acelaşi nume, dar tipuri de date diferite. Coloanele referite în clauza USING trebuie
să nu conţină calificatori (să nu fie precedate de nume de tabele sau alias-uri) în nici
o apariţie a lor în instrucţiunea SQL. Clauzele NATURAL JOIN şi USING nu pot
coexista în aceeaşi instrucţiune SQL.
 JOIN tabel_2 ON tabel_1.nume_coloană = tabel_2.nume_coloană efectuează un
equijoin pe baza condiţiei exprimate în clauza ON. Această clauză permite
specificarea separată a condiţiilor de join, respectiv a celor de căutare sau filtrare
(din clauza WHERE).
 LEFT, RIGHT şi FULL OUTER JOIN tabel_2 ON (tabel_1.nume_coloană =
tabel_2.nume_coloană) efectuează outer join la stânga, dreapta, respectiv în ambele
părţi pe baza condiţiei exprimate în clauza ON.
Un join care returnează rezultatele unui inner join, dar şi cele ale outer join-urilor la
stânga şi la dreapta se numeşte full outer join.

III. [Operatori pe mulţimi]

Operatorii pe mulţimi combină rezultatele obţinute din două sau mai multe
interogări. Cererile care conţin operatori pe mulţimi se numesc cereri compuse. Există
patru operatori pe mulţimi: UNION, UNION ALL, INTERSECT şi MINUS.
Toţi operatorii pe mulţimi au aceeaşi precedenţă. Dacă o instrucţiune SQL
conţine mai mulţi operatori pe mulţimi, server-ul Oracle evaluează cererea de la stânga
la dreapta (sau de sus în jos). Pentru a schimba această ordine de evaluare, se pot
utiliza paranteze.
 Operatorul UNION returnează toate liniile selectate de două cereri, eliminând
duplicatele. Acest operator nu ignoră valorile null şi are precedenţă mai mică
decât operatorul IN.
 Operatorul UNION ALL returnează toate liniile selectate de două cereri, fără a
elimina duplicatele. Precizările făcute asupra operatorului UNION sunt valabile şi
în cazul operatorului UNION ALL. În cererile asupra cărora se aplică UNION ALL
nu poate fi utilizat cuvântul cheie DISTINCT.
 Operatorul INTERSECT returnează toate liniile comune cererilor asupra cărora
se aplică. Acest operator nu ignoră valorile null.
 Operatorul MINUS determină liniile returnate de prima cerere care nu apar în
 rezultatul celei de-a doua cereri. Pentru ca operatorul MINUS să funcţioneze,
este necesar ca toate coloanele din clauza WHERE să se afle şi în clauza
SELECT.
Observaţii:
 În mod implicit, pentru toţi operatorii cu excepţia lui UNION ALL, rezultatul este
ordonat crescător după valorile primei coloane din clauza SELECT.
 Pentru o cerere care utilizează operatori pe mulţimi, cu excepţia lui UNION ALL,
server-ul Oracle elimină liniile duplicat.
 În instrucţiunile SELECT asupra cărora se aplică operatori pe mulţimi, coloanele
selectate trebuie să corespundă ca număr şi tip de date. Nu este necesar ca numele
coloanelor să fie identice. Numele coloanelor din rezultat sunt determinate de numele
care apar în clauza SELECT a primei cereri.

IV. [Subcereri]
O subcerere este o comandă SELECT încapsulată într-o clauză a altei
instrucţiuni SQL, numită instrucţiune „părinte“. Utilizând subcereri, se pot construi
interogări complexe pe baza unor instrucţiuni simple. Subcererile mai sunt numite
instrucţiuni SELECT imbricate sau interioare.
Subcererea returnează o valoare care este utilizată de către instrucţiunea
„părinte“. Utilizarea unei subcereri este echivalentă cu efectuarea a două cereri
secvenţiale şi utilizarea rezultatului cererii interne ca valoare de căutare în cererea
externă (principală).
Subcererile sunt de 2 feluri :
 necorelate, de forma :
SELECT lista_select
FROM nume_tabel
WHERE expresie operator (SELECT lista_select
FROM nume_tabel);
 cererea internă este executată prima şi determină o valoare (sau o mulţime
de valori);
 cererea externă se execută o singură dată, utilizând valorile returnate de
cererea internă.
 corelate, de forma :
SELECT nume_coloană_1[, nume_coloană_2 …]
FROM nume_tabel_1 extern
WHERE expresie operator
(SELECT nume_coloană_1 [, nume_coloană_2 …]
FROM nume_tabel_2
WHERE expresie_1 = extern.expresie_2);
 cererea externă determină o linie candidat;
 cererea internă este executată utilizând valoarea liniei candidat;
 valorile rezultate din cererea internă sunt utilizate pentru calificarea sau
descalificarea liniei candidat;
 paşii precedenţi se repetă până când nu mai există linii candidat.
Obs: operator poate fi:
 single-row operator (>, =, >=, <, <>, <=), care poate fi utilizat dacă
 subcererea returnează o singură linie;
 multiple-row operator (IN, ANY, ALL), care poate fi folosit dacă subcererea
returnează mai mult de o linie.
Operatorul NOT poate fi utilizat în combinaţie cu IN, ANY şi ALL.

V. [Exerciţii - join]
1. Scrieti o cerere pentru a se afisa numele, luna (în litere) şi anul angajării pentru toti
salariatii din acelasi departament cu Gates, al căror nume conţine litera “a”. Se va
exclude Gates. Se vor da 2 soluţii pentru determinarea apariţiei literei “A” în nume. De
asemenea, pentru una din metode se va da şi varianta join-ului conform standardului
SQL99.
Soluţia 1 (cu operatorul LIKE):
SELECT e.last_name, TO_CHAR(e.hire_date, ‘month’) Luna,
TO_CHAR(e.hire_date, ‘yyyy’) “An angajare”
FROM employees e, employees g
WHERE e.department_id=g.department_id
AND LOWER(g.last_name)=’gates’
AND LOWER(e.last_name) LIKE ’%a%’
AND e.employee_id != g.employee_id ;
Soluţia 2 (cu funcţia INSTR):
SELECT e.last_name, TO_CHAR(e.hire_date, ‘month’) Luna,
TO_CHAR(e.hire_date, ‘yyyy’) “An angajare”
FROM employees e, employees g
WHERE e.department_id=g.department_id
AND LOWER(g.last_name)=’gates’
AND INSTR(LOWER(e.last_name), ’a’)!=0
AND e.employee_id != g.employee_id ;
Join având sintaxa SQL99:
SELECT e.last_name, TO_CHAR(e.hire_date, ‘month’) Luna,
TO_CHAR(e.hire_date, ‘yyyy’) “An angajare”
FROM employees e JOIN employees g ON e.department_id=g.department_id
WHERE LOWER(g.last_name)=’gates’
AND INSTR(LOWER(e.last_name), ’a’) != 0
AND e.employee_id != g.employee_id ;
2. Sa se afiseze codul şi numele angajaţilor care lucrează în acelasi departament cu
cel puţin un angajat al cărui nume conţine litera “t”. Se vor afişa, de asemenea, codul şi
numele departamentului respectiv. Rezultatul va fi ordonat alfabetic după nume. Salvaţi
cererea într-un fişier [Link].
SELECT DISTINCT e.employee_id Cod, e.last_name Nume, d.department_id Cod_dep,
d.department_name “Nume departament”
FROM employees e JOIN employees t ON e.department_id=t.department_id
JOIN departments d ON e.department_id = d.department_id
WHERE LOWER(t.last_name) LIKE ‘%t%’
ORDER BY 2;
! Daţi şi soluţia care utilizează sintaxa specifică Oracle pentru join.
3. Sa se afiseze numele, salariul, titlul job-ului, oraşul şi ţara în care lucrează angajatii
condusi direct de King.
SELECT e.last_name, [Link], j.job_title, [Link], c.country_name
FROM jobs j
JOIN employees e ON j.job_id = e.job_id
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id=c.country_id
, employees k
WHERE LOWER(k.last_name)=’king’ AND e.manager_id=k.employee_id;
! Daţi şi alte metode de rezolvare a acestui exerciţiu.
4. Daţi comenzile SQL*Plus următoare:
SET LINESIZE 120
SET PAGESIZE 20
după care rulaţi comanda de la exerciţiul precedent (e suficient “/” pentru rularea buffer-
ului). Ce observaţi? Ce efect au comenzile SET LINESIZE n, SET PAGESIZE n?
5. Sa se afiseze codul departamentului, numele departamentului, numele si job-ul
tuturor angajatilor din departamentele al căror nume conţine şirul ‘ti’. De asemenea, se
va lista salariul angajaţilor, în formatul “$99,999.00”. Rezultatul se va ordona alfabetic
după numele departamentului, şi în cadrul acestuia, după numele angajaţilor.
SELECT d.department_id, d.department_name, e.last_name, j.job_title,
TO_CHAR([Link],’$99,999.00’) Salariu
FROM departments d, employees e, jobs j
WHERE d.department_id = e.department_id
AND j.job_id = e.job_id
AND INSTR(LOWER(d.department_name),’ti’)!=0
ORDER BY 2, 3;
6. Sa se afiseze numele angajatilor, numarul departamentului, numele departamentului,
oraşul si job-ul tuturor salariatilor al caror departament este localizat in Oxford.
SELECT e.last_name, e.department_id, d.department_name, [Link], j.job_title
FROM employees e, departments d, jobs j, locations l
WHERE e.department_id=d.department_id
AND e.job_id = j.job_id
AND d.location_id=l.location_id
AND LOWER(city)=’oxford’;
7. Sa se modifice fisierul [Link] pentru a afisa codul, numele si salariul tuturor
angajatilor care castiga mai mult decat salariul mediu pentru job-ul corespunzător si
lucreaza intr-un departament cu cel putin unul din angajatii al caror nume contine litera
“t”. Salvati ca [Link]. Executati cererea.
8. Să se afişeze numele salariaţilor şi numele departamentelor în care lucrează. Se vor
afişa şi salariaţii care nu au asociat un departament. (right outer join, 2 variante).
SELECT last_name, department_name
FROM departments d, employees e
WHERE d.department_id(+) = e.department_id;
SELECT last_name, department_name
FROM departments d RIGHT OUTER JOIN employees e
ON e.department_id = d.department_id;
9. Să se afişeze numele departamentelor şi numele salariaţilor care lucrează în ele. Se
vor afişa şi departamentele care nu au salariaţi. (left outer join, 2 variante)
SELECT department_name, last_name
FROM departments d, employees e
WHERE d.department_id = e.department_id(+);
SELECT last_name, department_name
FROM departments d LEFT OUTER JOIN employees e
ON e.department_id = d.department_id;
10. Cum se poate implementa full outer join?
Obs: Full outer join se poate realiza fie prin reuniunea rezultatelor lui right outer join şi
left outer join, fie utilizând sintaxa specifică standardului SQL99.
Metoda 1:
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
Metoda 2:
SELECT DISTINCT last_name, department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

VI. [Exerciţii - operatori pe mulţimi]

11. Se cer codurile departamentelor al căror nume conţine şirul “re” sau în care
lucrează angajaţi având codul job-ului “SA_REP”.
SELECT department_id “Cod departament”
FROM employees
WHERE UPPER(job_id)=’SA_REP’
UNION
SELECT department_id
FROM departments
WHERE LOWER(department_name) LIKE ‘%re%’;
Cum este ordonat rezultatul?
12. Ce se întâmplă dacă înlocuim UNION cu UNION ALL în comanda precedentă?
13. Sa se obtina codurile departamentelor in care nu lucreaza nimeni (nu este introdus
nici un salariat in tabelul employees). Se cer două soluţii.
Obs: Operatorii pe mulţimi pot fi utilizaţi în subcereri. Coloanele care apar în clauza
WHERE a interogării trebuie să corespundă, ca număr şi tip de date, celor din clauza
SELECT a subcererii.
SELECT department_id “Cod departament”
FROM departments
MINUS
SELECT department_id
FROM employees;
SELECT department_id
FROM departments
WHERE department_id NOT IN (SELECT DISTINCT NVL(department_id,0)
FROM employees);
? În a doua variantă, de ce este nevoie de utilizarea funcţiei NVL?
14. Se cer codurile departamentelor al căror nume conţine şirul “re” şi în care lucrează
angajaţi având codul job-ului “HR_REP”.
SELECT department_id “Cod departament”
FROM employees
WHERE UPPER(job_id)=’HR_REP’
INTERSECT
SELECT department_id
FROM departments
WHERE LOWER(department_name) LIKE ‘%re%’;
15. Să se determine codul angajaţilor, codul job-urilor şi numele celor al căror salariu
este mai mare decât 3000 sau este egal cu media dintre salariul minim şi cel maxim
pentru job-ul respectiv.
SELECT employee_id, job_id, last_name
FROM employees
WHERE (job_id, salary)
IN (SELECT job_id, salary
FROM employees
WHERE salary > 3000
UNION
SELECT job_id, (min_salary+max_salary)/2
FROM jobs);

VII. [Exercitii - subcereri necorelate]

16. Folosind subcereri, să se afişeze numele şi data angajării pentru salariaţii care au
fost angajaţi după Gates.
SELECT last_name, hire_date
FROM employees
WHERE hire_date > (SELECT hire_date
FROM employees
WHERE INITCAP(last_name)=’Gates’);
17. Folosind subcereri, scrieţi o cerere pentru a afişa numele şi salariul pentru toţi
colegii (din acelaşi departament) lui Gates. Se va exclude Gates.
SELECT last_name, salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE LOWER(last_name)=’gates’)
AND LOWER(last_name) <> ’gates’;
? Se putea pune ”=” în loc de ”IN”? In care caz nu se poate face această înlocuire?
18. Folosind subcereri, să se afişeze numele şi salariul angajaţilor conduşi direct de
preşedintele companiei (acesta este considerat angajatul care nu are manager).
SELECT last_name, salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE manager_id IS NULL);
19. Scrieti o cerere pentru a afişa numele, codul departamentului si salariul angajatilor
al caror număr de departament si salariu coincid cu numarul departamentului si salariul
unui angajat care castiga comision.
SELECT last_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, salary
FROM employees
WHERE commission_pct IS NOT NULL);
20. Rezolvaţi problema 7 utilizând subcereri.
21. Scrieti o cerere pentru a afisa angajatii care castiga mai mult decat oricare
functionar (job-ul conţine şirul “CLERK”). Sortati rezultatele dupa salariu, in ordine
descrescatoare.
SELECT last_name, salary
FROM employees
WHERE salary > ALL(SELECT salary
FROM employees
WHERE LOWER(job_id) LIKE ‘%clerk%’)
ORDER BY 2 DESC;

? Ce rezultat este returnat dacă se înlocuieşte “ALL” cu “ANY”?

22. Scrieţi o cerere pentru a afişa numele, numele departamentului şi salariul angajaţilor
care nu câştigă comision, dar al căror şef direct coincide cu şeful unui angajat care
câştigă comision.
SELECT e.last_name, d.department_name, [Link]
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND commission_pct IS NULL
AND e.manager_id IN (SELECT manager_id
FROM employees
WHERE commission_pct IS NOT NULL);
23. Sa se afiseze numele, departamentul, salariul şi job-ul tuturor angajatilor al caror
salariu si comision coincid cu salariul si comisionul unui angajat din Oxford.
SELECT e.last_name, d.department_name, [Link], j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND ([Link], NVL(e.commission_pct, 0)) IN
(SELECT salary, NVL(commission_pct,0)
FROM employees
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id IN
(SELECT location_id
FROM locations
WHERE LOWER(city)=’oxford’)));
24. Să se afişeze numele angajaţilor, codul departamentului şi codul job-ului salariaţilor
al căror departament se află în Toronto.
SELECT last_name, job_id, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = (SELECT location_id
FROM locations
WHERE city = ‘Toronto’));