SQL : fazele logice ale unei interogări

Lucrăm cu Microsoft SQL Server 2016 Enterprise (thanks, MSDNAA).

Descărcaţi codulPentru vizualizare, folosiţi scrollbar-ul din josul blocului de cod.
/**
*  Fazele logice ale frazei (logical phases of the sentence)
*  Propozitia subordonata, clauza (clause):
*  I. FROM este sursa datelor.
*  II. WHERE este filtrarea per randuri (rand cu rand).
*  III. GROUP BY este impartirea rezultatului de la II (multiset, bag)
*       in grupuri/submultimi (multiset, bag).
*  IV. HAVING este filtrarea per grup (grup cu grup).
*  V. SELECT este prezentarea/afisarea rezultatului fazelor anterioare.
*  VI. ORDER BY este sortarea/ordonarea rezultatului.
*/

USE [TSQLV4];
GO

SELECT *--prezentarea rezultatelor (faza V)
FROM HR.Employees AS he;--sursa datelor (faza I)
GO

/**
* Faza I poate lipsi.
*/
SELECT 2 + 3 AS [Numarul cinci];

SELECT he.titleofcourtesy AS [Formula de adresare]
,he.empid AS [ID-ul de angajat]
FROM HR.Employees AS he
WHERE he.titleofcourtesy LIKE '%M%';--filtrarea rand cu rand (faza II)
GO

SELECT he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) AS [Numarul celor cu aceeasi formula de adresare]
FROM HR.Employees AS he
WHERE he.titleofcourtesy LIKE '%M%'--filtrarea rand cu rand (faza II)
GROUP BY he.titleofcourtesy;--impartirea in grupuri (faza III)
GO

SELECT he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) AS [Numarul celor cu aceeasi formula de adresare]
FROM HR.Employees AS he
WHERE he.titleofcourtesy LIKE '%M%'--filtrarea rand cu rand (faza II)
GROUP BY he.titleofcourtesy--impartirea in grupuri (faza III)
HAVING COUNT(he.titleofcourtesy) > 1;--filtrarea grup cu grup (faza IV)
GO

/**
* Putem avea faza logica IV fara a avea faza logica II:
* --alta solutie a interogarii anterioare.
*/
SELECT he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) AS [Numarul celor cu aceeasi formula de adresare]
FROM HR.Employees AS he
GROUP BY he.titleofcourtesy--impartirea in grupuri (faza III)
HAVING COUNT(he.titleofcourtesy) > 1 AND he.titleofcourtesy LIKE '%M%';--filtrarea grup cu grup (faza IV)
GO

SELECT he.titleofcourtesy AS [Formula de adresare]
,he.empid AS [ID-ul de angajat]
FROM HR.Employees AS he
ORDER BY he.titleofcourtesy DESC;--ordonarea rezultatului (faza VI)
GO

/**
* Elemente speciale la diverse faze.
* (1) Calcule la ORDER BY.
* (2) La SELECT 
*      -- cu/fara ORDER BY:
*         ---- OVER (faza V)
*         ---- DISTINCT (faza V)
*	  -- cu ORDER BY:
*     -- TOP, TOP WITH TIES, TOP PERCENT, TOP PERCENT WITH TIES (faza VI)
* (3) La GROUP BY:
*     -- subtotaluri cu ROLLUP
* (4) La ORDER BY:
*     -- OFFSET ROWS, OFFSET-FETCH ROWS ONLY
*/
SELECT he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) AS [Numarul persoanelor]--functie de agregare, necesita GROUP BY
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
GROUP BY he.titleofcourtesy
ORDER BY COUNT(he.titleofcourtesy) DESC; -- (1)
GO

SELECT he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) OVER(PARTITION BY he.titleofcourtesy) AS [Numarul persoanelor]--functie de fereastra, nu necesita GROUP BY
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
ORDER BY he.titleofcourtesy DESC;
GO

/**
* DISTINCT (faza V) se executa DUPA functiile de fereastra de la SELECT (faza V)
*/
SELECT DISTINCT
he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) OVER(PARTITION BY he.titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
ORDER BY he.titleofcourtesy DESC; -- (2)
GO

SELECT TOP(2) --rezultat nedeterministic (cand ordonarea nu este stricta)
he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) OVER(PARTITION BY he.titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
ORDER BY he.titleofcourtesy DESC; -- (2)
GO

SELECT TOP(2) WITH TIES --rezultat deterministic (toate cele 4 randuri se afiseaza)
he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) OVER(PARTITION BY he.titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
ORDER BY he.titleofcourtesy DESC; -- (2)
GO

SELECT TOP (12) PERCENT --rezultat nedeterministic 
he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) OVER(PARTITION BY he.titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
ORDER BY he.titleofcourtesy DESC; -- (2)
GO

SELECT TOP (12) PERCENT WITH TIES --rezultat deterministic
he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) OVER(PARTITION BY he.titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
ORDER BY he.titleofcourtesy DESC; -- (2)
GO

/**
* Interogarile scrise corect trebuie sa aiba mereu o conditie de TIEBREAKER,
* adica sa fie DETERMINISTE.
*/

/**
* DISTINCT (faza V) se executa INAINTE de calcularea lui TOP (faza VI)
*/
SELECT DISTINCT TOP (12) PERCENT WITH TIES 
he.titleofcourtesy AS [Formula de adresare]
,COUNT(he.titleofcourtesy) OVER(PARTITION BY he.titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
WHERE titleofcourtesy LIKE '%M%'
ORDER BY he.titleofcourtesy DESC; -- (2)
GO

/**
* SUBTOTAL si GRAND TOTAL:
* (1) Subtotalul este completat cu marcaje NULL.
*/
SELECT he.titleofcourtesy AS [Formula de adresare]
,COUNT(titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
GROUP BY ROLLUP(he.titleofcourtesy); -- (3)
GO

/**
* SUBTOTAL si GRAND TOTAL:
* (2) Inlocuirea marcajelor NULL cu o expresie de tip TOTAL.
*/
SELECT
 CASE 
    WHEN he.titleofcourtesy IS NOT NULL THEN titleofcourtesy
    ELSE 'TOTAL'
 END AS  [Formula de adresare]
,COUNT(he.titleofcourtesy) AS [Numarul persoanelor]
FROM HR.Employees AS he
GROUP BY ROLLUP(he.titleofcourtesy); -- (3)
GO

/**
* SUBTOTAL si GRAND TOTAL:
* (3) Afisarea subtotalurilor si a grand-totalului.
*/
SELECT
 CASE
    WHEN he.titleofcourtesy IS NOT NULL THEN he.titleofcourtesy
    ELSE
       CASE
          WHEN he.country IS NOT NULL THEN 'SUBTOTAL'
          ELSE 'GRAND TOTAL'
       END
 END AS  [Formula de adresare]
,COUNT(he.titleofcourtesy) AS [Numarul persoanelor]
,CASE
    WHEN he.country IS NOT NULL THEN
                                   CASE
                                      WHEN he.titleofcourtesy IS NULL THEN NCHAR(354) + N'ara: ' + he.country
                                      ELSE he.country
                                   END
    ELSE N'TOATE ' + NCHAR(354) + NCHAR(258) + N'RILE'
 END AS [Tara]
FROM HR.Employees AS he
GROUP BY ROLLUP(he.country,he.titleofcourtesy); -- (3)
GO

SELECT so.empid AS [ID-ul angajatului]
FROM Sales.Orders AS so
ORDER BY empid ASC--ordonarea default
OFFSET 25 ROWS; --sar peste primele 25 de linii (4)
GO

SELECT so.empid AS [ID-ul angajatului]
FROM Sales.Orders AS so
ORDER BY empid ASC
OFFSET 0 ROWS FETCH NEXT 4 ROWS ONLY;--ma intereseaza doar primele 4 linii (4)
GO

/**
* DISTINCT (faza V) se executa INAINTE de calcularea lui OFFSET, OFFSET-FETCH (faza VI)
*/
SELECT DISTINCT so.empid AS [ID-ul angajatului]
FROM Sales.Orders AS so
ORDER BY empid ASC--ordonarea default
OFFSET 2 ROWS;
GO