Functii SQL

Functii SQL

1. Functii scalare

Obs. 1. Functiile scalare efectueaza un calcul si returneaza o valoare

2. Se apeleaza in cadrul instructiunii SELECT

SELECT functia(arg1,arg2,); pentru argumente constante (vezi Anexa C)

SELECT functia(arg1,arg2,) FROM nume_tabel;

pentru argumente atribute ale unei relatii

3. NU se insereaza spatiu intre functia apelata si paranteza care contine argumentul / argumentele functiei; intre argumentele functiei se pot insera spatii

Functii de comparatie:

GREATEST(expr1,expr2,) returneaza valoarea argumentului cea mai mare.

LEAST(expr1,expr2,) returneaza valoarea argumentului cea mai mica.

IF(expresie_de_comparat,expr1,expr2) returneaza expr1 daca expresia de comparat este adevarata si returneaza expr2 in caz contrar.

IFNULL(expr1,expr2) returneaza expr1 daca valoarea expresiei expr1 NU este NULL si returneaza expr2 daca valoarea expresiei expr1 este NULL.

ISNULL(expr1,expr2) returneaza 1 daca valoarea expresiei este NULL si 0 in caz contrar.

Functii numerice:

ABS(x) returneaza valoarea absoluta a numarului x;

CEILING(x) cel mai mic intreg, dar nu mai mic ca x;

FLOOR(x) cel mai mare intreg, dar nu mai mare ca x;

POW(x,y), POWER(x,y) x^y;

ROUND(x,d) valoarea lui x rotunjita la d cifre dupa virgula;

SQRT(x) radacina patrata a lui x, x>=0;

TRUNCATE(x,d) returneaza x cu partea zecimala trunchiata la d cifre dupa virgula;

Functii logaritmice: LOG(x) logaritmul natural al numarului x; LOG10(x) -logaritmul zecimal al lui x, x diferit de 0;

Functii trigonometrice: ACOS(x) valoarea arcului a carui cosinus este x, x [-1,1]; daca x nu apartine intervalului [-1,1], rezultatul returnat este NULL. ASIN(x), ATAN(x), COS(x), SIN(x), TAN(x); PI() returneaza valoarea pi; DEGREES(x) transforma radiani in grade; RADIANS(x) transforma gradele in radiani;

Functii sir de caractere:

ASCII(sir returneaza codul ASCII pentru primul caracter al expresiei;

CHAR(n1,n2,) returneaza caracterul corespunzator valorilor n1,n2, din codul ASCII;

CONCAT(sir1,sir2,) concateneaza sirurile de caractere;

CONV(n,din_baza,in_baza) returneaza reprezentarea sir a numarului n din_baza in_baza;

INSERT(sir,poz,lung,sir_nou) returneaza sirul sir inlocuind sub-sirul care incepe la pozitia poz si care are lung caractere cu subirul sir_nou;

LCASE(sir) returneaza sirul sir cu minuscule; LOWER(sir);/UCASE(sir) returneaza sirul sir cu majuscule;UPPER(sir);

LEFT(sir,lung) returneaza primele lung caractere ale sirului sir incepand de la stanga;/RIGHT(sir,lung) returneaza primele lung caractere ale sirului sir incepand de la dreapta;

LENGTH(sir) reurneaza lungimea sirului sir; CHAR_LENGTH();

LOCATE(sub_sir,sir) returneaza pozitia primei aparitii a subsirului sub_sir in sir; (case sensitive);

LPAD(sir,n,subsir) completeaza la stanga cu subsir pana la atingerea unei lungimi specificate;

LTRIM(sir) returneaza sirul fara spatii la stanga;/RTRIM(sir) returneaza sirul fara spatii la dreapta;/TRIM(sir) elimina spatiile din ambele capete;

REPLACE(sir,din_sir,in_sir) retunreaza sirul sir in care se inlocuieste secventa de caractere din_sir cu secventa de caractere in_sir;

SUBSTR(sir,n) extragere o portiune dintr-un sir incepand cu al n-lea caracter;

Functii de tip data si ora:

CURDATE() returneaza data curenta sub forma 'aaaa-ll-zz'; CURRENT_DATE varianta fara paranteze;

CURTIME() timpul actual sub forma 'oo:mm:ss'; CURRENT_TIME varianta fara paranteze;

NOW() returneaza data si ora curenta 'aaaa-ll-zz oo:mm:ss'; CURRENT_TIMESTAMP;

DATE_ADD(data,INTERVAL expr) returneaza data la care se adauga intervalul de timp exprimat in expr. Valorile expresiei expr pot fi n YEAR, n MONTH, n DAY, n HOUR, etc. unde n este un intreg pozitiv sau negativ (pentru alte valori vezi tabelul de la pg. 551, Anexa C);

DATE_FORMAT(data, expr) returneaza data in formatul dorit. In expresia expr se poate specifica, de exemplu '%y' - afiseaza anul cu doua cifre, '%H' - afiseaza ora cu doua cifre, de la 1 la 24, etc. (vezi tabelul de la pg. 552, Anexa C, cu specificatorii de formare ai expresiei functiei DATE_FORMAT);/ TIME_FORMAT(timp,expr);

DAY(data), MONTH(data), YEAR(data) returneaza ziua, luna respectiv anul continut in valoarea data;

SECOND(timp), MINUT(timp), HOUR(timp) retunreaza secundele, minutele respectiv ora incluse in valoarea timp;

Pb1.

1. Efectuati diferenta dintre cel mai mare si cel mai mic numar al sirului -3.2, -3, 3.2 (afisati  rezultatul ca numar zecimal, apoi ca intreg).
2. Aflati valoarea de adevar a urmatoarelor propozitii matematice 2.3>=-3.2, 5!=5, A=a, A=a in modul BINAR folosind mesaje ale expresiilor expr1, expr2 din multimile respectiv .
3. Exemplificati functionalitatea functiei IFNULL.

R: a)

SELECT greatest least

Sau

SELECT round greatest least
apoi
SELECT round greatest least
b)

SELECT if >= 'adevarat' 'fals'

SELECT if AS Comparatie2

SELECT if 'A' 'a' 'corect' 'incorect' AS Comparatie3

SELECT if BINARY 'A' 'a' 'A=a in modul Binar' 'A<>a in modul binar'

Pb.2. Folosind baza de date LABORATOR3 care contine tabelul PROGRAM(nr_ore_propuse,nr_ore_predate,lectia,data,pret) sa se realizeze urmatoarele cerinte:

a) comparati numarul de ore propuse cu numarul de ore predate afisand numarul de ore mai mare (apoi mai mic) dintre cele doua, in fiecare caz in parte.

b) verificati in care dintre inregistrarile tabelului PROGRAM s-a omis programarea unui anumit numar de ore afisand, acolo unde este cazul, mesajul: 'Nu s-a introdus nicio ora!'.

c) verificati daca lectiile se desfasoara in data curentra ; in caz afirmativ, afisati numele lectiei respective, iar in caz contrar afisati data cand a fost programata spre predare lectia respectiva.

R : a)

SELECT greatest nr_ore_propuse nr_ore_predate AS 'Nr de ore mai mare'
FROM program;

SELECT least nr_ore_propuse nr_ore_predate AS 'Nr de ore mai mic'
FROM program;

b)

SELECT ifnull nr_ore_propuse 'NU s-a introdus nicio ora!' AS 'Ore propuse' IFNULL nr_ore_predate 'NU s-a introdus nicio ora!' AS 'Ore predate'
FROM program

c)

SELECT if DATA curdate lectia data
FROM program;

Pb.3. Folosind baza de date LABORATOR3 care contine tabelul PROGRAM(nr_ore_propuse,nr_ore_predate,lectia,data, pret) sa se realizeze urmatoarele cerinte:

a) Afisati data programarii lectiilor modificand-o pentru anul 2010 (folositi mai multe functii pentru a realiza inlocuirea sirului de caractere cu cel dorit) ;

b) Afisati data corespunzatoare fiecarei lectii defalcata pe zi, luna si an sub forma Ziua :. Luna :. Anul : .

c) Aflati din cate caractere este alcatuit fiecare titlu de lectie ;

d) Afisati cu majuscule titlurile lectiilor astfel incat cuvantul  'BD' sa fie inlocuit cu expresia 'baza de date';

e) Calculati numarul de zile scurs de la data predarii lectiilor la data curenta.

R: a)

SELECT insert data
FROM program;

SAU

SELECT replace(data, year(data), year(data)+1)

FROM program;

SAU

SELECT date_add(data INTERVAL year
FROM program;

b)

SELECT concat('Ziua:', day(data), ' ', 'Luna:', month(data), ' ', 'Anul:', year(data)) AS Data

FROM program;

SAU

SELECT date_format(data 'Ziua: %e, Luna: %m, Anul: %Y'
FROM program;

c)

SELECT length(lectia) from program;

d)

SELECT uppeR( replace(lectia,'BD','baza de date')) from program;

e)

SELECT curdate AS 'azi' data curdate data AS 'diferenta'
FROM program;

2. Functii agregat

Obs. 1. Functiile agregat calculeaza un rezultat din mai multe linii ale unui tabel (functii de totalizare);

2. Sintaxa:

SELECT functia(arg1,arg2,) FROM nume_tabel;

pentru argumente atribute ale unei relatii;

COUNT(expr) furnizeaza numarul de linii (inregistrari) dintr-un tabel; Expresia expr NU va numara valorile NULL; Daca expr este * atunci se vor numara inclusiv valorile NULL;

SUM(expr) executa suma tuturor valorilor dintr-o coloana pentru toate valorile diferite de NULL;

MAX(expr) returneaza valoarea cea mai mare dintr-o coloana pentru toate valorile diferite de NULL;

MIN(expr) returneaza valoarea cea mai mica dintr-o coloana pentru toate valorile diferite de NULL;

AVG(expr) calculeaza media valorilor dintr-o coloana pentru toate valorile diferite de NULL.

Pb.4. Folosind baza de date LABORATOR3 care contine tabelul PROGRAM(nr_ore_propuse,nr_ore_predate,lectia,data, pret) sa se realizeze urmatoarele cerinte:

a) afisati numarul toral de inregistrari din tabel alaturi de numarul total de inregistrari din coloana nr_ore_ propuse si alaturi de inregistrarile din coloana nr_ore_ predate;

b) calculati suma necesara inscrierii la toate cele trei cursuri si afisati rezultatul alaturi de cel mai mare pret si de media numarului de ore programate pentru predarea celor trei cursuri.

c) afisati numarul maxim de caractere care apare in titlul lectiilor.

R: a)

SELECT counT AS Nr_total count nr_ore_propuse AS Nr_ore_propuse count nr_ore_predate AS Nr_ore_predate
FROM program

b)

SELECT sum pret AS Pret max pret AS Pret_Max avg nr_ore_propuse AS Media_ore_propuse FROM program

c)

SELECT max length lectia
FROM program

SAU

select if(max(length(lectia)),lectia, '' ), max(length(lectia)) from program; (afiseaza si titlul lectiei

ASCII: American Standard Code for Information Interchange (sistem de codificare a caracterelor bazat pe alfabetul englez