1

Introducere în SQL

Clauzele Select-From-Where

Interogări cu mai multe relaţii

Interogări imbricate

2

SQL

�Este un limbaj “very-high-level”.� Spune CE trebuie făcut şi nu CUM trebuie

făcut.

� Evită detaliile legate de lucrul cu datele,caracteristice limbajelor procedurale (C++ sau Java).

�SGBD-ul se îngrijeşte de modul de execuție a interogării. � Această funcţiune se numeşte “query

optimization.”

3

Clauzele Select-From-Where

SELECT listă de atribute

FROM una sau mai multe tabele

WHERE condiţie aplicată tuplelor

tabelelor

4

Schema BD Exemplu

�Interogările SQL prezentate în curs folosesc următoarea schemă:

Beers(name, manf)

Bars(name, addr, license)

Drinkers(name, addr, phone)

Likes(drinker, beer)

Sells(bar, beer, price)

Frequents(drinker, bar)� Atributele subliniate indică CHEIA fiecărei relaţii.

5

Exemplu

�Folosind Beers(name, manf), care sunt mărcile de bere produse de Anheuser-Busch?

SELECT name

FROM Beers

WHERE manf = ’Anheuser-Busch’;

6

Rezultatul Interogării

name

Bud

Bud Lite

Michelob

. . .

Răspunsul este o relaţie cu un singur atribut,“name”, şi tuplele cu denumirea fiecărei mărci debere produsă de Anheuser-Busch, cum este “Bud”.

7

Cum funcţionează interogareaasupra unei singure relaţii

�Relaţia este specificată în clauza FROM.

�Se aplică selecţia precizată în clauzaWHERE.

�Se aplică proiecţia extinsă indicată prin lista de atribute din clauza SELECT.

8

Semanticile Operaţiilor

Verifică dacămanf are valoarea‘Anheuser-Busch’

name manf

Bud Anheuser-Busch Se include t.name în rezultat, dacă eadevărat

Variabila de tuplă tparcurge toate tuplele

9

Semanticile Operaţiilor

�Se poate gândi la o variabilă de tuplă ce vizitează fiecare tuplă a relaţieimenţionate în clauza FROM.

�Se verifică dacă tupla “curentă” satisface clauza WHERE.

�Dacă se întâmplă aşa, se determinăatributele sau expresiile clauzei SELECT folosind componentele acestei tuple.

10

* în clauza SELECT

�Atunci când există o singură relaţie în clauza FROM, * în clauza SELECT semnifică“toate atributele acestei relaţii”.

�Exemplu: Se foloseşte Beers(name, manf):

SELECT *

FROM Beers

WHERE manf = ’Anheuser-Busch’;

11

Rezultatul Interogării:

name manf

Bud Anheuser-Busch

Bud Lite Anheuser-Busch

Michelob Anheuser-Busch

. . . . . .

Acum, rezultatul are fiecare atributal relaţiei Beers.

12

Redenumirea Atributelor

�Dacă se doreşte ca rezultatul să aibă nume diferite de atribute, se foloseşte “AS <numenou>” pentru a redenumi un atribut.

�Exemplu: Se foloseşte Beers(name, manf):

SELECT name AS beer, manf

FROM Beers

WHERE manf = ’Anheuser-Busch’

13

Rezultatul Interogării:

beer manf

Bud Anheuser-Busch

Bud Lite Anheuser-Busch

Michelob Anheuser-Busch

. . . . . .

14

Expresii în clauza SELECT

�Orice expresie ce are sens poate fi un element al clauzei SELECT.

�Exemplu: Se foloseşte Sells(bar, beer, price):

SELECT bar, beer,

price*114 AS priceInYen

FROM Sells;

15

Rezultatul Interogării

bar beer priceInYen

Joe’s Bud 285

Sue’s Miller 342

… … …

16

Exemplu: Constante ca Expresii

�Se foloseşte Likes(drinker, beer):

SELECT drinker,

’îi place Bud’ AS cuiîiplaceBud

FROM Likes

WHERE beer = ’Bud’;

17

Rezultatul Interogării

drinker cuiîiplaceBud

Sally îi place Bud

Fred îi place Bud

… …

18

Exemplu: Integrarea Informaţiei

�Adesea se construiesc “data warehouses” din surse multiple de date.

�Să presupunem că fiecare bar arepropria relaţie Menu(beer, price) .

�Pentru a contribui la Sells(bar, beer, price) este nevoie de interogareafiecărui bar şi de adăugarea (“insert”)denumirii barului.

19

Integrarea Informaţiei

�Să spunem, că la “Joe’s Bar” emiteminterogarea (query):

SELECT ’Joe’’s Bar’, beer, price

FROM Menu;

20

Condiţii Complexe în Clauza WHERE

�Operatori logici AND, OR, NOT.

�Comparaţii =, <>, <, >, <=, >=.

� Şi mulţi alţi operatori ce produc rezultate valoare-logică.

21

Exemplu: Condiţie Complexă

�Se foloseşte Sells(bar, beer, price), trebuie găsit preţul de la “Joe’s Bar” pentru “Bud”:

SELECT price

FROM Sells

WHERE bar = ’Joe’’s Bar’ AND

beer = ’Bud’;

22

Formate tipice (pattern)

�O condiţie poate compara un şir de caractere cu un format tipic în felul următor:� <Atribut> LIKE <pattern> sau <Atribut>

NOT LIKE <pattern>

�Pattern este un şir de caractere încadrat între ghilimele cu:% = “orice şir de caractere”;

_ = “orice caracter”.

23

Exemplu: LIKE

�Se foloseşte Drinkers(name, addr, phone)Să se găsească “drinkers” cu prefixul numărului de telefon 555:

SELECT name

FROM Drinkers

WHERE phone LIKE ’%555-_ _ _ _’;

24

Valori NULL

�Tuplele în relaţii SQL pot avea valoareaNULL pentru unul sau mai multe atribute.

�Semnificaţia depinde de context. Douăsituaţii obişnuite:

� Valoare lipsă : de exemplu, se ştie că “Joe’s Bar” are o adresă, dar aceasta nu se cunoaşte.

� Inaplicabil : de exemplu, valoarea atributuluisoţ pentru o persoană necăsătorită.

25

Compararea NULL cu Valoare

�Condiţiile au de fapt o logică 3-valori în SQL: TRUE, FALSE, UNKNOWN.

�Compararea oricărei valori (incluzând NULL cu el însuşi) cu NULL conduce la UNKNOWN.

�Pentru ca o tuplă să aparţină răspunsului la o interogare, condiţia din clauza WHERE trebuie să aibă valoarea logică TRUE (nici FALSE şi niciUNKNOWN).

26

Logica Trei-Valori

�Pentru a înţelege cum lucrează AND, OR, şiNOT în logica trei-valori, să ne gândim căTRUE = 1, FALSE = 0, şi UNKNOWN = ½.

�AND = MIN; OR = MAX, NOT(x) = 1-x.

�Exemplu:

TRUE AND (FALSE OR NOT(UNKNOWN)) =

MIN(1, MAX(0, (1 - ½ ))) =

MIN(1, MAX(0, ½ )) = MIN(1, ½ ) = ½.

27

Exemplu Surprizător

�Pentru următoarea relaţie Sells :

bar beer price

Joe’s Bar Bud NULL

SELECT bar

FROM Sells

WHERE price < 2.00 OR price >= 2.00;UNKNOWN UNKNOWN

UNKNOWN

28

Motivaţie: Legile 2-Valori!= Legile 3-Valori

�Anumite legi obişnuite, cum estecomutativitatea lui AND, rămân valabile în logica 3-valori.

�Dar altele nu, cum este legea excluderiimijlocului : p OR NOT p = TRUE.

� Când p = UNKNOWN, membrul stâng esteMAX( ½, (1 – ½ )) = ½ != 1.

29

Interogări asupramai multor relaţii

�Interogările combină adesea datele dinmai multe relaţii.

�Se pot menţiona mai multe relaţii într-ointerogare prin introducerea lor în clauza FROM.

�Se face distincţie între atributele cu acelaşi nume prin prefixare cu numele relaţiei: “<relaţie>.<atribut>” .

30

Exemplu: Reunirea a două Relaţii

�Se folosesc relaţiile Likes(drinker, beer) şiFrequents(drinker, bar), pentru a găsibăuturile preferate de cel puţin o persoanăce frecventează “Joe’s Bar”.SELECT beer

FROM Likes, Frequents

WHERE bar = ’Joe’’s Bar’ AND

Frequents.drinker =

Likes.drinker;

31

Semanticile Formale pentru oInterogare cu mai multe relaţii

� Sunt aproximativ aceleaşi ca şi în cazul interogării cu o singură relaţie:

1. Se începe cu produsul relaţiilor din clauzaFROM.

2. Se aplică selecţia impusă de condiţia din clauza WHERE.

3. Se aplică proiecţia extinsă pe lista de atribute şi expresii din clauza SELECT.

32

Semanticile Operaţionale pentruo Interogare cu mai multe relaţii

�Să ne imaginăm câte o variabilă de tuplă pentru fiecare relaţie din clauzaFROM.� Aceste variabile de tuplă parcurg fiecare

combinaţie de tuple, câte o tuplă dinfiecare relaţie.

�Dacă variabilele de tuplă indică tuple cesatisfac condiţia din clauza WHERE, aceste tuple fac parte din rezultat.

33

Exemplu

drinker bar drinker beer

vt1 vt2Sally Bud

Sally Joe’s

LikesFrequents

coloana “beer”face partedin rezultat

dacă acestevalori suntegale

verificăpentruJoe’s

34

Variabile de Tuplă Explicite

�Uneori, o interogare necesită folosirea adouă copii a aceleaşi relaţii.

�Se face distincţie între cele două copiiprin completarea după numele relaţiei anumelui unei variabile de tuplă, în clauza FROM.

�Oricând se poate proceda astfel, prinredenumirea relaţiilor, chiar dacă nu este o condiţie obligatorie.

35

Exemplu: Self-Join

�Folosind Beers(name, manf), să se găseascătoate perechile de bere produse de aceeaşifabrică.� Nu interesează perechi cum ar fi (Bud, Bud).

� Perechile vor fi obţinute în ordine alfabetică, de ex. (Bud, Miller), nu (Miller, Bud).

SELECT b1.name, b2.name

FROM Beers b1, Beers b2

WHERE b1.manf = b2.manf AND

b1.name < b2.name;

36

Interogări imbricate (Subqueries)

�O instrucţiune SELECT-FROM-WHERE amplasată între paranteze (subquery )poate fi folosită ca valoare în mai multe locuri, inclusiv în clauzele FROM şiWHERE.

�Exemplu: în locul unei relaţii în clauzaFROM, se poate folosi o interogare imbricată (subquery).� Este obligatoriu să fie utilizată o variabilă

de tuplă pentru a numi tuplele rezultatului subinterogării.

37

Exemplu: Subquery în FROM

�Să se găsească berea preferată de cel puţino persoană ce frecventează “Joe’s Bar”.

SELECT beer

FROM Likes, (SELECT drinker

FROM Frequents

WHERE bar = ’Joe’’s Bar’)JD

WHERE Likes.drinker = JD.drinker;

Persoane cefrecventează “Joe’s Bar”

38

Interogări imbricate ceReturnează 1 Tuplă

�Dacă o interogare imbricată returnează 1 tuplă şi numai una, atunci interogarea imbricată poate fi utilizată ca valoare.

� De obicei tupla are o singură componentă.

� Dacă interogarea imbricată nu returnează nici o tuplă sau mai mult de una, se semnalează o eroare run-time.

39

Exemplu:Interogare imbricată cu 1 tuplă

� Se foloseşte Sells(bar, beer, price), pentru a găsi barurile ce servesc “Miller”la preţul cu care “Joe’s Bar” vinde “Bud”.

� Răspunsul poate fi dat sub forma a două interogări:

1. Găseşte preţul cu care “Joe’s Bar” vinde“Bud”.

2. Găseşte barurile ce servesc “Miller” la acel preţ.

40

Soluţia cu interogare şiinterogare imbricată

SELECT bar

FROM Sells

WHERE beer = ’Miller’ AND

price = (SELECT price

FROM Sells

WHERE bar = ’Joe’’s Bar’

AND beer = ’Bud’);

Preţul cucare “Joe’s Bar”vinde “Bud”

41

Operatorul IN

�<tuplă> IN (<subquery>) are valoarea logică True dacă şi numai dacă tuplaeste membră a relaţiei produse de interogarea imbricată.

� La modul opus este: <tuplă> NOT IN (<subquery>).

�Expresiile “IN” pot apare în clauzeWHERE.

42

Exemplu: IN

�Se folosesc Beers(name, manf) şiLikes(drinker, beer), pentru a găsi numele şiproducătorul fiecărei beri preferată de Fred.

SELECT *

FROM Beers

WHERE name IN (SELECT beer

FROM Likes

WHERE drinker = ’Fred’);

Mulţimea (set)berilor preferatede Fred

43

Revenim la întrebareadin cursul introductiv:

SELECT a

FROM R, S

WHERE R.b = S.b;

prin ce diferă de:

SELECT a

FROM R

WHERE b IN (SELECT b FROM S);

44

Cu ajutorul IN se construieşte unpredicat pentru tuplele din R

SELECT a

FROM R

WHERE b IN (SELECT b FROM S);

O singurăparcurgere atuplelor din R

a b1 23 4R

b c2 52 6S

(1,2) satisfacecondiţia;

1 este afişato singură

dată.

Valoarea 2 apare de două ori

45

Tuplele din R şi S fac pereche prin această interogare

SELECT a

FROM R, S

WHERE R.b = S.b;

Ciclu dublu:primul peste tuplele din R şi

al doilea pestetuplele din S

a b1 23 4R

b c2 52 6S

(1,2) cu (2,5) şi(1,2) cu (2,6),ambele satisfaccondiţia;1 este afişat dedouă ori.

46

Operatorul EXISTS

�EXISTS(<subquery>) are valoarea logică True dacă şi numai dacărezultatul interogării imbricate estenevid.

�Exemplu: Se foloseşte Beers(name, manf) , pentru a găsi acele beri cereprezintă unica bere a producătorului ei.

47

Exemplu: EXISTS

SELECT name

FROM Beers b1

WHERE NOT EXISTS (

SELECT *

FROM Beers

WHERE manf = b1.manf AND

name <> b1.name);

Mulţimeaberilorce suntprodusede acelaşiproducător(cu b1), darau altădenumire

De notat regula: manf se referăla o relaţie din cel mai apropiatFROM, ce conţine acel atribut.

De notatoperatorulSQL “notequals”

48

Operatorul “ANY”

�x = ANY(<subquery>) este o condiţie booleană ceare valoarea logică True dacă x este “egal” cu cel puţin una din tuplele rezultatului interogării imbricate.

� “=“ poate fi oricare din operatorii de comparaţie.

�Exemplu: x >= ANY(<subquery>) semnifică x nu este singura tuplă cea mai mică produsă de interogarea imbricată.� De notat că tuplele trebuie să aibă doar o componentă.

49

Operatorul “ALL”

�x <> ALL(<subquery>) are valoarea logicăTrue dacă pentru fiecare tuplă t din relaţia “subquery”, x este diferit de t.� Cu alte cuvinte, x nu se regăseşte în rezultatul

interogării imbricate.

�“<>” poate fi orice operator de comparaţie.

�Exemplu: x >= ALL(<subquery>) semnificănu există tuplă mai mare ca x în rezultatul interogării imbricate.

50

Exemplu: ALL

�Se foloseşte Sells(bar, beer, price), pentru a găsi berea(-ile) vândute la cel mai mare preţ.

SELECT beer

FROM Sells

WHERE price >= ALL(

SELECT price

FROM Sells);

preţul “Sells” dinexterior să nu fiemai mic decâtnici un preţ.

51

Uniunea, Intersecţia şi Diferenţa

�Uniunea, intersecţia şi diferenţarelaţiilor sunt exprimate prin formele următoare, fiecare implică interogări imbricate:

� (<subquery>) UNION (<subquery>)

� (<subquery>) INTERSECT (<subquery>)

� (<subquery>) EXCEPT (<subquery>)

52

Exemplu: INTERSECT

� Se folosesc Likes(drinker, beer), Sells(bar, beer, price) şi Frequents(drinker, bar)pentru a găsi persoanele şi mărcile de bere astfel încât:

1. Persoana preferă berea şi

2. Persoana frecventează cel puţin un bar unde se vinde berea.

53

Soluţia

(SELECT * FROM Likes)

INTERSECT

(SELECT drinker, beer

FROM Sells, Frequents

WHERE Frequents.bar = Sells.bar

);

Persoana frecventeazăun bar ce vinde berea.

De notat trucul:interogareaimbricată esteo tabelă de bază.

54

Semanticile “Bag”

�Deşi instrucţiunea SELECT-FROM-WHERE foloseşte semantici “bag”, semanticile implicite pentru uniune, intersecţie şi diferenţă sunt “set”.

� Aceasta înseamnă că duplicatele sunteliminate implicit la aplicarea unuia din cei trei operatori (UNION, INTERSECT, EXCEPT).

55

Motivaţie: Eficienţă

�Atunci când se aplică proiecţia extinsă, estemai uşor să se evite eliminarea duplicatelor.

� Deoarece se poate lucra cu o singură tuplă la un moment dat.

�Pentru intersecţie sau diferenţă, este maieficient să se sorteze mai întâi relaţiile.

� La acel moment dat se poate la fel de bine să seelimine duplicatele.

56

Eliminarea explicită a Duplicatelor

�Pentru a forţa rezultatul unei interogări să fie “set”:

SELECT DISTINCT . . .

�Pentru a forţa rezultatul unei interogări să fie “bag” (adică să nu fie eliminate duplicatele):

se foloseşte ALL,

de exemplu . . . UNION ALL . . .

57

Exemplu: DISTINCT

�Se foloseşte Sells(bar, beer, price), pentru a găsi toate preţurile diferite pentru mărcile de bere vândute:

SELECT DISTINCT price

FROM Sells;

�De notat că fără DISTINCT, fiecare preţpoate să apară de mai multe ori, multiplicat după numărul de baruri/mărci de bere.

58

Exemplu: ALL

Interogarea:(SELECT drinker FROM Frequents)

EXCEPT ALL

(SELECT drinker FROM Likes);

foloseşte relaţiile Frequents(drinker, bar) şiLikes(drinker, beer) pentru a afişa persoanelepentru care numărul de baruri frecventateeste mai mare decât numărul de mărci de bere preferate şi o persoană apare de atâteaori în rezultat cât reprezintă diferenţa acestornumere.

59

Expresii “Join”

�SQL mai multe variante de reuniri (“bag”).

�Aceste expresii pot fi interogări de sine stătătoare (stand-alone queries) sausunt folosite în locul relaţiilor în clauzaFROM.

60

Produs şi Natural Join

�Natural join:

R NATURAL JOIN S;

�Produs:

R CROSS JOIN S;

�Exemplu:

Likes NATURAL JOIN Sells;

�Relaţiile pot fi la fel de bine interogări imbricate între paranteze.

61

Theta Join

�R JOIN S ON <condiţie>

�Exemplu: Drinkers(name, addr) şiFrequents(drinker, bar):

Drinkers JOIN Frequents ON

name = drinker;

generează toate cvadruplele (d, a, d, b) a.î. persoana d locuieşte la adresa a şi frecventează barul b.