Differences
This shows you the differences between two versions of the page.
|
bd:laboratoare:09 [2014/04/29 08:27] andreea.urzica [Crearea și definirea structurilor tabelare] |
bd:laboratoare:09 [2023/11/16 17:38] (current) sorin.ciolofan [Noțiuni teoretice] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ===== Laboratorul 09. ===== | + | ===== Laborator 09 - Subcereri SQL (II) ===== |
| - | ====Crearea și definirea structurilor tabelare===== | + | ==== Obiective ==== |
| - | ===Crearea unei tabele=== | + | * Subcereri pe clauza HAVING |
| - | Sintaxa comenzii de creare este următoarea: | + | * Subcereri pe clauza SELECT |
| - | <code sql> | + | * Subcereri pe clauza ORDER BY |
| - | CREATE TABLE [schema.]table_name [table_constraint] | + | * Operatori în subcereri |
| - | column datatype [DEFAULT expr] [column_constraints] | + | |
| - | [table_contstraints][TABLESPACE tablespace] | + | |
| - | [storage parameters] | + | |
| - | [ENABLE/DISABLE clause][AS subquery] | + | |
| - | </code> | + | |
| - | unde: | + | |
| - | ***schema** - este schema unde se creează tabela (specifică utilizatorului și bazei de date) | + | |
| - | ***table_name** - este numele tabelei | + | |
| - | ***column** - este numele coloanei | + | |
| - | ***datatype** - reprezintă tipul coloanei | + | |
| - | ***DEFAULT expr** - specifică valoarea implicită a coloanei | + | |
| - | ***column_constraints** - definește constrângerile de integritate pentru coloană | + | |
| - | ***table_constraints** - definește constrângerile de integritate la nivel de tabelă | + | |
| - | ***tablespace** - specifică în ce tablespace al bazei de date se creează tabela | + | |
| - | ***storage_parameters** - definește parametrii de creare și pot fi | + | |
| - | * PCTFREE - procentaj de spațiu rezervat pentru update | + | |
| - | * PCTUSED - procentaj minim folosit pentru un bloc de date | + | |
| - | * INITRANS - numărul inițial de tranzacții pentru fiecare bloc (1-255) | + | |
| - | * MAXTRANS - numărul maxim de tranzacții concurente (1-255) | + | |
| - | * CLUSTER - specifică dacă tabela face parte dintr-un cluster | + | |
| - | ***ENABLE/DISABLE clause** - activare / dezactivare de constrângeri | + | |
| - | ***AS subquery** - inserare de date dintr-o altă tabelă obținute printr-o interogare | + | |
| - | __OPTIUNEA DEFAULT__ | + | ==== Materiale ajutătoare ==== |
| + | [[bd:resurse:tables|Resurse BD]] | ||
| - | Unei coloane ii poate fi data o valoare implicita prin optiunea DEFAULT. Aceasta previne aparitia de valori null (sau erori, daca era specificat NOT NULL) atunci cand o linie este inserata fara o valoare din coloana. Valorile implicite pot fi literali, o expresie, dar nu numele altei coloane. Functii ca SYSDATE si USER sunt valide. | + | ==== Noțiuni teoretice ==== |
| - | Exemplu: | + | === Subcereri pe clauza HAVING === |
| + | Sintaxa este următoarea: | ||
| <code sql> | <code sql> | ||
| - | HIREDATE DATE DEFAULT SYSDATE, | + | SELECT |
| - | SAL NUMBER (7,2) DEFAULT 0; | + | [table_1.]expr_1, [table_1.]expr_2, ..., [table_1.]expr_n |
| + | FROM table_1 | ||
| + | WHERE conditions | ||
| + | [GROUP BY expression_group] | ||
| + | HAVING expressions_having operator | ||
| + | (SELECT expressions_nested_query | ||
| + | FROM table_nested_query | ||
| + | [WHERE conditions_where_nq] | ||
| + | [[GROUP BY expressions_group_nq] [HAVING conditions_having_nq]]) | ||
| + | [ORDER BY expression_order] | ||
| </code> | </code> | ||
| + | <color red> Ex. 1. </color> Să se determine care departament are cei mai mulți angajați pe aceeași funcție. | ||
| - | ===Tipurile de date=== | + | {{:bd:laboratoare:bd_carbon_lab9_ex1a.png?nolink&450|}} |
| - | Tipurile de date care pot fi asociate coloanelor unei tabele pot fi : | + | |
| - | * tipuri numerice | + | |
| - | * tipuri alfanumerice | + | |
| - | * tipuri pentru data calendaristică și timp | + | |
| - | * tipuri compuse (matrice sau tabelă) | + | |
| - | Câteva dintre cele mai uzuale tipuri de date sunt: | + | {{:bd:laboratoare:bd_carbon_lab9_ex1b.png?nolink&550|}} |
| - | ***NUMBER** - număr real de dimensiune variabilă (maxim 38 cifre) | + | |
| - | ***NUMBER(n)** - număr întreg de **n** cifre | + | |
| - | ***NUMBER(n,m)** - număr real de **n** cifre, __dintre care__ **m** zecimale | + | |
| - | ***CHAR(n)** - șir de caractere de lungime fixă **n** (1-2000) | + | |
| - | ***NCHAR(n)** - analog cu CHAR, dar poate stoca șiruri de caractere Unicode | + | |
| - | ***VARCHAR2(n)** - șir de caractere de lungime variabilă **n** | + | |
| - | ***NVARCHAR2(n)** - analog cu VARCHAR dar poate stoca șiruri de caractere Unicode | + | |
| - | ***LONG** - șir de caractere de maxim 2 la puterea 31 octeți | + | |
| - | ***LONG RAW** - similar cu LONG dar conține date binare | + | |
| - | ***ROWID** - poate stoca identificatorul unei linii din tabelă | + | |
| - | ***DATE** - data calendaristică | + | |
| - | ***TIMESTAMP(n)** - extensie pentru tipul DATE care conține și fracțiuni de secundă pe **n** zecimale | + | |
| - | ===Constrângeri de integritate=== | + | <color red> Ex. 2. </color> Să se determine angajații care au comisionul maxim pentru un departament introdus de la tastatură. |
| - | ***NOT NULL** - înregistrările nu pot conține valori nule | + | {{:bd:laboratoare:bd_carbon_lab9_ex2.png?nolink&450|}} |
| - | ***UNIQUE** - definește o cheie unică pe una sau mai multe coloane (nu pot fi mai multe înregistrări cu aceleași valori pe coloanele respective | + | |
| - | ***PRIMARY KEY** - definește o cheie primară la nivel de coloană sau tabelă (nu pot fi mai multe înregistrări cu aceeași cheie primară) | + | |
| - | ***FOREIGN KEY** - definește o cheie externă (tabela se relaționează cu altă tabelă pe o cheie unică sau cheie primară) | + | |
| - | ***CHECK** - forțează o condiție pe coloană | + | |
| - | ===Exemplu=== | + | <note>Clauza Having poate fi folosită și într-o subcerere pe tabela temporală.</note> |
| + | <color red> Ex. 3. </color> Să se afle ce angajat are salariul maxim în firmă | ||
| + | |||
| + | {{:bd:laboratoare:bd_carbon_lab9_ex3.png?nolink&460|}} | ||
| + | |||
| + | === Subcereri pe clauza SELECT === | ||
| + | Aceste subcereri pot fi necorelate sau corelate dar trebuie să returneze întotdeauna o singură valoare. Sintaxa este următoarea: | ||
| <code sql> | <code sql> | ||
| - | CREATE TABLE angajati ( | + | SELECT expressions_select, |
| - | id_ang number(4) PRIMARY KEY, | + | (SELECT expressions_nested_query |
| - | nume varchar2(30) CONSTRAINT CK_NUME CHECK (nume=UPPER(nume)), | + | FROM table_nested_query |
| - | functie varchar2(20) REFERENCES functii(den_functie), | + | [WHERE conditions_where_nq] |
| - | id_sef number(4) REFERENCES angajati(id_ang), | + | [[GROUP BY expressions_group_nq] [HAVING conditions_having_nq]]) alias |
| - | data_ang date, | + | FROM table |
| - | salariu number(7,2) CHECK (salariu>0), | + | [WHERE conditions_where] |
| - | comision number(7,2), | + | [[GROUP BY expressions_group] [HAVING conditions_having]] |
| - | id_dep number(2) REFERENCES departamente (id_dep), | + | [ORDER BY expressions_order] |
| - | CONSTRAINT CK_COM CHECK (comision<=salariu)); | + | |
| </code> | </code> | ||
| + | <color red>Ex. 4. </color> Să se afișeze șefii angajaților din departamentul 20. | ||
| - | __Constrangerea NOT NULL__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex4.png?nolink&450|}} |
| - | Constrangerea NOT NULL se aplica la nivel de coloane si verifica daca inregistrarile au valori nule pe coloanele respective, fortand un cod de eroare care anuleaza tranzactia. | + | === Subcereri pe clauza ORDER BY === |
| - | Cand se creaza constrangeri pe o cheie primara se creaza automat si o constrangere NOT NULL pe coloanele respective (o cheie primara nu trebuie sa contina valori nule pe coloanele care o definesc). | + | Sintaxa este următoarea: |
| + | <code sql> | ||
| + | SELECT expressions_select, | ||
| + | FROM table | ||
| + | [WHERE conditions_where] | ||
| + | [[GROUP BY expressions_group] [HAVING conditions_having]] | ||
| + | ORDER BY (SELECT expressions_nested_query | ||
| + | FROM table_nested_query | ||
| + | [WHERE conditions_where_nq] | ||
| + | [[GROUP BY expressions_group_nq] [HAVING conditions_having_nq]]) [ACS|DESC] | ||
| + | </code> | ||
| + | <color red>Ex. 5. </color> Să se facă o listă cu angajații din departamentele 10 și 20, ordonați descrescător după numărul de angajați din fiecare departament. | ||
| - | __Constrangerea UNIQUE__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex5.png?nolink&450|}} |
| - | Aceasta desemneaza o coloana sau o combinatie de coloane ca fiind cheie unica. Doua linii in aceeasi tabela nu pot avea aceeasi valoare pentru aceasta cheie. Valorile NULL sunt permise daca cheia unica este bazata pe o singura coloana. | + | === Operatori în subcereri === |
| + | * Operatorii prezentați pentru cereri sunt valabili și pentru subcereri; | ||
| + | * Operatorii **SOME(ANY)** și **ALL** sunt folosiți în subcereri care întorc mai multe linii și sunt folosiți împreună cu operatorii logici (de comparatie) în clauzele **WHERE** și **HAVING**; | ||
| + | * Operatorul **SOME (sau sinonimul lui ANY)** este un operator care returneaza o valoare booleana, respectiv TRUE daca exista cel putin o valoare returnata de subcerere care respecta conditia impusa de operatorul logic | ||
| + | * Operatorul **ALL** este un operator care returneaza o valoare booleana, respectiv TRUE daca TOATE valorile returnate de subcerere respecta conditia impusa de operatorul logic | ||
| + | * Dacă se folosește operatorul **SOME(ANY)** împreună cu operatorul logic **%%>(=)%%** atunci are semnificația de **mai mare(sau egal) decât minim**, iar cu operatorul logic **%%<(=)%%** are semnificația de **mai mic(sau egal) decât maxim**; | ||
| + | * Operatorul **ALL** folosit cu operatorul logic **%%>(=)%%** are semnificația **mai mare(sau egal) decât maxim**, iar cu operatorul logic **%%<(=)%%** are semnificația **mai mic(sau egal) decât minim**. | ||
| - | Exemplu: pentru a va asigura ca nu sunt 2 nume de departamente identice la aceeasi locatie: | + | <color red>Ex. 6.</color> Să se afle care sunt angajații care au salariul mai mare decât salariul cel mai mic pentru funcția de SALESMAN. |
| - | <code sql> | + | |
| - | CREATE TABLE DEPT | + | |
| - | (DEPTNO NUMBER, DNAME VARCHAR2(9), | + | |
| - | LOC VARCHAR2(10), | + | |
| - | CONSTRAINT UNQ_DEPT_LOC UNIQUE(DNAME,LOC)) | + | |
| - | </code> | + | |
| - | __Constrangere de cheie primara__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex6.png?nolink&500|}} |
| - | Ca si in cazul cheilor unice, o cheie primara forteaza unicitatea unei coloane sau a unei combinatii de coloane implicate si un index unic este creat pentru a permite acest lucru. Poate fi o singura cheie primara pe o tabela, si aceasta este cheia prin care liniile in tabela sunt identificate individual. In coloanele de chei primare nu sunt permise valori NULL. | + | <color red> Ex. 7.</color> Să se afle care sunt angajații care au salariul mai mare decât salariul cel mai mare pentru funcția de SALESMAN. |
| - | Exemplu: Nomenclator de functii in care fiecare functie sa aiba un cod unic. | + | {{:bd:laboratoare:bd_carbon_lab9_ex7.png?nolink&500|}} |
| - | <code sql> | + | |
| - | CREATE TABLE functii | + | |
| - | ( cod_functie number(2) CONSTRAINT PK PRIMARY KEY, | + | |
| - | den_functie varchar(10), | + | |
| - | data_valid date); | + | |
| - | </code> | + | |
| - | __Constrangere de cheie externa__ | + | Operatorul [NOT] EXISTS este folosit adesea în subcereri corelate și testează dacă subcererea returnează cel puțin o valoare, pentru **EXISTS**, sau niciuna, în cazul lui **NOT EXISTS**, returnând **TRUE** sau **FALSE**. |
| - | Cheile externe furnizeaza reguli de integritate de referinta in cadrul unei tabele sau intre tabele. O cheie exeterna este folosita numai in relatie cu o cheie primara sau unica. | + | <color red> Ex. 8.</color> Să se determine departamentele care au cel puțin un angajat. |
| - | Exemplu: Relationarea tabelei //angajati// cu tabelele //departamente// si //functii//. | + | {{:bd:laboratoare:bd_carbon_lab9_ex8.png?nolink&400|}} |
| - | <code sql> | + | <color red> Ex. 9.</color> Să se determine care angajați nu au șef. |
| - | CREATE TABLE angajati | + | |
| - | (id_ang number(4) PRIMARY KEY, | + | {{:bd:laboratoare:bd_carbon_lab9_ex9.png?nolink&450|}} |
| - | nume varchare2(10), | + | |
| - | functie varchar2(10) REFERENCES functii(den_functie), | + | * O construcție cu **[NOT] EXISTS** este mult mai performantă decât o construcție cu **IN**, **SOME(ANY)** sau **ALL**, deoarece, în cazul în care folosim tabele temporare, acestea nu sunt indexate, ducând la scăderea considerabilă a performanțelor. |
| - | id_sef number(4) REFERENCES angajati(id_ang), | + | * Performațele depind de folosirea indexărilor, de dimensiunea tabelelor din baza de date, de numărul de linii returnate de subcere și dacă sunt necesare tabele temporare pentru a evalua rezultatele returnate. |
| - | data_ang date, | + | * Deși o subcerere cu o construcție pe operatorul **NOT IN** poate fi la fel de eficient ca și în cazul unei construcții pe **NOT EXISTS**, cea din urmă este totuși mult mai sigură, dacă cererea întoarce și valori **NULL**. |
| - | salariu number(7,2), | + | * În cazul operatorului **NOT IN**, condiția se evaluează la **FALSE** când în lista de valori sunt incluse valori **NULL**. |
| - | comision number (7,2), | + | <color red> Ex 10. </color>Dacă ex. 9 se rescrie astfel încât cererea să folosească NOT IN în loc de NOT EXISTS, atunci cererea nu va returna nimic. |
| - | id_dep number(2) REFERENCES departamente(id_dep)); | + | |
| + | {{:bd:laboratoare:bd_carbon_lab9_ex10.png?nolink&450|}} | ||
| + | |||
| + | ==== Exerciții individuale ==== | ||
| + | <color blue>1.</color> Să se calculeze și afișeze funcția și venitul mediu lunar pentru fiecare funcție. Să se folosească o subcerere în clauza select. | ||
| + | |||
| + | <color blue>2.</color> Să se facă o listă cu funcție, gradul salarial, salariul mediu angajați calculat după funcție și grad unde salariul mediu angajați este mai mare sau egal cu salariu mediu pentru grad. | ||
| - | </code> | ||
| - | ====Exerciții==== | ||
| - | <note> | ||
| - | Exercițiile nu sunt identice între 2 ședințe de laborator. Diferențele sunt date de ''parametrii adiționali'' prezenți în fiecare dintre ele, ce vor fi descriși de laborant în cadrul fiecărui laborator. | ||
| - | </note> | ||
| - | 1. Să se creeze o tabela 'salarii' care să conțină ID și DENUMIRE DEPARTAMENT, SALARIU MINIM, SALARIU MAXIM și ''parametrii adiționali''. Tabela va fi populată, prin comanda de creare, din tabelele cu departamente și angajați. | + | <color blue>3.</color> Să se selecteze angajatul/angajații cu cel mai mare venit lunar din departamentul în care lucrează. |
| + | Afișați | ||
| + | * numele angajatului | ||
| + | * numele departamentului în care lucrează angajatul | ||
| + | * funcția angajatului | ||
| + | * venitul lunar | ||
| - | 2. Să se creeze trei tabele: INTRARI_GESTIUNE, IESIRI_GESTIUNE și STOCURI_GESTIUNE, cu următoarele structuri: | + | Ordonați după numele departamentului. |
| - | INTRARI_GESTIUNE: NR_DOC_IN, DATA_DOC_IN, COD_PRODUS, CANT_IN, COD_UM, PRET_UNITAR | + | |
| - | IESIRI_GESTIUNE: NR_DOC_OUT, DATA_DOC_OUT, COD_PRODUS,CANT_OUT,COD_UM, NR_DOC_IN, DATA_DOC_IN | + | |
| - | STOCURI_GESTIUNE: NR_DOC_IN,DATA_DOC_IN,COD_PRODUS,STOC,COD_UM, VALOARE, DATA_STOC | + | |
| - | Constrângeri: pe un tip produs, pot exista mai multe intrări; pe un document de intrare pot exista mai multe produse; același produs poate avea mai multe unități de măsură, deci implicit mai multe prețuri unitare; pentru un produs și document de intrare, pot exista mai multe documente de ieșire -- până la epuizarea stocurilor. | + | |
| - | Scrieți cereri pe aceste tabele care îndeplinesc ''parametrii adiționali''. Fiecare cerere va fi data sub forma CERERE(PARAMETRI): ea va fi implementata intr-un fisier cu numele CERERE, iar parametrii se vor cere ca variabile substituite. | + | |
| - | 3. Pe lângă tabelele de la exercițiul 2, construiți o tabelă FACTURI, care conține NUMAR_FACTURA,DATA_FACTURARII, DEBITOR, CREDITOR si o lista de produse intrate in gestiune, sau iesite din gestiune. Scrieți cereri pe această tabelă care îndeplinesc ''parametrii adiționali''. | + | Să se rezolve fără a folosi funcții agregate (de grup). |
| - | ====Bibliografie==== | + | Rezolvati in 3 metode, folosind: |
| - | - **Alexandru Boicea** -// Oracle SQL SQL Plus//, Editura Printech | + | * operatorul some/any |
| + | * operatorul all | ||
| + | * operatorul exists | ||
