Laborator SQL 8 bd
-
Upload
andrei-murariu -
Category
Documents
-
view
3 -
download
1
description
Transcript of Laborator SQL 8 bd
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