Consultes SQL amb JOIN i funcions d'agregació: exercicis resolts sobre la base de dades d'empleats

1. Nom i cognom dels treballadors que estan al nivell C.

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES NATURAL JOIN JOBS JOIN JOB_GRADES WHERE SALARY >= LOWEST_SAL AND SALARY <= HIGHEST_SAL AND GRADE_LEVEL = 'C';

2. Nom i cognom dels treballadors i nom del director que viuen en la ciutat que conté "TH".

SELECT worker.FIRST_NAME, worker.LAST_NAME, manager.FIRST_NAME FROM EMPLOYEES worker, EMPLOYEES manager NATURAL JOIN DEPARTMENTS NATURAL JOIN LOCATIONS WHERE LOCATE('TH', CITY) > 0 AND worker.MANAGER_ID = manager.EMPLOYEE_ID;

3. Nom i cognom dels treballadors d'Europa que cobren menys de la mitjana.

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES JOIN DEPARTMENTS ON (EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID) NATURAL JOIN LOCATIONS NATURAL JOIN COUNTRIES NATURAL JOIN REGIONS WHERE REGION_NAME = 'Europe' AND SALARY < (SELECT AVG(SALARY) FROM EMPLOYEES);

4. Nom i cognom dels treballadors que el seu director té al telèfon un "4".

SELECT worker.FIRST_NAME, worker.LAST_NAME FROM EMPLOYEES worker, EMPLOYEES manager WHERE LOCATE('4', manager.PHONE_NUMBER) > 0 AND worker.MANAGER_ID = manager.EMPLOYEE_ID;

5. Crea una consulta per mostrar el cognom i el salari dels empleats que guanyen més de $12.000.

SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY > 12000;

6. Crea una consulta per mostrar el cognom del treballador i el número de departament del treballador número 144.

SELECT LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 144;

7. Modifica la consulta de l'exercici 1 per mostrar el cognom i el salari de tots els treballadors el salari dels quals no està comprès entre $5.000 i $12.000.

SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY NOT BETWEEN 5000 AND 12000;

8. Mostra el cognom del treballador, l'identificador de càrrec i la data d'inici dels empleats contractats entre el 20 de febrer de 1998 i l'1 de maig de 1998. Ordena la consulta en ordre ascendent per data d'inici.

SELECT LAST_NAME, JOB_ID, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE >= '1998-02-20' AND HIRE_DATE <= '1998-05-01' ORDER BY HIRE_DATE;

9. Mostra el cognom i el número de departament de tots els empleats dels departaments 20 i 50 en ordre alfabètic per cognom.

SELECT LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 20 OR DEPARTMENT_ID = 50 ORDER BY LAST_NAME;

10. Modifica el fitxer exer3.sql per enumerar el cognom i el salari dels empleats que guanyen entre $5.000 i $12.000 i estan en el departament 20 o 50. Canvia el nom de les columnes.

SELECT LAST_NAME AS COGNOM, SALARY AS SALARI FROM EMPLOYEES WHERE SALARY BETWEEN 5000 AND 12000 AND (DEPARTMENT_ID = 20 OR DEPARTMENT_ID = 50);

11. Mostra el cognom i la data de contractació de tots els empleats contractats el 1994.

SELECT LAST_NAME, HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '1994-01-01' AND '1994-12-31';

12. Mostra el cognom i el càrrec de tots els empleats que no tenen director.

SELECT LAST_NAME, JOB_TITLE FROM EMPLOYEES NATURAL JOIN JOBS WHERE MANAGER_ID IS NULL;

13. Mostra el cognom, el salari i la comissió per a tots els empleats que guanyen comissions. Ordena les dades en ordre descendent de salari i comissions.

SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL ORDER BY SALARY DESC, COMMISSION_PCT DESC;

14. Mostra el cognom de tots els empleats que tinguin la "a" com a tercera lletra.

SELECT LAST_NAME FROM EMPLOYEES WHERE SUBSTRING(LAST_NAME, 3, 1) = 'a';

15. Mostra el cognom de tots els empleats que tinguin una "a" i una "e" en el cognom.

SELECT LAST_NAME FROM EMPLOYEES WHERE LOCATE('a', LAST_NAME) > 0 AND LOCATE('e', LAST_NAME) > 0;

16. Mostra el cognom, el càrrec i el salari de tots els empleats que siguin representants de vendes (SA_REP) o encarregats de magatzem (ST_CLERK) i que el salari no sigui igual a $2.500, $3.500 ni $7.000.

SELECT LAST_NAME, JOB_TITLE, SALARY FROM EMPLOYEES NATURAL JOIN JOBS WHERE (JOB_ID = 'SA_REP' OR JOB_ID = 'ST_CLERK') AND SALARY NOT IN (2500, 3500, 7000);

17. Modifica el fitxer exer6.sql per mostrar el cognom, el salari i la comissió per a tots els empleats que tinguin una comissió del 15 %.

SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT = 0.15;

18. Fes una consulta que visualitzi el salari màxim, mínim, la suma i la mitjana de tots els treballadors. Etiqueta adequadament les columnes. Arrodoneix fins al nombre enter més proper.

SELECT ROUND(MAX(SALARY)) AS SALARI_MAJOR, ROUND(MIN(SALARY)) AS SALARI_MENOR, ROUND(SUM(SALARY)) AS SUMA, ROUND(AVG(SALARY)) AS MITJANA FROM EMPLOYEES;

19. Modifica la consulta anterior per visualitzar els mateixos camps però per a cada tipus de càrrec (JOB_ID).

SELECT JOB_ID, ROUND(MAX(SALARY)) AS SALARI_MAJOR, ROUND(MIN(SALARY)) AS SALARI_MENOR, ROUND(SUM(SALARY)) AS SUMA, ROUND(AVG(SALARY)) AS MITJANA FROM EMPLOYEES GROUP BY JOB_ID;

20. Escriu una consulta per visualitzar el nombre de persones amb el mateix càrrec.

SELECT JOB_ID, COUNT(*) AS TOTAL_EMPLEATS FROM EMPLOYEES GROUP BY JOB_ID;

21. Determina el nombre de directors sense enumerar-los. Etiqueta la columna com a "Nombre de directors" (utilitza la columna MANAGER_ID).

SELECT COUNT(DISTINCT MANAGER_ID) AS NOMBRE_DIRECTORS FROM EMPLOYEES;

22. Escriu una consulta per visualitzar la diferència entre el salari màxim i el mínim. Etiqueta adequadament la columna.

SELECT (MAX(SALARY) - MIN(SALARY)) AS DIFERENCIA FROM EMPLOYEES;

23. Visualitza el número de director i el salari del treballador amb menor sou per a aquest director.

SELECT MANAGER_ID, MIN(SALARY) FROM EMPLOYEES GROUP BY MANAGER_ID;

24. Escriu una consulta per visualitzar el nom, la ubicació, el número d'empleats i el salari mitjà de tots els treballadors de cada departament.

SELECT DEPARTMENT_NAME, CITY, COUNT(*) AS NUMERO_EMPLEATS, AVG(SALARY) AS SALARI_MITJA FROM EMPLOYEES NATURAL JOIN DEPARTMENTS NATURAL JOIN LOCATIONS GROUP BY DEPARTMENT_NAME, CITY;

25. Crea una consulta que et mostri el nombre total d'empleats i, d'aquest total, el nombre d'empleats contractats el 1995, 1996, 1997 i 1998. Crea les capçaleres de columna adequades.

SELECT YEAR(HIRE_DATE) AS ANY, COUNT(*) AS EMPLEATS FROM EMPLOYEES WHERE YEAR(HIRE_DATE) BETWEEN 1995 AND 1998 GROUP BY YEAR(HIRE_DATE);

26. Escriu una consulta per visualitzar el cognom del treballador i el nom del departament on treballen per a tots els treballadors.

SELECT LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;

27. Crea un llistat únic amb tots els càrrecs que hi ha al departament 80. Inclou la ubicació del departament en el resultat.

SELECT DISTINCT JOB_TITLE, DEPARTMENT_ID, CITY FROM JOBS NATURAL JOIN DEPARTMENTS NATURAL JOIN LOCATIONS WHERE DEPARTMENT_ID = 80;

28. Escriu una consulta per mostrar el cognom del treballador, el nom del departament, l'identificador d'ubicació i la ciutat de tots els empleats que reben comissió.

SELECT LAST_NAME, DEPARTMENT_NAME, LOCATION_ID, CITY FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID) NATURAL JOIN LOCATIONS WHERE COMMISSION_PCT > 0;

29. Visualitza el cognom del treballador i el nom del departament per a tots els treballadors que tinguin una "a" minúscula en el cognom.

SELECT LAST_NAME, DEPARTMENT_NAME FROM EMPLOYEES NATURAL JOIN DEPARTMENTS WHERE LOCATE('a', LAST_NAME) > 0;

30. Escriu una consulta per visualitzar el cognom, el càrrec, el número i el número de departament per a tots els treballadors que treballen a Toronto.

SELECT LAST_NAME, JOB_TITLE, JOB_ID, DEPARTMENT_ID FROM EMPLOYEES NATURAL JOIN JOBS NATURAL JOIN DEPARTMENTS NATURAL JOIN LOCATIONS WHERE CITY = 'Toronto';

31. Visualitza el cognom i el número d'empleat amb el cognom i el número del seu director. Etiqueta les columnes perquè el resultat sigui més clar.

SELECT LAST_NAME, CONCAT(EMPLOYEE_ID, ' ', LAST_NAME) AS NUM_EMPLEAT_COGNOM, MANAGER_ID FROM EMPLOYEES;

32. Complexa. Visualitza l'estructura de la taula JOB_GRADES. A continuació crea una consulta que mostri el nom, el càrrec, el nom del departament, el salari i el grau de tots els treballadors.

SELECT FIRST_NAME, JOB_TITLE, DEPARTMENT_NAME, SALARY, GRADE_LEVEL FROM EMPLOYEES NATURAL JOIN JOBS JOIN DEPARTMENTS JOIN JOB_GRADES WHERE SALARY > LOWEST_SAL AND SALARY < HIGHEST_SAL;

33. Crea una consulta utilitzant NATURAL JOIN que llisti les ubicacions on hi ha departaments: l'ID, el carrer, el codi postal, la ciutat i el nom del país.

SELECT LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, COUNTRY_NAME FROM LOCATIONS NATURAL JOIN COUNTRIES NATURAL JOIN DEPARTMENTS WHERE DEPARTMENT_ID IS NOT NULL;

34. Fes la mateixa consulta utilitzant JOIN i la clàusula USING.

SELECT LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, COUNTRY_NAME FROM DEPARTMENTS JOIN LOCATIONS USING (LOCATION_ID) JOIN COUNTRIES USING (COUNTRY_ID) WHERE DEPARTMENT_ID IS NOT NULL;

35. Fes la mateixa consulta amb JOIN i ON. Compara els resultats de les consultes 33, 34 i 35: han de ser els mateixos.

SELECT LOCATIONS.LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, COUNTRY_NAME FROM DEPARTMENTS JOIN LOCATIONS ON DEPARTMENTS.LOCATION_ID = LOCATIONS.LOCATION_ID JOIN COUNTRIES ON LOCATIONS.COUNTRY_ID = COUNTRIES.COUNTRY_ID WHERE DEPARTMENT_ID IS NOT NULL;

36. Refés la consulta sense utilitzar cap de les paraules clau citades en els exercicis anteriors.

SELECT LOCATIONS.LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, COUNTRY_NAME FROM DEPARTMENTS, LOCATIONS, COUNTRIES WHERE DEPARTMENT_ID IS NOT NULL AND DEPARTMENTS.LOCATION_ID = LOCATIONS.LOCATION_ID AND LOCATIONS.COUNTRY_ID = COUNTRIES.COUNTRY_ID;

37. Amplia les 4 consultes anteriors de manera que només presentin els registres de la ciutat de Seattle.

SELECT LOCATIONS.LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, COUNTRY_NAME FROM DEPARTMENTS, LOCATIONS, COUNTRIES WHERE DEPARTMENT_ID IS NOT NULL AND DEPARTMENTS.LOCATION_ID = LOCATIONS.LOCATION_ID AND LOCATIONS.COUNTRY_ID = COUNTRIES.COUNTRY_ID AND CITY = 'Seattle';

38. Crea una consulta que indiqui: nom i cognom d'un treballador, el seu identificador de treball, el nom del seu treball i el salari mínim que podria cobrar.

Utilitzant NATURAL JOIN:
SELECT FIRST_NAME, LAST_NAME, JOB_ID, JOB_TITLE, MIN_SALARY FROM EMPLOYEES NATURAL JOIN JOBS;

Utilitzant JOIN i USING:
SELECT FIRST_NAME, LAST_NAME, JOB_ID, JOB_TITLE, MIN_SALARY FROM EMPLOYEES JOIN JOBS USING (JOB_ID);

Utilitzant JOIN i ON:
SELECT FIRST_NAME, LAST_NAME, JOBS.JOB_ID, JOB_TITLE, MIN_SALARY FROM EMPLOYEES JOIN JOBS ON (EMPLOYEES.JOB_ID = JOBS.JOB_ID);

Sense utilitzar cap de les anteriors:
SELECT FIRST_NAME, LAST_NAME, JOBS.JOB_ID, JOB_TITLE, MIN_SALARY FROM EMPLOYEES, JOBS WHERE EMPLOYEES.JOB_ID = JOBS.JOB_ID;

39. Utilitzant CROSS JOIN. Analitza el resultat que obtens i explica per què l'obtens.

SELECT FIRST_NAME, LAST_NAME, JOBS.JOB_ID, JOB_TITLE, MIN_SALARY FROM EMPLOYEES CROSS JOIN JOBS;

El CROSS JOIN relaciona tots els registres d'una taula amb tots els registres de l'altra, generant totes les combinacions possibles (producte cartesià). Per això el resultat té un nombre de files igual al nombre d'empleats multiplicat pel nombre de càrrecs, i moltes combinacions no tenen sentit real, ja que no s'aplica cap condició d'igualtat entre JOB_ID de les dues taules.