	Biologie	Chimie	Didactica	Fizica	Geografie	Informatica
	Istorie	Literatura	Matematica	Psihologie

Baze de date

Index » educatie » » informatica » Baze de date
» FUNCTII SQL

FUNCTII SQL

FUNCTII SQL

Analizati urmatoarele functii de tip caracter:

Functie	Semnificatie	Exemplu
LOWER (expresie)	Converteste un sir de caractere la minuscule.	LOWER ('AbCdE') = 'abcde'
UPPER (expresie)	Converteste un sir de caractere la majuscule.	UPPER ('AbCdE') = 'ABCDE'
INITCAP (expresie)	Converteste un sir de caractere la un sir care incepe cu majuscula si continua cu minuscule.	INITCAP ('AbCdE') = 'Abcde'
CONCAT (expr1, expr2)	Concateneaza doua expresii de tip caracter. Echivalent cu operatorul de concatenare '\|\|'.	CONCAT ('Ab', 'CdE') = 'AbCdE'
SUBSTR (expresie, m[, n])	Extrage din expresia de tip caracter, n caractere incepand cu pozitia m. Daca lipseste argumentul n, atunci extrage toate caracterele pana la sfarsitul sirului. Daca m este negativ numaratoarea pozitiilor incepe de la sfarsitul sirului de caractere spre inceput.	SUBSTR ('AbCdE', 2, 2) = 'bC' SUBSTR ('AbCdE', 2) = 'bCdE' SUBSTR ('AbCdE', -2) = 'dE'
LENGTH (expresie)	Returneaza numarul de caractere al expresiei.	LENGTH ('AbCdE') = 5
INSTR (expresie, expr1[, m][, n])	Returneaza pozitia la care se gaseste a n-a ocurenta a expresiei 'expr1' in cadrul expresiei 'expresie', cautarea incepand de la pozitia m. Daca m sau n lipsesc, valorile implicite sunt 1 pentru ambele.	INSTR (LOWER('AbC aBcDe'), 'ab', 5, 2) = 0 INSTR (LOWER('AbCdE aBcDe'), 'ab', 5) = 7
LPAD (expresie, n[, expr1]) sau RPAD (expresie, n[, expr1])	Completeaza expresia caracter data ca parametru (expresie), la stanga (LPAD), respectiv la dreapta (RPAD) cu caracterele specificate in expresia expr1, pana la lungimea specificata de parametrul n. Implicit, daca lipseste, expr1 este ' ' un spatiu.	RPAD (LOWER('AbCdE'), 10, 'X') = 'abcdeXXXXX' LPAD (LOWER('AbCdE'),10) = ' abcde'
LTRIM (expresie[, expr1]) sau RTRIM (expresie[, expr1])	Reversul functiilor LPAD, RPAD. Truncheaza expresia caracter la stanga sau la dreapta prin eliminarea succesiva a caracterelor din expresia expr1. Implicit, daca lipseste, expr1 este ' ' un spatiu.	RTRIM ('abcdeXXXX', 'X') = 'abcde' LTRIM (' abcde') = 'abcde'
TRIM (LEADING \| TRAILING \| BOTH caractere_trim FROM expresie)	Permite eliminarea caracterelor specificate (caractere_trim) de la inceputul (leading) , sfarsitul (trailing) sau din ambele parti, dintr-o expresie caracter data.	TRIM (LEADING 'X' FROM 'XXXabcdeXXX') = 'abcdeXXX' TRIM (TRAILING 'X' FROM 'XXXabcdeXXX') = 'XXXabcde' TRIM ( BOTH 'X' FROM 'XXXabcdeXXX') = 'abcde' TRIM ( BOTH FROM ' abcde ') = 'abcde'
REPLACE (expr, expr1, expr2)	Inlocuieste in prima expresie toate ocurentele sirului expr1 cu sirul expr2.	REPLACE ('%1%11','%','2') = '21211' REPLACE ('%1%11','%1','23') = '23231' REPLACE ('%1%11','%') = '111'
TRANSLATE(expr, expr1, expr2)	Fiecare caracter din sirul de caractere expr care apare si in expr1 este transformat in caracterul corespunzator (aflat pe aceeasi pozitie ca si in expr1) din sirul de caractere expr2.	TRANSLATE('%1%11','%','2') = '21211' TRANSLATE('%1%111','%1','23') = '
ASCII (expresie)	Returneaza codul ASCII al primului caracter din sirul 'expresie'.	ASCII ('curs') = ASCII ('c') = 99
CHR(expresie)	Intoarce caracterul corespunzator codului ASCII specificat.	CHR(99)= 'c'

Exemplu de testare:

SELECT LOWER ('AbCdE')

FROM DUAL;

Rezultatul comenzii este:

LOWER

abcde

Sa se listeze numele si data angajarii salariatului avand codul 200. Sa se eticheteze coloanele conform cu semnificatiile acestora (Nume, Data angajarii), fara ca aliasurile sa fie trunchiate la dimensiunea coloanei si respectandu-se forma de scriere a acestora (aliasurile incep cu majuscula).

SELECT last_name AS 'Nume' , RPAD(TO_CHAR(hire_date),20,' ') 'Data angajarii'

FROM employees

WHERE employee_id=200;

Sa se afiseze pentru fiecare angajat un sir de caractere de forma 'Codul functiei salariatului este '. Sa se afiseze prenumele cu initiala litera mare, iar numele cu litere mari (Stephen KING), iar codul functiei sa se afiseze cu litere mici.

SELECT 'Codul functie salariatului ' || last_name|| ' ' || UPPER(first_name)|| ' este ' || LOWER(job_id)

FROM employees;

Sa se afiseze pentru angajatul cu numele 'HIGGINS' codul, numele si codul departamentului. Cum se scrie conditia din WHERE astfel incat sa existe siguranta ca angajatul 'HIGGINS' va fi gasit oricum ar fi fost introdus numele acestuia? (litere mari, spatii la inceput, la sfarsit).

SELECT employee_id, last_name, department_id

FROM employees

WHERE TRIM(BOTH FROM UPPER(last_name))='HIGGINS';

Sa se afiseze pentru toti angajatii al caror nume se termina in 'n', codul, numele, lungimea numelui si pozitia din nume in care apare prima data litera 'a'. Asociati aliasuri coloanelor returnate de cerere.

SELECT employee_id, last_name, LENGTH(last_name), INSTR(last_name, 'a')

FROM employees

WHERE SUBSTR(last_name,-1)='n';

Analizati urmatoarele functii de tip numeric:

Functie	Semnificatie	Exemplu
ROUND (expresie [, n])	Returneaza valoarea rotunjita a expresiei pana la n zecimale. Daca n este negativ sunt rotunjite cifre din stanga virgulei. Valoarea implicita pentru n este 0.	ROUND(1.6) = 2 ROUND(1.4) = 1 ROUND (1234.56,1) = 1234.6 ROUND (1230.56, -2) = 1200 ROUND (1260.56, -2) = 1300
CEIL(n)	Partea intreaga superioara	CEIL(1.4) = 2 CEIL(1.6) = 2 CEIL(1234.56) = 1235 CEIL(-1234.56)= -1234
FLOOR (n)	Partea intreaga inferioare	FLOOR(1.4) = 1 FLOOR(1.6) = 1 FLOOR (1234.56) = 1234 FLOOR (-1234.56) = -1235
TRUNC (expresie[, n])	Returneaza valoarea trunchiata a expresiei pana la n zecimale. Daca n este negativ sunt trunchiate cifre din stanga virgulei. Valoare implicita pentru n este 0.	TRUNC (1234.56) = 1234 TRUNC (123456, -2) = 123400 TRUNC (1234.56) = 1234 TRUNC (1234.56,1) = 1234.5
MOD (m,n)	Returneaza restul impartirii lui m la n.	MOD (11, 4) = MOD (11, -4) = 3 MOD(-11, 4) = MOD (-11, -4) = -3
ABS(n)	Valoarea absoluta	ABS(-2) = 2
SQRT(n)	Radacina patrata
EXP(n)	Ridicarea la putere a lui e
LN(n)	Logaritm natural
LOG (n,m)	Logaritm in baza n din m
POWER(n,p)	Ridicarea la putere; n la puterea p
SIGN(n)	Semnul unui numar
COS, COSH	Cosinus, cosinus hiperbolic
SIN, SINH	Sinus, sinus hiperbolic
TAN, TANH	Tangenta, tangenta hiperbolica
STDDEV	Deviatia standard
VARIANCE	Dispersia

Sa se afiseze detalii despre salariatii al caror cod este par.

SELECT employee_id, last_name, salary

FROM employees

WHERE MOD(employee_id,2)=0;

Sa se afiseze numele intreg, salariul si numarul de mii al salariului rotunjit la o zecimala pentru salariile care nu se impart exact la 1000.

SELECT first_name || || last_name Nume , salary, ROUND(salary/1000,1) Nr de sute

FROM employees

WHERE MOD(salary,1000)!=0;

Analizati urmatoarele operatii pe expresii de tip data calendaristica:

Operatie	Tipul de date al rezultatului	Descriere
date -/+ number	Date	Scade/Aduna un numar de zile dintr-o / la o data.
date1 - date2	Number	Returneaza numarul de zile dintre doua date calendaristice.
date +/- number/24	Date	Aduna/scade un numar de ore la o / dintr-o data calendaristica.

Sa se afiseze data (luna, ziua, ora, minutul si secunda) de peste 10 zile.

SELECT TO_CHAR(SYSDATE+10, MONTH DD HH24:MM:SS Data

FROM DUAL;

Sa se afiseze numarul de zile ramase pana la sfarsitul anului.

SELECT TO_DATE( 31-DEC-2004 )-SYSDATE Nr zile ramase

FROM DUAL;

Sa se afiseze data de peste 12 ore.

SELECT TO_CHAR(SYSDATE+12/24, MONTH DD HH24:MM:SS Data

FROM DUAL;

Analizati urmatoarele functii de tip data calendaristica:

Functie	Semnificatie	Exemplu
SYSDATE	Intoarce data si timpul curent
MONTHS_BETWEEN (date1, date2)	Returneaza numarul de luni dintre data date1 si data date2. Rezultatul poate fi pozitiv sau negativ dupa cum date1 este mai recenta sau nu fata de date2. Zecimalele reprezinta parti dintr‑o luna!	ROUND(MONTHS_BETWEEN (SYSDATE + 30, SYSDATE)) = 1
ADD_MONTHS (date, n)	Adauga n luni la o data specificata. Valoarea n trebuie sa fie intreaga (pozitiva sau negativa).	MONTHS_BETWEEN (ADD_MONTHS(SYSDATE, 3), SYSDATE) = 3
NEXT_DAY (date, char)	Returneaza data corespunzatoare primei zile a saptamanii specificate (char) care urmeaza dupa date.	NEXT_DAY('15-dec-2004','Monday') = '20-dec-2004' NEXT_DAY ('15-dec-2004',1) = '20-dec-2004'
LAST_DAY (date)	Returneaza data corespunzatoare ultimei zile din luna calendaristica ce contine data specificata.	LAST_DAY ('15-feb-2004') = '29-feb-2004'
ROUND (date [, format)	Returneaza data calendaristica rotunjita dupa formatul specificat Valoarea implicita este 'DAY'.	TO_CHAR (ROUND (TO_DATE ('15-feb-2004 13:50','dd-mon-yyyy hh24:mi')), 'dd-mm-yyyy hh24:mi') = '16-02-2004 00:00'
TRUNC (date [, format)	Returneaza data calendaristica trunchiata dupa formatul specificat. Valoarea implicita este 'DAY'.	TO_CHAR (TRUNC (TO_DATE ('15-feb-2004 13:50','dd-mon-yyyy hh24:mi')), 'dd-mm-yyyy hh24:mi') = '15-02-2004 00:00'
LEAST(d1, d2, . , dn) GREATEST(d1, d2, . , dn)	Dintr-o lista de date calendaristice, functiile intorc prima, respectiv ultima data in ordine cronologica.

Sa se afiseze numele angajatului, data angajarii si data negocierii salariului, care este prima zi de Luni dupa 6 luni de serviciu. Etichetati aceasta coloana Negociere

SELECT first_name, hire_date, NEXT_DAY(ADD_MONTHS(hire_date, 6), Monday Negociere

FROM employees;

Pentru fiecare angajat sa se afiseze numele si numarul de luni de la data angajarii. Etichetati coloana Luni lucrate . Sa se ordoneze rezultatul dupa numarul de luni lucrate. Se va rotunji numarul de luni la cel mai apropiat numar intreg.

SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) Luni lucrate

FROM employees

ORDER BY MONTHS_BETWEEN(SYSDATE, hire_date);

SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) Luni lucrate

FROM employees

ORDER BY Luni lucrate

SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) Luni lucrate

FROM employees

ORDER BY 2;

Analizati urmatoarele functii de conversie:

Obs. Conversiile implicite asigurate de server-ul Oracle sunt:

de la VARCHAR2 sau CHAR la NUMBER;

de la VARCHAR2 sau CHAR la DATE;

de la NUMBER la VARCHAR2 sau CHAR;

de la DATE la VARCHAR2 sau CHAR.

Conversiile explicite se realizeaza cu ajutorul functiilor de tip TO_

Functie	Semnificatie	Exemplu
TO_CHAR (expr_number_sau_date[, format][, nlsparameters])	Converteste o valoare de tip numeric sau data calendaristica, la un sir de caractere conform cu formatul specificat sau cu setarile nationale specificate (NLS - National Language Support). Daca formatul sau parametrii lipsesc se utilizeaza formatul si parametrii impliciti. Formatul este case sensitive.	TO_CHAR('3') = ' 3' TO_CHAR(-12) = '-12' TO_CHAR(sysdate, 'DDMMYYYY') = ' 09122004' TO_CHAR (sysdate + 365 * 57, 'ddmmyyyy') = '
TO_NUMBER (expr_char[, format][, nlsparameters])	Converteste o valoare de tip sir de caractere la o valoare numerica conform cu formatul specificat. Daca formatul sau parametrii lipsesc se utilizeaza formatul si parametrii impliciti.	TO_NUMBER ('-12.22', 'S99.99') = -12.22
TO_DATE (expr_char[, format][, nlsparameters])	Converteste o valoare de tip sir de caractere la o valoare de tip data calendaristica in conformitate cu formatul specificat. Daca formatul sau parametrii lipsesc se utilizeaza formatul si parametrii impliciti.	TO_DATE ('15-feb-2004','dd-mon-yyyy')

Sa se afiseze numele si prenumele pentru toti angajatii care s-au angajat in luna mai.

SELECT last_name, first_name

FROM employees

WHERE to_char(hire_date, 'mm')=5;

Sa se afiseze data urmatoarei Vineri de dupa 5 luni de la data angajarii. Sa se eticheteze coloana. Rezultatul trebuie sa fie in format de tipul 'Friday August 13, 1999'. Sa se ordoneze descrescator dupa data angajarii.

SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 5), Friday , 'Day Month dd, yyyy') data_angaj

FROM employees

ORDER BY data_angaj;

Analizati urmatoarele functii SQL:

Functie	Semnificatie	Exemplu
NVL (expr1, expr2)	Returneaza expr1 daca aceasta nu este NULL, expr2 in caz contrar. Cele 2 expresii trebuie sa aiba acelasi tip sau expr2 sa permita conversia implicita la tipul expresiei expr1.	NVL(NULL, 1) = 1 NVL(2, 1) = 2 NVL('c', 1) = 'c' -- face conversie NVL(1, 'c') -- eroare --nu face conversie
NVL2 (expr1, expr2, expr3)	Daca expr1 este nenula atunci returneaza expr2, altfel Returneaza expr3	NVL2 (NULL, 1, 2) = 2
NULLIF (expr1, expr2)	Daca expr1 = expr2 atunci functia returneaza NULL, altfel returneaza expresia expr1. Echivalent cu CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END	NULLIF (1, 2) = 1 NULLIF (1,1) = NULL
COALESCE (expr1, expr2, , exprn)	Returneaza expr1 daca nu este NULL, altfel expr2, daca expr2 nu este NULL, altfel exprn , daca expr2 nu este NULL.	COALESCE (NULL, NULL, 1) = 1
UID, USER	Intorc ID ul, respectiv username-ul utilizatorului ORACLE curent
VSIZE(expresie)	Intoarce numarul de octeti ai unei expresii de tip DATE, NUMBER sau VARCHAR2	SELECT VSIZE(hire_date) FROM employees WHERE employee_id=104;

Sa se afiseze numele angajatilor si comisionul. Daca un angajat nu castiga comision, sa se scrie Fara comision . Etichetati coloana Comision

SELECT last_name, NVL(TO_CHAR(commission_pct), Fara comision ) comision

FROM employees;

Sa se listeze numele, salariul si comisionul tuturor angajatilor al caror venit lunar depaseste 10000$.

SELECT last_name, salary, commission_pct, salary + salary * NVL(commission_pct, 0) venit_lunar

FROM employees
WHERE salary + salary * NVL(commission_pct, 0) > 10000;

Analizati expresia CASE si functia DECODE:

Functie/Expresie	Semnificatie	Exemplu
CASE expr WHEN expr_bool1 THEN return_expr1 [WHEN expr_bool2 THEN return_expr2 WHEN expr_booln THEN return_exprn ] [ELSE return_expr] END	In functie de valoarea unei expresii returneaza valoarea primei perechi WHEN .. THEN care se potriveste sau daca nu se potriveste nici una expresia din ELSE. Nu se poate specifica NULL pentru toate expresiile de returnat. (return_expri). Toate expresiile trebuie sa aiba acelasi tip de date
DECODE (expr, expr_cautare1, expr_rezultat1, [expr_cautare2, expr_rezultat2, .. expr_cautaren, expr_rezultatn, ] [rezultat_implicit])	Decodifica valoarea expresiei. Daca valoarea expresiei este expr_cautarei atunci e returnata expr_rezultati. Daca nu se potriveste nici o expresie de cautare atunci e returnat rezultat_implicit.	DECODE (1, 1, 2, 3) = 2 DECODE (2, 1, 2, 3) = 3 DECODE (3, 1, 2, 3) = 3

Sa se afiseze numele, codul functiei, salariul si o coloana care sa arate salariul dupa marire. Se stie ca pentru IT_PROG are loc o marire de 10%, pentru ST_CLERK 15%, iar pentru SA_REP o marire de 20%. Pentru ceilalti angajati nu se acorda marire. Sa se denumeasca coloana 'Salariu revizuit'.

SELECT last_name, job_id, salary,

DECODE(job_id,

IT_PROG , salary*1.1,

ST_CLERK salary*1.15,

SA_REP , salary

salary) sal_nou

FROM employees;

SELECT last_name, job_id, salary,

CASE job_id WHEN IT_PROG THEN salary* 1.1

WHEN ST_CLERK THEN salary*1.15

WHEN SA_REP THEN salary*1.2

ELSE salary

END sal_nou

FROM employees;

Politica de confidentialitate