Differences

This shows you the differences between two versions of the page.

Link to this comparison view

bd:laboratoare:09 [2014/04/24 14:30]
valentin.raduti [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 
 +  * Subcereri pe clauza ORDER BY 
 +  * Operatori în subcereri 
 + 
 +==== Materiale ajutătoare ==== 
 +[[bd:​resurse:​tables|Resurse BD]] 
 + 
 +==== Noțiuni teoretice ==== 
 + 
 +=== Subcereri pe clauza HAVING ​=== 
 +Sintaxa este următoarea:​
 <code sql> <code sql>
-CREATE TABLE [schema.]table_name [table_constraint] +SELECT  
-  column datatype ​[DEFAULT expr] [column_constraints+   [table_1.]expr_1, ​ [table_1.]expr_2, ..., [table_1.]expr_n 
-  [table_contstraints][TABLESPACE tablespace+FROM table_1 
-  [storage parameters+WHERE conditions 
-  [ENABLE/​DISABLE clause][AS subquery]+[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>​
-unde: +<color red> Ex. 1. </​color>​ Să se determine care departament are cei mai mulțangajați pe aceeași funcție.
-  ***schema** - este schema unde se creează tabela (specifică utilizatorului ș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+
  
-===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.
 +
 +{{:​bd:​laboratoare:​bd_carbon_lab9_ex4.png?​nolink&​450|}}
 +
 +=== Subcereri pe clauza ORDER BY ===
 +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.
 +
 +{{:​bd:​laboratoare:​bd_carbon_lab9_ex5.png?​nolink&​450|}}
 +
 +=== 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**.
 +
 +<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.
 +
 +{{:​bd:​laboratoare:​bd_carbon_lab9_ex6.png?​nolink&​500|}}
 +
 +<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.
 +
 +{{:​bd:​laboratoare:​bd_carbon_lab9_ex7.png?​nolink&​500|}}
 +
 +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**.
 +
 +<color red> Ex. 8.</​color>​ Să se determine departamentele care au cel puțin un angajat.
 +
 +{{:​bd:​laboratoare:​bd_carbon_lab9_ex8.png?​nolink&​400|}}
 +
 +<color red> Ex. 9.</​color>​ Să se determine care angajați nu au șef.
 +
 +{{:​bd:​laboratoare:​bd_carbon_lab9_ex9.png?​nolink&​450|}}
 +
 +  * 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.
 +
 +{{:​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.
  
-====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 ș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
bd/laboratoare/09.1398339009.txt.gz · Last modified: 2014/04/24 14:30 by valentin.raduti
CC Attribution-Share Alike 3.0 Unported
www.chimeric.de Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0