M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions T5

1. Les funcions de grup treballen amb molts registres per a produir un resultat.

Cert 

2. Les funcions de grup inclouen nuls en els càlculs.

Fals. Les funcions de grup ignoren els valors nuls. Per incloure valors nuls en els càlculs cal utilitzar la funció NVL.

3. La clàusula WHERE restringeix registres abans de la inclusió en un càlcul de grup.

Cert

4. Mostra sobre el salari: màxim, mínim, suma i mitjana aritmètica, per a tots els empleats. Arrodoneix els resultats a la posició decimal. Salva la sentència SQL en un fitxer anomenat p5q4.sql.

SELECT ROUND(MAX(sal),0) "Maximum",
       ROUND(MIN(sal),0) "Minimum",
       ROUND(SUM(sal),0) "Sum",
       ROUND(AVG(sal),0) "Average"
FROM   emp;

SAVE p5q4.sql

5. Modifica p5q4.sql per mostrar: màxim, mínim, suma i mitjana aritmètica de salaris per a cada ofici. Salva la sentència SQL en un fitxer anomenat p5q5.sql. Executa la consulta.

EDIT p5q4.sql

SELECT job, ROUND(MAX(sal),0) "Maximum",
       ROUND(MIN(sal),0) "Minimum",
       ROUND(SUM(sal),0) "Sum",
       ROUND(AVG(sal),0) "Average"
FROM emp
GROUP BY job;

START p5q5.sql

6. Escriu una consulta que mostri el nombre de persones que tenen el mateix ofici.

SELECT job, COUNT(*)
FROM emp
GROUP BY job;

7. Determina el nombre total de directors. Etiqueta la columna com "nº de directors".

SELECT COUNT(DISTINCT mgr) "Number of Managers"
FROM emp;

8. Escriu una consulta que mostri la diferència entre el salari més alt i el més baix de la empresa. Etiqueta la columna com "DIFFERENCE".

SELECT MAX(sal)-MIN(sal) DIFFERENCE
FROM emp;

9. Mostri el número del director i salari de l'empleat amb menor salari amb dependència d'aquest director. Exclou a qualsevol empleat que no tingui director. Exclou qualsevol grup, que tingui un salari mínim inferior a 1000$. Classifica el resultat en ordre descendent de salaris.

SELECT mgr, MIN(sal)
FROM emp
WHERE mgr IS NOT NULL
GROUP BY mgr
HAVING MIN(sal) > 1000
ORDER BY MIN(sal) DESC;

10. Escriu una consulta que mostri el nom del departament, localitat, nombre d'empleats i la mitjana de salaris, per a tots els empleats de cada departament. Etiqueta les columnes com DNAME, LOC, Number of People i Salari, respectivament.

SELECT d.dname, d.loc, COUNT(*) "Number of People", ROUND(AVG(sal),2) "Salary"
FROM emp e NATURAL JOIN dept d 
GROUP BY d.dname, d.loc;

11. Fes una consulta que mostri el nombre total d'empleats i d'aquest total el nombre dels que es van contractar en 1980, 1981, 1982 i 1983. Etiqueta les columnes com TOTAL, 1980, 1981, 1982 i 1983.

SELECT COUNT(*) total, SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1980,1,0))"1980",
       SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1981,1,0))"1981",
       SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1982,1,0))"1982",
       SUM(DECODE(TO_CHAR(hiredate, 'YYYY'), 1983,1,0))"1983"
FROM emp;

12. Crea una matriu que mostri l'ofici, salari corresponent segons departament i el salari total per aquest ofici de tots els departaments. Etiqueta les columnes com Job, Dept 10, Dept 20, Dept 30 i Total.

SELECT job "Job", SUM(DECODE(deptno, 10, sal)) "Dept 10",
       SUM(DECODE(deptno, 20, sal)) "Dept 20",
       SUM(DECODE(deptno, 30, sal)) "Dept 30",
       SUM(sal) "Total"
FROM emp
GROUP BY job;