Laborator 08 - Subcereri SQL (I)

Obiective

  • Subcereri
  • Subcereri necorelate în clauze WHERE
  • Subcereri corelate în clauze WHERE
  • Subcereri în clauze FROM

Materiale ajutătoare

Subcereri

Subcererile sunt cereri SQL incluse în clauzele SELECT, FROM, WHERE, HAVING și ORDER BY ale altei cereri numită și cerere principală. Rezultatele întoarse de subcereri sunt folosite de o altă subcerere sau de cererea principală în situații cum ar fi:

  • crearea de tabele sau view-uri;
  • inserarea, modificarea și ștergerea înregistrărilor din tabele;
  • în furnizarea valorilor pentru condițiile puse în comenzile SELECT, UPDATE, DELETE, INSERT și CREATE TABLE.

Subcererile pot fi imbricate, adică, o subcerere poate avea propriile ei subcereri.

Dacă există o legătură de asociere sau nu între subcere și cererea exterioară, subcererile pot fi de 2 tipuri:

  • Subcereri necorelate
  • Subcereri corelate

Din punct de vedere al rolului pe care îl au într-o comandă SQL și a modului de a face construcția comenzii, subcererile pot fi împărțite în:

  • Subcereri în clauza WHERE
  • Subcereri în clauza FROM (Subcereri pe tabela temporală)
  • Subcereri în clauza HAVING
  • Subcereri în clauza SELECT
  • Subcereri în clauza ORDER BY

Subcererile pot fi împărțite pe mai multe categorii, în funcție de numărul de coloane sau linii pe care le returnează:

  • Subcereri care întorc o valoare
  • Subcereri care întorc o coloană
  • Subcereri care întorc o linie
  • Subcereri care întorc mai multe linii

Această împărțire pe categorii (în funcție de numărul de coloane și linii pe care le întoarce subcererea) a subcererilor se aplică atât pentru subcererile necorelate cât și pentru cele corelate.

Subcererile pot fi împărțite în două categorii, în funcție de modul în care sunt corelate cu cererea principală (cerea care cuprinde subcererea):

  • Subcereri necorelate: rezultatul subcererii nu este condiționat de valorile din cererea principală
  • Subcereri corelate: rezultatul subcererii este condiționat de valorile din cererea principală

Când folosim subcereri, trebuie să respectăm câteva reguli:

  • Subcererea trebuie să fie inclusă între paranteze;
  • Pentru cazurile în care subcererea se află în clauza WHERE, sau HAVING, aceasta trebuie să fie în partea dreaptă a condiției;
  • Expresiile din lista de expresii ale subcererii trebuie să fie în aceeași ordine ca expresiile din lista de condiții din clauza WHERE sau HAVING a cererii principale (având același tip și număr de expresii);
  • Subcererile nu pot fi ordonate, deci nu conțin clauza ORDER BY
  • Clauza ORDER BY apare la sfârșitul cererii principale.
  • Subcererile necorelate sunt executate de la cea mai adâncă imbricare până la nivelul principal de îmbricare;
  • Subcererile pot folosi funcții de grup și clauza GROUP BY;
  • Subcererile pot fi înlănțuite cu predicate multiple AND sau OR în aceeași cerere externă în clauze WHERE sau HAVING ale acesteia;
  • În subcereri se pot folosi operatori de mulțimi;
  • Subcererile pot fi imbricate până la nivelul 255;
  • În clauza SELECT o subcerere trebuie să întoarcă o singură valoare.

Subcereri necorelate în clauza WHERE

  • Subcererile necorelate sunt subcereri care nu au o legătură de asociere între expresiile cererii exterioare și cele ale cererii interioare.
  • O subcerere necorelată se execută o singură dată și se execută prima în momentul când începe execuția cererii principale.
  • Subcererea necorelată în clauza WHERE se construiește prima pentru a verifica condiția din caluza WHERE.
  • Condiția din subcererea necorelată din clauza WHERE se verifică pe datele din tabela folosită în cererea principală.
Subcereri necorelate care întorc o valoare în clauza WHERE
SELECT 
		[table_1.]expr_1, 
		[table_1.]expr_2,
		..., 
		[table_1.]expr_n
FROM table_1
WHERE [NOT] [table_1.]expr_k OPERATOR
	(SELECT [table_2.]expr_i 
	FROM table_2
	[WHERE conditions_1]
	[HAVING conditions_2])

[table_1.]expr_k – sunt expresii pe coloanele(sau doar o coloană) care aparțin tabelului table_1, k=1..n;
[table_2.]expr_i – este o expresie pe coloanele(sau doar o coloană) care aparțin tabelului table_2;
OPERATOR – este mulțimea operatorilor de comparație {>, >=, =, <=, <};
conditions_1 – sunt condițiile din clauza WHERE (opțională) a subcererii;
conditions_2 – sunt condițiile din clauza HAVING (opțională) a subcererii;

Ex. 1. Să se selecteze angajatul cu cel mai mare salariu din firmă.

Subcereri necorelate care întorc o coloană în clauza WHERE

Sunt subcereri care întorc mai multe valori și folosesc operatorii IN și NOT IN. În cazul în care se folosesc operatori de comparație în locul operatorului IN (NOT IN), atunci se generează o eroare. Observatie:

Operatorul IN (NOT IN) se poate folosi în loc de operatori de comparație, dar nu și invers.

SELECT 
		[table_1.]expr_1, 
		[table_1.]expr_2,
		..., 
		[table_1.]expr_n
FROM table_1
WHERE [table_1.]expr_k  [NOT] IN
	(SELECT [table_2.]expr_i 
	FROM table_2
	[WHERE conditions_1]
	[[GROUP BY expressions] [HAVING conditions_2]])

Ex. 2. Să se selecteze angajații care au funcții similare funcțiilor din departamentul 20 și nu lucrează în acest departament. Ex. 3. Să se selecteze angajații care nu s-au angajat în lunile decembrie, ianuarie și februarie. Ex. 4. Să se selecteze angajații care au salariile in lista de salarii maxime pe departament.

Subcereri necorelate care întorc o linie în clauza WHERE
  • Sunt cereri care întorc mai multe colonale dar o singură linie;
  • Folosesc operatorii IN și NOT IN
  • Numărul și tipul expresiilor (coloanelor) din clauza WHERE a cererii principale trebuie să coincidă cu cele din subcerere.
SELECT  [table_1.]expr_1, [table_1.]expr_2,..., [table_1.]expr_n
FROM table_1
WHERE ([table_1.]expr_i1, ..., [table_1.]expr_im)  [NOT] IN
	(SELECT  [table_2.]expr_j1, ..., [table_1.]expr_jm
	FROM table_2
	[WHERE conditions_1]
	[HAVING conditions_2])

Ex. 5. Să se selecteze angajații care au venit în același an și au aceeași funcție cu angajatul care are numele JONES.

Subcereri necorelate care întorc mai multe linii în clauza WHERE
  • Aceste subcereri au sintaxa asemănătoare cu subcererile care întorc o singură linie;
  • Aceste subcereri întorc mai multe linii și mai multe coloane, motiv pentru care se mai numesc și cereri care întorc o tabelă;
  • Numărul expresiilor(coloanelor) din clauza WHERE a cererii principale trebuie să coincidă cu numărul de expresii din întoarse de subcerere și trebuie să fie de același tip.
SELECT  [table_1.]expr_1, [table_1.]expr_2,..., [table_1.]expr_n
FROM table_1
WHERE ([table_1.]expr_i1, ..., [table_1.]expr_im)  [NOT] IN
	(SELECT  [table_2.]expr_j1, ..., [table_1.]expr_jm
	FROM table_2
	[WHERE conditions_1]
	[[GROUP BY expressions] [HAVING conditions_2]])

Ex. 6. Să se afișeze angajatii care au venitul lunar minim pe fiecare departament. Ex. 7. Să se afișeze angajații care au salariul mai mare decât salariul maxim din departamentul SALES. Acesta este un exemplu de subcereri imbricate (in total 3 nivele), subcererile insa NU intorc mai multe linii in final, ci o singura valoare.

Subcereri corelate în clauze WHERE

Subcererile corelate se execută o singură dată pentru fiecare linie candidat prelucrată de cererea principală. O subcerere corelată se join-ează cu cererea exterioară prin folosirea unei coloane a cererii exterioare în clauza predicatului cererii interioare.

SELECT  [table_1.]expr_1, [table_1.]expr_2,..., [table_1.]expr_n
FROM table_1
WHERE ([table_1.]expr_i1, ..., [table_1.]expr_im)  [NOT] IN
	(SELECT  [table_2.]expr_j1, ..., [table_1.]expr_jm
	FROM table_2
	WHERE [table_2.]expr_x OPERATOR [table_1.]expr_y [{AND|OR} ...]
	[[GROUP BY expressions] 
	[HAVING [table_2.]expr_t OPERATOR [table_1.]expr_z [{AND|OR} ...]
]])

Pentru o subcerere corelată, subcererea se execută de mai multe ori, câte o dată pentru fiecare linie prelucrată de cererea principală, deci cererea interioară este condusă de cererea exterioară; Pașii de execuție a unei subcereri corelate sunt:

  • Se obține linia candidat prin procesarea cererii exterioare;
  • Se execută cererea interioară corelată cu valarea linie candidat;
  • Se folosește valoarea rezultatului din cererea interioară pentru a prelucra linia candidat;
  • Se repetă până nu mai rămâne nicio linie candidat.

Deși o subcerere corelată se execută de mai multe ori, acest lucru nu implică că subcererile corelate sunt mai ineficiente decât subcererile necorelate.

Ex. 8. Să se afișeze angajații care au salariul peste valoarea medie a departamentului din care fac parte. Ex. 9. Să se mărească salariile angajaților cu 10% din salariul mediu și să se acorde tuturor angajaților un comision egal cu comisionul mediu pe departamentul din care face parte angajatul, numai pentru persoanele angajate înainte de 1-JUN-1981.

Subcereri pe tabelă temporară (în clauza FROM)

  • Aceste subcereri se întâlnesc în cazul în care se folosește o subcerere la nivelul clauzei FROM;
  • În clauza FROM se pot folosi doar subcereri necorelate;
  • Corelarea dintre tabele și tabelele temporare din clauza FROM se face folosind metode de join.
SELECT t1.expr_1, ..., t1.expr_n, t2.expr_1,..., t2.expr_m
FROM table_1 t1
	{,|[[INNER|CROSS|NATURAL]|[FULL|RIGHT|LEFT] [OUTER] ]] JOIN}
	(SELECT ti.expr_1, ... , ti.expr_s
	FROM table_2
	[WHERE nested_where_conditions]
	[[GROUP BY nested_expresions] 
		[HAVING nested_having_conditions]]
	) t2 
	[ON on_conditions]
	[WHERE where_conditions]
	[[GROUP BY expresions] 
		[HAVING having_conditions]]
[ORDER BY {COLUMNS|expressions|possition} [ASC|DESC]]

Ex. 10. Să se afle salariul maxim pentru fiecare departament.

Exerciții individuale

Să se selecteze numele angajatului, numele departamentului si salariul pentru angajatii care au salariul egal cu salariul minim din departamentul in care lucreaza. Ordonati dupa nume.

Rezolvati prin 4 metode:

  • o metoda care sa foloseasca o subcerere care intoarce o valoare
  • o metoda care sa foloseasca o subcerere care intoarce o linie cu mai multe coloane
  • o metoda care sa foloseasca o subcerere care intoarce mai multe linii cu mai multe coloane
  • o metoda care sa foloseasca o subcerere in clauza from
bd/laboratoare/08.txt · Last modified: 2023/04/24 12:32 by sorin.ciolofan
CC Attribution-Share Alike 3.0 Unported
www.chimeric.de Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0