Limbajul SQL : tipuri de instrucţiuni SQL, formatul frazei SELECT (INNER JOIN, LEFT/RIGHT

JOIN)

Limbajul SQL• este unul dintre cele mai vechi limbaje de programare• a fost implementat pentru prima la inceputul anilor ‘70 de către firma IBM în

scopul procesării datelor memorate în structuri relaționale. • are la bază algebra relațională, • obiectivele SQL au vizat de la început definirea structurilor de date și manipularea

ulterioară a datelor (operații de adăugare, ștergere, actualizare și consultare), precum și asigurarea unui set de instrucțiuni pentru controlul accesului la date.

• deși este prezentat în multe lucrări ca un limbaj declarativ , SQL prezintă în cele mai multe dintre versiuni și elemente caracteristice limbajelor procedurale

• de-a lungul timpului, limbajul SQL a fost adoptat și de către alți producători de sisteme de gestiune a bazelor de date, precum Oracle sau Microsoft, ceea ce a condus la apariția mai multor “dialecte” ale limbajului

• 1986 – a fost declarat de către ANSI (American National Standards Institute) drept limbaj standard utilizat în sistemele de gestiune pentru bazele de date relaționale

• 1987 – SQL a devenit standard ISO (International Organization for Standardization)

Limbajul SQL în Microsoft Access 2013• Dialectul SQL pentru Microsoft Access este o versiune simplificată a

limbajului T-SQL utilizat în MS SQL Server • Are particularități specifice produselor din pachetul Office și elemente de

sintaxă mai permisive• În spatele fiecărei interogări (query) realizată prin interfața grafică QBE se

generează automat codul în limbajul SQL aferent interogării. • Există situații în care anumite interogări complexe pot fi rezolvate mai

simplu prin scrierea codului direct în SQL • Varianta generată automat în SQL la realizarea interogării prin modul

Design (QBE) conține multe elemente opționale (precum parantezele sau denumirile de tabele ce preced numele de câmpuri), care fac instrucțiunile să pară mai complicate decât sunt în realitate• Generarea unui query prin interfața QBE poate fi utilizată pentru

învățarea limbajului SQL prin studierea codului generat automat de către sistem

Definirea unei interogări în limbajul SQL

Limbajul SQL – reguli de sintaxă• Se utilizează punctul “.” ca separator între numele tabelei și numele câmpului, atunci când o

interogare are ca sursă de date mai multe tabele sau interogări si există câmpuri ce au același nume în sursa de date. De exemplu: Factura.CodFurnizor;• Se utilizează parantezele drepte [ ] pentru a încadra nume de câmpuri ori tabele ce conțin

spații. De exemplu: [Concedii angajati].[Data Concediu]; • În cazul interogărilor parametrizate, parametrii se vor completa încadrați între paranteze

drepte. De exemplu: [Tastati cod furnizor];• Se utilizează virgula pentru a delimita elementele unei liste. De exemplu IN(10,11,16,20); • Se utilizează virgula pentru a delimita argumentele funcțiilor. De exemplu: Iif(Valoare>100,

Valoare*1.2, Valoare*1.15); • Valorile de tip șir de caractere se vor încadra între ghilimele. De exemplu “Ionescu Ion”; • Valorile de tip dată calendaristică se vor marca între simboluri #. De exemplu #12/10/2014#;• Orice frază SQL se va termina cu semnul “;” (în Access aceasta regula este opțională); • Limbajul SQL pentru Access nu face distincție între majuscule și minuscule; • Este permisă scrierea unei instrucțiuni pe unul sau mai multe rânduri.

Limbajul SQL – cuvinte cheie• Instrucţiunile care determină tipul de acțiune ce urmează a fi executat. De exemplu:

• CREATE (definirea structurii unui tabel) • INSERT (adăugarea de înregistrări)• DELETE (ștergerea de înregistrări)• UPDATE (actualizarea de înregistrări)• SELECT (consultarea unei surse de date și afișarea rezultatelor)

• Clauzele care determină sursa de date, impunerea de criterii de filtrare asupra sursei de date sau pot realiza ordonări ori grupări ale datelor. De exemplu:• FROM (stabilește sursa de date pentru o interogare SQL)• WHERE (impunerea unor condiții de selecție asupra înregistrărilor din sursa de date) • ORDER BY (sortarea înregistrărilor)• GROUP BY (gruparea datelor)

• Funcţiile cu rolul de a facilita efectuarea de calcule în cadru comenzilor SQL. De exemplu (Sum, Max, Avg, Year, Month, Iif, etc.)• Operatorii (aritmetici, logici, de atribuire si comparare)

Limbajul SQL – selecția datelor

SELECT [DISTINCT | DISTINCT ROW | TOP n ] listă câmpuri selectate/calculateFROM Sursa de date[ WHERE condiţii aplicate înregistrărilor negrupate (înainte de gruparea datelor)][ GROUP BY lista câmpuri de grupare ][ HAVING condiţii aplicate înregistrărilor grupate ][ ORDER BY câmp1 [ASC / DESC] [, camp2, ...] ]

Limbajul SQL – selecția datelorDISTINCT - elimină înregistrările duplicate returnate de interogare (duplicatele sunt identificate pe baza câmpurilor selectate);DISTINCTROW- elimină înregistrările duplicate returnate de interogare atunci când în sursa de date este formată din mai multe tabele;TOP N – afișează numai primele N înregistrări rezultate din interogare;FROM – specifică sursa de date (sursa poate fi compusă din unul sau mai multe tabele sau alte interogări)WHERE – permite definirea unor criterii de filtrare a datelor înainte de realizarea eventualelor grupări;GROUP BY – conține numele câmpurilor după care se vor grupa datele, separate prin virgulă;HAVING – permite adăugarea unor criterii de filtrare a datelor rezultate în urma grupării. Se utilizează pentru funcții de tip agregat (SUM, COUNT, MIN, MAX, etc.)ORDER BY – conține denumirile câmpurilor folosite la sortarea datelor. Cuvintele cheie ASC sau DESC semnifică sortarea ascendentă sau descendentă și sunt opționale. Implicit sortarea se realizează ascendent (ASC).

Limbajul SQL – selecția datelorAlte observaţii:• o comandă de tip SELECT trebuie să conțină obligatoriu instrucțiunea SELECT și

clauza FROM (celelalte elemente sunt opționale și se adaugă în funcție de cerințele informaționale pentru care a fost proictată interogarea). • câmpurile din lista ce urmează instrucțiunii SELECT sunt separate prin virgulă• atribuirea unui nume (alias) pentru un câmp calculat se realizează prin cuvântul

cheie AS (de exemplu Pret*Cantitate AS Valoare). • în SQL pentru Access nu se pot utiliza denumirile de tip alias pentru câmpuri în

clauzele WHERE, GROUP BY, HAVING sau ORDER BY.• dacă se dorește afișarea în lista de rezultate a tuturor câmpurilor din sursa de

date, lista de câmpuri ce urmează instrucțiunii SELECT poate fi înlocuită de simbolul *.

• Exemplul 1. Să se afișeze informațiile privind contractele realizate în zilele de 3 ianuarie 2014 și 9 ianuarie 2014, pentru care valoarea tarifului negociat este cuprinsă între 900000 lei și 1500000.

SELECT *FROM ContractWHERE (DataContract=#4/1/2014# OR DataContract=#9/1/2014# ) AND TarifNegociat>=900000 AND TarifNegociat<=1500000

SELECT *FROM ContractWHERE DataContract IN(#4/1/2014# , #9/1/2014# ) AND TarifNegociat BETWEEN 900000 AND 1500000

• Exemplul 2. Sa se afișeze lista ordonată alfabetic cu numele și prenumele angajaților născuți în luna aprilie care locuiesc în București (observație: adresele sunt înregistrate în formatul ” București, strada 1 Mai, nr 15”

SELECT NumeANG, PrenumeAngFROM AngajatWHERE CNPAngajat LIKE "???04*" AND Adresa NOT LIKE "Bucuresti*"ORDER BY NumeAng, PrenumeAng

• Exemplul 3. Care sunt ultimii 3 salariați angajați în firmă ?

SELECT TOP 3 NumeAng, PrenumeAngFROM AngajatORDER BY DataAng DESC

• Exemplul 4. Care sunt localitățile din care provin clienții firmei (fiecare localitate va figura o singură dată în listă) ?

SELECT DISTINCT LocalitateCL FROM Client

• Exemplul 5. Calculați pentru fiecare angajat numărul de zile lucrate la fiecare contract iar, pentru cei care au lucrat mai mult de 3 săptămâni la un contract, se va va afișa într-un câmp numit observații textul „De verificat”. Lista de rezultate va fi ordonată ascendent, după numărul contractului și, în cazul în care mai multe persoane au lucrat la același contract, se va ordona descrescător după numărul de zile lucrate.

SELECT CNPAngajat, NrContract, DataSfarsit-DataInceput AS ZileLucrate, Iif(ZileLucrate>21, "de verificat", "-") AS ObservatiiFROM LucreazaORDER BY NrContract ASC, DataSfarsit-DataInceput DESC

• Exemplul 6. Calculați pentru fiecare ofertă valabilă în data de 10 ianuarie 2014 tariful de bază calculat în euro. Cursul euro va fi specificat prin parametru în momentul interogării bazei de date.

SELECT CodOferta, TarifBaza, TarifBaza/[Tastati curs euro] AS TarifBazaEuroFROM OfertaWHERE DataInceputValab<=#10/1/2013# and DataSfarsitValab>=#10/1/2013#

Interogări cu sursă de date formată din mai multe tabele/interogări

• legăturile realizate prin intermediul câmpurilor cheie primară – cheie externă pot fi descrise în clauza FROM utilizând predicatul JOIN conform sintaxei:

FROM Tabel1 [INNER]|[RIGHT]|LEFT] JOIN Tabel2 ON Tabel1.Camp1=Tabel2.Camp2

• INNER JOIN (implicit), setul de rezultate al interogării va conține doar înregistările din Tabel1 care au corespondent în Tabel2 prin intermediul câmpului de legătură.

• RIGHT JOIN, setul de rezultate va conține toate înregistrările din Tabel2, indiferent dacă au sau nu corespondent în primul tabel (cel din stânga în acest caz).

• LEFT JOIN va conduce la preluarea în setul de rezultatea tuturor înregistrărilor din tabelul Tabel1. • În toate cazurile, setul final de rezultate va depinde de eventualele condiții precizate în clauzele

WHERE sau HAVING. • O soluție alternativă la descrierea legăturilor dintre tabele în clauza FROM o reprezintă

specificarea modului de compunere a înregistrărilor rezultate prin intermediul condițiilor din clauza WHERE (se condiționează ca valorile cheii primare să fie egale cu cele ale cheii externe corespondente).

• Exemplul 1 (JOIN): Să se afișeze lista alfabetică a contractelor nefinalizate, specificând și codul și numele clientului pentru fiecare dintre acestea.

SELECT DISTINCT NrContract, Client.CUIClient, DenumireCLFROM Client INNER JOIN Contract ON Client.CUIClient=Contract.CUIClientWHERE DataFinContract>Date()

• Exemplul 1 (WHERE):

SELECT DISTINCT NrContract, Client.CUIClient, DenumireCLFROM Client, Contract WHERE DataFinContract>Date() AND Client.CUIClient=Contract.CUIClient

• Exemplul 2 (LEFT/RIGHT JOIN): Să se afișeze lista contractelor ordonate cronologic și descrierea ofertei pentru fiecare dintre acestea.

SELECT DISTINCT NrContract, DataContract, DescriereOfertăFROM Contract LEFT JOIN Oferta ON Contract.CodOferta=Oferta.CodOfertaORDER BY DataContract

• Exemplul 3 (RIGHT JOIN) Care sunt clienții pentru care nu s-au întocmit contracte?

SELECT Client.CUIClient, DenumireCLFROM Contract RIGHT JOIN Client ON Contract.CUIClient=Client.CUIClientWHERE Contract.CUIClient IS NULL