Differences
This shows you the differences between two versions of the page.
|
bd:laboratoare:09 [2018/10/08 15:00] fbratiloveanu [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> | ||
| - | create table exemplu | + | SELECT |
| - | (hiredate date DEFAULT SYSDATE, | + | [table_1.]expr_1, [table_1.]expr_2, ..., [table_1.]expr_n |
| - | sal number (7,2) DEFAULT 0, | + | FROM table_1 |
| - | operator varchar2(10) DEFAULT USER, | + | WHERE conditions |
| - | university varchar2(5) DEFAULT 'UPB'); | + | [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. | ||
| - | __Crearea unei tabele cu linii din alta tabela__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex1a.png?nolink&450|}} |
| - | Exemplu 1: | + | {{:bd:laboratoare:bd_carbon_lab9_ex1b.png?nolink&550|}} |
| - | <code sql> | + | <color red> Ex. 2. </color> Să se determine angajații care au comisionul maxim pentru un departament introdus de la tastatură. |
| - | CREATE TABLE DEPT30 | + | |
| - | AS | + | |
| - | SELECT EMPNO,ENAME,JOB,SAL | + | |
| - | FROM EMP | + | |
| - | WHERE DEPTNO = 30; | + | |
| - | </code> | + | {{:bd:laboratoare:bd_carbon_lab9_ex2.png?nolink&450|}} |
| - | + | ||
| - | Exemplu 2: | + | |
| - | + | ||
| - | <code sql> | + | |
| - | CREATE TABLE EMP_SALS | + | |
| - | (NAME,SALARY,GRADE) | + | |
| - | AS | + | |
| - | SELECT ENAME, SAL, GRADE | + | |
| - | FROM EMP, SALGRADE | + | |
| - | WHERE EMP.SAL BETWEEN LOSAL AND HISAL; | + | |
| - | </code> | + | |
| - | + | ||
| - | + | ||
| - | ===Tipurile de date=== | + | |
| - | 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: | + | |
| - | ***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=== | + | <note>Clauza Having poate fi folosită și într-o subcerere pe tabela temporală.</note> |
| - | ***NOT NULL** - înregistrările nu pot conține valori nule | + | <color red> Ex. 3. </color> Să se afle ce angajat are salariul maxim în firmă |
| - | ***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=== | + | {{: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: |
| - | + | ||
| - | __Constrangerea UNIQUE__ | + | |
| - | + | ||
| - | 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. | + | |
| - | + | ||
| - | Exemplu: pentru a va asigura ca nu sunt 2 nume de departamente identice la aceeasi locatie: | + | |
| <code sql> | <code sql> | ||
| - | CREATE TABLE DEPT | + | SELECT expressions_select, |
| - | (DEPTNO NUMBER, DNAME VARCHAR2(9), | + | FROM table |
| - | LOC VARCHAR2(10), | + | [WHERE conditions_where] |
| - | CONSTRAINT UNQ_DEPT_LOC UNIQUE(DNAME,LOC)) | + | [[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> | </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. | ||
| - | __Constrangere de cheie primara__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex5.png?nolink&450|}} |
| - | Ca și 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. | + | === 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: Nomenclator de functii in care fiecare functie sa aiba un cod unic. | + | <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 functii | + | |
| - | ( cod_functie number(2) CONSTRAINT PK PRIMARY KEY, | + | |
| - | den_functie varchar(10), | + | |
| - | data_valid date); | + | |
| - | </code> | + | |
| - | __Constrangere de cheie externa__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex6.png?nolink&500|}} |
| - | 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. 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: Relationarea tabelei //angajati// cu tabelele //departamente// si //functii//. | + | {{:bd:laboratoare:bd_carbon_lab9_ex7.png?nolink&500|}} |
| - | <code sql> | + | 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**. |
| - | CREATE TABLE angajati | + | |
| - | (id_ang number(4) PRIMARY KEY, | + | |
| - | nume varchare2(10), | + | |
| - | functie varchar2(10) REFERENCES functii(den_functie), | + | |
| - | id_sef number(4) REFERENCES angajati(id_ang), | + | |
| - | data_ang date, | + | |
| - | salariu number(7,2), | + | |
| - | comision number (7,2), | + | |
| - | id_dep number(2) REFERENCES departamente(id_dep)); | + | |
| - | </code> | + | <color red> Ex. 8.</color> Să se determine departamentele care au cel puțin un angajat. |
| - | __Optiunea ON DELETE CASCADE__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex8.png?nolink&400|}} |
| - | Pentru a putea sterge in tabela de referinta linii referite in alte tabele se foloseste optiunea ON DELETE CASCADE. In acest caz, cand se sterge o linie in tabela de referinta se vor sterge toate liniile din tabelele relationate care sunt in relatie cu linia respectiva. | + | <color red> Ex. 9.</color> Să se determine care angajați nu au șef. |
| - | In cazul unei tabele relationata cu ea insasi, stergerea unei linii care este referita duce la aparitia de valori nule pe toate coloanele in liniile relationate. Spre exemplu, in tabela angajati, cand se sterge linia aferenta unui sef, toti angajatii care au seful respectiv vor primi valoare null pe coloana id_sef. | + | {{:bd:laboratoare:bd_carbon_lab9_ex9.png?nolink&450|}} |
| - | Folosind optiunea ON DELETE SET NULL, coloanele de relatie din tabela relationata devin nule si nu sunt sterse liniile realationate atunci cand se sterge o linie din tabela de referinta. | + | * 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. |
| + | * 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. | ||
| + | * 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**. | ||
| + | * În cazul operatorului **NOT IN**, condiția se evaluează la **FALSE** când în lista de valori sunt incluse valori **NULL**. | ||
| + | <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. | ||
| - | __Constrangerea de verificare (CHECK)__ | + | {{:bd:laboratoare:bd_carbon_lab9_ex10.png?nolink&450|}} |
| - | Constrangerea CHECK defineste explicit conditia pe care trebuie sa o satisfaca o linie Conditia poate folosi aceleasi constructii ca cele dintr-o restrictie de cerere, cu urmatoarele exceptii: (1) nu sunt permise subcereri, (2) nu sunt permise referirile la pseudo-coloane precum SYSDATE. | + | ==== 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. | ||
| - | Exemplu: | + | <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 sql> | + | |
| - | CREATE TABLE EMP | + | |
| - | (EMPNO NUMBER(4) CONSTRAINT EMP_PRIM PRIMARY KEY, | + | |
| - | ENAME VARCHAR2(10) CONSTRAINT ENAME_CONS | ||
| - | CHECK(ENAME=UPPER(ENAME)), | ||
| - | |||
| - | JOB VARCHAR2(10), | ||
| - | |||
| - | MGR NUMBER(4) CONSTRAINT EMP_MGR | ||
| - | REFERENCES EMP(EMPNO), | ||
| - | |||
| - | HIREDATE DATE DEFAULT SYSDATE, | ||
| - | |||
| - | SAL NUMBER(7,2) CONSTRAINT SAL_CONS | ||
| - | NOT NULL, | ||
| - | |||
| - | COMM NUMBER(7,2), | ||
| - | |||
| - | DEPTNO NUMBER(2) CONSTRAINT DEPTNO_CONS | ||
| - | NOT NULL, | ||
| - | |||
| - | CONSTRAINT EMP_DEPT FOREIGN KEY (DEPTNO) | ||
| - | REFERENCES DEPT(DEPTNO)); | ||
| - | </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 | ||
