Î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

1. Dorința de a învăța
2. Executarea scriptului pentru crearea tabelelor necesare
3. Noțiuni de bază despre SQL
4. Citirea scriptului de crearea a bazelor de date

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:

[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

Tot ce ați văzut între [paranteze pătrate] poate să lipsească din interogare și clauza să fie validă, spre exemplu:

SELECT  N'Aceasta este o clauză cât se poate de validă'

Ex. 1 Scrieți o clauză T-SQL care întoarce răspunsul la întrebarea: De ce am folosit litera N înainte șirul de caractere constant?

Ca în orice alt limbaj de programare avem dreptul de a folosi explicații pentru codul nostru. Se pot face în 2 moduri:

-- 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.
*/

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):

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.

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:

SELECT  'Un Alias poate fi specificat în acest mod'  [Modul1]--Cu sau fără spațiu/Paranteze Drepte
 
,'Sau așa'  AS  'Modul 2'

Ex. 2 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?

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:

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:

Ex. 3 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 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:

Ex. 4 Aceeași problemă ca mai sus. Se rezolvă la fel? Rescrieți clauza, în mod corect și complet, fără CTE.

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.

Ex. 5 Scrieți o clauză care întoarce numele angajaților care sunt manageri.

Avem o mulțime de funcții de agregare predefinite pe care le găsiți 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:

Ex. 6 Dorim să îmbunătățim clauza prin adăugarea unei coloane în care vom preciza numele managerilor din acel departament. (HINT: SUBCLAUZE)

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

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.

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:

-- 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'
)

Ex. 7 Să genereze textul, folosind UNION de 3 ori:

Ana are Mere
Ana are Mere
Ana are MERE
Ana are MERE
Ana are Mere
Ana are Mere

Ex. 8. 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

Î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.

-- 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);

SQL Server ne permită să facem diverse operațiuni cu date de tip XML, în afară de stocarea lor în mod optim în tabele.

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.

Exemplu Creare XML din Tabele:

Întrebare 1 Ce face această clauză, dacă ignorăm partea de XML?

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.

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 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.

Întrebare 2 Ce credeți că returnează clauza dacă stricăm ordinea/nodurile?