Baze de Date

Anca Ion2011-2012

Baze de Date-Limbajul SQL-Interogări-

Universitatea din Craiova,Facultatea de Automatica, Calculatoare si Electronica

Baze de Date

Anca Ion2011-2012

Introducere• Comanda SQL pentru regăsirea informației în baze de date este SELECT.

•Nu există nici o legatură între comanda SELECT din SQL și operația de selecție din algebra relațională. Comanda SELECT din SQL are multe opțiuni care vor fi prezentate la acest curs.

•Spre deosebire de modelul relațional, în SQL este permis ca un tabel să aiba unul sau mai multe tupluri duplicat; o tabelă în SQL nu este o mulțime de valori, deoarece intr-o mulțime nu se permit înregistrări duplicat.

•Unele relații în SQL sunt constrânse să fie mulțimi datorită cheii primare sau datorită opțiunii DISTINCT care se folosește cu comanda SELECT.

•Exemplele din acest capitol folosesc baza de date din anexa 4.2 din curs.

Baze de Date

Anca Ion2011-2012

Structura unei interogari în SQL• Interogările în SQL pot fi foarte complexe. Structura de bază a unei

interogări în SQL este formată din 3 clauze: SELECT, FROM, și WHERE.

SELECT <lista atribute>FROM <lista tabele>WHERE <condiție>;

unde< lista atribute> este lista de nume a atributelor ale căror valori vor fi

afișate de interogare. < lista tabele >este lista cu numele tabelelor necesare pentru a

procesa interogarea. < condiție > este condiția booleană care selectează tuplurile care

trebuiesc afișate de interogare.

• În SQL, operatorii logici pentru compararea valorilor atributelor, fie între ele, fie cu alte valori constante sunt =, <, <=, >, >=, and <>. SQL are mai mulți operatori de comparație care vor fi prezentați pe parcursul capitolului.

Baze de Date

Anca Ion2011-2012

Exemple de utilizare a comenzii SELECT

• Să se afișeze ziua de naștere și adresa angajaților cu numele Ion Vasile.

SELECT DataNastere, AdresaFROM AngajatWHEREPrenume= ' ion' AND Nume= ' vasile';

• Această interogare folosește doar relația Angajat care apare în clauza FROM. Interogarea selectează tuplurile din tabela Angajat care satisfac clauza WHERE, apoi se face o proiecție după atributele DataNastere și Adresa care apar în clauza SELECT.

• Interogarea de mai sus este similară cu următoarea expresie în algebra relațională, exceptând faptul că, dacă există duplicate, acestea vor fi eliminate în algebra relațională.

∏DataNastere, Adresa (σ(Prenume=' ion' AND Nume=‘vasile') (Angajat))

Baze de Date

Anca Ion2011-2012

Exemple de utilizare a comenzii SELECT

• Să se afișeze numele și adresa angajaților care lucrează la departamentul 'Cercetare'.

SELECT Nume, Prenume,Adresa FROM Angajat, DepartamentWHERENrDep = ADep AND DenumireDep='Cercetare';

• Interogarea 2 este similară cu operațiile SELECȚIE-PROIECȚIE-JONCȚIUNE din algebra relațională.

• În clauza WHERE a interogării 2, condiția DenumireDep='Cercetare' reprezintă condiția de selecție și corespunde operației de selecție din algebra relațională.

• Condiția NrDep = Adep reprezintă condiția de joncțiune, care corespunde condiției de joncțiune din algebra relațională.

• În SQL, de obicei, se specifică într-o singură interogare mai multe operații de selecție și mai multe joncțiuni.

Baze de Date

Anca Ion2011-2012

Exemple de utilizare a comenzii SELECT

• În următorul exemplu se observă o interogare selecție-proiecție-joncțiune folosind 2 condiții de joncțiune.

• Pentru fiecare proiect cu locația în 'Craiova', să se afișeze numărul proiectului, numele departamentului care controlează proiectul, numele, adresa și ziua de naștere a managerului de departament.

SELECT NrProiect,DenumireDep,Nume, Adresa, DataNastereFROM Proiecte, Departament,AngajatWHERE Pdep=NrDep AND ManagerDep=CNP ANDLocatie='Craiova';

• Condiția de joncțiune Pdep=NrDep realizează legătura dintre proiectele și departamentele de care sunt controlate, iar condiția de joncțiune ManagerDep = CNP realizează legătura dintre departament și angajatul care este manager de departament.

Baze de Date

Anca Ion2011-2012

Ambiguitatea numelor, alias, variabile de tuplu

•În SQL se pot folosi aceleași nume pentru 2 sau mai multe atribute atâta timp cât ele sunt în relații diferite. •Dacă interogarea folosește atribute cu același nume, trebuie prefixat numele atributelor cu numele relațiilor pentru a preveni ambiguitatea.•De exemplu, în relațiile Departament și Locatii atributul NrDep apare în ambele relații cu același nume.

•Sa se afiseze locatiile departamentului Cercetare

SELECT Locatii.LocatieFROM Departament, LocatiiWHEREDepartament.DenumireDep='Cercetare' AND Departament.NrDep=Locatii.NrDep;

Sau

SELECT L.LocatieFROM Departament As D, Locatii As LWHERED.DenumireDep='Cercetare' AND D.NrDep=L.NrDep;

Baze de Date

Anca Ion2011-2012•Ambiguitatea poate să apară și în cazurile în care o interogare

folosește aceeași relație de 2 ori, ca în exemplu:•Pentru fiecare angajat, să se afișeze numele și prenumele său, și numele și prenumele supervizorului său direct.

SELECT A.Nume, A.Prenume, S. Nume, S.PrenumeFROM Angajat AS A, Angajat AS SWHEREA.SCNP=S.CNP;

•În acest caz, este permisă declararea de denumiri alternative A și S – numite alias-uri sau variabile tuplu- pentru aceeași relație Angajat. Aliasul poate să apară după cuvântul cheie AS, ca în exemplu precedent, sau poate să apară direct în clauza FROM după numele relației Angajat A, sau Angajat S.

•A și S sunt copii diferite ale relației Angajat; A reprezintă angajații care sunt supervizați, iar S reprezintă angajații care sunt supervizori. Între cele 2 copii se realizează o joncțiune A.SCNP=S.CNP. •Aceasta este un exemplu de interogare recursivă cu un singur nivel.

Ambiguitatea numelor, alias, variabile de tuplu

Baze de Date

Anca Ion2011-2012•Să se afișeze numele, prenumele, adresa angajaților care

lucrează la departamentul Cercetare.

SELECT A.Nume, A.Prenume, A. AdresaFROM Angajat A, Departament DWHERED.DenumireDep='Cercetare' AND D.NrDep=A.ADep;

Exemple de utilizare a comenzii SELECT

Baze de Date

Anca Ion2011-2012•Când clauza WHERE lipsește, nu există nici o condiție de selecție a tuplurilor.

Atunci toate tuplurile relației specificate in clauza FROM sunt selectate de interogare.

•Dacă una sau mai multe relații sunt specificate în clauza FROM, atunci PRODUSUL CARTEZIAN –toate combinațiile posibile de tupluri- al relațiilor este realizat.

•Interogarea 7 selectează toate CNP-urile din Angajat, iar interogarea 8 selectează toate combinațiile posibile ale CNP-urilor din Angajat cu DenumireDep din Departament.

Interogare7

SELECT CNPFROM Angajat

Interogare8

SELECT CNP, DenumireDepFROM Angajat, Departament

Interogarea 8 este similară unor operații de PRODUS CARTEZIAN și PROIECȚIE din algebra relațională.

Nespecificarea clazei WHERE și folosirea Asterisk-ului

Baze de Date

Anca Ion2011-2012•Pentru a afișa toate valorilor atributelor tuplurilor selectate, în

SQL nu trebuie date explicit numele tuturor atributelor, ci se poate folosi asterisk (*)-care înseamnă toate atributele. •De exemplu, interogarea 9 afișează toate valorile atibutelor din tabela Angajat care lucrează în departamentul cu numărul 1.

Interogarea 9SELECT *FROM AngajatWHERE ADep=1;

•Interogarea 10 afișează toate atributele din tabela Angajat și toate atributele din tabela Departament cu condiția ca angajatul sa lucreze pentru departamentul cu numele ‘Cercetare'.

Interogarea 10SELECT *FROM Angajat, DepartamentWHERE DenumireDep='Cercetare' AND NrDep=ADep;

Nespecificarea clazei WHERE și folosirea Asterisk-ului

Baze de Date

Anca Ion2011-2012•Interogarea 11 realizează produsul cartezian al înregistrărilor

din tabela Angajat și Departament.

Interogarea 11SELECT *FROM Angajat, Departament;

Nespecificarea clazei WHERE și folosirea Asterisk-ului

Baze de Date

Anca Ion2011-2012•Așa cum am menționat mai devreme, SQL nu tratează tabelele

ca mulțimi cu înregistrări unice, astfel tuplurile duplicat pot apărea în tabelă și în rezultatul interogării. •SQL nu elimină automat tuplurile duplicat din rezultatul interogărilor, din următoarele motive:

•Eliminarea duplicatelor este o operație consumatoare de timp; o modalitate de a implementa această operație este de a sorta tuplurile și apoi de a elimina duplicatele.•Utilizatorul poate dori să vizualizeze tuplurile duplicat în rezultatul interogărilor.•Când o funcție de agregare este aplicată tuplurilor, în cele mai multe cazuri nu dorim eliminarea duplicatelor.

Tabele ca mulțimi în SQL

Baze de Date

Anca Ion2011-2012•O tabelă în SQL cu o cheie este restricționată să fie mulțime,

deoarece valoarea cheii este unică pentru fiecare tuplu.•Dacă se dorește eliminarea tuplurilor duplicat din rezultatul unei interogări se folosește opțiunea DISTINCT în clauza SELECT, însemnând că numai tuplurile distincte trebuie să rămână ca și rezultat.

SELECT DISTINCT SalariuFROM Angajat

•Spre deosebire de interogarea precedentă, următoarea interogare afișează salariul fiecărui angajat, chiar dacă mai mulți angajați au același salariu .

SELECT ALL SalariuFROM Angajat

Tabele ca mulțimi în SQL

Baze de Date

Anca Ion2011-2012SQL include câteva operații pe mulțimi din algebra relațională:

•reuniune pe mulțimi (UNION)•diferența pe mulțimi (EXCEPT)•intersecție pe mulțimi (INTERSECT)

! Relațiile rezultate în urma acestor operații sunt mulțimi de tupluri, tuplurile duplicat fiind eliminate.

! Operațiile pe mulțimi pot fi aplicate doar pe relațiile compatibile cu reuniunea (care au același număr de atribute și domeniile atributelor corespunzătoare sunt aceleași).

Tabele ca mulțimi în SQL

Baze de Date

Anca Ion2011-2012•Să se afișeze lista cu proiectele (NrProiect) la care

lucrează un angajat cu numele ‘Popescu', ca angajat sau manager la departamentul care controlează proiectul.

(SELECT DISTINCT NrProiectFROM PROIECTE, DEPARTAMENT, ANGAJATWHERE PDep=NrDep AND ManagerDep=CNP AND Nume='popescu')UNION(SELECT DISTINCT PROIECTE.NrProiect FROM PROIECTE, ANGAJATIPROIECTE, ANGAJATWHERE PROIECTE.NrProiect=ANGAJATIPROIECTE.NrProiect AND PCNP=CNP AND Nume ='popescu');

•SQL oferă și operații care nu elimină duplicatele din relația rezultat: UNION ALL, EXCEPT ALL, INTERSECT ALL.

Tabele ca mulțimi în SQL-Exemple

Baze de Date

Anca Ion2011-2012 Pentru operațiile de comparare a șirurilor de caractere, în

SQL se folosește operatorul de comparație LIKE si NOT Like.

Subșirurile pot fi specificate folosind 2 caractere rezervate: %-înlocuiește un anumit număr de caractere și _(underscore) înlocuiește un singur caracter.

Exemple:

Să se afișeze angajații care au adresa în Craiova.

SELECT Nume, PrenumeFROM AngajatWHEREAdresa LIKE '%Craiova%';

Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL

Baze de Date

Anca Ion2011-2012 Exemple:

Să se afișeze angajații care a caror adresa se termina in %.

SELECT Nume, PrenumeFROM AngajatWHEREAdresa LIKE '%!%' Escape '!';

Dacă caracterele % și _ sunt folosite într-un șir de caractere ca și literali, ele trebuie precedate de caracterul ESCAPE ‘!’, care este specificat la sfârșitul șirului de caractere.

Exemplu: 'AB!_CD!%EF' ESCAPE '!' reprezintă șirul de caractere 'AB_CD%EF'.

Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL

Baze de Date

Anca Ion2011-2012•De asemenea, este nevoie de o regulă care să specifice

apostroful sau ghilimele simple daca acestea sunt incluse într-un șir de caractere, deoarece acestea sunt folosite și pentru a indica începutul și sfârșitul unui șir de caractere.

•Dacă este nevoie de un apostrof ('), acesta trebuie precedat de inca un apostroaf (‘) ca să nu fie interpretat ca și sfârșit de șir de caractere.

•Exemple:

SELECT Nume, Prenume, AdresaFROM AngajatWHERENume LIKE 'io''na';

Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL

Baze de Date

Anca Ion2011-2012•O altă caracteristică permisă în SQL, este folosirea operațiilor

aritmetice în interogări. Operațiile aritmetice standard de adunare (+), scădere(-), înmulțire(*), împărțire(/) pot fi aplicate pe valori numerice sau pe atributele al căror domenii sunt numerice. • Exemple:

•De exemplu, să presupunem că se dorește mărirea salariului angajaților care lucrează la proiectul 'ProductX' cu 10%.

SELECT Nume,Prenume, 1.1*Salariu AS SalariuMaritFROM Angajat, AngajatiProiecte, ProiecteWHERE CNP=PCNP AND Proiecte.NrProiect=AngajatiProiecte. NrProiect ANDDenProiect='ProdusX';

Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL

Baze de Date

Anca Ion2011-2012•Un alt operator de comparare este BETWEEN.

•Să se afișeze toți angajații din departamentul 5 cu salariul între 3000000 și 5500000.

SELECT *FROM AngajatWHERE (Salariu BETWEEN 3000000 AND 5500000) AND ADep =1;

•Condiția (Salariu BETWEEN 3000000 AND 5500000 ) este echivalentă cu ((Salariu >= 3000000) AND Salariu <= 5500000)).

Operații pe șiruri de caractere în SQL. Operații aritmetice în SQL

Baze de Date

Anca Ion2011-2012•SQL permite ordonarea tuplurilor din interogări după valorile

unuia sau mai multor atribute, folosind clauza ORDER BY.•Exemple:

•Să se afișeze lista angajaților și a proiectelor la care ei lucrează, ordonate după denumirea departamentului la care lucrează, și în cadrul fiecărui departament să se ordoneze alfabetic după nume și prenume.

SELECT DenumireDep, Nume, Prenume, DenProiectFROM Departament, Angajat, Proiecte, AngajatiProiecteWHERE Adep=NrDep and CNP=PCNP and Proiecte.NrProiect = AngajatiProiecte. NrProiectORDER BY DenumireDep, Nume, Prenume

Ordonarea rezultatelor interogărilor

Baze de Date

Anca Ion2011-2012•Ordonarea implicită este cea ascendentă a valorilor. Se pot

specifica tipurile de ordonare ascendentă și descendentă folosind cuvintele cheie ASC, rescpectiv DESC.

•De exemplu, putem ordona descendent după numele departamentului, și ascendent după nume și prenume:ORDER BY DenumireDep DESC, Nume ASC, Prenume ASC

Ordonarea rezultatelor interogărilor