Curs 8 - BD

7/25/2019 Curs 8 - BD

1/7

1

Limbaje relaionale pentrudefinirea i manipularea datelor.

Limbajul SQL.

ACADEMIA DE STUDII ECONOMICE BUCURETIFACULTATEA DE CIBERNETIC, STATISTIC I INFORMATIC ECONOMIC

BAZE DE DATE

BUCURETI2015-2016

2

Comand LMD Descriere

SELECT regsete date din una sau mai multe tabele

INSERT adaugo nregistrare nou ntr-o tabel

UPDATE modific valori asociatecoloanelor unei tabele

MERGErealizeaz fie modificri ale datelor,fie adugri de nregistrridintr-o alt tabel, n funcie de o condiie de potrivire

DELETE terge nregistrri dintr-otabel

Limbajul SQL-Oracle

3

Limbajul SQL-OracleInterogarea datelor

Clauze n SELECT Descriere

WHEREcondiierestricioneaz liniile care se returneaz pe baza unuicriteriu specificat n condiia de selecie

clauze de ordonare ierarhicstructureaz rezultatul ntr-o manier ierarhic(asemntor cu o organigram)

GROUP BY coloane_gruparegrupeaz liniile n scopul identificrii valorilor comunegrupurilor (valori agregate calculate prin funcii de grup)

HAVINGcondiierestricioneaz grupurile create prin clauza GROUP BYpebaza unei condiii asupra funciilor de grup

ORDER BY coloane_ordonare sorteaz liniile (implicit ascendent)

4

Rezolvai! Se considertabela: PORTS(PORT_ID, PORT_NAME, COUNTRY, CAPACITY), avnd

urmtoarelenregistrri:

Ce rezultat va produce urmtoarea interogare SQL:

SELECT *FROM PORTS

WHERE PORT_NAME LIKE 'San%'OR PORT_NAME LIKE 'Grand%'

AND CAPACITY = 4;

P OR T_ ID P OR T_ NAM E C OU NTRY C APA CITY

1 Galveston USA 4

2 San Diego USA 4

3 San Francisco USA 3

4 Los Angeles USA 45 San Juan USA 3

6 Grand Cayman UK 3

5

Limbajul SQL-OraclePrecedena operatorilor

Operator

* , / , - , +

=, >= , >,

7/25/2019 Curs 8 - BD

2/7

1

2

7

SELECT ROWNUM, id_angajat, nume, salariul

FROM angajati

ORDER BY salariul;

ROWNUM ID_ANGAJAT NUME SALARIUL---------- ---------- ------ ------------------- ----------

33 132 Olson 2100,0029 128 Markle 2200,00

.............


8

Rezolvai!

Afiaiprimii 3 angajaicu celmai micsalariu.

9

Rezolvai!

Afiai primii 3 angajai cu cel mai mic salariu.

SELECT ROWNUM, id_angajat, nume, salariul

FROM angajati

WHERE ROWNUM

7/25/2019 Curs 8 - BD

3/7

3

13

Tabela DUAL

tabelexistentnoriceBD Oracle

areo singurcoloan:DUMMYde tipVARCHAR2(1)

areo singurlinie,coninndvaloareax

util izat pentru testarea unor comenzi SELECT care nu fac referirineapratla o tabeldintr-oBD

SELECT 1000* 1.5

FROM dual;

SELECT SYSDATE

FROM dual;


14

Limbajul SQL-OracleFuncii SQL

Sintax Descriere Rezultat

UPPER (s) / LOWER (s) ir de caractere

INITCAP (s) ir de caractere

CONCAT (s1,s2) ir de caractere

LPAD (s1,n,s2) / RPAD (s1,n,s2) ir de caractere

LTRIM (s1,s2) / RTRIM (s1,s2) ir de caractere

TRIM (info s1 FROM s2) ir de caractere

LENGTH(s) numr

INSTR (s1,s2, poz,n) numr

SUBSTR (s,poz,n) ir de caractere

REPLACE (s1,s2,s3)

ir de caractere

Funcii SQL care manipuleaz iruride caractere

15


SELECT UPPER('oracle'), LOWER('oracle'), INITCAP('oracle')

FROM dual;UPPER('ORACLE') LOWER('ORACLE') INITCAP('ORACLE')

--------------- --------------- -----------------ORACLE oracle Oracle

SELECT SUBSTR('oracle database',1,3), SUBSTR('oracledatabase',-8,4)

FROM dual;

SUBSTR('ORACLEDATABASE',1,3) SUBSTR('ORACLEDATABASE',-8,4)---------------------------- -----------------------------

ora data

16


SELECT CONCAT('Oracle ' ,'Database'),LPAD('Oracle',15,'*'), RTRIM('Oracle****','*'),TRIM(BOTH '*' FROM '**Oracle****'), LENGTH('Oracle'),INSTR('Oracle Database','a',1,3)FROM dual;

CONCAT('ORACLE','DATABASE') LPAD('ORACLE',15,'*') RTRIM('ORACLE****','*')

--------------------------- --------------------- -----------------------

O ra cl e D at ab as e * ** ** ** ** Or ac le O ra cl e

TRIM(BOTH'*'FROM'**ORACLE****' LENGTH('ORACLE')

------------------------------ ----------------

Oracle 6

INSTR('ORACLEDATABASE','A',1,3

------------------------------

11

17


SELECT REPLACE('oracle database','a'),REPLACE('oracle database','a','*')

FROM dual;

REPLACE('ORACLEDATABASE','A') REPLACE('ORACLEDATABASE','A','----------------------------- ------------------------------orcle dtbse or*cle d*t*b*se

18

Ce afieaz comanda urmtoare?

SELECT CONCAT(RPAD(CONCAT('Capitolul 1',' '),20,'.'),LPAD(CONCAT(' pag.','20'),20,'.')) Cuprins

FROM dual;

7/25/2019 Curs 8 - BD

4/7

4

19

Ce afieaz comanda urmtoare?

SELECT CONCAT(RPAD(CONCAT('Capitolul 1',' '),20,'.'),

LPAD(CONCAT(' pag.','20'),20,'.')) Cuprins

FROM dual;

CUPRINS----------------------------------------Capitolul 1 ..................... pag.20

20



ROUND (n,i) numr

TRUNC (n,i) numr

MOD (n1,n2) numr

Funcii SQL care manipuleaz valorinumerice

21


SELECT ROUND(3.148,2), TRUNC(3.148,2), MOD(11,3)FROM dual;

ROUND(3.148,2) TRUNC(3.148,2) MOD(11,3)

-------------- -------------- ----------

3,15 3,14 2

22



SYSDATE dat calendaristic

ROUND (d,i) dat calendaristic

TRUNC (d,i) dat calendaristic

NEXT_DAY(d,s) dat calendaristic

LAST_DAY (d) dat calendaristic

ADD_MONTHS (d,n) dat calendaristic

MONTHS_BETWEEN (d1,d2) numr

EXTRACT(DAY FROM d)EXTRACT(MONTH FROM d)EXTRACT(YEAR FROM d)

numr

Funcii SQL care manipuleazdate calendaristice

23


SELECT SYSDATE, ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YY'),TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YY')

FROM dual;

SYSDATE ROUND(SYSDATE,'MM') ROUND(SYSDATE,'YY')

----------- ------------------- -------------------1 8. 11 .2 01 5 0 1. 12 .2 01 5 0 1. 01 .2 01 6

TRUNC(SYSDATE,'MM') TRUNC(SYSDATE,'YY')------------------- -------------------0 1. 11 .2 01 5 0 1. 01 .2 01 5

SELECT EXTRACT (YEAR FROM SYSDATE)FROM dual;

EXTRACT(YEARFROMSYSDATE)------------------------

2015 24


SELECT SYSDATE, NEXT_DAY(SYSDATE,'Vineri'),LAST_DAY(SYSDATE)

FROM dual;

SYSDATE NEXT_DAY(SYSDATE,'VINERI') LAST_DAY(SYSDATE)----------- -------------------------- -----------------1 8. 11 .2 015 2 0. 11 .2 015 3 0. 11 .20 15

SELECT ADD_MONTHS(SYSDATE,4),MONTHS_BETWEEN(ADD_MONTHS(SYSDATE,4),SYSDATE)FROM dual;

ADD_MONTHS(SYSDATE,4) MONTHS_BETWEEN(ADD_MONTHS(SYSD--------------------- ------------------------------

18.03.2016 4

7/25/2019 Curs 8 - BD

5/7

5

25


Sintax Descriere

NVL (e1,e2)

NULLIF (e1,e2)

DECODE (e,expresii_de_cutare,d )

CASEexprWHEN cond THEN rez...ELSE rezEND

Funcii SQL speciale

26


SELECT 14+NULL-4

FROM dual;

27


SELECT 14+NULL-4, NVL(NULL,0), 14-NVL(NULL,0)-4FROM dual;

14+NULL-4 NVL(NULL,0) 14-NVL(NULL,0)-4---------- ----------- ----------------

0 10

28


SELECT nume, salariul+comision "Venit total"FROM angajati

WHERE comision IS NULL;

NUME Venit total

------------------------- -----------KingKochhar

...

SELECT nume, salariul+NVL(comision,0) "Venit total"FROM angajati

WHERE comision IS NULL;

NUME Venit total------------------------- -----------King 24000

Kochhar 17000...

29


SELECT pret_lista, pret_min, NULLIF(pret_lista,pret_min)FROM produse;

PRET_LISTA PRET_MIN NULLIF(PRET_LISTA,PRET_MIN)---------- ---------- ---------------------------

221,00 180,00 22173 ,0 0 7 3, 00

299,00 244,00 299399,00 355,00 399

30


SELECT denumire_produs,pret_lista,CASE

WHEN pret_lista < 100 THEN 'pret mic 'WHEN pret_lista BETWEEN 100 AND 200 THEN 'pret mediu 'ELSE 'pret mare'END CalificativFROM produse;

DENUMIRE_PRODUS PRET_LISTA CALIFICATIV------------------------------------------------- --- ------- -----------DIMM - 128 MB 305,00 pret mare

DIMM - 16 MB 124,00 pret mediuDIMM - 1GB 599,00 pret mareBattery - EL 44,00 pret mic.......

7/25/2019 Curs 8 - BD

6/7

6

31


SELECT denumire_produs, categorie, pret_lista,CASE LOWER(categorie)

WHEN 'hardware4' THEN 0.2WHEN 'hardware2' THEN 0.15ELSE 0END * pret_lista Majorare_pretFROM produse

WHERE LOWER(categorie) LIKE 'h%';

D EN UM IR E_ PR OD US C AT E GO R IE P RE T _L IS TA M AJ OR A RE _P RE T--------------------- ------------------- ---------- -------------DIMM - 128 MB hardware4 305,00 61DIMM - 16 MB hardware2 124,00 24,8.......

32


SELECT denumire_produs, categorie, pret_lista,

DECODE(LOWER(categorie),'hardware4' ,0.2,'hardware2' ,0.15,0)* pret_lista Majorare_pretFROM produse

WHERE LOWER(categorie) LIKE 'h%';

D EN UM IR E_ PR OD US C AT EG O RI E P RE T_ L IS TA M AJ O RA RE _P RE T--------------------- ------------------- ---------- -------------

DIMM - 128 MB hardware4 305,00 61DIMM - 16 MB hardware2 124,00 24,8.......

33


Sintax Descriere

TO_NUMBER(s,format)

TO_CHAR (n,format)

TO_CHAR (d,format)

TO_DATE (s,format)

TO_TIMESTAMP (s,format)

Funcii SQLde conversientretipuri dedate

34


SELECT TO_NUMBER('$17,000.23', '$999,999.99')FROM dual;

TO_NUMBER('$17,000.23','$999,99.99')------------------------------------

17000,23

SELECT TO_CHAR(1987, '$999,999.99')FROM dual;

TO_CHAR(1987,'$999,999.99')--------------------------

$1,987.00

35


SELECT TO_CHAR(SYSDATE,'DAY, DD MONTH YYYY')FROM dual;

TO_CHAR(SYSDATE,'DAY,DDMONTHYYYY')----------------------------------

MIERCURI, 18 NOIEMBRIE 2015

SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS')FROM dual;

TO_CHAR(SYSDATE,'DD-MON-YYYYHH------------------------------18-NOI-2015 01:11:02

36


SELECT TO_DATE('21-10-2010','DD-MM-YYYY')FROM dual;

TO_DATE('21-10-2010','DD-MM-YY------------------------------21.10.2010

7/25/2019 Curs 8 - BD

7/7

7

37


Sintax Descriere

COUNT(e)

SUM(e)

MIN(e)

MAX(e)

AVG(e)

Funcii SQLde grup

38


SELECT COUNT(*)

FROM angajati;

COUNT(*)----------

107

SELECT COUNT(comision)FROM angajati;

COUNT(COMISION)---------------

35

SELECT COUNT(id_angajat)

FROM angajati;

COUNT(ID_ANGAJAT)-----------------

107

39

Rezolvai!

Afiai pentru fiecare angajat identificat prin nume numrul totaldecomenziintermediate, precum i valoarea acestora.

40

Rezolvare

SELECT'Angajatul '|| a.nume ||' a incheiat '||COUNT(c.nr_comanda) ||' comenzi, in valoare totala de ' ||TO_CHAR(SUM(r.pret*r.cantitate), '$999,999.99')

"Informatii comenzi"FROM angajati a, comenzi c, rand_comenzi r

WHERE a.id_angajat = c.id_angajatAND c.nr_comanda = r.nr_comandaGROUP BY a.numeORDER BY a.nume;

CURSUL 9...

Limbaje relaionale pentru definirea i manipularea

datelor. Limbajul SQL. (continuare)

Curs 8 - BD

Documents

Transcript of Curs 8 - BD