Exercitii Rezolvate Baze de date SQL

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ă: emp empno ename job mgr hiredate sal comm deptno

dept deptno dname loc

Structura tabelelor: Tabelul emp Coloană Allow Nulls Data Type NOT NULL numeric(4,0) empno ename job mgr hiredate sal comm deptno

Semnificaţie coloană Codul angajatului (cheie primară) NULL varchar(50) Numele angajatului NULL varchar(9) Job-ul angajatului NULL numeric(4,0) Codul managerului NULL datetime Data angajării NULL numeric(7, 2) Salariul angajatului NULL numeric(7, 2) Comisionul angajatului NOT NULL numeric(2,0) Nr. departamentului angajatului(cheie străină)

Tabelul dept Coloană Allow Nulls Data Type NOT NULL numeric(2,0) deptno dname loc

NULL NULL

varchar(14) varchar(13)

Semnificaţie coloană Numărul departamentului (cheie primară) Numele departamentului 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: Utilizarea instrucţiunii SELECT şi a diferitelor clauze ale acesteia:

I.

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 emp SELECT ‘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 emp WHERE deptno = 30 GROUP 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 emp WHERE 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 emp WHERE 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 emp WHERE 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, job FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE loc = ’Dallas’) 5. Afişaţi numele şi salariul angajaţilor subordonaţi lui King. SELECT ename, sal FROM emp WHERE 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, job FROM emp WHERE 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, sal FROM emp WHERE 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 emp GROUP BY job 3. Afişaţi numărul de persoane cu acelaşi job. SELECT job, COUNT(*) [Nr. de persoane] FROM emp GROUP 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 emp WHERE mgr IS NOT NULL GROUP BY mgr HAVING MIN(sal) > 1000 ORDER BY MIN(sal) DESC Verificare (pentru un manager): SELECT MIN(sal) FROM emp WHERE 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 d WHERE d.deptno = e.deptno GROUP 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 emp GROUP 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 emp GROUP BY job, deptno --(executie de la dreapta la stanga) Verificare: SELECT deptno, ename, job,sal FROM emp ORDER BY deptno, job --faceti suma pe joburi in fiecare departament si comparati