BAZE DE DATE

SQL (3)Fraza SELECT:expresii, predicate, funcii, grupuri

Universitatea Al.I. Cuza Iai Facultatea de Economie i Administrarea AfacerilorDepartamentul de Contabilitate, Informatic economic i Statistic Marin Fotache 1Constante, expresii, funcii sistemIn clauza SELECT pot fi plasate nu numai atribute din tabele, dar i:ConstanteFuncii sistemFuncii definite de utilizatorFuncii agregatExpresii de constante, atribute i funciiColoanele definite prin expresii pot avea un antet (nume) stabilit de utilizator O formul i o funcie-sistem

Expresii numerice (1)Care este, pentru fiecare produs din factura 1111, codul, cantitatea, preul unitar i valoarea fr TVA ? SELECT CodPr, Cantitate, PretUnit, Cantitate * PretUnit AS ValFaraTVAFROM liniifactWHERE NrFact = 1111

4Expresii numerice (2)SELECT Linie, lf.CodPr, DenPr, Cantitate, PretUnit, Cantitate * PretUnit AS ValFaraTVA, Cantitate * PretUnit * ProcTVA AS TVALinieFROM liniifact lf INNER JOIN produse p ON lf.CodPr=p.CodPr WHERE NrFact = 1111

Expresii numerice (3)

!!!!!!Coloane calculate - valori & denumiriCare este, pentru fiecare produs din factura 1111, codul, cantitatea, preul unitar i valoarea fr TVA ? SELECT CodPr, Cantitate, PretUnit, Cantitate * PretUnit AS "Valoare fr TVA"FROM liniifactWHERE NrFact = 1111

7Expresii-ir (1)SELECT 'Factura ' || NrFact || ' a fost emisa pe data ' || DataFact AS Concatenare_Oracle_PgSQLFROM facturi

8Expresii-ir (2)SELECT 'Factura ' || CAST (NrFact AS CHAR(8)) || ' a fost emisa pe data ' ||CAST (DataFact AS VARCHAR(10)) AS Concatenare_DB2_PgSQLFROM facturi

9Expresii-ir (3)SELECT CodCl, DenCl, Adresa, LENGTH(Adresa) AS "Lungimea adresei"FROM clienti

Funcii pentru date calendaristiceSELECT NrFact, DataFact, EXTRACT (YEAR FROM DataFact) AS "An_Factura",EXTRACT (MONTH FROM DataFact) AS "Luna_Factura",EXTRACT (DAY FROM DataFact) AS "Zi_Factura"FROM facturi

Expresii date calendaristice (scaden: 2 spt.)SELECT NrFact AS Factura, DataFact AS Data_Facturare, DataFact + 14 AS Scadenta_Incasare1,DataFact + INTERVAL '14 DAYS' AS Scadenta2,DataFact + INTERVAL '2 WEEKS' AS Scadenta3FROM facturi

12Expresii date calendaristice (scaden: 2 luni) PostgreSQL:SELECT NrFact AS Factura, DataFact AS Data_Facturare, DataFact + INTERVAL '2 MONTHS' AS Scadenta_IncasareFROM FACTURI

Oracle :

SELECT NrFact AS Factura, DataFact AS Data_Facturare, DataFact + INTERVAL '2' MONTH AS Scadenta_IncasareFROM FACTURI13Scaden: 1 an, 2 luni, 25 zile (1)Soluia 1 PostgreSQL:SELECT NrFact AS Factura, DataFact, DataFact + INTERVAL '1 YEAR' + INTERVAL '2 MONTH + INTERVAL '25 DAY' AS O_Data_ViitoareFROM FACTURI

Soluia 2 PostgreSQL:SELECT NrFact AS Factura, DataFact, DataFact + INTERVAL '1 YEAR 2 MONTH 25 DAY' AS O_Data_ViitoareFROM FACTURI14Scaden: 1 an, 2 luni, 25 zile (2)Soluia 1 Oracle (transformarea anilor n luni):SELECT NrFact AS Factura, DataFact, ADD_MONTHS(DataFact,14)+25 AS O_Data_ViitoareFROM FACTURI

Soluia 2 Oracle:SELECT NrFact AS Factura, DataFact, DataFact + INTERVAL '1-2' YEAR TO MONTH + 25 AS O_Data_ViitoareFROM FACTURI15Diferena a dou date calendaristice(interval) - 1SELECT NrFact, DataFact, CURRENT_DATE, CURRENT_DATE - DataFact AS Interval1,AGE( CURRENT_DATE, DataFact) AS Interval2FROM facturi

Diferena a dou date calendaristice(interval) - 2SELECT NrFact, DataFact, CURRENT_DATE, AGE (CURRENT_DATE, DataFact) AS Interval, EXTRACT (YEAR FROM AGE( CURRENT_DATE, DataFact)) AS Interval_Ani, EXTRACT (YEAR FROM AGE( CURRENT_DATE,DataFact)) * 12+ EXTRACT (MONTH FROM AGE( CURRENT_DATE,DataFact)) AS Interval_LuniFROM facturi

17Conversii generalizate: CASTSELECT 'Scadenta facturii ' || TRIM( TRAILING FROM CAST (NrFact AS CHAR(8)) ) || ' (trimisa clientului ' || dencl || ') are scadenta pe ' || CAST ((DataFact + INTERVAL '14' DAY) AS CHAR(10) ) AS Scadente_facturi_Sept2011FROM facturi f INNER JOIN clienti c ON f.CodCl=c.CodClWHERE EXTRACT (YEAR FROM datafact) = 2011 AND EXTRACT (MONTH FROM datafact)=9

Ordonarea nregistrrilor n rezultatS se obin, n ordinea descresctoare a indicativului judeelor, lista localitilor n ordinea cresctoare a denumirii.

SELECT Jud, Loc, CodPost FROM coduri_postaleORDER BY Jud DESC, Loc ASC19Operatorul BETWEENCare sunt facturile emise n perioada 3-5 august 2011 ? SELECT * FROM facturi WHERE DataFact BETWEENDATE'2011-08-03' AND TO_DATE('05/08/2011','DD/MM/YYYY') ;

20Operatorul OVERLAPSSELECT IdExaminare,DataOra_Examinare, IdPacient, IdDoctorFROM triaj INNER JOIN garzi ON (triaj.dataora_examinare, triaj.dataora_examinare) OVERLAPS (garzi.inceput_garda , garzi.sfirsit_garda)

Care este ID-ul fiecrui doctor care a examinat cazurile din triaj ? Criterii inexacte. Operatorul LIKE-Ce persoane au nume care conin litera S pe a treia poziie ?SELECT * FROM persoaneWHERE Nume LIKE '__s%' OR Nume LIKE '__S%'

22Operatorul LIKE (2)Care sunt persoanele ale cror prenume conine secvena ION sau IOAN? SELECT * FROM persoaneWHERE UPPER(Prenume) LIKE '%ION%' OR UPPER(Prenume) LIKE '%IOAN%'

Operatorul LIKE (3)Care sunt persoanele ce trebuie felicitate de Sf.Ion? SELECT * FROM persoaneWHERE UPPER(Prenume) LIKE 'ION%' OR UPPER(Prenume) LIKE 'IOAN%' OR UPPER(Prenume) LIKE '% ION%' OR UPPER(Prenume) LIKE '% IOAN%' OR UPPER(Prenume) LIKE '%-ION%' OR UPPER(Prenume) LIKE '%-IOAN%'

24ILIKE cutare nesenzitivSELECT * FROM persoaneWHERE Prenume ILIKE 'ION%' OR Prenume ILIKE 'IOAN%' OR Prenume ILIKE '% ION%' OR Prenume LIKE '% IOAN%' OR Prenume LIKE '%-ION%' OR Prenume LIKE '%-IOAN%'

Expresii obinuite (Regular Expressions)SIMILAR TO (SQL:1999)Mecanism elegant de cutare preluat din UNIX (Posix)Caracterele de baz pentru construirea mtilor sunt tot procent (%) i underscore (_) Alte caractere disponibile :| - pentru structuri alterative;* - pentru repetarea irului (de caractere) precedent de zero sau mai multe ori;+ - pentru repetarea irului precedent de unu sau mai multe ori;() pentru gruparea mai multor iruri de caractere ntr-o unitate logic;[] pentru indicarea unei clase de caractereSIMILAR TO (2)SELECT * FROM persoaneWHERE UPPER(Prenume) SIMILAR TO 'ION%' OR UPPER(Prenume) SIMILAR TO 'IOAN%' OR UPPER(Prenume) SIMILAR TO '% ION%' OR UPPER(Prenume) SIMILAR TO '% IOAN%' OR UPPER(Prenume) SIMILAR TO '%-ION%' OR UPPER(Prenume) SIMILAR TO '%-IOAN%'

Care sunt persoanele ce trebuie felicitate de Sf.Ion? SIMILAR TO (3)Care sunt persoanele ale cror prenume conine secvena ION sau IOAN? SELECT * FROM persoaneWHERE UPPER(Prenume) SIMILAR TO '%(| |-)IO(|A)N(|A)%'

SIMILAR TO (4)Care sunt persoanele ce trebuie felicitate de Sf.Ion? SELECT * FROM persoaneWHERE UPPER(Prenume) SIMILAR TO'(|% |%-)(I|)O(|A)N%'

Obs. Soluia este mai bun dect cea cu LIKE/ILIKE, ntruct extrage i Oanele

SIMILAR TO (5)Care sunt clienii cu sediul la un numr (de pe orice strad) care conine cifre 2, 3 sau 5 ?SELECT * FROM clientiWHERE Adresa SIMILAR TO '%[235]+%'

Operatorul INCare sunt localitile din judeele Iai (IS), Vaslui (VS) i Timi (TM) ?

Fr operatorul IN:SELECT DISTINCT loc, judFROM coduri_postale WHERE Jud = 'IS' OR Jud = 'VS' OR Jud = 'TM'ORDER BY Jud, LocCu operatorul IN:SELECT DISTINCT loc, judFROM coduri_postaleWHERE Jud IN ('IS', 'VS', 'TM')ORDER BY Jud, Loc31Funcii agregatDintr-un set de nregistrri obin o singur valoare (agregat)COUNT, SUM, AVG, MIN, MAXCORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMPRestricie: ntr-o clauz SELECT, o funcie agregat trebuie s apar numai:Singurnsoit numai de alte funcii agregatnsoit de atribute i funcii ne-agregat, dar n prezena clauzei GROUP BY

Funcia COUNT (1)Ci clieni are firma ?SELECT COUNT (*) AS NrClienti FROM clientisauSELECT COUNT (CodCl) AS NrClienti FROM clienti

Funcia COUNT (2)Cte linii are produsul cartezian al tabelelor FACTURI i LINIIFACT ?SELECT COUNT(*) FROM facturi CROSS JOIN liniifact

Funcia COUNT (3)Pentru ci clieni se cunoate adresa ?SELECT COUNT (Adresa) AS NrClienti_cu_adresa_cunoscuta FROM clienti

Funcia COUNT (4)Cte facturi au fost emise clienilor din judeul Vaslui ?

SELECT COUNT(NrFact) AS NrFacturi FROM facturi f INNER JOIN clienti c ON f.codcl=c.codcl INNER JOIN coduri_postale cp ON c.codpost=cp.codpost INNER JOIN judete j ON cp.jud=j.judWHERE Judet='Vaslui'36Funcia COUNT (5)n cte zile s-au nregistrat vnzri ?

SELECT COUNT(DataFact) AS NrZileVnz_Gresit FROM facturi

SELECT COUNT(DISTINCT DataFact) AS NrZileVnz FROM facturi

37Funcia SUM (1)Care este valoarea fr TVA a facturii 1111? SELECT SUM(Cantitate * PretUnit) AS ValFaraTVA FROM liniifact WHERE NrFact = 1111

Funcia SUM (2)Care este valoarea fr TVA a facturilor ntocmite pe 7 august 2011 ?SELECT SUM(Cantitate * PretUnit) AS ValFaraTVA_7aug2011FROM liniifact lf INNER JOIN facturi f ON lf.NrFact=f.NrFactWHERE DataFact = DATE'2011-08-07'

Funcia SUM (3)Care sunt cele trei valori: fr TVA, TVA i total ale facturii 1111 ? SELECT SUM(Cantitate * PretUnit) AS ValFaraTVA, SUM(Cantitate * PretUnit * ProcTVA) AS TVA, SUM(Cantitate * PretUnit + Cantitate * PretUnit * ProcTVA) AS ValTotalaFROM liniifact lf INNER JOIN facturi f ON lf.NrFact=f.NrFactINNER JOIN produse p ON lf.CodPr=p.CodPrWHERE f.NrFact=1111

40O

alt

vari-ant

de

afi-areSELECT '1' AS " ",'Valoarea fara TVA' AS "Factura 1111",TRUNC(SUM(Cantitate * PretUnit)) AS SumaFROM liniifact lf INNER JOIN produse p ON lf.CodPr=p.CodPrWHERE NrFact = 1111UNION SELECT '2','TVA', TRUNC(SUM(Cantitate * PretUnit * ProcTVA))FROM liniifact lf INNER JOIN facturi f ON lf.NrFact=f.NrFact INNER JOIN produse p ON lf.CodPr=p.CodPrWHERE lf.NrFact = 1111 UNION SELECT '3','Valoarea totala', TRUNC(SUM(Cantitate*PretUnit*(1+ProcTVA)))FROM liniifact lf INNER JOIN facturi f ON lf.NrFact=f.NrFact INNER JOIN produse p ON lf.CodPr=p.CodPrWHERE lf.NrFact = 1111ORDER BY 1

S se afieze valorile (fr TVA, TVA, cu TVA) liniilor facturii 1111, plus o linie de total.

SELECT linie AS "Linia", DenPr AS "Produs", Cantitate AS "Cantitate", TRUNC(PretUnit) AS "Pret Unitar", TRUNC(Cantitate * PretUnit) AS "Val. fara TVA", TRUNC(Cantitate * PretUnit * ProcTVA) AS "TVA linie", TRUNC(Cantitate * PretUnit * (1 + ProcTVA)) AS "Val cu TVA linie"FROM liniifact lf INNER JOIN produse p ON lf.CodPr=p.CodPrWHERE NrFact = 1111UNIONSELECT 101, ' TOTAL factura ', NULL, NULL, TRUNC(SUM(Cantitate * PretUnit)), TRUNC(SUM(Cantitate * PretUnit * ProcTVA)), TRUNC(SUM(Cantitate * PretUnit * (1 + ProcTVA)))FROM liniifact lf INNER JOIN produse p ON lf.CodPr=p.CodPrWHERE NrFact = 1111ORDER BY 1

Funcia AVG (1)Care este media valorilor (cu TVA) la care a fost vndut Produs 1? SELECT 'Val. medie a vinzarilor Produs 1' AS Explicatii,ROUND(AVG(Cantitate*PretUnit*(1+ProcTVA)),2) AS "Valoare (cu TVA) medie"FROM liniifact lf INNER JOIN produse p ON lf.CodPr=p.CodPrWHERE DenPr = 'Produs 1'

43MAX & MIN (1)Care este primul client i ultimul client (n ordinea numelui) din judeul IaiSELECT MIN(DenCl) AS Primul_Client,MAX(DenCl) AS Ultimul_ClientFROM clienti c INNER JOIN coduri_postale cp ON c.codpost=cp.codpost INNER JOIN judete j ON cp.jud=j.jud WHERE Judet = 'Iasi'

MAX & MIN (2)Care este cea lungimea maxim a unei adrese de firm-client ?

SELECT MAX(LENGTH(Adresa)) AS "LgMxAdr"FROM clienti

MAX & MIN (3)Care este firma client cu lungimea maxim a adresei ?SELECT DenCl, Adresa, MAX(LENGTH(Adresa)) AS "LgMxAdr"FROM clienti

Grupuri GROUP BYFunciile agregat pot fi aplicate la nivel de grup de nregistrriRezultatul are un numr de linii egal cu numrul de valori distincte ale atributu-lui/coloanei de grupareGrupurile pot fi filtrate folosind un predicat cu o clauz HAVING

Clauza GROUP BY (1) Care este valoarea fr TVA a facturilor cu numere cuprinse ntre 1111 i 1119?

SELECT NrFact, SUM(Cantitate*PretUnit) AS ValFaraTVAFROM liniifact WHERE NrFact BETWEEN 1111 AND 1119 GROUP BY NrFactORDER BY NrFact48Clauza GROUP BY (2) 1. Din tabela LINIIFACT se extrag numai tuplurile n care valorile atributului NrFact sunt cuprinse ntre 1111 i 1119.

Clauza GROUP BY (3) 2. Se constituie cte un grup pentru fiecare valoare distinct a NrFact

Clauza GROUP BY (4) 3. n interiorul fiecrui grup se execut funcia-agregat SUM(Cantitate * PretUnit)

3658750.00112400.0097500.005070850.00138750.00116250.00195000.00172500.004861650.00Clauza GROUP BY (5) 4. Se obine rezultatul al crui numr de linii coincide cu valorile distincte ale NrFact. Ordinea liniilor nu este garantat n lipsa clauzei ORDER BY

Cte facturi au fost emise n fiecare zi cu vnzri ?

COUNTGROUP BY DataFactFROM facturi SELECT DataFact, COUNT(*) Ordinea grupurilorSELECT DataFact, COUNT(*) FROM facturi GROUP BY DataFact

SELECT DataFact, COUNT(*) FROM facturi GROUP BY DataFactORDER BY 1

Care este valoarea zilnic a vnzrilor

GROUP BY DataFactFROM facturi f INNER JOIN liniifact lf ON f.NrFact=lf.NrFact INNER JOIN produse p ON lf.CodPr=p.CodPrSELECT DataFact, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS ValTotalaORDER BY DataFactSELECT DataFact, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS ValTotalaFROM facturi f INNER JOIN liniifact lf ON f.NrFact=lf.NrFact INNER JOIN produse p ON lf.CodPr=p.CodPrGROUP BY DataFact ORDER BY DataFact Valoarea zilnic a vnzrilor (2)

Grupri dup dou sau mai multe criteriiUna sau mai multe funcii agregat pot aprea n clauzele SELECT i GROUP BY n combinaie cu dou sau mai multe atribute sau expresiiOrdinea enumerrii atributelor expresiilor (ce preced funciile agregat) trebuie s fie identic n clauzele SELECT i GROUP BYO grupare eronatSELECT DenPr, UM, SUM(Cantitate) AS Cantitativ, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS ValoricFROM liniifact lfINNER JOIN produse p ON lf.CodPr=p.CodPrGROUP BY DenPr

Mesaj PostgreSQL de grupare eronat

Vnzri, pe clieni & zile SELECT DenCl AS Client, DataFact AS Data,SUM(Cantitate * PretUnit * (1+ProcTVA)) AS VinzariFROM liniifact lf INNER JOIN produse p ON p.CodPr = lf.CodPrINNER JOIN facturi f ON lf.NrFact = f.NrFactINNER JOIN clienti c ON f.CodCl = c.CodClGROUP BY DenCl, DataFactORDER BY DenCl, DataFact60

Subtotaluri (1)GROUP BY DenCl, DataFactGROUP BY DenClSubtotaluri (2)SELECT DenCl AS Client, DataFact AS Data, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS VinzariFROM liniifact lf INNER JOIN produse p ON p.CodPr = lf.CodPrINNER JOIN facturi f ON lf.NrFact = f.NrFactINNER JOIN clienti c ON f.CodCl = c.CodClWHERE EXTRACT (YEAR FROM DataFact) = 2011 AND EXTRACT (MONTH FROM DataFact)=9GROUP BY DenCl, DataFactUNIONSELECT DenCl || '-Subtotal', NULL, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS VinzariFROM liniifact lf INNER JOIN produse p ON p.CodPr = lf.CodPrINNER JOIN facturi f ON lf.NrFact = f.NrFactINNER JOIN clienti c ON f.CodCl = c.CodClWHERE EXTRACT (YEAR FROM DataFact) = 2011 AND EXTRACT (MONTH FROM DataFact)=9GROUP BY DenClORDER BY 1,2Subtotal i total general (1)GROUP BY DenCl, DataFactGROUP BY DenClFr grupare

Subtotal i total general (2)SELECT DenCl AS Client, DataFact AS Data, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS VinzariFROM liniifact lf INNER JOIN produse p ON p.CodPr = lf.CodPr INNER JOIN facturi f ON lf.NrFact = f.NrFact INNER JOIN clienti c ON f.CodCl = c.CodCl WHERE EXTRACT (YEAR FROM DataFact) = 2011 AND EXTRACT (MONTH FROM DataFact)=9GROUP BY DenCl, DataFactUNIONSELECT DenCl || '-Subtotal', NULL, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS VinzariFROM liniifact lf INNER JOIN produse p ON p.CodPr = lf.CodPr INNER JOIN facturi f ON lf.NrFact = f.NrFact INNER JOIN clienti c ON f.CodCl = c.CodClWHERE EXTRACT (YEAR FROM DataFact) = 2011AND EXTRACT (MONTH FROM DataFact)=9GROUP BY DenClUNIONSELECT 'TOTAL ', NULL, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS VinzariFROM liniifact lf INNER JOIN produse p ON p.CodPr = lf.CodPr INNER JOIN facturi f ON lf.NrFact = f.NrFact INNER JOIN clienti c ON f.CodCl = c.CodClWHERE EXTRACT (YEAR FROM DataFact) = 2011 AND EXTRACT (MONTH FROM DataFact)=9ORDER BY 1,2

Gruparea dup expresii (1)Care este valoarea vnzrilor din fiecare zi a sptmnii ?SELECT TO_CHAR(DataFact, 'day') AS Zi_Saptamina,TRUNC(SUM(Cantitate * PretUnit * (1+ProcTVA)),0) AS Vinzari_Zi_SaptFROM facturi f INNER JOIN liniifact lf ON f.Nrfact=lf.NrFactINNER JOIN produse p ON lf.CodPr=p.CodPr GROUP BY TO_CHAR(DataFact, 'day')

Gruparea dup expresii (2)S se afle numrul de facturi ntocmite n fiecare lun (calendaristic).SELECT EXTRACT (YEAR FROM DataFact) AS An, EXTRACT (MONTH FROM DataFact) AS Luna, COUNT(*) AS Nr_facturiFROM facturiGROUP BY EXTRACT (YEAR FROM DataFact), EXTRACT (MONTH FROM DataFact)ORDER BY 1,2

Gruparea dup expresii (3)S se calculeze vnzrile sptmnaleSELECT DATE_TRUNC('week',DataFact) AS "De la...",DATE_TRUNC('week',DataFact) + INTERVAL '6 DAYS' AS "Pana la...",TRUNC(SUM(Cantitate * PretUnit * (1+ProcTVA)),0) AS Vinzari_SaptFROM facturi f INNER JOIN liniifact lf ON f.Nrfact=lf.NrFactINNER JOIN produse p ON lf.CodPr=p.CodPr GROUP BY DATE_TRUNC('week', DataFact), DATE_TRUNC('week', DataFact) + INTERVAL '6 DAYS'ORDER BY 1

Clauza HAVING (1)Care sunt zilele n care s-au ntocmit cel puin trei facturi ?SELECT DataFact AS "Zi", COUNT(*) AS "Numar facturi"FROM facturiGROUP BY DataFactHAVING COUNT(*) >= 369

Clauza HAVING (2)SELECT DataFact AS "Zi", COUNT(*) AS "Numar facturi" FROM facturiGROUP BY DataFactSELECT DataFact AS "Zi", COUNT(*) AS "Numar facturi"FROM facturiGROUP BY DataFactHAVING COUNT(*) >= 3

70Clauza HAVING (3)In ce zile s-au emis mai multe facturi decat pe 2 august 2007?Vezi tutorialul video de la adresa:http://1drv.ms/1rLaUZm