Ing. Informático

Solucionario de evaluación final

Select DEPTNO, sum(SAL) as PLANILLA from SCOTT.EMP group by DEPTNO;

Select DEPTNO, ENAME, SAL from SCOTT.EMP where SAL IN (select max(SAL) as SUELDO_MAX fromSCOTT.EMP group by DEPTNO);

Select DEPARTMENT_ID, JOB_ID, COUNT(*) as EMPLEADOS from HR.EMPLOYEES group by DEPARTMENT_ID, JOB_ID

Select DEPARTMENT_ID, COUNT (*) as EMPLEADOS, sum(salary) asPLANILLA from HR.EMPLOYEES where DEPARTMENT_ID IS NOT NULL group by DEPARTMENT_ID HAVING sum (salary)> 50000 order by 1;

Select EXTRACT(YEAR FROM HIRE_DATE) as AÑO, count(*) as EMPLEADOS fromHR.EMPLOYEES group by EXTRACT(YEAR FROM HIRE_DATE) order by 1;

select extract(year from hire_date) as AÑO,
sum(case extract(month from hire_date) when 1 then 1 else 0 end) as ENE,
sum (caseextract(month from hire_date) when 2 then 1 else 0 end) as FEB,
sum (case extract(month from hire_date) when 3 then 1 else 0 end) as MAR,
sum (case extract(month from hire_date) when 4 then 1 else 0 end) asABR,
sum (case extract(month from hire_date) when 5 then 1 else 0 end) as MAY,
sum (case extract(month from hire_date) when 6 then 1 else 0 end) as JUN,
sum (case extract(month from hire_date) when7 then 1 else 0 end) as JUL,
sum (case extract(month from hire_date) when 8 then 1 else 0 end) as AGO,
sum (case extract(month from hire_date) when 9 then 1 else 0 end) as SEP,
sum (caseextract(month from hire_date) when 10 then 1 else 0 end) as OCT,
sum (case extract(month from hire_date) when 11 then 1 else 0 end) as NOV,
sum (case extract(month from hire_date) when 12 then 1 else 0 end)as DIC,
count (*) as TOTAL
from HR.EMPLOYEES
group by extract(year from hire_date)
order by 1;

select department_id as DEPT,
sum (case when salary < 1500 then 1 else 0 end) as MALO,
sum(case when salary < 3500 then 1 else 0 end) as REGULAR,
sum (case when salary < 7000 then 1 else 0 end) as BUENO,
sum (case when salary > 7000 then 1 else 0 end) as MUYBUENO,
count (*)...