- Extrage familiile tutror angajatilor care lucrează la proiectul Apollo

---

4.Subinterogarile si operatorii ANY si ALL

 - Intotdeauna se utilizeaza cu un operator de comparatie

 - ANY returneaza true daca rezultatul subinterogarii contine macar un rind care corespunde conditiei de comparare

 - ANY sinonim SOME

 

 - ALL

USE sample;
Select DISTINCT project_no, project_name, budget
    FROM project
    WHERE budget <= ALL
    (SELECT budget
        FROM project);

---

5.Subinterogarile si operatorul EXISTS

 - Primeste cubinterogarea ca argument si returneaza false daca subinterogarea nu returneaza tupluri:

 - Extrage numele tuturor angajatilor care lucreaza la proiectul p1:

USE sample;
Select emp_lname
    FROM employee
    WHERE EXISTS (SELECT *
        FROM works_on
        WHERE employee.emp_no = works_on.emp_no
        AND project_no = 'p1');

 

 - Extrage numele tuturor angajatilor departamentul carora nu se afla in Seattle:

USE sample;
Select emp_lname
    FROM employee
    WHERE NOT EXISTS (SELECT *
        FROM departament
        WHERE employee.dept_no = departament.dept_no
        AND location = 'Seattle');

---

6.Subinterogari corelate

SQL - Server   realizeaza o subinterogare corelata cand o subinterogare face referinta la  o coloana a tabelului  referit in interogarea principala. Subinterogarea este evaluata  cate o data pentru  fiecare rand procesat  de  declaratia parinte. 

    Declaratia parinte poate  sa constea intr-o fraza SELECT, sau instructiuni UPDATE (actualizari corelate) respectiv DELETE (stergeri corelate).

Subinterogari  incapsulate  versus subinterogari corelate

     In cazul subinterogarilor normale (incapsulate), la inceput este rulata subinterogarea, o singura data,  si intoarce valorile care vor fi utilizate de  interogarea principala. 
    O interogare corelata este executata  pentru fiecare inregistrare  candidata considarata de interogarea principala. Cu alte cuvinte, executia subinterogarii este dictata de  interogarea principala. Este utilizata ori de cate ori  o  subinterogare trebuie sa intoarca diferite rezultate sau multimi de rezultate  pentru fiecare inregistrare  considerata de  interogarea principala.

 Sintaxa generala pentru subinterogarile corelate

SELECT col₁, col₂,... 
    FROM  tabel₁ alias₁ 
    WHERE col _i operator 
                        ( SELECT  col₁, col₂,... 
                            FROM tabel₂ 
                            WHERE  expr₁ = alias₁.expr₂) 
In interogarile corelate se pot folosi  operatorii ANY si ALL. 
Tipic, subinterogarile corelate se folosesc impreuna cu operatorul EXISTS. 

Exemplu: Extrage proiectele cu bugetul mai mic de 150000

USE sample;
Select project_no, project_name, budget
    FROM  project  pr 
    WHERE 150000 >= 
        (SELECT  budget
             FROM project
             WHERE  budget = pr.budget);

Exemplu:Extrage proiectele pentru care nu există nici un job

USE sample;
Select emp_no, project_no, job, enter_date
    FROM  works_on  wk 
    WHERE NOT EXISTS
        (SELECT  *
             FROM works_on 
             WHERE  wk.Job = works_on.Job );

---

7.Functiile agregate

• simple
• statice
• determinate de utilizator
• de analiza

---

8.Functiile agregate simple

•  MIN
• MAX
• SUM
• AVG
• COUNT
• COUNT_BIG 

 - Toate functiile agregate executa calcule asupra unui argument, care poate fi colaona sau expresie (in aafara de COUNT(*) si COUNT_BIG(*))

 - Rezultatul este o constatnta,afisata in tabelul de rezultate

 - Se declara in lista instructiunii SELECT, care poate avea si predicatul GROUP BY

---

9.Functia agregata AVG

 - Returneaza valoarea medie pentru toate valorile colaonei 

 - Argument al acestei functii este tip de date numneric

 

EX:  valoarea medie a bugetelor, care depășesc 100000:

USE sample;
SELECT AVG(budget) avg_budget
    FROM project
    WHERE budget > 10000;

EX: valoarea medie a bugetelor proiectelor, care sunt egale sau depășesc 100000 și  numele proiectului este diferit de d1:

USE sample;
SELECT AVG(budget) avg_budget
    FROM project
    WHERE (budget >= 10000) AND (project_no <> 'd1');

---

10.Functia agregata COUNT si COUNT_BIG

 - COUNT are doua forme

• COUNT ([DISTINCT] col_name)
• COUNT(*)

EX:– extrage numarul de funcții diferite pentru fiecare proiect:

USE sample;
SELECT project_no, COUNT (DISTINCT job) job_count
    FROM works_on
    GROUP BY project_no;

 

EX: - extrage numarul de funcții  pentru toate proiectele.

USE sample;
SELECT job, COUNT (*) job_count
    FROM works_on
    GROUP BY job;

- Diferenta intre COUNT si COUNT_BIG este in tipul returnat: COUNT returneaza INTEGER, iar COUNT_BIG - BIGINT

 

EX:–– extrage numarul de angajati pentru fiecare departament.:

USE sample;
SELECT dept_no, COUNT_BIG(*) dept_count
    FROM employee
    GROUP BY dept_no

 

EX:– extrage numarul de departamente pentru fiecare locatie.

USE sample;
SELECT location, COUNT_BIG (dept_no) loc_count
    FROM departament
    GROUP BY location;

---

11.Functiile agregate MIN si MAX

 - extrag valoarea maximum si minimum din coloane 

EX(MIN):– extrage cea mai mică valoare al numărului angajatului

USE sample;
SELECT MIN(emp_no) AS min_eployee_no
    FROM employee;

EX(MIN):  – extrage cea mai mică valoare al numărului angajatului și numele acestuia.

USE sample;
SELECT emp_no, emp_lname
    FROM employee
    WHERE emp_no = 
    (SELECT MIN (emp_no)
        FROM employee);

EX(MAX):– extrage numărul angajatului care a fost introdus ultimulu în tabelul works_on.

SELECT emp_no
    FROM works_on
    WHERE enter_date = 
    (SELECT MAX (enter_date)
        FROM works_on
        WHERE job = 'Manager');

EX(MAX): – extrage numele proiectului cu cel mai mare buget din tabelul project.

USE sample;
SELECT project_name, budget
    FROM project
    WHERE budget = 
    (SELECT MAX (budget)
        FROM project);

---

​12.Functia agregata SUM

 - Calculeaza suma totala al valorilor coloanei.

EX: – calculeaza suma totală a bugetelor tuturor proiectelor

USE sample;
SELECT SUM (DISTINCT budget) sum_of_bugets
    FROM project;

 

EX: – calculeaza suma totală a bugetelor care depășesc 100000, al tuturor proiectelor 

    USE sample;
SELECT SUM (DISTINCT budget) sum_of_bugets 
    FROM project
    WHERE budget > 100000;

---

​13.Operatorul GROUP BY

 - Grupeaza coloanele alese pentru a primi un set de coloane conform valorilor unui sau a mai multor rinduri sau exepresii

EX:– selectare și grupare a funcțiilor angajaților:

USE sample;
SELECT job
    FROM works_on
    GROUP BY job;

EX:– grupare angajați conform numărului proiectului și al funcției

USE sample;
SELECT project_no, job
    FROM works_on
    GROUP BY project_no, job;

---

​14.Operatorul HAVING

 - Determina conditia care se aplica la grupul de rinduri

EX: – extrage numarul proiectelor la care participă mai tuțin de patru angajați

USE sample;
SELECT project_no
    FROM works_on
    GROUP BY project_no
    HAVING COUNT(*) < 4;

EX:– grupează tuplurile din  tabelul works_on conform acelor funcții care încep cu litera ‚M’.

USE sample;
SELECT job
    FROM works_on
    GROUP BY job
    HAVING job LIKE 'M%';

---

​15.Operatorul ROLLUP

 - Ierarhia grupurilor se determina conform ordinii indicate in coloanele ce se grupeaza

EX: – grupează tuplurile coloanei emp_fname și dept_no din  tabelul employee conform condițiilor impuse de operatorul IN în care se cere ca numărul departamentului să fie ‚d1’ sau ‚d3’.

USE sample;
SELECT emp_fname, dept_no
    FROM employee
    WHERE dept_no IN ('d1', 'd3')
    GROUP BY ROLLUP (emp_fname, dept_no);

---

​16.Instructiunea INSERT

 - Adauga rinduri (sau parti ale rindului) in tabel.

 - Sun doua forme:

• INSERT [INTO] tab_name [(col_list)]
      DEFAULT VALUES | VALUES ({DEFAULT NULL expresion}[,...n])
• INSERT INTO tab_name | view_name [(col_list)]
      {select_statement | execute_statement}

​ - Prima forma permite introducerea in tabel al unui rind(sau parti de rind). Iar a doua permite introducerea rezultatul instructiunii SELECT, sau al procedurii executate cu instructia EXECUTE

 - INSERT xu SELECT poate extrage valori din acelasi tabel sau din alte tabele, cu conditia ca datele sa fie compatibile

ADAUGARE UN RIND:

 - In ambele forme ale instruciei INSERT lista coloanelor nu este obligatori. Lipsa listei indica toate coloanele

 - DEFAULT VALUES introduce valoarea by default pentru toate coloanele

EX: completarea unui singur tuplu: Completare tuplu din tabelul employee

use sample;
INSERT INTO employee VALUES (15201, 'Dave', 'Davis', NULL);

EX: completarea mai multor  tupluri: 

use sample;
CREATE TABLE dallas_dept
    (dept_no CHAR(4) NOT NULL,
    dept_name CHAR(20) NOT NULL);

INSERT INTO dallas_dept (dept_no, dept_name)
    SELECT dept_no, dept_name
    FROM departament
    WHERE location = 'Dallas';

---

​16.Instructiunea UPDATE

 - Se foloseste pentru modificare rinduri

EX:Cu ajutorul operatorului UPDATE modificăm funcția angajatului cu numărul 18316:

use sample;
UPDATE works_on
    SET job = 'Manager'
        WHERE emp_no = 18316
        AND project_no = 'p2';

EX:Cu  operatorul UPDATE modificăm funcția angajatului cu numele Jones

use sample;
UPDATE works_on
    SET job = NULL
        WHERE emp_no IN (
        SELECT emp_no
            FROM employee
            WHERE emp_lname = 'Jones');
        

---

​16.Instructiunea DELETE

 - Sterge rinduri din tabel 

EX: Cu  operatorul DELETE ștergem toți angajații cu funcția de manager

USE sample;
DELETE FROM works_on
    WHERE job = 'Manager';

---

​16.Instructiunea MERGE

- Uneste consecutivitate de instructii INSERT,  UPDATE, si DELETE intro singura instructiune.

EX: Creare tabel bonus.  Cu operatorul Merge adaugăm tupluri noi în tabelul bonus.

 

USE sample;
CREATE TABLE bonus
    (pr_no CHAR(4),
    bonus SMALLINT DEFAULT 100);
INSERT INTO BONUS (pr_no) VALUES ('p1');

USE sample;
MERGE INTO bonus B
    USING (SELECT project_no, budget
            FROM project) E 
            ON (B.pr_no = E.project_no)
            WHEN MATCHED THEN
                UPDATE SET B.bonus = E.budget * 0.1
            WHEN NOT MATCHED THEN
                INSERT (pr_no, bonus)
                    VALUES (E.project_no, E.budget * 0.05);

---

​16.Instructiunea OUTPUT

 - Afiseaza modifiarie efectuate

 - Pentru a completa tabelul, in operatorul OUTPUT trebuie de folosit expresia INTO.

EX: 

Inițial creăm tabelul table1(CREATE TABLE), apoi introducem valori în table1 (INSERT INTO dbo.table1 VALUES). În continuare declarăm tabelul ajutător MyTableVar(DECLARE @MyTableVar). După afișare table1 ștergem din el emp_no > 2, acestea din urm[ fiind plasate in tebelul MyTableVar prin intermediul expresiei:

 

 (OUTPUT DELETED. * INTO @MytableVar)

 

În zonele pentru afișare avem rezultatele table1 înainte de ștergere, table1 dupa ștergere și respectiv @MyTableVar – care conține elementele șterse din tabelul table1.

USE sample;
CREATE TABLE dbo.table1
 (
    emp_no INT,
    emp_lname CHAR(20)
 );
 go
INSERT INTO dbo.table1 VALUES 
      (1, 'Fred')
     ,(2, 'Tom')
     ,(3, 'Sally')
     ,(4, 'Alice');
GO
DECLARE @MytableVar TABLE
 (
    emp_no INT,
    emp_lname CHAR(20)
 );
PRINT 'table1, inainte de stergere'
SELECT * FROM dbo.table1;
DELETE  FROM dbo.table1
OUTPUT DELETED. * INTO @MytableVar
WHERE emp_no > 2;
PRINT 'table1, dupa stergere'
SELECT * FROM dbo.table1;
PRINT '@MyTableVar, dupa stergere'
SELECT * FROM @MytableVar;
DROP TABLE dbo.table1;

 

EX: Instruțiunea OUTPUT cu instrucținea UPDATE

USE sample;
GO
DECLARE @update_table TABLE
(
    emp_no INT,
    project_no CHAR(20),
    old_job CHAR(20),
    new_job CHAR(20)
);

UPDATE  dbo.works_on
SET Job = NULL
OUTPUT DELETED.emp_no,
       DELETED.project_no,
       DELETED.Job,
       INSERTED.Job
INTO @update_table 
WHERE Job = 'Analyst';
SELECT * FROM @update_table