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

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

Cerințe Preliminarii

  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

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:

[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?

Comentarii

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

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

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.

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

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?

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:

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:

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.

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

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.

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.

32 este nivelul maxim, în cam tot ce presupune o oarecare recursivitate și T-SQL, nu doar pentru subclauze.

Ex. 5 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 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)

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

Se poate sorta după mai multe criterii, lista fiind separată prin virgulă.

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:

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

Exercițiu final Clauze

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

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.

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

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.

Puteți să vă definiți și voi schema/structura pentru XML, dar pentru început este recomandat să o folosiți pe cea implicită.

Exemplu Creare XML din Tabele:

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

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

bd2/laboratoare/07.txt · Last modified: 2020/11/19 15:40 by ciprian.truica
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