LABORATOR 9 SQL

LABORATOR 8 SQL

Limbajul de definire a datelor (CREATE, ALTER, DROP)

O baz de date Oracle poate conine mai multe structuri de date. n general, instruciunile LDD sunt utilizate pentru definirea structurii corespunztoare obiectelor unei scheme: tabele, vizualizri, vizualizri materializate, indeci, sinonime, clustere, proceduri i funcii stocate, declanatori, pachete stocate etc. Instruciunile LDD permit:

crearea, modificarea i suprimarea obiectelor unei scheme i a altor obiecte ale bazei de date, inclusiv baza nsi i utilizatorii acesteia (CREATE, ALTER, DROP);

modificarea numelor obiectelor unei scheme (RENAME);

tergerea datelor din obiectele unei scheme, fr suprimarea structurii obiectelor respective (TRUNCATE).

Implicit, o instruciune LDD permanentizeaz (COMMIT) efectul tuturor instruciunilor precedente i marcheaz nceputul unei noi tranzacii.

Instruciunile LDD au efect imediat asupra bazei de date i nregistreaz informaia n dicionarul datelor. Definirea unui obiect presupune: crearea (CREATE), modificarea (ALTER) i suprimarea sa (DROP). Reguli de numire a obiectelor bazei de date

Identificatorii obiectelor trebuie s nceap cu o liter i s aib maxim 30 de caractere, cu excepia numelui bazei de date care este limitat la 8 caractere i celui al legturii unei baze de date, a crui lungime poate atinge 128 de caractere.

Numele poate conine caracterele A-Z, a-z, 0-9, _, $ i #.

Dou obiecte ale aceluiai utilizator al server-ului Oracle nu pot avea acelai nume.

Identificatorii nu pot fi cuvinte rezervate ale server-ului Oracle.

Identificatorii obiectelor nu sunt case-sensitive.

Crearea tabelelor

CREATE TABLE [schema.]nume_tabel (

nume_coloana tip_de_date [DEFAULT expr], ...);

CREATE TABLE nume_tabel [(col1, col2...)]

AS subcerere;

1. Creai tabelul salariat_*** avnd urmtoarea structur:

Nume Caracteristici Tip

cod_angNOT NULL NUMBER(4)

nume VARCHAR2(25)

prenumeVARCHAR2(25)

functia VARCHAR2(20)

sef NUMBER(4)

data_angajarii Valoare implicit data curentDATE

varstaNUMBER(2)

emailCHAR(20)

salariu Valoare implicit 0NUMBER(9,2)

CREATE TABLE salariat_*** (

cod_ang NUMBER(4) NOT NULL,

nume VARCHAR2(25),

prenume VARCHAR2(25),

functia VARCHAR2(20),

sef NUMBER(4),

data_angajarii DATE DEFAULT SYSDATE,

varsta NUMBER(2),

email CHAR(20),

salariu NUMBER(9,2) DEFAULT 0);

2. Afiai structura tabelului creat anterior.

3. Se dau urmtoarele valori:

COD _ANGNUMEPRENUMEFUNCTIASEFDATA_ANGVARSTAEMAILSALARIU

1..........directornull........30.....5500

2 ..... ..... functionar 1......... 25 ..... 0

3 ..... ...... economist 1 ......... 45 ..... 3000

4 ..... .... functionar 1 ......... 35 ...... 1000

4. Inserai n tabelul salariat_*** prima nregistrare din tabelul de mai sus fr s precizai lista de coloane n comanda INSERT.

5. Inserai a doua nregistrare folosind o list de coloane din care excludei data_angajarii i salariul care au valori implicite. Observai apoi rezultatul.

6. Creai un script care s permit inserarea de valori de la tastatur cu ajutorul cruia inserai urmtoarele dou nregistrri.

7. Creai tabelul functionar_*** care s conin funcionarii din tabelul salariat_***, avnd urmtoarele coloane: codul, numele, salariul anual i data angajrii. Verificai cum a fost creat tabelul i ce date conine.

Modificarea tabelelor

Modificarea structurii unui tabel se face cu ajutorul comenzii ALTER TABLE.

Modificarea poate consta n:

adugarea unei noi coloane (nu se poate specifica poziia unei coloane noi n structura tabelului; o coloan nou devine automat ultima n cadrul structurii tabelului)

ALTER TABLE nume_tabel

ADD (coloana tip_de_date [NOT NULL] [DEFAULT] expr][, ...]);

modificarea unei coloane (schimbarea tipului de date, a dimensiunii sau a valorii implicite a acesteia; schimbarea valorii implicite afecteaz numai inserrile care succed modificrii)

ALTER TABLE nume_tabel

MODIFY (coloana tip_de_date [NOT NULL | NULL] [DEFAULT expr][, ...]);

eliminarea unei coloane:

ALTER TABLE nume_tabel

DROP COLUMN coloana;

sauALTER TABLE nume_tabelDROP (coloana);Observaii: dimensiunea unei coloane numerice sau de tip caracter poate fi mrit, dar nu poate fi micorat dect dac acea coloan conine numai valori null sau dac tabelul nu conine nici o linie.

tipul de date al unei coloane poate fi modificat doar dac valorile coloanei respective sunt null.

o coloan CHAR poate fi convertit la tipul de date VARCHAR2 sau invers, numai dac valorile coloanei sunt null sau dac nu se modific dimensiunea coloanei.8. Adugai o nou coloan tabelului salariat_*** care s conin data naterii.

ALTER TABLE salariat_***

ADD (datan DATE);

9. Modificai dimensiunea coloanei nume la 30 i pe cea a salariului la 12 cu 3 zecimale.

ALTER TABLE salariat_***

MODIFY (nume VARCHAR2(30), salariu NUMBER(12,3));

10. Modificai tipul coloanei email la VARCHAR2.11. Modificai valoarea implicit a coloanei data_angajarii la data sistemului+ o zi.

12. Eliminai coloana varsta din tabelul salariat_***.

ALTER TABLE salariat_***

DROP COLUMN varsta;

Eliminarea tabelelor

tergerea fizic a unui tabel, inclusiv a nregistrrilor acestuia, se realizeaz prin comanda DROP TABLE nume_tabel;Pentru tergerea coninutului unui tabel i pstrarea structurii acestuia se poate utiliza comanda:TRUNCATE TABLE nume_tabel;

Observaie: Fiind operaie LDD, comanda TRUNCATE are efect definitiv.

Redenumirea tabelelor

Comanda RENAME permite redenumirea unui tabel, vizualizare sau secven.

RENAME nume1_obiect TO nume2_obiect;

n urma redenumirii sunt transferate automat constrngerile de integritate, indecii i privilegiile asupra vechilor obiecte.

Sunt invalidate toate obiectele ce depind de obiectul redenumit, cum ar fi vizualizri, sinonime sau proceduri i funcii stocate.

13. Redenumii tabelul functionar_*** cu f_***.14. Recreai tabelul functionar_*** utiliznd tabelul f_***. .

15. Eliminai tabelul f_***.

Constrngeri

Tipuri de constrngeri:

NOT NULL - coloane ce nu pot conine valoarea Null; (NOT NULL) UNIQUE - coloane sau combinaii de coloane care trebuie s aib valori unice n cadrul tabelului; ( UNIQUE (col1, col2, ) )

PRIMARY KEY - identific n mod unic orice nregistrare din tabel. Echivalent cu NOT NULL + UNIQUE; (PRIMARY KEY (col1, col2, )) FOREIGN KEY - stabilete o relaie de cheie extern - cheie primar ntre o coloan a tabelului i o alt coloana dintr-un tabel specificat.

[FOREIGN KEY nume_col]

REFERENCES nume_tabel(nume_coloana)

[ ON DELETE {CASCADE| SET NULL}]

- FOREIGN KEY este utilizat ntr-o constrngere la nivel de tabel pentru a defini coloana din tabelul copil;

- REFERENCES identific tabelul printe i coloana corespunztoare din acest tabel;

- ON DELETE CASCADE determin ca, odat cu tergerea unei linii din tabelul printe, s fie terse i liniile dependente din tabelul copil;

- ON DELETE SET NULL determin modificarea automat a valorilor cheii externe la valoarea null, atunci cnd se terge valoarea printe.

CHECK - o condiie care s fie adevrat la nivel de coloan sau linie (CHECK (conditie)).

Constrngerile pot fi create cu tabelul sau adugate ulterior cu o comand ALTER TABLE.

Adugarea constrngerilor la crearea tabelului (CREATE TABLE)

CREATE TABLE [schema.]nume_tabel (

nume_coloana tip_de_date [DEFAULT expr] [constrangere_de_coloana], ...

[constrangere la nivel de tabel])

16. tergei i apoi creai din nou tabelul salariat_*** cu urmtoarea structur.

NUME TIP CONSTRNGERE

cod_ang NUMBER(4) Cheie primar

nume VARCHAR2(25) NOT NULL

prenume VARCHAR2(25)

data_nasterii DATEdata_nasterii 0

cod_deptNUMBER(4)

Combinaia NUME i PRENUME s fie unic

Observaie:

Constrngerile de tip CHECK se pot implementa la nivel de coloan doar dac nu refer o alt coloan a tabelului.

DROP TABLE salariat_***;

CREATE TABLE salariat_*** (

cod_ang NUMBER(4) PRIMARY KEY,

nume VARCHAR2(25) NOT NULL,

prenume VARCHAR2(25), data_nasterii DATE, functia VARCHAR2(9) NOT NULL,

sef NUMBER(4) REFERENCES salariat_*** (cod_ang),

data_angajarii DATE DEFAULT SYSDATE,

email VARCHAR2(20)UNIQUE, salariu NUMBER(9,2) CONSTRAINT c_*** CHECK (salariu > 0),

cod_dep NUMBER(4),

CONSTRAINT const_c_*** CHECK (data_angajarii > data_nasterii),

CONSTRAINT const_u_*** UNIQUE (nume,prenume,data_nasterii));

17. tergei tabelul salariat_***, iar apoi recreai-l implementnd toate constrngerile la nivel de tabel.

Observaie: Constrngerea de tip NOT NULL se poate declara doar la nivel de coloan.

DROP TABLE salariat_***;

CREATE TABLE salariat_*** (

cod_ang NUMBER(4),

nume VARCHAR2(25) NOT NULL,

prenume VARCHAR2(25),

data_nasterii DATE,

functia VARCHAR2(9) NOT NULL,

sef NUMBER(4),

data_angajarii DATE DEFAULT SYSDATE,

email VARCHAR2(20),

salariu NUMBER(9,2),

cod_dep NUMBER(4),

CONSTRAINT ccp_*** PRIMARY KEY (cod_ang),

CONSTRAINT cce_*** FOREIGN KEY (sef) REFERENCES salariat_***(cod_ang),

CONSTRAINT cu1_*** UNIQUE (email),

CONSTRAINT cc1_*** CHECK (data_angajarii > data_nasterii),

CONSTRAINT cc2_*** CHECK (salariu > 0),

CONSTRAINT cu2_*** UNIQUE (nume,prenume,data_nasterii));

18. a. Creai tabelul departament_*** care s aib urmtoarea structur.

NUME TIP CONSTRNGERI

COD_DEP NUMBER(4) Cheie primar

NUME VARCHAR2(20)

ORAS VARCHAR2(25) Not null

b. Ulterior crerii tabelului, adugai constrngerea NOT NULL pe coloana nume.

ALTER TABLE departament_*** MODIFY nume NOT NULL;

c. Eliminai constrngerea NOT NULL definit pe coloana oras.

Adugarea constrngerilor ulterior crerii tabelului, eliminarea, activarea sau dezactivarea constrngerilor (ALTER TABLE nu se aplic pentru NOT NULL)- adaug constrngeri ALTER TABLE nume_tabel

ADD [CONSTRAINT nume_constr] tip_constr (coloana);

- elimin constrngeri

ALTER TABLE nume_tabel

DROP [CONSTRAINT nume_constr] tip_constr (coloana);

- activare/dezactivare constrngere

ALTER TABLE nume_tabel

MODIFY CONSTRAINT nume_constr ENABLE|DISABLE;

sau

ALTER TABLE nume_tabel

ENABLE| DISABLE nume_constr;

19. Inserai o nou nregistrare n salariat_*** de forma:

cod nume prenume data_n functia sef data_ang email salariu cod_dep

2N2P211-JUN-1960economist1SysdateE2200010

Ce observai? Introducei nregistrarea dar specificnd valoarea NULL pentru coloana sef. 20. ncercai s adugai o constrngere de cheie extern pe cod_dep din salariat_***. Ce observai?

ALTER TABLE salariat_***

ADD CONSTRAINT cce2_*** FOREIGN KEY (cod_dep) REFERENCES departament_*** (cod_dep);

21. Inserai o nou nregistrare n departament_***. Apoi adugai constrngerea de cheie extern definit anterior.

cod_dep nume loc

10EconomicBucuresti

22. Inserai noi nregistrri n salariat_***, respectiv n departament_***. Care trebuie s fie ordinea de inserare?cod nume prenume data_n functia sef data_ang email salariu cod_dep

3N3P311-JUN-1967jurist2SysdateE3250020

cod_dep nume loc

20JuriticConstanta

23. tergei departamentul 20 din tabelul departament_***. Ce observai?

24. tergei constrngerea cce2_***. Recreai aceast constrngere adugnd opiunea ON DELETE CASCADE.

ALTER TABLE salariat_***

DROP CONSTRAINT cce2_***;

ALTER TABLE salariat_***

ADD CONSTRAINT cce2_*** FOREIGN KEY (cod_dep) REFERENCES departament_*** (cod_dep) ON DELETE CASCADE;

25. tergei departamentul 20 din tabelul departament_***. Ce observai n tabelul salariat_***? Anulai modificrile.

26. tergei constrngerea cce2_***. Recreai aceast constrngere adugnd opiunea ON DELETE SET NULL.

ALTER TABLE salariat_***

DROP CONSTRAINT cce2_***;

ALTER TABLE salariat_***

ADD CONSTRAINT cce2_*** FOREIGN KEY (cod_dep) REFERENCES departament_*** (cod_dep) ON DELETE SET NULL;

27. ncercai s tergei departamentul 10 din tabelul departament_***. Ce observai?

Consultarea dicionarului datelor

Informaii despre tabelele create se gsesc n vizualizrile:

USER_TABLES informaii complete despre tabelele utilizatorului curent.

ALL_TABLES informaii complete despre tabelele tuturor utilizatorilor.

COLS informaii despre coloane.

TAB informaii de baz despre tabelele existente n schema utilizatorului curent.

Informaii despre constrngeri gsim n:

USER_CONSTRAINTS informaii despre constrngerile definite de utilizatorul curent;

ALL_CONSTRAINTS informaii despre cosntrngerile definite de toi utilizatorii.

1