Differences
This shows you the differences between two versions of the page.
|
bd2:laboratoare:07 [2019/10/22 13:30] ciprian.truica |
bd2:laboratoare:07 [2020/11/19 15:40] (current) ciprian.truica [Variabile] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Laboratorul 07 - Triggers ====== | + | ====== Laboratorul 07 - SQL Server : Interogari, Variabile si XML ====== |
| + | ===== Conținut ===== | ||
| + | În acest laborator vom învăța numeroase noțiuni de SQL Server 2017 precum: | ||
| + | * Structura unei interogări | ||
| + | * Variabile | ||
| + | * Lucrul cu date de tip XML | ||
| + | |||
| + | <note> | ||
| + | Încercați să rulație fiecare instrucțiune din laborator chiar dacă nu vă este cerut în mod explicit acest lucru pentru a vă ajuta să înțelegeți. | ||
| + | </note> | ||
| + | |||
| + | ===== Cerințe Preliminarii ===== | ||
| + | |||
| + | - Dorința de a învăța | ||
| + | - Executarea scriptului pentru crearea tabelelor necesare | ||
| + | - Noțiuni de bază despre SQL | ||
| + | - Citirea scriptului de crearea a bazelor de date | ||
| + | |||
| + | ===== Interogări ===== | ||
| + | Ca și în orice alt limbaj de tip SQL avem instrucțiunea **SELECT** care are numeroase posibilități pentru a obține sau formata date. Ordinea clauzelor este foarte importantă și este următoarea: | ||
| + | <code sql> | ||
| + | [WITH] --CTE | ||
| + | SELECT | ||
| + | [INTO] -- Putem folosi rezultatul instruțiunii select pentru a popula o tabelă deja existentă | ||
| + | [FROM] -- Sursa sau sursele de date de date | ||
| + | [[[CROSS]|[INNER]|[LEFT|RIGHT|FULL OUTER]] JOIN] -- Putem folosi mai multe surse de date cu condiții speciale | ||
| + | [WHERE] -- Condițiile de filtrare a datelor deja existente | ||
| + | [GROUP BY] -- Agregarea datelor deja existente până în acest punct | ||
| + | [HAVING] -- Condiții pentru noile grupări | ||
| + | [ORDER BY] -- Ordonare, se pot folosi alias * uri | ||
| + | </code> | ||
| + | Tot ce ați văzut între **[paranteze pătrate]** poate să lipsească din interogare și clauza să fie validă, spre exemplu: | ||
| + | <code sql> | ||
| + | SELECT N'Aceasta este o clauză cât se poate de validă' | ||
| + | </code> | ||
| + | |||
| + | <color red>Ex. 1</color> Scrieți o clauză T-SQL care întoarce răspunsul la întrebarea: De ce am folosit litera **N** înainte șirul de caractere constant? | ||
| + | |||
| + | ===== Comentarii ===== | ||
| + | Ca în orice alt limbaj de programare avem dreptul de a folosi explicații pentru codul nostru. Se pot face în 2 moduri: | ||
| + | <code sql> | ||
| + | -- Primul | ||
| + | -- mod | ||
| + | -- Aici trebuie să avem caractele speciale '--' pe fiecare rând. | ||
| + | |||
| + | /* | ||
| + | Al | ||
| + | doilea | ||
| + | mod | ||
| + | |||
| + | Aici trebuie doar să deschidem comentariul înainte de primul rând | ||
| + | și să-l închidem după ultimul. | ||
| + | Nu este nevoie să avem vreun string special pe fiecare rând. | ||
| + | */ | ||
| + | </code> | ||
| + | |||
| + | ==== Coloane, TOP și ALIAS ==== | ||
| + | Putem să avem oricâte coloane în clauza noastră și va trebui să le declarăm prin una din următoarele 2 metode: | ||
| + | |||
| + | * Explicit: listă delimitată prin virgule | ||
| + | * Implicit pe toate, folosind operatorul ***** | ||
| + | Următoarele 2 clauze sunt echivalente (și generate de SQL Management Studio): | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex02a.png?nolink&740|}} | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex02b.png?nolink&740|}} | ||
| + | |||
| + | Ne lovim de necunoscut, clauza **TOP**, care reduce numărul de linii returnate la **minimumul(numărul specificat, câte rezultate sunt într-adevăr)**. Aceasta nu are nevoie în mod deosebit de **(paranteze rotunde)** pentru a delimita numărul dar ne este mai ușor să ne dăm seama când avem de aface și cu constante. | ||
| + | |||
| + | <note> | ||
| + | După cum puteți observa avem o sintaxă clasică de SQL, până acum. Majoritatea bazelor de date relaționale folosesc același trunchi comun, SQL, duoă care fiecare adaugă "jucării noi". | ||
| + | </note> | ||
| + | |||
| + | Alias-urile sunt un mod de a oferi un nume unei coloane (câteodata este obigatoriu să avem unul, vom vedea asta). Dacă dorim să avem spațiu pentru un alias este nevoie să folosim (N)VARCHAR. Un ALIAS se poate folsi astfel: | ||
| + | <code sql> | ||
| + | SELECT 'Un Alias poate fi specificat în acest mod' [Modul1]--Cu sau fără spațiu/Paranteze Drepte | ||
| + | |||
| + | ,'Sau așa' AS 'Modul 2' | ||
| + | </code> | ||
| + | |||
| + | <color red>Ex. 2</color> Scrieți o instrucțiune care întoarce 2 coloane, cu alias, și răspunde următoarei întrebări: De ce nu mai avem diacritice în clauza de mai sus? | ||
| + | |||
| + | ===== Surse de date, WHERE și JOIN ===== | ||
| + | Clauza **WHERE** este la fel ca în toate limbajele, filtrează date. Ce este important este că NU ține cont de ALIAS-uri și este foarte importană ordinea operatorilor booleani. În funcție de natura clauzei procesul din spate poate să difere: | ||
| + | |||
| + | * Datele sunt aduse și după filtrate | ||
| + | * Datele aduse sunt deja filtrate | ||
| + | |||
| + | În T-SQL avem mai multe tipuri de **JOIN** (+- cele teoretice care se reduc tot la acestea): | ||
| + | |||
| + | * **CROSS JOIN**, produs cartezian | ||
| + | * **INNER JOIN**, produs cartezian și condițiile | ||
| + | * **OUTER JOIN**, cu cele 3 variante **LEFT** **RIGHT** și **FULL** | ||
| + | |||
| + | Produsul cartezian se poate genera în următorul mod: | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex030.png?nolink&740|}} | ||
| + | |||
| + | Aici pentru fiecare angajat am adus fiecare manager, ne-am dori să aducem doar managerul lui așa că vom scrie un (SELF) **INNER JOIN** în 2 moduri: | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex03a.png?nolink&740|}} | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex03b.png?nolink&740|}} | ||
| + | |||
| + | <note> | ||
| + | După cum puteți observa, cuvântul **INNER** poate să lipsească cu desăvârșire. Același lucru se aplică și pentru clauza **OUTER**, fiind nevoie doar de **LEFT**, **RIGHT** sau **FULL**. | ||
| + | </note> | ||
| + | |||
| + | <color red>Ex. 3</color> Ne dăm seama că nu sunt aduși toți angajații, de ce? Cine lipsește? De ce lipsește? Scrieți o clauză care rezolvă acest lucru! | ||
| + | |||
| + | ==== CTE ==== | ||
| + | **CTE** provine de la Common Table Expression și este un mod de a ne aduce date pentru o clauză înainte de a se executa efectiv acea clauză. Putem aveam oricâte astfel de expresii, le separăm prin virgulă. | ||
| + | Exemplu: | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex04.png?nolink&740|}} | ||
| + | |||
| + | <color red>Ex. 4</color> Aceeași problemă ca mai sus. Se rezolvă la fel? Rescrieți clauza, în mod corect și complet, fără CTE. | ||
| + | |||
| + | ==== Subclauze ==== | ||
| + | O subclauză este o clauză aflată în interiourl altei clauze și se poate folosi de valorile din clauzele superioare.. Nivelul 2 știe de Nivelul 1, Nivelul 3 știe de nivelele 1 și 2... Nivelul 32 știe de nivelele 1,2..31 și STOP. | ||
| + | |||
| + | <note> | ||
| + | 32 este nivelul maxim, în cam tot ce presupune o oarecare recursivitate și T-SQL, nu doar pentru subclauze. | ||
| + | </note> | ||
| + | |||
| + | <color red>Ex. 5</color> Scrieți o clauză care întoarce numele angajaților care sunt manageri. | ||
| + | |||
| + | ==== Grupări, Agregate și HAVING ==== | ||
| + | Avem o mulțime de funcții de agregare predefinite pe care le găsiți [[https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15|aici]]. Pe lângă aceste funcții mai sunt și cele fare funcționează DOAR când apare clauza **GROUP BY**. | ||
| + | |||
| + | Gruparea este la fel ca și în celelate limbaje de tip SQL: dispare noțiunea de individ și apre noțiunea de întreg. Pentru a filtra, în acest caz, se folosește **HAVING** care se referă la proprietățile unui grup. Se pot folosi funcții și în acest caz. | ||
| + | |||
| + | Exemplu: | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex06.png?nolink&740|}} | ||
| + | |||
| + | <color red>Ex. 6</color> Dorim să îmbunătățim clauza prin adăugarea unei coloane în care vom preciza numele managerilor din acel departament. (**HINT: SUBCLAUZE**) | ||
| + | |||
| + | ==== ORDER BY ==== | ||
| + | Această clauză sortează datele după obținerea lor. Clauza **TOP** ține cont de această clauză, de aceea veți obserca diferențe de performanță neliniare când aceastea două sunt combinate. | ||
| + | |||
| + | Sortarea este fie: | ||
| + | |||
| + | * Ascendentă, modul implicit, **ASC** | ||
| + | * Descendentă, trebuie specificat explicit, **DESC** | ||
| + | |||
| + | <note> | ||
| + | Se poate sorta după mai multe criterii, lista fiind separată prin virgulă. | ||
| + | </note> | ||
| + | |||
| + | Acestă clauză ține cont de ALIAS, deoare ce este executată tocmai la final, și de aceea ne putem folosi de ele. De exemplu dacă dorim să sortăm rezultatele din exemplul precedent în mod descrescător după numărul de angajați vom adăuga **ORDER BY NUMAR_ANG DESC** la final. Se poate folosi și o condiție specială cum ar fi **ORDER BY COUNT(D.DNAME) DESC**, care în cazul nostru face fix același lucru. | ||
| + | |||
| + | ==== UNION, EXCEPT, INTERSECT ==== | ||
| + | Avem și posibilitatea de de a folosi operatori definiți special pentru mulțimi, având în vedere că rezultatul unei clauze este o mulțime de elemente. Singurul lucru care trebuie respectat este structura, adică: | ||
| + | |||
| + | * Fiecare coloană trebuie să fie de același tip | ||
| + | * Ordinea coloanelor este foarte importantă, trebuie să coincidă pentru a avea rezultatul așteptat | ||
| + | * Rezultatul operației va avea drep coloane (numele coloanelor) ce a avut primul tabel din operație | ||
| + | |||
| + | Exemplu: | ||
| + | <code sql> | ||
| + | -- Ordinea este vitală, de aici parantezele | ||
| + | ( | ||
| + | ( | ||
| + | SELECT 'Ana are Mere' | ||
| + | UNION | ||
| + | SELECT 'Dar are si Pere' | ||
| + | ) | ||
| + | EXCEPT | ||
| + | ( | ||
| + | SELECT 'Ana nu are Mere' | ||
| + | ) | ||
| + | ) | ||
| + | INTERSECT | ||
| + | ( | ||
| + | SELECT 'Ana are Mere' | ||
| + | ) | ||
| + | </code> | ||
| + | |||
| + | <color red>Ex. 7</color> Să genereze textul, folosind **UNION** de 3 ori: | ||
| + | <code sql> | ||
| + | Ana are Mere | ||
| + | Ana are Mere | ||
| + | Ana are MERE | ||
| + | Ana are MERE | ||
| + | Ana are Mere | ||
| + | Ana are Mere | ||
| + | </code> | ||
| + | |||
| + | ===== Exercițiu final Clauze ===== | ||
| + | <color red>Ex. 8. </color> Scrieți o clauză care pentru ficare departament în care deviația standard a grilei salariale este între 1 și 1.5 obține următoarele: | ||
| + | - Numele departamentului | ||
| + | - Numele angajaților din acel departament | ||
| + | - Numele managerului cu cel mai mare salariu din departament | ||
| + | - Grila salarială minimă din departament | ||
| + | - Grila salarială maximă din deprtament | ||
| + | |||
| + | ===== Variabile ===== | ||
| + | În T-SQL putem să declarăm variabile oriunde, nu doar în blocuri speciale, dar trebuie să ținem cont de **SCOPE**, ca în orice alt limbaj de programare. | ||
| + | |||
| + | <code sql> | ||
| + | -- Putem să declarăm variabile fără valoare | ||
| + | DECLARE @VAR_WITH_NO_VALUE NVARCHAR(MAX); | ||
| + | -- Sau care au deja valoare | ||
| + | DECLARE @VAR_WITH_VALUE NVARCHAR(MAX) = N'Se știe'; | ||
| + | |||
| + | -- Se poate seta în mod explicit valoare unei variabile | ||
| + | SET @VAR_WITH_NO_VALUE = NULL;-- Atenție la tip | ||
| + | |||
| + | -- Se poate seta ca rezultat al unei clauze, oricare ar fi el | ||
| + | SELECT TOP 1 @VAR_WITH_VALUE = N'Se știe' | ||
| + | FROM [hr].[dbo].[EMPLOYEES]; | ||
| + | |||
| + | -- Putem afișa valoare unei variabile | ||
| + | PRINT (@VAR_WITH_NO_VALUE); | ||
| + | PRINT (@VAR_WITH_VALUE); | ||
| + | </code> | ||
| + | |||
| + | ===== XML ===== | ||
| + | SQL Server ne permită să facem diverse operațiuni cu date de tip XML, în afară de stocarea lor în mod optim în tabele. | ||
| + | |||
| + | ==== Din tabele în XML ==== | ||
| + | După cum ați văzut în scriptul de creare, putem să generăm un XML prin sipla adăugare a clauzei **FOR XML AUTO** la finalul instrucțiuniilor din cladrul cererii **SELECT** (aici nu avem voie să folosim INTO). Rezultatul este un XML formatat în modul preferat de SQL Server, de aici provine **AUTO**. | ||
| + | |||
| + | <note> | ||
| + | Puteți să vă definiți și voi schema/structura pentru XML, dar pentru început este recomandat să o folosiți pe cea implicită. | ||
| + | </note> | ||
| + | |||
| + | Exemplu Creare XML din Tabele: | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex070.png?nolink&740|}} | ||
| + | |||
| + | <color red>Întrebare 1</color> Ce face această clauză, dacă ignorăm partea de XML? | ||
| + | |||
| + | ==== Interogare de XML pentru tabele ==== | ||
| + | Putem să interogăm conținutul unui XML pentru a obține date tabelare. În exemplul de mai jos vom lua XML-ul generat anterior și ne vom folosi doar de primul nivel pentru a obține datele legate direct de angajat. | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex080.png?nolink&740|}} | ||
| + | |||
| + | ==== Din XML în tabele ==== | ||
| + | Putem să revenim din XML înapoi la o tabelă (dacă ne permite structura) folosind operatorul **OUTER APPLY**, despre care puteți să citiți mai multe [[https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15|aici]], dar el aplică anumite operațiuni pe TABELE, mai exact ce este în stânga lui și ce este în dreapta lui, la fel ca și **JOIN**. În exemplul de mai jos puteți să vedeți cum refacem datele tabelare transformate în XML. | ||
| + | |||
| + | {{:bd2:laboratoare:sqlserverlab01ex090.png?nolink&740|}} | ||
| + | |||
| + | <color red>Întrebare 2</color> Ce credeți că returnează clauza dacă stricăm ordinea/nodurile? | ||
