Baze de dateLimbajul SQL

Obiective

Utilizarea limbajului SQL pentru a interoga o baz de date pentru a extrage informaii utile

Sa ne amintimCe este SQL?Funciile SQL (DLL, DML)Structura lexicala (cuvinte cheie, identificatori, constante si caractere speciale)Operatori (aritmetici, de comparatie, logici si relationali)Instructiuni DLLInstructiuni DML

Instructiunea SELECTSe folosete pentru a extrage date din baza de dateRezultatul este stocat ntr-un tabel-rezultat numit result-setSintaxa simplificat a acesteia este: SELECT [ALL/DISTINCT] coloana1, coloana2, FROM tabela1, tabela 2, [WHERE conditie] [clauze secundare]Clauzele secundare: GROUP BY, HAVING, ORDER BY

Instructiunea SELECTExemplu: S se afiseze toate localitatile din judetul Sibiu.SELECT nume_loc FROM LOCALITATIWHERE simbol_judet=SB

Nume_locSibiuMedias

Instruciunea SELECT DISTINCTntr-o tabel, unele coloane pot conine valori duplicate. Totui, uneori vrem s listm doar valorile diferite (distincte) din tabel.Cuvntul cheie DISTINCT poate fi folosit pentru aceasta.SintaxaSELECT DISTINCT coloana1 FROM tabela1

Exemplu SELECT DISTINCTS se afiseze o singura data simbolurile de judet din tabela LOCALITATI.SELECT DISTINCT simbol_judet FROM LOCALITATI

Simbol_judetBVCJPHSBTM

Clauza WHEREFolosit pentru a filtra nregistrriFolosit pentru a extrage doar nregistrrile care ndeplinesc un anumit criteriuSintaxaSELECT coloana1, coloana2,FROM tabel1WHERE coloana1 operator valoare

Exemplu clauza WHEREExemplu: S se afiseze cod_loc, simbol_judet, nume_loc din judetul Cluj.SELECT cod_loc, simbol_judet, nume_locFROM LOCALITATIWHERE simbol_judet ='CJ';

Cod_locSimbol_judetNume_loc313CJCluj-Napoca314CJHuedin315CJCampia Turzii

Ghilimele - apostroafeSQL-ul din ACCESS folosete ghilimele/apostrof pentru a delimita valorile de tip text/stringValorile numerice nu se delimiteaz cu ghilimeleCorect:SELECT * FROM Localitati WHERE Cod_jud=SBSELECT * FROM Facturi WHERE Pret=19650Greit:SELECT * FROM Localitati WHERE Cod_jud=SBSELECT * FROM Facturi WHERE Pret=19500

Operatorii AND i ORSe folosesc pentru a filtra nregistrrile dup mai multe condiiiExemplu:SELECT nume, adresa, banca_client FROM Date_Persoana WHERE nume='Popescu Andrei' AND banca_client='BCR'

SELECT nume, adresa, banca_client FROM Data_Persoana WHERE nume= 'Popescu Andrei' OR banca_client='BCR'

numeadresabanca_clientPopescu AndreiStr. Toamnei nr. 23BCR

numeadresabanca_clientPopescu AndreiStr. Toamnei nr. 23BCRVasilache MariaStr. Padina Nr. 123BCR

NULLReprezint date lips/necunoscute/inaplicabileImplicit o coloan poate conine valoarea NULLOperatori pentru NULL:IS NULLIS NOT NULLCnd valoarea unei coloane este opional putem aduga o nregistrare sau o putem actualiza fr a specifica o valoare pentru coloana respectiva. n acest caz se va salva valoarea NULL

NULLValoarea NULL este tratat diferit fa derestul valorilorSe folosete ca un marcator pentru datenecunoscute/inaplicabileNULL i 0 (zero) nu sunt echivalente saucomparabile

Operatorii IS NULL si IS NOT NULLExemplu:SELECT nume FROM DATE_PERSOANAWHERE email IS NULL;

SELECT nume FROM DATE_PERSOANAWHERE email IS NOT NULL;

numeemailIonescu AnaIordache Eugen

numeemailPopescu Andreiandrei@gmail.comVasilache Mariamaria@yahoo.com

Operatorii IN si NOT INSe folosesc pentru a filtra nregistrrile dup mai multe condiiiExemplu:SELECT nr_factura,pret FROM FACTURAWHERE pret IN (28000,30000,36000);

SELECT nr_factura,pret FROM FACTURAWHERE pret NOT IN (28000,30000,36000);

Nr_facturapret230000336000

Nr_facturapret1225

Operatorul LIKEFolosit n clauza WHERE pentru a specifica un ablon de cutare ntr-o coloanSintaxa:SELECT coloana1, coloana2, FROM tabel1WHERE coloana1 LIKE ablon Modaliti de utilizare:- pentru o expresie care ncepe cu o anumit liter, de exemplu litera A: LIKE A*;- pentru o expresie care se termin cu o anumit liter, de exemplu litera A: LIKE *A;- pentru o expresie care include o anumit liter, de exemplu litera A: LIKE *A*;

Operatorul LIKEModaliti de utilizare:pentru o expresie care ncepe cu o anumit liter, de exemplu litera A: LIKE A*;Exemplu:SELECT nume, email FROM DATE_PERSOANAWHERE nume LIKE 'I* pentru o expresie care se termin cu o anumit liter, de exemplu litera A: LIKE *A;SELECT nume, email FROM DATE_PERSOANAWHERE nume LIKE '*n

numeIonescu AnaIordache Eugen

numeIordache Eugen

Operatorul LIKEModaliti de utilizare:pentru o expresie care include o anumit liter, de exemplu litera A: LIKE *A*;

Exemplu:SELECT nume, email FROM DATE_PERSOANAWHERE nume LIKE '*ana*

numeIonescu Ana

Operatorul BETWEENFolosit n clauza WHERE pentru a stabili un interval de valori dup care se va face filtrarea datelorCapetele intervalului pot fi numere, text sau date calendaristiceSintaxa:SELECT coloana1, coloana2FROM tabel1WHERE coloana1 BETWEEN value1 AND value2

Operatorul BETWEEN - exempluExemplu:SELECT nr_factura, pret FROM FACTURAWHERE pret BETWEEN 20000 AND 40000;

nr_facturapret230000336000

Clauza ORDER BY - sortareCuvntul cheie ORDER BY se folosete pentru a sorta rezultatul dup o anumit coloan sau coloaneOrdonarea/sortarea se face n mod implicit cresctorOrdonarea descresctoare DESCSintaxa:SELECT coloana1, coloana2 FROM tabel1 ORDER BY coloana2 ASC | DESC

Clauza ORDER BY - exempluSELECT nume, banca_client FROM Date_Persoana ORDER BY nume DESC;

numebanca_clientVasilache MariaBCRPopescu AndreiBCRIordache EugenINGIonescu AnaBNR

Clauza TOPUtila cnd tabelul are foarte multe nregistrriReturnarea unui numr mare de nregistrri poate afecta performanaNu este suportat de toate SGBD-urileSintaxa SQLSELECT TOP numar | procent coloana1, coloana2, FROM tabel1

Clauza TOP - ExempluSELECT TOP 2 nume, adresa, banca_client FROM Data_Persoana

numeadresabanca_clientPopescu AndreiStr. Toameni nr. 23BCRIonescu AnaStr. Banatului nr. 2BRD

AliasSe poate da un nume alias unei tabele sauunei coloaneUtil cnd o tabel sau un cmp are un numelung sau complexUtil cnd vrem s dm un nume unei coloanecu valoare calculatInterogrile devin mai simplu de scris i decitit

AliasSintaxa pentru tabeleSELECT coloana1, coloana2, FROM tabela1AS alias_name

Sintaxa pentru coloaneSELECT nume_coloana AS nume_alias FROM tabela1

Funcii definite n SQLFuncii agregat: COUNT(), SUM(), MAX(), MIN(), AVG();Funcii scalarenumerice: sin(), cos(), tg(), ctg(), log(), ln(), lg(), pow(), etc.funcii pentru iruri de caractere: CONCAT(), LOWER(), UPPER(), LENGTH(), REPLACE(), SUBSTR();funcii pentru data calendaristic: DAY(), MONTH(),YEAR(), DATE();funcii de conversie:TO_CHAR(), TO_NUMBER(), TO_DATE()

Funcia COUNT()Calculeaz numrul de nregistrri carerespect un anumit criteriuValorile NULL nu vor fi numrateSintaxaSELECT COUNT(nume_coloana) FROMnume_tabelaPentru a afla numrul de nregistrri dintabelSELECT COUNT(*) FROM nume_tabela

Funcia COUNT() - exempluExemplu:SELECT COUNT(id_co) FROM CERERI_OFERTE

SELECT COUNT(id_co) AS nr_co FROM CERERI_OFERTE

expr10003

Nr_co3

Funcia AVG()Calculeaz valoarea medie pe o coloanSintaxaSELECT AVG(nume_coloana) FROM nume_tabelaExemplu:SELECT AVG(pret) AS Media FROM Factura WHERE MONTH(data_factura)=03;

Media18112,5

Funcia SUM()Calculeaza suma valorilor dintr-un atribut.SintaxaSELECT SUM(nume_coloana) FROM nume_tabelaExemplu:SELECT SUM(pret) AS Total FROM Factura WHERE MONTH(data_factura)=03;

Total36225

Funcia MAX()Determin cea mai mare valoare dintr-ocoloanSintaxa:SELECT MAX(nume_coloana) FROM nume_tabelaExemplu:SELECT MAX(pret) AS Pret_maximFROM FACTURA;

Pret_maxim36000

Date calendaristiceForma datei pe care ncercm s o insermtrebuie s se potriveasc cu formatul coloaneide tip dat din tabelAtunci cnd apare i partea de timp/orlucrurile se complicExist o serie de funcii predefinite pentruprocesarea informaiile legate de dat/or

Funcii calendaristice YEAR, DAY, MONTHFunciile YEAR, DAY, MONTH rein dintr-un cmp de tip dat calendaristic anul, ziua, respectiv luna.Exemplu:SELECT id_co, data_inreg FROM CERERI_OFERTEWHERE MONTH(data_inreg)=03;

id_codata_inreg110.03.2015216.03.2015326.03.2015

Clauza GROUP BYDe cele mai multe ori funciile de agregarefolosesc clauza GROUP BYAre rolul de a grupa datele dintr-una sau maimulte coloaneSintaxa:SELECT nume_coloana(e),functie_agregat(nume_coloana)FROM nume_tabelaWHERE nume_coloana operator valoareGROUP BY nume_coloana

Clauza GROUP BY - exempluExemplu:Afisati numarul de localitati din fiecare judet.SELECT simbol_judet, COUNT(cod_loc) AS nr_locFROM LOCALITATIGROUP BY simbol_judetRezultat

simbol_judetnr_locBV3SB2CJ2

Clauza HAVINGE nevoie de aceast clauz pentru ca WHEREnu poate fi folosit cu funciile de agregareSintaxaSELECT nume_coloana(e),functie_agregat(nume_coloana)FROM nume_tabelaWHERE nume_coloana operator valoareGROUP BY nume_coloanaHAVING functie_agregat (nume_coloana)operator valoare

Clauza HAVING - exempluExemplu:Afisati numarul de localitati din Brasov si Sibiu.SELECT simbol_judet, COUNT(cod_loc) AS nr_locFROM LOCALITATIGROUP BY simbol_judetHAVING simbol_judet='SB' OR simbol_judet='BV'Rezultat

simbol_judetnr_locSB2BV3

Operatorul UNIONFolosit pentru a combina dou sau mai multeinstruciuni SELECTFiecare instruciune SELECT trebuie s aibacelai numr de coloaneColoanele corespunztoare trebuie s aib iaceleai tipuriColoanele trebuie s fie i n aceeai ordine

Operatorul UNION - sintaxaDoar valorile distincteSELECT coloana1, coloana2, FROM tabela1UNIONSELECT coloana1, coloana2, FROM tabela2Permite valori duplicateSELECT coloana1, coloana2, FROM tabela1 UNION ALLSELECT coloana1, coloana2, FROM tabela2 Numele coloanelor din result-set vor fi numele coloanelor din primul SELECT

Operatorul UNION - exempluExemplu:SELECT simbol_judet, nume_judet FROM judeteUNIONSELECT simbol_judet, nume_judet FROM judete_bis

Rezultat

simbol_judetnume_judetABAlbaBCBacauBVBrasovSBSibiu

simbol_judetnume_judetABAlbaBCBacauBVBrasov

simbol_judetnume_judetBVBrasovSBSibiu

Operatorul UNION ALL - exempluExemplu:SELECT simbol_judet, nume_judet FROM judeteUNION ALLSELECT simbol_judet, nume_judet FROM judete_bis

Rezultat

simbol_judetnume_judetABAlbaBCBacauBVBrasovBVBrasovSBSibiu

simbol_judetnume_judetABAlbaBCBacauBVBrasov

simbol_judetnume_judetBVBrasovSBSibiu

Produsul cartezianPermite concatenarea inregistrarilor din doua sau mai multe tabeleIn practica produsul cartezian se utilizeaza doar impreuna cu alti operatoriSintaxaSELECT coloana1, coloana2, FROM tabela1, tabela2,

Produsul cartezian - exempluExemplu:SELECT nume_judet, cod_loc, nume_locFROM LOCALITATI, JUDETERezultat

nume_judetcod_locnume_locBacau123RupeaBrasov123RupeaSibiu123RupeaBacau124RasnovBrasov124RasnovSibiu124RasnovBacau125Fagaras

JonctiuneSe foloseste pentru a extrage date ntr-un result-set din dou sau mai multe tabele, pe baza unei relaii ntre anumite coloane din aceste tabeleSe realizeaza pe baza valorilor din atribute comune.

Jonctiune mai multe tipuriJOIN/INNER JOIN - extrage linii cnd este celpuin o potrivire n ambele tabeleLEFT JOIN - extrage toate liniile din tabela dinstnga, chiar dac nu au potriviri n tabela dindreaptaRIGHT JOIN - extrage toate liniile din tabeladin dreapta, chiar dac nu au potriviri ntabela din stnga

INNER JOINExtrage linii cnd este cel puin o potrivire nambele tabeleSintaxa:SELECT nume_coloana(e)FROM tabela1 INNER JOIN tabela2ON tabela1.nume_coloana =tabela2.nume_coloana Dac sunt linii n prima tabel care nu au corespondent n a doua atunci ele nu sunt extrase

INNER JOIN - exempluExemplu:Vrem s extragem persoanele pentru care aufost intocmite facturi.

SELECT DATE_PERSOANE.nume,,FACTURA.nr_facture FROM DATE_PERSOANEINNER JOIN FACTURA ON DATE_PERSOANE.cnp=FACTURA.cnpORDER BY DATE_PERSOANE.nume

INNER JOIN - exempluRezultat

FACTURAnr_facturacod_ofertadata_facturacnppretTVA1125.03.20151234567890123225242201.04.2015212345678901230000243331.03.201520123456789013600024

DATE_PERSOANAcnpnumeadresa1234567890123Popescu AndreiStr. Toameni nr. 231412451391023Iordache EugenStr. M. Viteazul nr. 52012345678901Vasilache MariaStr. Padina Nr. 1232123456789012Ionescu AnaStr. Banatului nr. 2

Query1numenr_facturaIonescu Ana2Popescu Andrei1Vasilache Maria3

LEFT JOINExtrage toate nregistrrile din table din stnga, chiar i atunci cnd nu sunt potriviri n tabela din dreaptaSintaxa:SELECT nume_coloana(e)FROM tabela1 LEFT JOIN tabela2 ON tabela1 tabela1. nume_coloana =tabela2 nume_coloana n unele SGBD-uri se numete LEFT OUTERJOIN

LEFT JOIN - exempluVrem s extragem datele tuturor persoanele indiferent dac facturi sau nu.SELECT DATE_PERSOANA.nume,FACTURA.nr_factura FROM DATE_PERSOANALEFT JOIN FACTURA ON DATE_PERSOANA.cnp=FACTURA.cnpORDER BY DATE_PERSOANA.nume

numenr_facturaIonescu Ana2Iordache EugenPopescu Andrei1Vasilache Maria3

RIGHT JOINExtrage toate nregistrrile din tabela dindreapta, chiar i cele pentru care nu suntpotriviri n tabela din stngaSintaxa:SELECT nume_coloana(e)FROM tabela1 LEFT JOIN tabela2 ON tabela1 tabela1. nume_coloana =tabela2 nume_coloana n unele SGBD-uri se numete RIGHT OUTERJOIN

RIGHT JOIN - exempluVrem s extragem datele tuturor persoanele indiferent dac facturi sau nu.SELECT DATE_PERSOANA.nume,FACTURA.nr_factura FROM DATE_PERSOANARIGHT JOIN FACTURA ON DATE_PERSOANA.cnp=FACTURA.cnpORDER BY DATE_PERSOANA.nume

numenr_facturaIonescu Ana2Popescu Andrei1Vasilache Maria3

Sa ne reamintimSELECT ALL/DISTINCTOperatorii: AND si OR, IS si IS NOT, IN si NOT IN, LIKE, BETWEEN, IS NULL si IS NOT NULLClauzele: WHERE, ORDER BY, TOP, GROUP BY, HAVINGAliasFunctii agregat: AVG, SUM, MAX, COUNTFunctii calendaristice: DAY, MONTH, YEAROperatorul UNIONJonctiune: INNER JOIN, LEFT JOIN, RIGHT JOIN