UTILIZAREA LIMBAJULUI TRANSACT SQL(T-SQL) DE INTEROGARE A BAZELOR DE DATE MICROSOFT SQL SERVER

Se presupune că într-o instanţă Microsoft SQL Server 2005/2008 avem creată baza de date angajat, cu tabelele emp –care conţine angajaţii unei firme şi dept---care descrie departamentele din care fac parte angajaţii (cu diagrama de relaţii şi structura date mai jos), populate cu date care să respecte structura propusă:

Structura tabelelor:Tabelul emp

Coloană Allow Nulls Data Type Semnificaţie coloanăempno NOT NULL numeric(4,0) Codul angajatului (cheie

primară)ename NULL varchar(50) Numele angajatuluijob NULL varchar(9) Job-ul angajatuluimgr NULL numeric(4,0) Codul manageruluihiredate NULL datetime Data angajăriisal NULL numeric(7, 2) Salariul angajatuluicomm NULL numeric(7, 2) Comisionul angajatuluideptno NOT NULL numeric(2,0) Nr. departamentului

angajatului(cheie străină)

deptdeptno

dname

loc

empempno

ename

job

mgr

hiredate

sal

comm

deptno

Tabelul dept

Coloană

Allow Nulls Data Type Semnificaţie coloană

deptno NOT NULL numeric(2,0) Numărul departamentului (cheie primară)

dname NULL varchar(14) Numele departamentuluiloc NULL varchar(13) Locaţia departamentului

Exerciţii rezolvate- Utilizând limbajul de interogare a datelor Microsoft Transact SQL

Să se scrie instrucţiunile Microsoft Transact-SQL care să răspundă la următoarele cerinţe:

I. Utilizarea instrucţiunii SELECT şi a diferitelor clauze ale acesteia:

1. Să se afişeze numele angajaţilor, concatenat cu jobul, separat de o virgulă şi un spaţiu, sub aliasul ” Angajat si titlu”.

SELECT ename+’, ’+job AS [Angajat si titlu] FROM emp

2. Să se afişeze conţinutul tabelei emp şi apoi numărul de înregistrări.SELECT * FROM empSELECT ‘Tabela emp: ‘, COUNT(*) AS [Nr. De inregistrari] FROM emp

3. Să se afişeze numele angajaţilor a căror a treia literă a numelor este A.SELECT ename FROM emp WHERE ename COLLATE Latin1_General_Cs_As LIKE ‘__A’

4. Să se afişeze angajaţii din departamentul 30, folosind alias-uri adecvate.SELECT ’In departamentul ’+STR(deptno)+’ sunt ’,STR(COUNT(*))+’ angajati!’ FROM empWHERE deptno = 30GROUP BY deptno

5. Afişaţi numărul de manageri existenţi în tabela de angajaţi.SELECT COUNT(DISTINCT(mgr)) [Nr. de manageri]FROM emp

II. Utilizarea instrucţiunii SELECT în subinterogări:

1. Să se afişeze numele şi data angajării pentru toţi angajaţii din acelaşi departament cu Blake (excludeţi Blake)SELECT ename, hiredate FROM empWHERE deptno IN

(SELECT deptno FROM emp WHERE ename = ’Blake’)

AND ename < > ‘Blake’

2. Să se afişeze codul şi numele angajaţilor care câştigă mai mult decât media salariilor din firmă; sortaţi rezultatul în ordinea descendentă a salariilor.SELECT empno [Codul angajat], ename [Numele]FROM empWHERE sal > (SELECT AVG(sal) FROM emp)ORDER BY sal DESC

3. Afişaţi codul şi numele tuturor angajaţilor care lucrează într-un departament care are şi angajaţi al căror nume conţine pe T sau t.SELECT empno [Codul angajat], ename [Numele]FROM empWHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE ‘%T%’)

4. Afişaţi numele, numărul departamentului şi jobul tuturor angajaţilor a căror locaţie de departament este Dallas:SELECT ename, deptno, jobFROM empWHERE deptno IN (SELECT deptno FROM emp WHERE loc = ’Dallas’)

5. Afişaţi numele şi salariul angajaţilor subordonaţi lui King.SELECT ename, salFROM empWHERE mgr IN(SELECT empno FROM emp WHERE ename=’King’)

6. Afişaţi numărul departamentului, numele şi jobul angajaţilor din departamentul Sales.SELECT dept, ename, jobFROM empWHERE deptno IN(SELECT deptno FROM dept WHERE dname=’Sales’)

7. Afişaţi codul, numele şi salariul angajaţilor care câştigă mai mult decât salariul mediu din firmă şi care lucrează în departamentele care au cel puţin un angajat care are litera T în nume.SELECT empno, ename, salFROM empWHERE sa l> (SELECT AVG(sal) FROM emp )AND deptno IN

(SELECT deptno FROM emp WHERE ename LIKE ’%T%’)

III. Utilizarea instrucţiunii SELECT în gruparea datelor(funcţii de grup)

1. Salariul maxim, minim, suma şi media acestuia pentru toţi angajaţii; rotunjiţi rezultatul la prima poziţie zecimală .SELECT ROUND(MAX(sal), 0) ‘Maxim’,

ROUND(MIN(sal), 0) ‘Minim’, ROUND(SUM(sal), 0) ‘Suma’, ROUND(AVG(sal), 0) ‘Media’, FROM emp

2. Afişaţi maximul, minimul şi suma salariilor pentru fiecare tip de job.SELECT job, ROUND(MAX(sal),0) [Maximul],

MIN(sal) ’Minimul’,SUM(sal) ’Suma’,AVG(sal) [Media salariului]FROM empGROUP BY job

3. Afişaţi numărul de persoane cu acelaşi job.SELECT job, COUNT(*) [Nr. de persoane]FROM empGROUP BY job

4. Afişaţi codul managerului şi cel mai mic salariu al angajaţilor care-l au ca manager ; excludeţi angajaţii care nu au manager şi grupurile de angajaţi care au salariul minim mai mic decât 1000; sortaţi rezultatul în ordinea descrescătoare a salariului.SELECT mgr, MIN(sal)FROM empWHERE mgr IS NOT NULLGROUP BY mgrHAVING MIN(sal) > 1000ORDER BY MIN(sal) DESCVerificare (pentru un manager):SELECT MIN(sal) FROM empWHERE mgr = 7566

5. Afişaţi numele şi locaţia departamentului, nr. de angajaţi şi salariul mediu pentru toţi angajaţii din acel departament.SELECT d.dname, d.loc,COUNT(*) [Nr. de angajati],ROUND(AVG(sal), 2) [Salariul mediu]FROM emp e, dept dWHERE d.deptno = e.deptnoGROUP BY d.dname, d.loc

6. Să se afişeze numărul de angajaţi din fiecare an calendaristic.SELECT YEAR(hiredate) [Anul angajarii],COUNT(hiredate) [Nr. de angajati]FROM empGROUP BY YEAR(hiredate)Verificare:SELECT YEAR(hiredate) [Anul angajarii],COUNT(hiredate) [Nr. de angajati]FROM emp WHERE YEAR(hiredate) = ‘1981’GROUP BY YEAR(hiredate)

7. Să se afişeze numărul departamentului, joburile din departament (tipurile) şi suma salariilor pentru joburile respective.SELECT deptno, job, SUM(sal) [Suma salariilor pe job]FROM empGROUP BY job, deptno --(executie de la dreapta la stanga)Verificare:SELECT deptno, ename, job,salFROM empORDER BY deptno, job--faceti suma pe joburi in fiecare departament si comparati