Interogări SQL (click de 2 ori pe SQL langa APP BUILDER)
https://iacademy.oracle.com/pls/iacademy/f?p=4500:1003:3391007919958272::NO:::
Tipuri de date
- sir de caractere VARCHAR2
- numeric NUMBER (lungimea in cifre, cate zecimale) (5,2) - inseamna 10.00 (5 digits in total, din care "." si 2 zecimale)
- data calendaristica DATE (10-jun-1999)
Operatori
a) aritmetici + - * /
b) pentru siruri de caractere
|| concatenare
' ' delimitator pentru sir de caractere
ex. select 'ABCD' || 'SGTR' from dual
" " se folosesc pentru numele coloanei
ex. select 'ABCD' || ' SGTR' as "Concatenare" from dual
Exista si functie pentru concatenare: CONCAT(sir1,sir2) <=> sir1 || sir2
c) relationali =, <, >, !=, <=, >=
Structura unei tabele
DESCRIBE nume_tabel
ex.
describe employees
select first_name from employees where departmend_id = 90
Se selecteaza doar randu care trebuie sa fie executat (aici randu 2)
ex.
describe employees
select first_name as "NUME", last_name as "PRENUME" from employees where department_id != 90
Operatori specifici oracle (relationali)
BETWEEN ... AND ... (interval inchis)
IN(e1,e2,...) (apartenenta la multimea e1,e2,...)
LIKE
ex.
describe employees
select first_name as "NUME", last_name as "PRENUME",department_id,job_id from employees where department_id between 10 and 20
ex.
describe employees
select first_name as "NUME", last_name as "PRENUME",department_id,job_id from employees where department_id between 10 and 20 and job id = 'AD_ASST'
pt functia LIKE
_ si %
% inseamna ca acolo unde apare poate sa fie 0 sau mai multe caractere (orice caractere)
"Toate first name-urile care au pe prima pozitie k si in continuare oricate si orice caractere"
'k%'
ex.
describe employees
select first_name as "NUME", last_name as "PRENUME" from employees where (first_name) like 'K%'
Afiseaza numele care incep cu K si continua cu orice si oricate caractere
'%K%' - afiseaza numele care il contin pe K
exista functiile LOWER si UPPER care se pun inainte de (first_name)
_ inseamna spatiu liber
de ex: '_a%' - afiseaza numele care au a pe pozitia a doua si in continuare orice si oricate
ex.
describe employees
select first_name as "NUME", last_name as "PRENUME" from employees where (first_name) like '_a_'
ex de conditie compusa
describe employees
select first_name as "NUME", last_name as "PRENUME" from employees where (first_name) like '%a_' and first_name like 'P%'
Sectiunea 16 - Comparison operators - Slide 13
describe employees
select first_name as "NUME", last_name as "PRENUME", job_id as "ID_JOB" from employees where JOB_ID like '%AD\_P%' ESCAPE '\'
caracterul din ESCAPE poate fi si altul
Daca vrem sa cautam _ sau \ folosim pe post de ESCAPE alt caracter care nu ne intereseaza
d) Operatori pentru coloane nule
ex.
describe employees
select first_name as "NUME", last_name as "PRENUME", job_id as "ID_JOB", COMMISSION_PCT from employees where COMMISSION_PCT is NULL
e) Operatori logici: NOT AND OR
se foloseste is NULL pt coloane nule
se foloseste is not NULL pt coloane nenule
describe employees
select first_name as "NUME", last_name as "PRENUME", salary as "Salariu" from employees where department_id in (20,50) and commission_pct is NULL
describe employees
select first_name as "NUME", last_name as "PRENUME", salary as "Salariu" from employees where (department_id in (80,50) or department_id is null) and commission_pct is not NULL
Sa se scrie first name, last name, salariu si venitul (salariul + salariul inmultit cu comision)
functia NVL (expresi1,expresie2) returneaza expresie2 daca expresie1 este null si returneaza expresie1 daca expresie1 nu este null
Expresie 1 si expresie 2 de acelasi tip trebuie sa fie
describe employees
select first_name as "NUME", last_name as "PRENUME", salary as "Salariu", NVL(salary+salary*commission_pct,salary) as "venit" from employees
Sa se listeze fname lname comision (cat este in cifre sau 'Nu are' daca nu exista)
Functie TO_CHAR transforma expresie numerica in sir de caractere
describe employees
select first_name as "NUME", last_name as "PRENUME", NVL(to_char(commission_pct),'Nu are') as "Comision" from employees
Functia TO_NUMBER nu accepta litere
to_number('abc123') - invalid
Sa se scrie fname lname intr-o singura coloana "Nume si prenume" si o alta coloana "De cate zile este angajat"
describe employees
select first_name || ' ' || last_Name as "NUME SI PRENUME", trunc(sysdate-hire_date) as "Zile de cand lucreaza" from employees
SYSDATE - data curenta
trunc - functie pt convertire la intreg
Sa se afiseze de cati ani lucreaza
describe employees
select first_name || ' ' || last_Name as "NUME SI PRENUME", trunc((sysdate-hire_date)/365) as "Ani de cand lucreaza" from employees
describe employees
select first_name || ' ' || last_Name as "NUME SI PRENUME", trunc((sysdate-hire_date)/365) as "Ani de cand lucreaza", trunc(months_between(sysdate,hire_date)/12) as "Ani de cand lucreaza" from employees
select trunc(months_between(sysdate,to_date('1-nov-1994','dd-mon-yyyy'))/12) from dual
Functii pe siruri de caractere
UPPER
LOWER
expC - expresie caracter
INITCAP (expC) - transforma in majuscula prima litera din fiecare cuvant care apare expC (separator intre cuvinte e orice caracter non-litera)
Rezultatul e Prima mare si restul mici, indiferent de restul literelor
select initcap('ion vasile*vasile') from dual
CONCAT (sir1,sir2)
SUBSTR (expC,exp_numerica1,[exp_numerica2]) - (SIR,POZITIE,LUNGIME) - functia extrage din sir un subsir incepand de la pozitia POZITIE de lungime LUNGIME. Daca LUNGIME nu apare, extrage pana la sfarsit
[...] - parametru optional
select substr('abcd',2,1) rezultat b
Observatii
- Daca LUNGIME e mai mare decat lungimea sirului, nu da eroare, ci se opreste la sfarsit
- Daca POZITIE este negativ, pozitia se determina de la sfarsit numarand (afisarea se face normal, spre dreapta; doar se incepe cu numararea de la dreapta la stanga)
- ('abcd',-20,1) nu va afisa nimic (NULL)
select substr('abcd',-3,2) from dual
INSTR (sir,subsir,pozitie,apar) (ultimele 2 numerice) - returneaza pozitia subsirului in sir, cautarea facandu-se incepand cu pozitia poz a celei de a apar-a aparitie a lui. Daca nu exista, returneaza 0. Daca apar lipseste, indica prima aparitie. Daca lipseste si pozitie, o ia de la inceput cu cautarea in primul sir.
Se incepe cautarea de la a pozitie-a pozitie a primului sir.
select INSTR ('abcdecd','cd',4) from dual - afiseaza 6, pt ca se sare peste primele 4 litere din 'abcdecd'
LENGTH (sir) - returneaza lungimea sirului sir (returneaza numar)
Sa se afiseze first name si ultima litera din first name
select first_name , substr(first_name,length(first_name)) as "ultima litera" from employees
select first_name , substr(first_name,-1) as "ult lit" from employees
Sa se listeze toti angajatii care incep (Fname) cu litera a
select first_name as "NUME" from employees where lower(substr(first_name,1,1))='a'
select first_name as "NUME" from employees where substr(first_name,1,1)=:litera
=: cere litera dorita intr-o fereastra noua
Sa se afiseze toti angajatii care au combinatia de litere RT in first name
select first_name as "NUME" from employees where instr(first_name,'rt') !=0
Functii
LPAD (sir1,numar,sir2) - completeaza sir1 la stanga cu caractere din sir 2 pana ce sirul obtinut are lungiea numar
select LPAD('abcd',7,'b') from dual
Daca numar e mai mic decat lungimea sir1, se sterg caractere pana cand sir1 ajunge la lungimea numar
RPAD - similar, completeaza la dreapta sirului1
Sa se scrie first name completat la dreapta cu * pana la lungimea 40
select RPAD(first_name,40,'*') from employees
Functia
TRIM sterge caracterul din sir daca e la inceput/sfarsit
TRIM (LEADING carac FROM sir) - de la inceput
TRIM (TRAILING carac FROM sir) - de la sfarsit
TRIM (BOTH carac FROM sir) - si de la inceput si de la sfarsit
TRIM (sir) - sterge spatiile si de la inceput si de la sfarsit
TRIM (carac FROM sir) - elimina un caracter si de la inceput si de la sfarsit
Functia sterge doar caracterele (inclusiv spatiile) de la inceputul/sfarsitul sirului
REPLACE (sir, subsir, sir nou) - inlocuieste toate aparitiile lui subsir din sir cu sir nou
Functii numerice
REMAINDER (x,y) - determina multiplul lui y cel mai apropiat de x si returneaza diferenta dintre x si acel multiplu
MOD (x,y) - restul impartirii lui x la y
SIGN (x) - returneaza semnul lui x (1 sau -1 in functie de pozitiv sau negativ sau 0)
CEIL (x) - returneaza cel mai mic nr intreg mai mare sau egal cu parametrul transmis (parte intreaga)
FLOOR (x) - cel mai mare nr intreg mai mic sau egal cu parametrul transims
ROUND (x,y) - returneaza valoarea lui x aproximata la y zecimale
Daca y>0, se merge spre dreapta la zecimala y+1 si o verifica in functie de 5
Daca y<0, se merge spre stanga cu y zecimale si se compara a y-a zecimala cu 5
select round (172.4454,-2) from dual - returneaza 200
Daca parametrul y lipseste, e ca si cum ar fi 0. Atunci se uita la prima zecimala si rotunjeste la unitati.
TRUNC (x) - converteste la intreg
TRUNC (x,y) - truncheaza nr x dupa y zecimale. y poate fi si negativ si se comporta la fel ca ROUND
select trunc(999992.55555,-4) from dual - returneaza 990000
Functii pt date calendaristice
ADD_MONTHS (expD,nr_luni) - adauga nr de luni la data din expD
Data se trece sub forma de sir de caractere '1-nov-2012'
MONTHS_BETWEEN (data1,data2) - data1 - data2
