Subiectul 1 1. Situaţia : Total şefi 1980-1985 1986-1990 după 1990, reprezentând în prima coloană numărul total al şefilor, iar în coloanele următoare numărul total al şefilor încadraţi în perioada ce reprezintă antetul coloanei. SELECT count(distinct(mgr)) TOTAL_SEFI, sum(case when to_char(hiredate,'yyyy') between 1980 and 1985 and empno in(select distinct mgr from emp) then 1 else 0 end) "1980-1985", sum(case when to_char(hiredate,'yyyy') between 1986 and 1990 and empno in(select distinct mgr from emp) then 1 else 0 end) "1986-1990", sum(case when to_char(hiredate,'yyyy') >1990 and empno in(select distinct mgr from emp) then 1 else 0 end) "DUPA 1990" FROM emp; 2. Numele angajaţilor care nu sunt şefi . SELECT ename FROM emp a WHERE not exists (select * from emp b where a.empno=b.mgr); 3. Numele angajatului, salariul şi maximul salariilor medii ale departamentelor, pentru angajaţii care au salariul mai mare decât maximul salariilor medii ale departamentelor. SELECT ename, sal, x.max_sal_med FROM emp a, (SELECT max(avg(sal)) max_sal_med FROM emp GROUP BY deptno ) x sal>x.max_sal_med;
WHERE
4.Numele departamentului şi numărul mediu de săptămâni lucrate în fiecare departament în anul 1981, dacă numărul mediu de săptămâni lucrate este peste 26. SELECT dname, x.nr_sapt FROM dept, (SELECT avg(52-to_char(hiredate,'ww')) nr_sapt FROM emp WHERE to_char(hiredate,'yyyy')=1981 GROUP BY deptno) x WHERE x.nr_sapt>26 ; Subiectul 2 1. Situaţia : Total salarii şefi Plătite în departamentul 10 Plătite în departamentul 30
Plătite în departamentul 20
SELECT sum(sal), sum(decode(deptno,10,sal,0))"Dept 10",sum(decode(deptno,20,sal,0))"Dept 20", sum(decode(deptno,30,sal,0))"Dept 30" FROM emp WHERE empno in(select mgr from emp); 2. Numele angajaţilor de pe nivelul 2 al ierarhiei organizaţiei. SELECT ename FROM emp WHERE mgr=(select empno from emp where ename='KING'); 3. Numele angajatului, minimul salariului mediu al localitaţilor, salariul angajatului, maximul salariului mediu al localităţilor, pentru cei ce au salariul cuprins între minimul salariului mediu al localitaţilor şi maximul salariului mediu al localităţilor. SELECT ename, x.min_sal_med, sal, x.max_sal_med FROM emp , (SELECT min(avg(sal)) min_sal_med,max(avg(sal)) max_sal_med FROM emp b, dept WHERE b.deptno=dept.deptno GROUP BY loc) x WHERE sal between x.min_sal_med and x.max_sal_med;
4. Numele angajatului, salariul şi comisionul pentru cei ce au salariul şi comisionul identice cu ale vreunui angajat din departamentul cu cel mai mare număr de angajaţi. SELECT ename, sal, comm, deptno FROM emp WHERE (sal,comm) IN (SELECT sal,comm FROM emp WHERE deptno = (SELECT deptno FROM emp GROUP BY deptno HAVING count(ename)=(SELECT max(count (ename)) FROM emp GROUP BY deptno))); Subiectul 3 1. Situaţia : Numele şefului minim
Salariul minim subordonaţi
SELECT a.ename NUME_SEF, NUME_ANG_CU_SAL_MIN
Numele subordonatului cu salar
x.sal_min
SAL_MIN_Subordonati,b.ename
FROM emp a,emp b, (SELECT min(sal)sal_min, mgr FROM emp GROUP BY mgr )x WHERE exists (SELECT * FROM emp b WHERE (a.empno=b.mgr)) and (x.mgr=a.empno) and (b.ename in (SELECT ename FROM emp c WHERE x.sal_min=c.sal )); 2. Numele angajaţilor de pe nivelul 3 al ierarhiei organizaţiei. SELECT ename FROM emp a WHERE a.mgr in (SELECT empno FROM emp b WHERE b.mgr in (SELECT empno FROM emp c WHERE c.ename = 'KING')); 3. Numele angajaţilor care au aceeaşi meserie şi lucrează în aceiaşi localitate cu cei ce au aceiaşi grupă salarială cu şeful lor direct. SELECT ename FROM emp a, dept e WHERE (a.deptno=e.deptno) and (job, loc) in (SELECT job,d.loc FROM emp b, dept d, salgrade s WHERE b.deptno=d.deptno and sal between s.hisal and s.losal and grade = (SELECT grade FROM salgrade t, emp c WHERE b.mgr=c.empno and c.sal between t.losal and t.hisal)); 4. Numele angajatului, salariul, grupa salarială în care se află salariul, salariul majorat cu 20%, grupa salarială în care se află salariul majorat, pentru cei care prin majorarea salariului ajung în grupa salarială imediat următoare aceleia în care au fost iniţial. SELECT ename NUME, sal SALARIUL, s.grade GRUPA_SALARIALA, 1.2*sal SAL_MAJORAT, b.grade GRUPA_SAL_MAJ FROM emp a, salgrade s, salgrade b WHERE sal between s.losal and s.hisal AND b.grade = (SELECT grade FROM salgrade c WHERE 1.2*a.sal between c.losal AND c.hisal ) AND s.grade!=b.grade; Subiectul 4 1. Numărul angajaţilor care nu sunt şefi. SELECT count(ename) FROM emp a WHERE not exists (SELECT * FROM emp b WHERE a.empno=b.mgr); 2. Situaţia: Nume angajat
Colegi_S1_81
Colegi_S2_81
Colegi_S1_82
Colegi_S2_82
Colegi_S1_83 Colegi_S2_83 Total_colegi unde în linia afişaţă pentru fiecare angajat se afişează numele acestuia, numărul de colegi încadraţi în semestrul şi respectiv anul corespunzător fiecăreia dintre coloanele anterioare, precum şi numărul total de colegi. SELECT ename nume, (SELECT count(*) FROM emp c WHERE to_char(c.hiredate,'yyyy')=1981 to_char(c.hiredate,'ddd')<182 AND c.empno<>a.empno) colegi_s1_81,
AND
(SELECT count(*) FROM emp c WHERE to_char(c.hiredate,'yyyy')=1981 to_char(c.hiredate,'ddd')>=182 AND c.empno<>a.empno) colegi_s2_81,
AND
(SELECT count(*) FROM emp c WHERE to_char(c.hiredate,'yyyy')=1982 to_char(c.hiredate,'ddd')<182 AND c.empno<>a.empno) colegi_s1_82,
AND
(SELECT count(*) FROM emp c WHERE to_char(c.hiredate,'yyyy')=1982 to_char(c.hiredate,'ddd')>=182 AND c.empno<>a.empno) colegi_s2_82,
AND
(SELECT count(*) FROM emp c WHERE to_char(c.hiredate,'yyyy')=1983 to_char(c.hiredate,'ddd')<182 AND c.empno<>a.empno) colegi_s1_83,
AND
(SELECT count(*) FROM emp c where to_char(c.hiredate,'yyyy')=1983 to_char(c.hiredate,'ddd')>=182 AND c.empno<>a.empno) colegi_s2_83,
AND
(SELECT count(*) FROM emp)"Total_colegi"
FROM emp a ORDER BY hiredate;
3. Numele lunii, numărul angajaţilor încadraţi în luna respectivă şi numele angajatului încadrat în luna reapectivă care are cea mai mică vechime. SELECT to_char(a.hiredate,'mon') NUME_LUNA, x.nr,y.ename FROM emp a, (SELECT to_char(hiredate,'mon') to_char(hiredate,'mon'))x,
data,count(*)
nr
FROM
emp
b
GROUP
BY
(SELECT to_char(hiredate,'mon') dat, ename, max(hiredate) FROM emp d GROUP BY to_char(hiredate,'mon'),ename HAVING max(hiredate) IN (SELECT max(hiredate) FROM emp GROUP BY to_char(hiredate,'mon')))y WHERE to_char(a.hiredate,'mon')= x.data AND to_char(a.hiredate,'mon')=y.dat AND y.dat=x.data GROUP BY to_char(a.hiredate,'mon'), x.nr,y.ename; 4. Maximul salariilor medii ale localitaţilor. SELECT max(avg(sal)) FROM emp a, dept WHERE a.deptno=dept.DEPTNO GROUP BY loc; Subiectul 5 1. Numărul angajaţilor de pe nivelul 2 al organizaţiei. SELECT ename FROM emp a WHERE a.mgr=(SELECT empno FROM emp WHERE ename='KING'); 2. Situaţia : Nume angajat Salar Comision Nume dep Comision_mediu_dep Loc Comision_mediu_loc Grupă_salarială Comision_mediu_grupă în care în coloanele aferente comisionului afişat sau calculat unde nu există o valoare se afişează « Nu s-a negociat ». SELECT
ename
NUME_ANGAJAT,
sal
SALAR,
DECODE(comm,null,'NU_S-
A_NEGOCIAT',comm) COMISION, dname NUME_DEP, x.com_mediu_dep, d.loc LOC, y.com_mediu_loc, GRUPA_SALARIALA, z.com_mediu_grupa FROM emp a, dept d,salgrade s,
s.grade
(SELECT deptno, avg(nvl(comm,0)) com_mediu_dep FROM emp GROUP BY deptno) x, (SELECT loc, avg(nvl(comm,0)) com_mediu_loc m.deptno=n.deptnoGROUP BY loc) y,
FROM
emp
m,
dept
n
WHERE
(SELECT grade, avg(nvl(comm,0)) com_mediu_grupa FROM emp m, salgrade zz WHERE m.sal between zz.LOSAL and zz.HISAL GROUP BY grade) z WHERE a.deptno=d.deptno and a.sal between s.losal and s.hisal and a.deptno=x.deptno and d.loc=y.loc and s.grade=z.grade; 3. Numărul întreg de luni dintre primul angajat şi ultimul angajat. SELECT(to_char(max(hiredate),'yyyy')-to_char(min(hiredate),'yyyy'))*12to_char(max(hiredate),'mm')+to_char(min(hiredate),'mm') FROM emp; 4. Graficul salariului mediu al localităţilor, reprezentând în fiecare linie numele localităţii şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea alfabetică a localităţilor. SELECT Rpad(loc,avg(sal)/100,'*') FROM salgrade s, dept d, emp a WHERE a.deptno=d.deptno and a.sal between s.losal and s.hisal GROUP BY loc ORDER BY loc; Subiectul 6 1. Numărul angajaţilor de pe nivelul 3 al organizaţiei SELECT ename FROM emp a WHERE a.mgr in (SELECT empno FROM emp b WHERE b.mgr in (SELECT empno FROM emp c WHERE ename like 'KING')); 2. Situaţia : Localitatea
Nr_manageri Nr_analisti
Nr_altii Total_angajaţi
SELECT loc, sum(decode(job,'MANAGER',1,0)) NR_MANAGERI, sum(decode(job,'ANALYST', 1,0)) NR_ANALISTI, sum(case when job!='MANAGER' and job !='ANALYST' then 1 else 0 end) "NR_ALTII",count(*) TOTAL_ANGAJATI FROM emp a, dept d WHERE a.deptno=d.deptno GROUP BY loc; 3. Graficul salariului mediu al meseriilor distincte din fiecare localitate, reprezentând în fiecare linie numele localităţii, meseria şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea alfabetică a localităţilor şi meseriilor. SELECT d.loc, RPAD(a.JOB,x.sal_med/100,'*') FROM emp a, dept d, (SELECT avg(sal) sal_med, e.loc, b.job FROM emp b, dept e WHERE b.DEPTNO=e.DEPTNO GROUP BY loc,job)x WHERE a.deptno=d.DEPTNO and d.loc=x.loc and a.job=x.job GROUP BY d.loc, a.job, RPAD(a.job,x.sal_med/100,'*') ORDER BY d.loc, a.job; 4. Sefii care au fost angajaţi în acelaşi trimestru cu şefii lor. SELECT ename FROM emp a WHERE empno in (SELECT distinct mgr FROM emp b) and to_char(hiredate,'q') in (SELECT to_char (hiredate,'q') FROM emp c WHERE a.mgr=c.empno and a.deptno=c.deptno)
Subiectul 7 1. Situaţia : Nume_şef şefi
Nr_total_subord_direct
Nr_şefi_subord
Nr_subord ce nu sunt
SELECT ename NUME_SEF, (SELECT count(*) aaa FROM emp c WHERE c.mgr=a.empno)NR_TOTAL_SUBORD_DIRECT, (SELECT count(*) bbb FROM emp d WHERE d.mgr=a.empno and exists( SELECT * FROM emp bb WHERE bb.mgr=d.empno))NR_SEFI_SUBORD, (SELECT count(*) FROM emp e WHERE e.mgr=a.empno and not exists (SELECT * FROM emp bbb WHERE bbb.mgr=e.empno))NR_NESEFI_SUBORD FROM emp a WHERE exists (SELECT* FROM emp b WHERE b.mgr=a.empno); 2. Numele zilei din săptămână, numărul angajaţilor încadraţi în ziua respectivă a săptămânii şi numele angajatului angajat în ziua reapectivă a săptămânii care are cea mai mare vechime. SELECT zi,nrang,a.ename,zi_ang FROM emp a , (SELECT to_char(hiredate,'day') zi, count(*)nrang, min(hiredate) zi_ang FROM emp GROUP BY to_char(hiredate,'day'))x WHERE to_char(a.hiredate,'day')=zi and hiredate=zi_ang; 3. Angajaţii care au meseria şi comisionul identice cu meseria şi comisionul celor ce au salariul mai mic decât maximul salariilor medii ale localităţilor. SELECT ename FROM emp a WHERE (job,nvl(comm,-1)) in (SELECT job, nvl(comm,-1) FROM emp b WHERE b.sal< (SELECT max(avg(sal)) FROM emp c, dept WHERE c.deptno=dept.deptno GROUP BY loc)); 4. Numele salariatului, localitatea, salariul mediu al localităţii, salariul minim al localităţii şi salariul maxim al localităţii pentru localităţile în care lucrează cel puţin 4 angajaţi. SELECT ename, loc, min(sal), max(sal) FROM emp a, dept d WHERE a.deptno=d.DEPTNO GROUP BY loc,ename HAVING loc in (SELECT loc FROM emp b, dept e WHERE b.deptno=e.DEPTNO GROUP BY loc HAVING count(ename)>=4); Subiectul 8 1. Situaţia : Localitatea Salar_med_gr_1 Salar_med_gr_2 Salar_med_gr_3 Salar_med_gr_4 Salar_med_gr_5 Salar_med_loc unde salariile medii afişate sunt salariile medii pe grupele salariale în care se încadrează salariul angajaţilor din localiatea respectivă. SELECT dd.loc,x.sal_med_gr1,y.sal_med_gr2,z.sal_med_gr3,q.sal_med_dept FROM dept dd, (SELECT avg(sal) sal_med_gr1, loc FROM emp b, dept d, salgrade s WHERE b.deptno=d.deptno and b.sal between s.losal and s.HISAL and grade=1 GROUP BY loc) x,
(SELECT avg(sal) sal_med_gr2, loc FROM emp b, dept d, salgrade s WHERE b.deptno=d.deptno and b.sal between s.losal and s.HISAL and grade=2 GROUP BY loc)y, ( SELECT avg(sal) sal_med_gr3, loc FROM emp b, dept d, salgrade s WHERE b.deptno=d.deptno and b.sal between s.losal and s.HISAL and grade=3 GROUP BY loc)z, (SELECT avg(sal) sal_med_dept,loc,ddd.deptno FROM cc.deptno=ddd.DEPTNO GROUP BY ddd.deptno,loc)q
emp
cc,dept
ddd
WHERE
WHERE d.deptno=q.deptno GROUP dd.loc,x.sal_med_gr1,y.sal_med_gr2,z.sal_med_gr3,q.sal_med_dept;
BY
2. Numele salariatului, salariul, localitatea şi salariul mediu al localităţii pentru acei salariaţi care s-au angajat până cel târziu în a 200-a zi din an. SELECT ename, sal, d.loc, x.sal_med FROM emp a, dept d, (SELECT avg(sal) sal_med,loc FROM emp b, dept c WHERE b.deptno=c.DEPTNO GROUP BY loc) x WHERE d.loc=x.loc and a.deptno=d.deptno and to_char(hiredate,'ddd')<200; 3. Numele angajatului, meseria, salariul şi salariul mediu al meseriei pentru cei ce au salariul mai mare decât maximul salariilor medii ale meseriilor. SELECT ename,a.job, sal,x.sal_med_job FROM emp a,
(SELECT avg(sal)sal_med_job,job FROM emp b GROUP BY job)x WHERE a.job=x.job and sal> all(SELECT avg(sal)sal_med_job FROM emp b GROUP BY job);
4. Numele departamentului şi numărul mediu de luni lucrate de salariaţi în departament. SELECT d.dname, x.med_luni FROM dept d, emp a, (SELECT avg(months_between(sysdate,hiredate))med_luni FROM emp GROUP BY deptno)x WHERE a.deptno=d.deptno GROUP BY d.deptno,dname,x.med_luni; Subiectul 9 1. Situaţia : Nume_dep Salar_med_gr_1 Salar_med_gr_2 Salar_med_gr_3 Salar_med_gr_4 Salar_med_gr_5 Salar_med_dep unde salariile medii afişate sunt salariile medii pe grupele salariale în care se încadrează salariul angajaţilor din departamentul respectiv. 2. Numele angajatului, salariul, grupa salarială în care se află salariul, salariul majorat cu 40%, grupa salarială în care se află salariul majorat, pentru cei care prin majorarea salariului ajung într-o grupă salarială cu 2 faţă de aceea în care au fost iniţial. SELECT ename, sal, s.grade, sal*1.4, b.grade FROM emp a, salgrade s, salgrade b WHERE a.sal between s.losal and s.hisal and sal*1.4 between b.losal and b.hisal and b.grades.grade=2; 3. Numele salariatului, meseria şi salariul mediu al meseriei pentru acei salariaţi care s-au angajat până cel târziu în a 40-a săptămână din an. SELECT ename, a.job, x.sal_med FROM emp a, (SELECT avg(sal) sal_med,job FROM emp GROUP BY job) x
WHERE a.job=x.job and to_char(hiredate,'ww')<=40; 4. Numele departamentului şi numărul mediu de săptămâni lucrate de salariaţii din departament în anul 1981. SELECT dname, x.nr_med FROM emp a, dept d , (SELECT avg(52-to_char(hiredate,'ww'))nr_med,deptno FROM emp c WHERE to_char(hiredate,'yy')=81 GROUP BY deptno)x WHERE a.deptno=d.deptno and x.deptno=a.deptno GROUP BY d.deptno,dname,x.nr_med; Subiectul 10 1. Situaţia : Luna_angajării Salar_med_gr_1 Salar_med_gr_2 Salar_med_gr_3 Salar_med_gr_4 Salar_med_gr_5 unde salariile medii afişate sunt salariile medii pe grupele salariale în care se încadrează salariul angajaţilor din luna respectivă. 2. Graficul salariului mediu al departamentelor, reprezentând în fiecare linie numele departamentului şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea alfabetică a departamentelor. SELECT dname, RPAD(' ',avg(sal)/100,'*') FROM emp a, dept d WHERE a.deptno=d.deptno GROUP BY dname; 3. Localitatea şi meseria şi numărul mediu de zile lucrate de salariaţi în anul 1981. SELECT loc, job, x.nr_mediu_zile FROM dept d,emp a, (SELECT avg(52-to_char(hiredate,'ww'))nr_mediu_zile FROM emp)x WHERE a.deptno=d.deptno and to_char(hiredate,'yy')=81 GROUP BY loc,job,x.nr_mediu_zile; 4. Numele şefului, salariul minim al subordonaţilor săi, numele subordonatului cu salariul minim. SELECT a.ename, x.min_sal,b.ename FROM emp a,emp b, (SELECT min(sal) min_sal ,mgr FROM emp c GROUP BY mgr)x WHERE exists (SELECT * FROM emp b WHERE a.empno=b.mgr) and x.mgr=a.empno and a.deptno=b.deptno and b.sal=x.min_sal GROUP BY a.mgr,a.ename,x.min_sal,b.ename; Subiectul 11 1. Situaţia : Anul_angajării Salar_med_gr_1 Salar_med_gr_2 Salar_med_gr_3 Salar_med_gr_4 Salar_med_gr_5 unde salariile medii afişate sunt salariile medii pe grupele salariale în care se încadrează salariul angajaţilor din anul respectiv. Numele angajatului, meseria şi comisionul pentru cei ce au meseria şi comisionul identice cu ale vreunui SELECT ename, job, comm. FROM emp a WHERE (job, nvl(comm,-1)) in (SELECT job, nvl(comm,-1) FROM b WHERE deptno=(select deptno FROM emp c GROUP BY deptno HAVING count(*) in (SELECT min(count(*)) FROM emp d GROUP BY deptno)));
2. Numele şefului, salariul mediu al subordonaţilor. SELECT ename, (SELECT avg(sal) FROM emp c WHERE c.mgr=a.empno)sal_mediu_subord FROM emp a WHERE exists (SELECT * FROM emp b WHERE a.empno=b.mgr) 3. Graficul salariului pentru angajaţii de pe nivelul 3 al ierarhiei, constând în precizarea pe fiecare linie a numelui angajatului şi a câte unui caracter ‘$’ pentru fiecare 100 de unităţi salariale. SELECT ename,RPAD(' ',sal/100,'$') FROM emp WHERE level=3 CONNECT BY PRIOR empno = mgr START WITH mgr IS NULL; Subiectul 12 Scrieţi interogările care afişează: 1. Situaţia : Trimestrul_angajării Salar_med_gr_1 Salar_med_gr_2 Salar_med_gr_3 Salar_med_gr_4 Salar_med_gr_5 unde salariile medii afişate sunt salariile medii pe grupele salariale în care se încadrează salariul angajaţilor din trimestrul respectiv. 2. Numele departamentului şi numărul mediu de zile lucrate în fiecare departament în anul 1981, dacă numărul mediu de zile lucrate este peste 180. SELECT dname, x.avg_zile FROM dept d , (SELECT avg((52-to_char(hiredate,'ww'))*7)avg_zile,deptno to_char(hiredate,'yyyy')=1981 GROUP BY deptno)x dname,x.avg_zile;
WHERE
x.avg_zile>18
and
FROM
emp
b
WHERE
d.deptno=x.deptno GROUP BY
3. Numele şefului, salariul maxim al subordonaţilor, subordonatul care are salariul maxim. SELECT a.ename,x.sal_max, b.ename FROM emp a, emp b, (SELECT max(sal) sal_max,mgr FROM emp c GROUP BY mgr)x WHERE exists (SELECT * FROM emp b WHERE a.empno=b.mgr) and b.sal=x.sal_max and x.mgr=a.empno; 4. Graficul salariului pentru angajaţii de pe nivelul 2 al ierarhiei, constând în precizarea pe fiecare linie a numelui angajatului şi a câte unui caracter ‘$’ pentru fiecare 100 de unităţi salariale. SELECT ename,RPAD(' ',sal/100,'*')FROM emp WHERE level=2 connect by prior empno=mgr start with mgr is null; Subiectul 13 1. Situaţia : Nume şef Salar_med_gr_1 Salar_med_gr_2 Salar_med_gr_3 Salar_med_gr_4 Salar_med_gr_5 unde salariile medii afişate sunt salariile medii ale subordonaţilor în funcţie de grupa salarială în care se încadrează salariul acestora. SELECT ename, decode(x.g,1,x.sal_med_sub,NULL) GR_1, decode(x.g,2,x.sal_med_sub,NULL) GR_2, decode(x.g,3,x.sal_med_sub,NULL) GR_3,decode(x.g,4,x.sal_med_sub,NULL) GR_4,
decode(x.g,5,x.sal_med_sub,NULL) GR_5 FROM emp a, (SELECT avg(sal) sal_med_sub,mgr,grade g FROM emp c, salgrade s WHERE c.sal between losal and hisal GROUP BY mgr,grade)x WHERE exists (SELECT * FROM emp b WHERE a.empno=b.mgr) and a.empno=x.mgr; 2. Trimestrul, numărul angajaţilor încadraţi în trimestrul respectiv şi numele angajatului încadrat în trimestrul reapectiv care are cea mai mare vechime. SELECT to_char(a.hiredate,'q'),count(a.ename),d.ename FROM emp a,emp d GROUP BY to_char(a.hiredate,'q') ,d.ename HAVING d.ename in (SELECT ename FROM emp b WHERE to_char(a.hiredate,'q')=to_char(b.hiredate,'q') GROUP BY to_char(hiredate,'q'),ename,sysdate-hiredate HAVING sysdate-hiredate in (SELECT max(sysdate-hiredate) FROM emp c GROUP BY to_char(hiredate,'q'))); 3. Graficul salariului mediu al grupelor salariale, reprezentând în fiecare linie numărul grupei salariale, salariul minim din grupă, salariul maxim din grupă şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea grupelor salariale. SELECT s.grade, x.sal_min,x.sal_max,RPAD(' ',x.med_sal/100,'*') FROM salgrade s,emp a, (SELECT min(sal) sal_min,avg(sal)med_sal, max(sal) sal_max,grade FROM emp a, salgrade ss WHERE a.sal between ss.losal and ss.HISAL GROUP BY ss.grade) x WHERE a.sal between losal and hisal and s.grade,x.sal_min,x.sal_max,x.med_sal ORDER BY grade;
s.grade=x.grade
GROUP
BY
4. Numele angajatului, departamentul, salariul şi salariul mediu al departamentului pentru cei ce au salariul mai mare decât maximul salariilor medii ale departamentelor. SELECT ename, dname, sal, x.sal_med FROM emp a, dept b, (SELECT avg(sal) sal_med,deptno FROM emp GROUP BY deptno) x WHERE a.deptno=b.deptno and a.deptno=x.deptno GROUP BY a.deptno,ename,dname,sal,x.sal_med HAVING sal >=all(SELECT avg(sal) sal_med FROM emp GROUP BY deptno); Subiectul 14 1. Situaţia : Localitatea Nr_ang_gr_1 Nr_ang gr_2 Nr_ang gr_3 Nr_ang gr_4 Nr_ang gr_5 Nr_ang loc unde numărul angajaţilor afişat reprezintă numărul de angajaţi pe grupele salariale în care se încadrează salariul angajaţilor din localiatea respectivă. SELECT d.loc, sum(decode(x.gr,1,1,0)) NR_ANG_GR_1, sum(decode(x.gr,2,1,0))NR_ANG_GR_2, sum(decode(x.gr,3,1,0)) NR_ANG_GR_3,sum(decode(x.gr,4,1,0))NR_ANG_GR_4, sum(decode(x.gr,5,1,0))NR_ANG_GR_5,y.nr_ang_loc NR_ANG_GR_5 FROM dept d,emp a, (SELECT grade gr,losal, hisal FROM salgrade s GROUP BY grade,losal, hisal)x, (SELECT count(ename) nr_ang_loc,loc FROM dept,emp WHERE emp.deptno=dept.deptno GROUP BY loc)y WHERE d.deptno=a.deptno and a.sal between x.losal and x.hisal and d.loc=y.loc GROUP BY
d.loc,y.nr_ang_loc; 2. Numărul şefilor. SELECT count(distinct(mgr)) FROM emp ; 3. Numele salariatului, salariul, grupa salarială şi salariul mediu al grupei salariale pentru acei salariaţi care s-au angajat în zilele de luni, marţi, miercuri şi joi şi în săptămânile pare din an. SELECT ename, sal, s.grade, x.sal_med FROM emp a,salgrade s, (SELECT avg(sal)sal_med,grade FROM emp a, salgrade WHERE a.sal between losal and hisal GROUP BY grade)x WHERE mod(to_char(hiredate,'ww'),2)=0 and x.grade=s.grade and a.sal between s.losal and s.hisal and (to_char(hiredate,'d')=2 or to_char(hiredate,'d')=3 or to_char(hiredate,'d')=4 or to_char(hiredate,'d')=5); 4. Graficul salariului pentru angajaţii de pe ultimul nivel al ierarhiei, constând în precizarea pe fiecare linie a numelui angajatului şi a câte unui caracter ‘$’ pentru fiecare 100 de unităţi salariale. SELECT ename, RPAD(' ',sal/100,'$') FROM emp WHERE level=4 connect by prior empno=mgr; Subiectul 15 1. Situaţia : Nume_dep Nr_ang_gr_1 Nr_ang gr_2 Nr_ang gr_3 Nr_ang gr_4 Nr_ang gr_5 Nr_ang dep unde numărul angajaţilor afişat reprezintă numărul de angajaţi pe grupele salariale în care se încadrează salariul angajaţilor din departamentul respectiv. SELECT d.dname,sum(decode(x.gr,1,1,0)),sum(decode(x.gr,2,1,0)),sum(decode(x.gr,3,1,0)), sum(decode(x.gr,4,1,0)),sum(decode(x.gr,5,1,0)), y.nr_ang FROM emp a, dept d,salgrade s, (SELECT grade gr,dd.deptno FROM emp aa, dept dd, salgrade ss WHERE aa.sal between ss.losal and ss.HISAL and aa.deptno=dd.DEPTNO GROUP BY dd.dname,dd.deptno,grade) x , (SELECT count(ename) nr_ang,dname FROM emp aaa,dept ddd WHERE aaa.deptno=ddd.deptno GROUP BY dname)y WHERE x.deptno=d.deptno and x.gr=s.grade and a.sal between s.losal and s.hisal and a.deptno=d.deptno and d.dname=y.dname GROUP BY d.dname,y.nr_ang ORDER BY dname; 2. Numele angajatului, meseria, salariul şi maximul salariilor minime ale departamentelor, pentru angajaţii care au salariul mai mare decât maximul salariilor minime ale departamentelor. SELECT a.ename, a.job, a.sal, x.max_min FROM emp a , (SELECT max(min(sal)) max_min FROM emp GROUP BY deptno)x a.sal>x.max_min;
WHERE
3. Numele şefului, salariul mediu al subordonaţilor, graficul salariului mediu al subordonaţilor constând din reprezentarea pentru fiecare 100 de unităţi salariale a unui caracter $.
SELECT ename, x.sal_med,RPAD(' ',x.sal_med/100,'$') FROM emp a, (SELECT avg(sal) sal_med, mgr FROM emp a GROUP BY mgr)x WHERE a.empno=x.mgr and exists(SELECT * FROM emp b WHERE a.empno=b.mgr) 4. Angajaţii care au aceleaşi număr şef şi meserie cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. Subiectul 16 1. Situaţia : Luna_angajării Nr_ang_gr_1 Nr_ang_gr_2 Nr_ang_gr_3 Nr_ang_gr_4 Nr_ang_gr_5 unde numărul angajaţilor afişat reprezintă numărul de angajaţi pe grupele salariale în care se încadrează salariul angajaţilor din luna respectivă SELECT to_char(hiredate,'month') Luna,sum(decode(grade,1,1,0)) Nr_gr_1,sum(decode(grade,2,1,0)) Nr_gr_2, sum(decode(grade,3,1,0)) Nr_gr_3, sum(decode(grade,4,1,0)) Nr_gr_4,sum(decode(grade, 5,1,0)) Nr_gr_5 FROM emp,salgrade WHERE sal between losal and hisal GROUP BY to_char(hiredate,'month'); 2. Numele angajatului, salariul, grupa salarială în care se află salariul, salariul majorat cu 60%, grupa salarială în care se află salariul majorat, pentru cei care prin majorarea salariului ajung într-o grupă salarială cu 3 faţă de aceea în care au fost iniţial. SELECT ename,sal,a.grade,sal*1.6,b.grade FROM emp,salgrade a,salgrade b WHERE sal between a.losal and a.hisal and sal*1.6 between b.losal and b.hisal and b.grade=a.grade+2; 3. Numele şefului, salariul minim al subordonaţilor, graficul salariului minim al subordonaţilor constând din reprezentarea pentru fiecare 100 de unităţi salariale a unui caracter $. SELECT b.ename,min(a.sal),min(a.ename) FROM emp a, emp b WHERE a.mgr=b.empno GROUP BY b.ename; 4. Angajaţii care au aceleaşi comision şi meserie cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT distinct b.ename FROM emp a,emp b WHERE a.mgr=b.empno and nvl(b.comm,-1)=nvl(a.comm,-1) and b.job=a.job;
Subiectul 17 1. Situaţia : Anul_angajării Nr_ang_gr_1 Nr_ang_gr_2 Nr_ang_gr_3 Nr_ang_gr_4 Nr_ang_gr_5 unde numărul angajaţilor afişat reprezintă numărul de angajaţi pe grupele salariale în care se încadrează salariul angajaţilor din anul respectiv. SELECT to_char(hiredate,'yyyy') Luna, sum(decode(grade,1,1,0)) Nr_gr_1, sum(decode(grade,2,1,0)) Nr_gr_2,sum(decode(grade,3,1,0)) Nr_gr_3, sum(decode(grade,4,1,0)) Nr_gr_4,sum(decode(grade,5,1,0)) Nr_gr_5 FROM emp,salgrade WHERE sal between losal and hisal GROUP BY to_char(hiredate,'yyyy'); 2. Numele angajatului, numele departamentului şi comisionul pentru cei ce au departamentul şi comisionul identice cu ale vreunui angajat din localitatea cu cel mai mare număr de angajaţi SELECT
ename,dname,comm.
FROM
emp,dept
WHERE
emp.deptno=dept.deptno
and
(emp.deptno,nvl(comm,-1)) = any (SELECT emp.deptno,nvl(comm,-1) FROM emp,dept, (SELECT max(count(*)) maxnrang FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY loc) x, (SELECT loc,count(*) nrang FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY loc) y WHERE nrang=maxnrang);
emp.deptno=dept.deptno
and
dept.loc=y.loc
and
3. Graficul salariului mediu al anilor în care s-au produs angajări, reprezentând în fiecare linie anul şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea descrescătoare a anilor. SELECT lpad(to_char(hiredate,'yyyy'),10)||' '||lpad(' ',trunc(avg(sal)/100,0),'*') Grafic FROM emp GROUP BY to_char(hiredate,'yyyy') ORDER BY to_char(hiredate,'yyyy') desc; 4. Numele şefului şi numărul de subordonaţi ai acestuia. SELECT b.ename Nume_sef,count(*) Nr_subordonati FROM emp a,emp b WHERE a.mgr=b.empno GROUP BY b.ename; Subiectul 18 1. Situaţia : Trimestrul_angajării Nr_ang_gr_1 Nr_ang_gr_2 Nr_ang_gr_3 Nr_ang_gr_4 Nr_ang_gr_5 unde numărul angajaţilor afişat reprezintă numărul de angajaţi pe grupele salariale în care se încadrează salariul angajaţilor din trimestrul respectiv. SELECT to_char(hiredate,'Q') Trimestrul, sum(decode(grade,1,1,0)) Nr_gr_1, sum(decode(grade,2,1,0)) Nr_gr_2,sum(decode(grade,3,1,0)) Nr_gr_3, sum(decode(grade,4,1,0)) Nr_gr_4,sum(decode(grade,5,1,0)) Nr_gr_5 FROM emp,salgrade WHERE sal between losal and hisal GROUP BY to_char(hiredate,'Q'); 2. Numele angajatului, minimul salariului maxim al localitaţilor, salariul angajatului, maximul salariului maxim al localităţilor, pentru cei ce au salariul cuprins între minimul salariului maxim al localitaţilor şi maximul salariului maxim al localităţilor. SELECT ename,minmaxloc,sal,maxmaxloc FROM emp, (SELECT min(max(sal)) minmaxloc,max(max(sal)) maxmaxloc FROM emp,dept WHERE emp.deptno=dept.deptno group by loc) x WHERE sal between minmaxloc and maxmaxloc; 3. Anul, numărul angajaţilor încadraţi în anul respectiv şi numele angajatului încadrat în anul reapectiv care are cea mai mare vechime. SELECT to_char(hiredate,'yyyy') Anul, Nr_angajati, ename Angajat_cu_vechime_maxima FROM emp, (SELECT to_char(hiredate,'yyyy') to_char(hiredate,'yyyy'))
aaaa,count(*)
nr_angajati
FROM emp GROUP BY
WHERE to_char(hiredate,'yyyy')=aaaa and sysdate-hiredate=( SELECT max(sysdate-hiredate) FROM emp);
4. Minimil salariilor medii ale grupelor salariale. SELECT min(avg(sal)) FROM emp,salgrade WHERE sal between losal and hisal GROUP BY grade; Subiectul 19 1. Situaţia : Nume şef Nr_ang_gr_1 Nr_ang_gr_2 Nr_ang_gr_3 Nr_ang_gr_4 Nr_ang_gr_5 unde numărul angajaţilor afişat reprezintă numărul subordonaţilor în funcţie de grupa salarială în care se încadrează salariul acestora. SELECT b.ename Sef, sum(decode(grade,1,1,0)) Nr_gr_1, sum(decode(grade,2,1,0)) Nr_gr_2,sum(decode(grade,3,1,0)) Nr_gr_3, sum(decode(grade,4,1,0)) Nr_gr_4,sum(decode(grade,5,1,0)) Nr_gr_5 FROM emp a,emp b,salgrade WHERE a.mgr=b.empno and a.sal between losal and hisal GROUP BY b.ename; 2. Numele salariatului, data angajării, salariul şi salariul mediu al anului în care s-a angajat pentru acei salariaţi care s-au angajat în zilele de vineri sau sâmbătă şi în săptămânile impare din an. SELECT ename,hiredate,sal,salmedan FROM emp, (SELECT to_char(hiredate,'yyyy') aaaa, avg(sal) salmedan FROM emp GROUP BY to_char(hiredate,'yyyy')) x WHERE to_char(hiredate,'yyyy')=aaaa mod(to_char(hiredate,'ww'),2)=0;
and
to_char(hiredate,'d')
in
(6,7)
and
3. Numele şefului, salariul maxim al subordonaţilor, graficul salariului maxim al subordonaţilor constând din reprezentarea pentru fiecare 100 de unităţi salariale a unui caracter $. SELECT b.ename,max(a.sal),rpad(' ',max(a.sal)/100,'$') FROM emp a, emp b WHERE a.mgr=b.empno GROUP BY b.ename; 4. Angajaţii care au aceleaşi comision şi localitate cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT b.ename FROM emp a, emp b, salgrade x, salgrade y, dept q, dept w WHERE a.mgr=b.empno and a.deptno=q.deptno and b.deptno=w.deptno and a.sal between x.losal and x.hisal and b.sal between y.losal and y.hisal and x.grade=y.grade and a.comm=b.comm and q.loc=w.loc; Subiectul 20 1. Situaţia : Numele _zilei Nr_sefi Salar_mediu_sefi unde în coloana referitoare la numărul de şefi se afişează numărul şefilor încadraţi în ziua respectivă, iar în coloana aferentă salariului mediu se repreazintă salariul mediu al şefilor încadraţi în ziua respectivă. SELECT distinct to_char(hiredate,'day') ziua,x.numar_sefi, x.salariu_mediu FROM emp e,dept d,
(SELECT to_char(hiredate,'day') zi,count(*) numar_sefi, avg(sal) salariu_mediu FROM emp,dept WHERE empno in (SELECT mgr FROM emp) and emp.deptno=dept.deptno GROUP BY to_char(hiredate,'day') )x WHERE d.deptno=e.deptno and x.zi=to_char(hiredate,'day'); 2. Numele salariatului, departamentul, salariul mediu al departamentului, salariul minim al departamentului şi salariul maxim al departamentului pentru departamentele în care lucrează cel puţin 3 persoane angajate în aceiaşi lună. SELECT ename, dname, emp.deptno=dept.deptno
avg(sal),
min(sal),
max(sal)
FROM
emp,
dept
WHERE
GROUP BY dname,ename HAVING dname in (SELECT dname FROM emp b,dept e WHERE b.deptno=e.deptno GROUP BY dname HAVING count(ename)>=3); 3. Angajaţii care au aceleaşi comision şi departament cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT b.ename FROM emp a, emp b, salgrade x, salgrade y WHERE a.mgr=b.empno and a.sal between x.losal and x.hisal and b.sal between y.losal and y.hisal and x.grade=y.grade and a.comm=b.comm and a.deptno=b.deptno; 4. Numele şefului, salariul maxim al subordonaţilor, numele subordonatului care are salariul maxim. SELECT a.ename,x.sal_max, b.ename FROM emp a, emp b (SELECT max(sal) sal_max,mgr FROM emp c GROUP BY mgr)x WHERE exists (SELECT * FROM emp b WHERE a.empno=b.mgr) and b.sal=x.sal_max and x.mgr=a.empno; Subiectul 21 1. Situaţia : Trimestrul Nr_sefi Total salarii_sefi unde în coloana referitoare la numărul de şefi se afişează numărul şefilor încadraţi în trimestrul respectiv, iar în coloana aferentă salariului total se repreazintă suma salariilor şefilor încadraţi în trimestrul respectiv. SELECT distinct to_char(hiredate,'q') ,x.numar_sefi, x.salarii_sefi FROM emp e,dept d, (SELECT to_char(hiredate,'q') trim,count(*) numar_sefi, sum(sal) salarii_sefi FROM emp,dept WHERE empno in (SELECT mgr FROM emp) and emp.deptno=dept.deptno GROUP BY to_char(hiredate,'q') )x WHERE d.deptno=e.deptno and x.trim=to_char(hiredate,'q'); 2. Numele angajatului, localitatea, salariul şi salariul minim al localităţii, salariul maxim al localităţii şi salariul mediu al localităţii pentru cei ce au salariul mai mare decât media salariilor mminime ale localităţilor. SELECT ename, dept.loc, sal, salminloc, salmaxloc, salmedloc FROM emp,dept, (SELECT loc, min(sal) salminloc, max(sal) salmaxloc, avg(sal) salmedloc FROM emp, dept WHERE emp.deptno=dept.deptno GROUP BY loc) x
WHERE emp.deptno=dept.deptno and dept.loc=x.loc HAVING sal>avg(salminloc) GROUP BY ename, dept.loc, sal, salminloc, salmaxloc, salmedloc; 3. Angajaţii care au aceleaşi comision şi lună a angajării cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT b.ename FROM emp a, emp b, salgrade x, salgrade y WHERE a.mgr=b.empno and a.sal between x.losal and x.hisal and b.sal between y.losal and y.hisal and x.grade=y.grade and a.comm=b.comm and to_char(a.hiredate,'mm')=to_char(b.hiredate,'mm'); Subiectul 22 1. Situaţia : Grupa salarială 1980-1982 1983-1985 din 1986 unde pentru fiecare grupă salarială se afişează numărul salariaţilor care au salariul în grupa respectivă separaţi în perioadele specificate în antetele coloanelor afişate. SELECT grade Grupa, sum(case when to_char(hiredate,'yyyy') between 1980 and 1982 and grade in(select grade from emp,salgrade where sal between losal and hisal) then 1 else 0 end) "1980-1982", sum(case when to_char(hiredate,'yyyy') between 1983 and 1985 and grade in(select grade from emp,salgrade where sal between losal and hisal) then 1 else 0 end) "1983-1985", sum(case when to_char(hiredate,'yyyy') >1986 and grade in(select grade from emp,salgrade where sal between losal and hisal) then 1 else 0 end) "din 86" FROM emp,salgrade WHERE sal between losal and hisal GROUP BY grade; 2. Graficul salariului mediu al subordonaţilor unui şef, reprezentând în fiecare linie numele şefului şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea alfabetică a şefilor. SELECT ename, rpad(' ',x.sal_med/100,'$') FROM emp a, (SELECT avg(sal) sal_med, mgr FROM emp a GROUP BY mgr)x WHERE a.empno=x.mgr and exists(SELECT * FROM emp b WHERE a.empno=b.mgr); 3. Numele salariatului, numărul departamentului, numele departamentului şi salariul mediu al departamentului pentru acei salariaţi care s-au angajat în zilele de luni, marţi sau joi şi în zilele pare din an. SELECT ename, a.deptno,dname, x.sal_med FROM emp a, dept b, (SELECT avg(sal) sal_med,deptno FROM emp GROUP BY deptno) x WHERE a.deptno=b.deptno and a.deptno=x.deptno and to_char(hiredate,'d') in (1,3,5) mod(to_char(hiredate,'ddd'),2)=0 GROUP BY a.deptno,ename,dname,sal,x.sal_med;
and
4. Angajaţii care au aceleaşi comision şi an al angajării cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT b.ename FROM a, emp b, salgrade x, salgrade y WHERE a.mgr=b.empno and a.sal between x.losal and x.hisal and b.sal between y.losal and y.hisal and x.grade=y.grade and a.comm=b.comm and to_char(a.hiredate,'yy')=to_char(b.hiredate,'yy');
Subiectul 23 1. Numele salariatului, meseria, salariul mediu al meseriei, salariul minim al meseriei şi salariul maxim al meseriei pentru meseriile în care lucrează cel puţin 2 persoane angajate în acelaşi trimestru. 2. Situaţia : Total şefi Ian Feb Mar Apr Mai Iun Iul Aug Sep Oct Nov Dec unde în coloana rezervată fiecărei luni calendaristice se vor înscrie numărul total de şefi angajaţi în luna respectivă. SELECT count(distinct(mgr)) TOTAL_SEFI, sum(case when to_char(hiredate,'mon')='jan' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Ian", sum(case when to_char(hiredate,'mon')='feb' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Feb", sum(case when to_char(hiredate,'mon')='mar' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Mar", sum(case when to_char(hiredate,'mon')='apr' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Apr", sum(case when to_char(hiredate,'mon')='may' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Mai", sum(case when to_char(hiredate,'mon')='jun' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Iun", sum(case when to_char(hiredate,'mon')='jul' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Iul", sum(case when to_char(hiredate,'mon')='aug' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Aug", sum(case when to_char(hiredate,'mon')='sep' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Sep", sum(case when to_char(hiredate,'mon')='oct' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Oct",
sum(case when to_char(hiredate,'mon')='nov' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Noi", sum(case when to_char(hiredate,'mon')='dec' and empno in(SELECT distinct mgr FROM emp) then 1 else 0 end) "Dec" FROM emp; 3. Angajaţii care au aceleaşi comision şi număr de departament cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT b.ename FROM emp a, emp b, salgrade x, salgrade y WHERE a.mgr=b.empno and a.sal between x.losal and x.hisal and b.sal between y.losal and y.hisal and x.grade=y.grade and a.comm=b.comm and a.deptno=b.deptno; 4. Numele salariatului care prin majorarea salariului cu 30% se încadrează în grupa salarială următoare celei în care se afla salariul său înaintea majorării. SELECT ename, sal, s.grade, sal*1.3, b.grade FROM emp a, salgrade s, salgrade b WHERE a.sal between s.losal and s.hisal and sal*1.3 between b.losal and b.hisal and b.grades.grade=1; Subiectul 24 1. Graficul salariului mediu al angajaţilor care nu sunt şefi, reprezentând în fiecare linie numele angajatului şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea alfabetică a angajaţilor. SELECT ename,lpad(' ',x.sal_med/100+1,'*') FROM emp, (SELECT avg(sal) sal_med FROM emp WHERE empno not in (SELECT nvl(mgr,-1) FROM emp)) x WHERE empno not in (SELECT nvl(mgr,-1) FROM emp); 2. Situaţia : Total şefi Angajaţi în trimestrul I trimestrul III Angajaţii în trimestrul IV.
Angajaţi în trimestrul II
Angajaţi în
SELECT distinct x.nr_sefi, y.nr_sefi1,z.nr_sefi2,u.nr_sefi3,v.nr_sefi4 FROM emp,
(SELECT count(empno) nr_sefi FROM emp WHERE empno in (SELECT mgr FROM emp)) x, (SELECT count(empno) nr_sefi1 FROM emp WHERE empno in (SELECT mgr FROM emp) and to_char(hiredate,'q')=1) y, (SELECT count(empno) nr_sefi2 FROM emp WHERE empno in (SELECT mgr FROM emp) and to_char(hiredate,'q')=2) z, (SELECT count(empno) nr_sefi3 FROM emp WHERE empno in (SELECT mgr FROM emp) and to_char(hiredate,'q')=3) u, (SELECT count(empno) nr_sefi4 FROM emp WHERE empno in (SELECT mgr FROM emp) and to_char(hiredate,'q')=4) v ;
3. Angajaţii care au aceleaşi comision şi trimestru al angajării cu a celor ce au aceiaşi grupă
salariala cu vreunul din subordonaţii lor. SELECT ename, nvl(comm,-1),to_char(hiredate,'q') FROM emp WHERE(nvl(comm,-1),to_char(hiredate,'q')) in (SELECT nvl(a.comm,-1),to_char(a.hiredate,'q') FROM emp a,emp b,salgrade s,salgrade t WHERE a.empno=b.mgr and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade ); 4. Graficul salariului mediu în funcţie de trimestrul în care s-a produs angajarea, reprezentând pe fiecare linie numărul trimestrului şi câte un caracter ‘$’ pentru fiecare 100 unităţi salariale. SELECT to_char(hiredate,'q') trimestru,lpad(' ',avg(sal)/100+1,'&') sal_mediu FROM emp GROUP BY to_char(hiredate,'q'); Subiectul 25 1. Situaţia : Trimestrul 1981-1982 1983-1984 din 1985 unde pentru fiecare trimestru în care s-au produs angajări se afişează numărul de angajări produse în perioadele specificate ca şi titluri de coloane. SELECT to_char(hiredate,'q'), sum(case when to_char(hiredate,'yyyy')between 1980 and 1982 then 1 else 0 end) "1980-1982", sum(case when to_char(hiredate,'yyyy')between 1983 and 1984 then 1 else 0 end) "1983-1984", sum(case when to_char(hiredate,'yyyy')>=1985 then 1 else 0 end) "1985-" FROM emp GROUP BY to_char(hiredate,'q'); 2. Grupa salarială, număr şefi, numele şefului cu cei mai puţini subordonaţi. SELECT s.grade,x.numar_sefi,e.ename FROM emp e,salgrade s, (SELECT grade,count(*) numar_sefi FROM emp,salgrade WHERE empno in (SELECT mgr FROM emp) and sal between losal and hisal GROUP BY grade ) x, (SELECT distinct s.grade grad, a.empno numar_sef,count(b.ename) FROM emp a,emp b,salgrade s WHERE a.empno=b.mgr and a.sal between s.losal and s.hisal GROUP BY a.empno,s.grade HAVING count(b.ename)<=all(SELECT count(b.ename) FROM emp a,emp b,salgrade s WHERE a.empno=b.mgr and a.sal between s.losal and s.hisal GROUP BY a.empno,s.grade )) z WHERE e.sal between s.losal and s.hisal and e.empno=z.numar_sef and s.grade=z.grad and x.grade=s.grade; 3. Numele şefilor care au salariul mai mare decât minimul salariului mediu al departamentelor în care sunt angajaţi din 1983. SELECT ename FROM emp WHERE sal>(SELECT min(sal_dept) FROM (SELECT deptno,avg(sal) sal_dept FROM emp WHERE to_char(hiredate,'yyyy')=1981 GROUP BY deptno)) and empno in (SELECT mgr FROM emp);
4. Numele angajaţilor care au mai puţine săptămâni lucrate decât şeful lor. SELECT b.ename FROM emp b.hiredate)/7<(sysdate-a.hiredate)/7; Subiectul 26
b,emp
a
WHERE
b.mgr=a.empno
and
(sysdate-
1. Numele salariatului, salariul, grupa salarială şi salariul mediu al grupei salariale pentru acele grupe salariale în care se află salariile a cel puţin 3 persoane angajate în aceiaşi săptămână a anului. 2. Angajaţii care au aceleaşi comision şi semestru al angajării cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT ename,comm,to_char(hiredate,'q') (nvl(comm,-1),to_char(hiredate,'q')) in
FROM
emp
WHERE
(SELECT nvl(a.comm,-1),to_char(a.hiredate,'q') FROM emp a,emp b,salgrade s,salgrade t WHERE b.mgr=a.empno and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade); 3. Situaţia : Numele zilei 1981-1982 1983-1984 din 1985 unde pentru fiecare nume al zilei din săptămână în care s-au produs angajări se afişează numărul de angajări produse în perioadele specificate ca şi titluri de coloane. SELECT to_char(hiredate,'day'), sum(case when to_char(hiredate,'yyyy') between 1980 and 1981 then 1 else 0 end) "1980-1981", sum(case when to_char(hiredate,'yyyy') between 1982 and 1983 then 1 else 0 end) "1982-1983", sum(case when to_char(hiredate,'yyyy') >1983 then 1 else 0 end) "1983-" FROM emp GROUP BY to_char(hiredate,'day'); 4. Localitatea, număr şefi, numele şefului cu cei mai puţini subordonaţi. SELECT d.loc,x.numar_sefi,e.ename FROM emp e,dept d, (SELECT loc,count(*) numar_sefi FROM emp,dept WHERE empno in (SELECT mgr FROM emp) and emp.deptno= dept.deptno GROUP BY loc ) x, (SELECT distinct d.loc localitate, a.empno numar_sef,count(b.ename) FROM emp a,emp b,dept d WHERE a.empno=b.mgr and d.deptno=a.deptno GROUP BY a.empno,d.loc HAVING count(b.ename)<=all(SELECT count(b.ename) FROM emp a,emp b,dept d WHERE a.empno=b.mgr and a.deptno=d.deptno GROUP BY a.empno,d.deptno)) z WHERE d.deptno=e.deptno and e.empno=z.numar_sef and d.loc=z.localitate and x.loc=d.loc; Subiectul 27 1. Graficul salariului mediu al meseriilor, reprezentând în fiecare linie meseria şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea alfabetică a meseriilor. SELECT job, lpad(' ',avg(sal)/100+1,'*') FROM emp GROUP BY job ORDER BY job; 2. Luna angajării, numărul de şefi angajaţi în luna şi salariul mediu al şefilor angajaţi în luna. SELECT to_char(x.hiredate,'mon'),count(x.nume),avg(x.sal) FROM (SELECT ename nume,sal,hiredate FROM emp WHERE empno in (SELECT mgr FROM emp)) x GROUP BY to_char(x.hiredate,'mon'); 3. Situaţia : Total angajaţi Ian Feb Mar Apr Mai Iun Iul Aug Sep Oct Nov Dec unde în coloana rezervată fiecărei luni calendaristice se vor înscrie numărul
total de angajaţi în luna respectivă. select count(*) , sum(case when to_char(hiredate,'mm')=1 then 1 else 0 end) "ian", sum(case when to_char(hiredate,'mm')=2 then 1 else 0 end) "feb", sum(case when to_char(hiredate,'mm')=3 then 1 else 0 end) "mar" from emp; 4. Angajaţii care au aceleaşi comision şi săptămână din an a angajării cu a celor ce au aceiaşi grupă salariala cu vreunul din subordonaţii lor. SELECT ename,comm,to_char(hiredate,'ww') (nvl(comm,-1),to_char(hiredate,'ww')) in
FROM
emp
WHERE
(SELECT nvl(a.comm,-1),to_char(a.hiredate,'ww') FROM emp a,emp b,salgrade s,salgrade t WHERE b.mgr=a.empno and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade); Subiectul 28 1. Situaţia : Departamentul 1980-1982 1983-1985 din 1986 unde pentru fiecare departament se afişează maximul salariilor salariaţilor care lucrează în departamentul respectiv separaţi în perioadele specificate în antetele coloanelor afişate. SELECT distinct dname,x.max_sal FROM emp natural join dept, (SELECT dname numedep, max(sal) max_sal FROM emp natural join dept WHERE to_char(hiredate,'yyyy') between 1980 and 1982 GROUP BY dname) x WHERE x.numedep=dname; 2. Media salariilor medii ale meseriilor. SELECT avg(x.sal_med) FROM (SELECT avg(sal) sal_med FROM emp GROUP BY job) x; 3. Numele angajatului, localitatea, salariul şi salariul mediu al localităţii pentru cei ce au salariul mai mare decât maximul salariilor medii ale localităţilor. SELECT ename,loc,sal, x.sal_medloc FROM emp NATURAL JOIN dept, (SELECT loc local, avg(sal) sal_medloc FROM emp NATURAL JOIN dept group by loc) x WHERE loc=x.local and sal>( SELECT max(y.sal_med) FROM (SELECT avg(sal) sal_med FROM emp NATURAL JOIN dept GROUP BY loc) y) 4. Angajaţii care au acelaşi număr al şefului şi comision cu cei ce au aceiaşi grupă salarială cu şeful lor. SELECT ename FROM emp WHERE (mgr,nvl(comm,-1)) in (SELECT b.mgr,nvl(b.comm,-1) FROM emp a,emp b,salgrade s,salgrade t WHERE b.mgr=a.empno and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade); Subiectul 29 1. Angajaţii care au aceiaşi lună a angajării şi comision identice cu a celor ce au grupa salarială superioară grupei salariale a celor ce lucrează în New York.
SELECT ename FROM emp WHERE (to_char(hiredate,'mm'),nvl(comm,-1)) in (SELECT to_char(hiredate,'mm'),nvl(comm,-1) FROM emp a,salgrade s WHERE (a.sal between s.losal and s.hisal ) and (s.grade >all(SELECT grade from emp,dept,salgrade where emp.deptno=dept.deptno and sal between losal and hisal and loc='NEW YORK'))); 2. Situaţia : Luna 1981-1982 1983-1984 din 1985 unde pentru fiecare lună în care s-au produs angajări se afişează numărul de angajări produse în perioadele specificate ca şi titluri de coloane. SELECT to_char(hiredate,'month'), sum(case when to_char(hiredate,'yyyy')between 1980 and 1981 then 1 else 0 end) "1980-1881", sum(case when to_char(hiredate,'yyyy')between 1982 and 1983 then 1 else 0 end) "1982-1883", sum(case when to_char(hiredate,'yyyy')>1983 then 1 else 0 end) "1983- " FROM emp GROUP BY to_char(hiredate,'month'); 3. Departamentul, număr şefi, numele şefului cu cei mai puţini subordonaţi. SELECT e.deptno,x.numar_sefi,e.ename FROM emp e, (SELECT deptno,count(*) numar_sefi FROM emp WHERE empno in(SELECT mgr FROM emp)GROUP BY deptno)x, (SELECT distinct a.deptno departament, a.empno numar_sef,count(b.ename) FROM emp a,emp b WHERE a.empno=b.mgr GROUP BY a.empno,a.deptno HAVING count(b.ename)<= all(SELECT count(b.ename) FROM emp a,emp b WHERE a.empno=b.mgr GROUP BY a.empno,a.deptno)) z WHERE e.empno=z.numar_sef and e.deptno=z.departament and x.deptno=e.deptno; 4. Numele şefilor care au salariul mai mare decât maximul salariului minim al departamentelor în care sunt angajaţi din 1982. SELECT ename,sal FROM emp WHERE sal>= (SELECT max(min(sal)) FROM emp WHERE to_char(hiredate,'yyyy')=1982 GROUP BY deptno) and empno in (SELECT mgr FROM emp); Subiectul 30 1. Maximul din salariile medii ale semestrelor în care s-au făcut angajări. SELECT max(x.sal_med) FROM (SELECT avg(sal) sal_med FROM emp GROUP BY to_char(hiredate,'q')) x; 2. Graficul salariului mediu al angajaţilor de pe nivelul 3 al ierarhiei, reprezentând în fiecare linie numele angajatului şi câte un caracter ‘* ‘ pentru fiecare 100 de unităţi salariale în ordinea alfabetică a angajaţilor. SELECT ename, lpad(' ',sal_med/100,'*') FROM emp, (SELECT avg(sal) sal_med FROM emp WHERE level=3 connect by prior empno=mgr start with mgr is null) WHERE level=3 connect by prior empno=mgr start with mgr is null; 3. Numele salariatului, data angajării, salariul mediu al departamentului, numărul săptămânii din an în care s-a făcut angajarea salariatului pentru salariaţii care lucrează în departamente în care sunt încadrate cel puţin 3 persoane angajate în aceiaşi lună.
4. Situaţia : Departamentul 1981-1982 1983-1984 din 1985 unde pentru fiecare departament în care s-au produs angajări se afişează numărul de angajări produse în perioadele specificate ca şi titluri de coloane. SELECT dname, sum(case when to_char(hiredate,'yyyy')between 1980 and 1981 then 1 else 0 end) "1980-1881", sum(case when to_char(hiredate,'yyyy')between 1982 and 1983 then 1 else 0 end) "1982-1883", sum(case when to_char(hiredate,'yyyy')>1983 then 1 else 0 end) "1983- " FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname; Subiectul 31 1. Angajaţii care au acelaşi an al angajării şi comision identice cu a celor ce au salariul mai mare decât media salariilor maxime ale grupelor salariale. SELECT ename FROM emp WHERE (to_char(hiredate,'yyyy'),nvl(comm,-1)) in (SELECT to_char(hiredate,'yyyy'),nvl(comm,-1) FROM emp WHERE sal > (SELECT avg(max(sal)) FROM emp,salgrade WHERE sal between losal and hisal GROUP BY grade)); 2. Numele salariatului, numărul departamentului, numele departamentului şi salariul mediu al departamentului pentru acei salariaţi care s-au angajat în zilele de luni, marţi sau joi şi în zilele impare din an. SELECT ename, deptno, dname, x.sal_med FROM emp NATURAL JOIN dept, (SELECT deptno nrdep,avg(sal) sal_med FROM emp GROUP BY deptno) x WHERE to_char(hiredate,'d') in (1,2,4) and mod(to_char(hiredate,'d') ,2)=1 and deptno=x.nrdep; 3. Situaţia : Localitatea 1981-1982 1983-1984 din 1985 unde pentru localitate departament în care s-au produs angajări se afişează numărul de angajări produse în perioadele specificate ca şi titluri de coloane. SELECT loc, sum(case when to_char(hiredate,'yyyy')between 1980 and 1981 then 1 else 0 end) "1980-1881", sum(case when to_char(hiredate,'yyyy')between 1982 and 1983 then 1 else 0 end) "1982-1883", sum(case when to_char(hiredate,'yyyy')>1983 then 1 else 0 end) "1983- " FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY loc; 4. Numele şefilor care au venitul (salariul plus comisionul) mai mare decât maximul salariului mediu al departamentelor în care sunt cel puţin doi şefi. Subiectul 32 1. Situaţia : Localitatea 1980-1982 1983-1985 din 1986 unde pentru fiecare localitate se afişează salariul minim al salariaţilor care lucrează în localitatea respectivă separaţi în perioadele specificate în antetele coloanelor afişate. SELECT loc Loc,
min(case when to_char(hiredate,'yyyy') between 1980 and 1982 and loc in(SELECT loc FROM emp,dept WHERE emp.deptno=dept.deptno) then sal else 0 end) "1980-1982", min(case when to_char(hiredate,'yyyy') between 1983 and 1985 and loc in(SELECT loc FROM emp,dept WHERE emp.deptno=dept.deptno) then sal else 0 end) "1983-1985", min(case when to_char(hiredate,'yyyy') >1986 and loc in(SELECT loc FROM emp,dept WHERE emp.deptno=dept.deptno) then sal else 0 end) "din 86" FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY loc; 2. Suma salariilor medii ale departamentelor. SELECT sum(avg(sal)) FROM emp NATURAL JOIN dept GROUP BY dname; Angajaţii care au acelaşi număr al şefului şi meserie cu cei ce au aceiaşi grupă salarială cu şeful lor. SELECT ename FROM emp WHERE (mgr,job) in (SELECT b.mgr,b.job FROM emp a,emp b,salgrade s,salgrade t WHERE b.mgr=a.empno and (a.sal between s.losal and s.hisal) and (b.sal between t.losal and t.hisal) and s.grade=t.grade); 3. Numele şefilor care au mai puţine luni lucrate decât unii din subordonaţii lor. SELECT a.ename FROM emp a WHERE (sysdate-hiredate)/30
all(SELECT grade FROM emp,dept,salgrade WHERE emp.deptno=dept.deptno and sal between losal and hisal and loc='DALLAS'))); 3. Situaţia : Grupa salarială 1981-1982 1983-1984 din 1985 unde pentru fiecare grupă salarială în care se află salariul celor angajaţi se afişează numărul de angajări produse în perioadele specificate ca şi titluri de coloane. SELECT grade Grupa, sum(case when to_char(hiredate,'yyyy') between 1981 and 1982 and grade in (SELECT grade FROM emp,salgrade WHERE sal between losal and hisal) then 1 else 0 end) "1981-1982", sum(case when to_char(hiredate,'yyyy') between 1983 and 1984 and grade in (SELECT grade FROM emp,salgrade WHERE sal between losal and hisal) then 1 else 0 end)
"1983-1984", sum(case when to_char(hiredate,'yyyy') >1985 and grade in (SELECT grade FROM emp,salgrade WHERE sal between losal and hisal) then 1 else 0 end) "din 85" FROM emp,salgrade WHERE sal between losal and hisal GROUP BY grade; 4. Graficul salariului mediu al angajaţilor încadraţi într-un trimestru, reprezentând numărul trimestrului şi câte un caracter ‘*’ pentru fiecare 100 unităţi salariale. SELECT to_char(hiredate,'q'),rpad(' ',avg(sal)/100,'*') FROM salgrade s, dept d, emp a WHERE a.deptno=d.deptno and a.sal between s.losal and s.hisal GROUP BY to_char(hiredate,'q') ORDER BY to_char(hiredate,'q'); Subiectul 34 1. Situaţia : Departamentul 1980-1982 1983-1985 din 1986 unde pentru fiecare departament se afişează numărul salariaţilor care lucrează în departamentul respectiv separaţi în perioadele specificate în antetele coloanelor afişate SELECT dname Departamentul, sum(case when to_char(hiredate,'yyyy') between 1980 and 1982 and dname in (SELECT dname FROM emp,dept WHERE emp.deptno=dept.deptno) then 1 else 0 end) "1980-1982", sum(case when to_char(hiredate,'yyyy') between 1983 and 1985 and dname in (SELECT dname FROM emp,dept WHERE emp.deptno=dept.deptno) then 1 else 0 end) "1983-1985", sum(case when to_char(hiredate,'yyyy') >1986 and dname in (SELECT dname FROM emp,dept WHERE emp.deptno=dept.deptno) then 1 else 0 end) "din 86" FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname; 2. Angajaţii care au acelaşi departament şi comision identice cu a celor ce au salariul mai mic decât media salariilor minime ale departamentelor. SELECT ename FROM emp a WHERE (deptno,nvl(comm,-1)) in (SELECT deptno, nvl(comm,-1) FROM emp b WHERE b.sal< (SELECT avg(min(sal)) FROM emp c, dept WHERE c.deptno=dept.deptno GROUP BY loc)); 3. Numele şefilor care au venitul (salariul plus comisionul) mai mare decât minimul salariului mediu al localităţilor în care sunt cel puţin doi şefi. 4. Graficul salariului mediu al angajaţilor încadraţi într-o lună, reprezentând numărul lunii cu cifre romane şi câte un caracter ‘*’ pentru fiecare 100 unităţi salariale. SELECT to_char(hiredate,'rm'), rpad(' ',avg(sal)/100,'*') FROM salgrade s, dept d, emp a WHERE a.deptno=d.deptno and a.sal between s.losal and s.hisal GROUP BY to_char(hiredate,'rm') ORDER BY to_char(hiredate,'rm');
Subiectul 35 1. Maximul salariilor medii ale lunilor în care s-au făcut angajări. SELECT max(avg(sal)) FROM emp,salgrade WHERE sal between losal and hisal GROUP BY
to_char(hiredate,'mm'); 2. Angajaţii care au acelaşi localitate şi comision cu cei ce au aceiaşi grupă salarială cu şeful lor. SELECT ename FROM emp,dept WHERE emp.deptno=dept.DEPTNO and (loc,nvl(comm,-1)) in (SELECT q.loc,nvl(a.comm,-1) FROM emp a,emp b,salgrade s,salgrade t, dept q WHERE a.deptno=q.deptno and a.mgr=b.empno and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade); 3. Situaţia : Grupa salarială Număr şefi
Numele şefului cu salariul maxim
4. Graficul salariului mediu al angajaţilor încadraţi într-un an, reprezentând numărul anului şi câte un caracter ‘*’ pentru fiecare 100 unităţi salariale. SELECT to_char(hiredate,'yyyy'),lpad(' ',avg(sal)/100,'*') FROM salgrade s, dept d, emp a WHERE a.deptno=d.deptno and a.sal between s.losal and s.hisal GROUP BY to_char(hiredate,'yyyy') ORDER BY to_char(hiredate,'yyyy'); Subiectul 36 1. Situaţia : Localitatea 1980-1982 1983-1985 din 1986 unde pentru fiecare localitate se afişează numărul salariaţilor care lucrează în localitatea respectivă separaţi în perioadele specificate în antetele coloanelor afişate. SELECT loc Localitatea, sum(case when to_char(hiredate,'yyyy') between 1980 and 1982 and loc in(SELECT loc FROM emp,dept WHERE emp.deptno=dept.deptno) then 1 else 0 end) "1980-1982", sum(case when to_char(hiredate,'yyyy') between 1983 and 1985 and loc in(SELECT loc FROM emp,dept WHERE emp.deptno=dept.deptno) then 1 else 0 end) "1983-1985", sum(case when to_char(hiredate,'yyyy') >1986 and loc in(SELECT loc FROM emp,dept WHERE emp.deptno=dept.deptno) then 1 else 0 end) "din 86" FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY loc; 2. Angajaţii care au meseria şi comisionul identice cu meseria şi comisionul celor ce au salariul mai mic decât minimul salariilor medii ale departamentelor. SELECT ename FROM emp a WHERE (job,nvl(comm,-1)) in (SELECT job, nvl(comm,-1) FROM emp b WHERE b.sal< (SELECT min(avg(sal)) FROM emp c, dept WHERE c.deptno=dept.deptno GROUP BY loc)); 3. Numele şefilor care au venitul (salariul plus comisionul) mai mare decât media salariilor minime ale grupelor salariale în care au salariul cel puţin doi şefi.
4. Situaţia : Număr şefi Dallas New York Alte oraşe unde în coloanele reprezentând numele localităţilor se reprezintă numărul de şefi din localitatea respectivă. SELECT distinct x.nr_sefi, y.nr_sefid,z.nr_sefin,u.nr_sefia FROM emp, (SELECT count(empno) nr_sefi FROM emp WHERE empno in (SELECT mgr FROM emp)) x, (SELECT count(empno) nr_sefid FROM emp,dept WHERE emp.deptno=dept.deptno and empno in (SELECT mgr FROM emp) and loc='DALLAS') y, (SELECT count(empno) nr_sefin FROM emp,dept WHERE emp.deptno=dept.deptno and empno in (SELECT mgr FROM emp) and loc='NEW YORK') z, (SELECT count(empno) nr_sefia FROM emp,dept WHERE emp.deptno=dept.deptno and empno in (SELECT mgr FROM emp) and loc not in('DALLAS','NEW YORK') ) u ; Subiectul 37 1. Salariul mediu al salariilor minime ale anilor în care s-au făcut angajări. SELECT avg(min(sal)) FROM emp GROUP BY to_number(to_char(hiredate,'YYYY')); 2. Numele salariatului, departamentul, salariul mediu al departamentului pentru acei salariaţi care s-au angajat până cel târziu în a 25 săptămână din an. SELECT ename, dname, x.sal_med FROM emp a, dept b, (SELECT avg(sal) sal_med,deptno FROM emp GROUP BY deptno) x WHERE a.deptno=b.deptno and a.deptno=x.deptno and to_char(hiredate,'ww')<25 GROUP BY ename,dname, x.sal_med; SAU SELECT ang.ename, ang.deptno, avg(colegi.sal) FROM emp ang, emp colegi WHERE ang.deptno=colegi.deptno and to_number(to_char(ang.hiredate,'ww'))<=25 GROUP BY ang.deptno, ang.ename; 3. Angajaţii care au acelaşi număr al şefului şi departament cu cei ce au aceiaşi grupă salarială cu şeful lor. SELECT ename FROM emp WHERE (mgr,deptno) in (SELECT b.mgr,b.deptno FROM emp a,emp b,salgrade s,salgrade t WHERE b.mgr=a.empno and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade); SAU SELECT ang.ename FROM emp ang, emp sef, salgrade x WHERE ang.mgr=sef.empno and sef.sal between x.losal and x.hisal and (ang.mgr,ang.deptno) in (SELECT grsef.mgr, grsef.deptno FROM emp grsef, salgrade WHERE salgrade.grade=x.grade and grsef.sal between salgrade.losal and salgrade.hisal); 4. Situaţia : Grupa salarială
Număr şefi Numele şefului cu salariul minim
SELECT grade, count(distinct sef.ename), min(sef.ename) FROM emp sef, salgrade, emp ang WHERE ang.mgr=sef.empno and sef.sal between losal and hisal and sef.sal= (SELECT min(sal) FROM emp x WHERE x.ename=sef.ename) GROUP BY grade ; Subiectul 38 1. Minimul salariilor medii ale departamentelor în care s-au făcut angajări în 1982. SELECT min(avg(sal)) FROM emp,salgrade WHERE sal between losal and hisal to_char(hiredate,'yyyy')=1982 GROUP BY deptno;
and
2. Angajaţii care au acelaşi nume al departamentului şi comision cu cei ce au aceiaşi grupă salarială cu şeful lor. SELECT ename FROM (dname,nvl(comm,-1)) in
emp,dept
WHERE
emp.DEPTNO=dept.DEPTNO
and
(SELECT q.dname,nvl(b.comm,-1) FROM emp a,emp b,salgrade s,salgrade t, dept q WHERE b.deptno=q.deptno and b.mgr=a.empno and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade); 3. Situaţia : Localitatea
Număr şefi Numele şefului cu salariul maxim
4. Numele şefilor care au mai puţine săptămâni lucrate decât unii din subordonaţii lor. SELECT distinct(b.ename) FROM emp a, emp b WHERE a.mgr=b.empno and b.hiredate (SELECT avg(min(sal)) FROM emp c, dept WHERE c.deptno=dept.deptno GROUP BY loc)) ; 3. Situaţia : Departamentul
Număr şefi Numele şefului cu cei mai mulţi subordonaţi
4. Numele salariaţilor care prin majorarea salariului cu 70% realizează un salt de 3 grupe salariale fată de grupa salarială în care se afla salariul lor înaintea producerii majorării. SELECT ename, sal, s.grade, sal*1.7, b.grade FROM emp a, salgrade s, salgrade b WHERE a.sal between s.losal and s.hisal and sal*1.7 between b.losal and b.hisal and b.grade-s.grade=3;
Subiectul 40 1. Situaţia : Grupa salarială 1980-1982 1983-1985 din 1986 unde pentru fiecare grupă salarială se afişează suma salariilor salariaţilor care au salariul în grupa respectivă separaţi în perioadele specificate în antetele coloanelor afişate. SELECT grade Grupa, sum(case when to_char(hiredate,'yyyy') between 1980 and 1982 and grade in(SELECT grade FROM emp,salgrade WHERE sal between losal and hisal) then 1 else 0 end) "1980-1982", sum(case when to_char(hiredate,'yyyy') between 1983 and 1985 and grade in(SELECT grade FROM emp,salgrade WHERE sal between losal and hisal) then 1 else 0 end) "1983-1985", sum(case when to_char(hiredate,'yyyy') >1986 and grade in(SELECT grade FROM emp,salgrade WHERE sal between losal and hisal) then 1 else 0 end) "din 86" FROM emp,salgrade WHERE sal between losal and hisal GROUP BY grade; 2. Minimul salariilor medii ale trimestrelor în care s-au făcut angajări. SELECT min(avg(sal)) FROM emp,salgrade WHERE sal between losal and hisal GROUP BY to_char(hiredate,'q'); 3. Angajaţii care au meseria şi comisionul identice cu meseria şi comisionul celor ce au salariul mai mic decât minimul salariilor medii ale meseriilor. SELECT ename FROM emp a WHERE (job,nvl(comm,-1)) in (SELECT job, nvl(comm,-1) FROM emp b WHERE b.sal< (SELECT min(avg(sal)) FROM emp c, dept WHERE c.deptno=dept.deptno GROUP BY loc)); 4. Angajaţii care au acelaşi an al angajării şi comision cu cei ce au aceiaşi grupă salarială cu şeful lor. SELECT ename FROM emp WHERE (to_char(hiredate,'yy'),nvl(comm,-1)) in (SELECT to_char(b.hiredate,'yy'),nvl(b.comm,-1) FROM emp a,emp b,salgrade s,salgrade t WHERE b.mgr=a.empno and a.sal between s.losal and s.hisal and b.sal between t.losal and t.hisal and s.grade=t.grade);