LA FUNCION GROUP IGNORA LOS VALORES NULOS. SI TU QUIERES INCLUIR VALORES NULOS, USA LA FUNCION NVL

/* PRACTICA 5 SOLUCIONES */

1.- TRUE

2.- FALSE. LA FUNCION GROUP IGNORA LOS VALORES NULOS. SI TU QUIERES INCLUIR VALORES NULOS, USA LA FUNCION NVL

3.- TRUE

4.-

SELECT ROUND (MAX(SALARY),0) "MAXIMUM",

       ROUND (MIN(SALARY),0) "MINIMUM",

       ROUND (SUM(SALARY),0) "SUM",

       ROUND (AVG(SALARY),0) "AVERAGE"

FROM EMPLOYEES;

5.-

SELECT JOB_ID, ROUND (MAX(SALARY),0) "MAXIMUM",

               ROUND (MIN(SALARY),0) "MINIMUM",

               ROUND (SUM(SALARY),0) "SUM",

               ROUND (AVG(SALARY),0) "AVERAGE"

FROM EMPLOYEES

GROUP BY JOB_ID;

6.-

SELECT JOB_ID, COUNT(*)

FROM HR.EMPLOYEES

GROUP BY JOB_ID;

7.-

SELECT COUNT(DISTINCT MANAGER_ID) "NUMBER OF MANAGERS"

FROM EMPLOYEES;

8.-ESCRIBA UNA CONSULTA QUE MUESTRE LA DIFERENCIA ENTRE EL SALARIO MAS ALTO Y MAS BAJO.

ETIQUELOS EN UNA COLUMNA DIFFERENCE.

SELECT MAX(SALARY) - MIN(SALARY) DIFFERENCE

FROM EMPLOYEES;

9.-

SELECT MANAGER_ID, MIN(SALARY)

FROM EMPLOYEES

WHERE MANAGER_ID IS NOT NULL

GROUP BY MANAGER_ID

HAVING MIN(SALARY)>6000

ORDER BY MIN(SALARY)DESC;

10.-

SELECT D.DEPARTMENT_NAME "NAME", D.LOCATION_ID "LOCATION",

COUNT(*) "NUMBER OF PEOPLE"

ROUND(AVG(SALARY),2) "SALARY"

FROM HR.EMPLOYEES E, HR.DEPARTMENTS D

WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID

GROUP BY D.DEPARTMENT_NAME, D.LOCATION_ID;

11.- MOSTRAR EL TOTAL DE NUMERO DE EMPLEADOS, EL TOTAL DE NUMEROS DE EMPLEADOS QUE ENTRARON EN 1995, 96,97

Y EL 98.

SELECT COUNT(*) TOTAL,

       SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), 1995,1,0)) "1995",

       SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), 1996,1,0)) "1996",

       SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), 1997,1,0)) "1997",

       SUM(DECODE(TO_CHAR(HIRE_DATE, 'YYYY'), 1998,1,0)) "1998"

FROM EMPLOYEES;

12.- CREAR UNA MATRIX QUE MUESTRE EL JOB, THE SALARY FOR THAT JOB BASED ON DEPARTMENT NUMBER, Y EL TOTAL DE SALARIO

POR JOB, POR DEPARTMENTS 20, 50, 80, Y 90.

SELECT JOB_ID "JOB",

       SUM (DECODE(DEPARTMENT_ID, 20, SALARY)) "DEPT 20",

       SUM (DECODE(DEPARTMENT_ID, 50, SALARY)) "DEPT 50",

       SUM (DECODE(DEPARTMENT_ID, 80, SALARY)) "DEPT 80",

       SUM (DECODE(DEPARTMENT_ID, 90, SALARY)) "DEPT 90",

       SUM (SALARY) "TOTAL"

...