Limbajul SQL – interogări avansate

Aprofundarea limbajului SQL pentru interogări complexe asupra bazelor de date relaționale.

Funcțiile de agregare: de la multe linii la o valoare

O funcție de agregare primește o mulțime de valori (o coloană, pe liniile selectate) și întoarce o singură valoare:

• COUNT — numără; SUM — adună; AVG — media aritmetică; MIN / MAX — extremele.
• `SELECT AVG(medie) FROM elevi WHERE clasa = 11;` — media mediilor din clasele a XI-a.

Subtilitățile care fac diferența la examen se învârt toate în jurul lui NULL și al lui COUNT:

• funcțiile de agregare ignoră valorile NULL: AVG(medie) împarte suma la numărul valorilor existente, nu la numărul total de linii — dacă 3 elevi din 30 nu au medie, media se calculează pe 27;
• *COUNT() numără liniile, indiferent de conținut; COUNT(camp) numără doar liniile în care camp nu e NULL; COUNT(DISTINCT camp) numără valorile distincte** nenule. Cele trei forme dau rezultate diferite pe același tabel și sunt grila favorită a capitolului.

Regula de amestec, sursa clasică de eroare: într-un SELECT fără GROUP BY, nu poți pune alături o coloană simplă și un agregat — `SELECT nume, MAX(medie) FROM elevi;` nu returnează elevul cu media maximă, ci este incorect logic (și respins de majoritatea SGBD-urilor): MAX produce o valoare pentru tot tabelul, iar nume are câte o valoare pe linie. Elevul cu media maximă se obține corect cu subinterogare: `WHERE medie = (SELECT MAX(medie) FROM elevi)`.

GROUP BY: agregate pe grupuri

GROUP BY împarte liniile în grupuri după valorile unei coloane (sau ale mai multor coloane), iar funcțiile de agregare se calculează pe fiecare grup în parte, nu pe tot tabelul:

• `SELECT clasa, COUNT(*), AVG(medie) FROM elevi GROUP BY clasa;` — pentru fiecare clasă: numărul de elevi și media lor.

Rezultatul are câte o linie per grup. De aici regula de aur a clauzei, punctată explicit la corectare: în SELECT pot apărea doar coloanele după care s-a grupat și funcții de agregare — orice altă coloană e ambiguă (care nume ai afișa pentru un grup de 30 de elevi?).

Gruparea se poate face după mai multe coloane: `GROUP BY clasa, sex` produce câte un grup pentru fiecare combinație existentă (11A-fete, 11A-băieți, …).

Ordinea de evaluare a unei interogări complete — schema care limpezește tot capitolul:

• FROM (și JOIN) → WHERE (filtrează linii individuale) → GROUP BY (formează grupuri) → HAVING (filtrează grupuri) → SELECT (calculează expresiile afișate) → ORDER BY (sortează rezultatul final).

Din această ordine rezultă și de ce WHERE nu poate conține funcții de agregare: WHERE rulează înainte de formarea grupurilor, când agregatele nici nu există încă. Tot din ea rezultă că ORDER BY poate sorta după un agregat (`ORDER BY AVG(medie) DESC`), fiind evaluat ultimul.

HAVING: filtrul grupurilor, nu al liniilor

HAVING filtrează grupurile create de GROUP BY, pe baza unor condiții cu funcții de agregare — exact ceea ce WHERE nu are voie să facă:

• `SELECT clasa, AVG(medie) FROM elevi GROUP BY clasa HAVING AVG(medie) >= 9;` — doar clasele cu media generală cel puțin 9.

Perechea WHERE / HAVING trebuie stăpânită contrastiv, pentru că orice subiect o testează:

• WHERE — filtrează linii individuale, înainte de grupare; nu poate conține agregate;
• HAVING — filtrează grupuri întregi, după grupare; condițiile lui folosesc tipic agregate.

Cele două clauze cooperează, nu se exclud: „media claselor, calculată doar din elevii promovați, afișată doar pentru clasele cu peste 20 de promovați” cere ambele — `WHERE medie >= 5 GROUP BY clasa HAVING COUNT(*) > 20`. WHERE elimină întâi elevii nepromovați (liniile), apoi HAVING elimină clasele mici (grupurile).

Greșelile care pică sistematic candidații:

• condiție de agregat pusă în WHERE: `WHERE AVG(medie) > 9` — eroare, agregatele nu există înaintea grupării;
• condiție de linie pusă inutil în HAVING: `HAVING clasa = 11` funcționează uneori sintactic, dar corect și eficient este `WHERE clasa = 11` — filtrarea liniilor înainte de grupare;
• HAVING fără GROUP BY: posibil în teorie (tot tabelul devine un singur grup), dar în practica de examen HAVING însoțește gruparea.

JOIN: interogări pe mai multe tabele

JOIN combină linii din două (sau mai multe) tabele pe baza unei condiții de legătură, tipic egalitatea dintre cheia externă și cheia primară referită:

• `SELECT e.nume, c.titlu FROM imprumuturi i JOIN elevi e ON i.id_elev = e.id JOIN carti c ON i.id_carte = c.id;`

Aliasurile (e, c, i) scurtează scrierea și devin obligatorii când coloane cu același nume apar în ambele tabele (id) — altfel referirea e ambiguă.

Tipurile de JOIN, cu diferența care se cere:

• INNER JOIN (sau simplu JOIN): păstrează doar perechile care satisfac condiția — elevii fără împrumuturi dispar din rezultat;
• LEFT JOIN: păstrează toate liniile tabelului din stânga, completate cu NULL unde nu există pereche în dreapta — elevii fără împrumuturi apar, cu titlu NULL; RIGHT JOIN este simetricul.

De aici idiomul de examen „elevii care NU au împrumutat nimic”: `LEFT JOIN … WHERE i.id_elev IS NULL` — liniile rămase fără pereche sunt exact cele căutate.

Două capcane cotate maxim:

• uitarea condiției ON (sau enumerarea tabelelor cu virgulă fără WHERE de legătură) produce produsul cartezian: fiecare linie cu fiecare — 100 de elevi × 200 de împrumuturi = 20.000 de combinații fără sens;
• într-o relație 1-n, JOIN-ul multiplică liniile părții „unu”: un elev cu 5 împrumuturi apare de 5 ori în rezultat; `COUNT(*)` numără atunci împrumuturi, nu elevi — pentru elevi distincți se folosește `COUNT(DISTINCT e.id)`.

Subinterogări

O subinterogare (subcerere) este un SELECT inclus în altă comandă, ale cărei rezultate le folosește interogarea exterioară. După forma rezultatului:

• scalară (o singură valoare): `SELECT nume FROM elevi WHERE medie = (SELECT MAX(medie) FROM elevi);` — șablonul standard pentru „linia cu valoarea extremă”, acolo unde amestecul nume + MAX într-un singur SELECT ar fi incorect;
• de mulțime (o coloană de valori), folosită cu IN: `SELECT nume FROM elevi WHERE id IN (SELECT id_elev FROM imprumuturi);` — elevii care au cel puțin un împrumut; cu NOT IN, cei fără împrumuturi;
• cu EXISTS: testează doar dacă subinterogarea întoarce măcar o linie — echivalent logic cu IN în cazurile uzuale de examen.

Regulile de corectitudine care se verifică în subiecte:

• unde interogarea exterioară așteaptă o valoare (comparație cu =, <, >), subinterogarea trebuie să întoarcă cel mult una — dacă întoarce mai multe, comanda eșuează; pentru mulțimi se folosesc IN, ANY, ALL;
• subinterogarea se scrie între paranteze, întotdeauna;
• multe interogări se pot scrie echivalent cu JOIN sau cu subinterogare — la examen alege forma cerută de enunț sau pe cea pe care o stăpânești fără риск de sintaxă.

Capcana specifică NOT IN: dacă subinterogarea întoarce și NULL, NOT IN nu mai returnează nicio linie (comparațiile cu NULL sunt necunoscute) — de aceea varianta cu LEFT JOIN … IS NULL sau filtrarea NULL-urilor în subcerere e mai sigură.

DDL: crearea și modificarea structurii tabelelor

Comenzile de până acum (SELECT, INSERT, UPDATE, DELETE) formează DML — limbajul de manipulare a datelor. Structura tabelelor se administrează cu DDL — limbajul de definire a datelor:

• CREATE TABLE definește tabelul, câmpurile, tipurile și restricțiile: `CREATE TABLE elevi (id INT PRIMARY KEY, nume VARCHAR(50) NOT NULL, medie DECIMAL(4,2), id_clasa INT, FOREIGN KEY (id_clasa) REFERENCES clase(id));`
• ALTER TABLE modifică structura unui tabel existent: `ALTER TABLE elevi ADD COLUMN telefon VARCHAR(15);` — se pot adăuga, șterge sau modifica coloane și restricții, fără a pierde datele existente;
• DROP TABLE șterge tabelul cu totul — structură și date, definitiv.

Restricțiile (constrângerile) care se atașează câmpurilor, fiecare cu rolul ei:

• PRIMARY KEY — unicitate + NOT NULL, o singură cheie primară pe tabel (posibil compusă);
• FOREIGN KEY … REFERENCES — impune integritatea referențială;
• NOT NULL — valoarea este obligatorie; UNIQUE — valorile nu se repetă (dar, spre deosebire de cheia primară, admite NULL);
• DEFAULT — valoarea implicită la inserare; CHECK — condiție pe valoare (medie între 1 și 10).

Tripleta de ștergeri, nelipsită din grile pentru că numele se aseamănă și efectele diferă radical: DELETE șterge înregistrări (DML, tabelul rămâne), TRUNCATE golește rapid tot tabelul (structura rămâne), DROP șterge tabelul însuși. Cine le tratează ca sinonime pierde punctul la fiecare apariție.

Test — 6 întrebări ca la examen