DAM - DAW - MP 0484 Bases de Dades - NF3

Pràctica 15: - Vistes - empresa

Aquesta pràctica la podem realitzar a la base empresa.

Crea una vista anomenada EMPLOYEES_VU que seleccioni els números i noms d’empleats i els numeros de departament de la taula EMPLOYEES. Canvia la capçalera (nom del camp) del nom de l’empleat a EMPLOYEE.
Possible Solució
```
CREATE OR REPLACE VIEW employees_vu
    AS
    SELECT EMPLOYEE_ID, LAST_NAME AS EMPLOYEE,
            DEPARTMENT_ID
    FROM EMPLOYEES;
```

Visualitza el continugt de la vista EMPLOYEES_VU

Possible sortida

-------------+-----------+---------------+
| EMPLOYEE_ID | EMPLOYEE  | DEPARTMENT_ID |
+-------------+-----------+---------------+
|         100 | King      |            90 |
|         101 | Kochhar   |            90 |
|         102 | De Haan   |            90 |
|         103 | Hunold    |            60 |
|         104 | Ernst     |            60 |
|         107 | Lorentz   |            60 |
|         124 | Mourgos   |            50 |
|         141 | Rajs      |            50 |
|         142 | Davies    |            50 |
|         143 | Matos     |            50 |
|         144 | Vargas    |            50 |
|         149 | Zlotkey   |            80 |
|         174 | Abel      |            80 |
|         176 | Taylor    |            80 |
|         178 | Grant     |          NULL |
|         200 | Whalen    |            10 |
|         201 | Hartstein |            20 |
|         202 | Fay       |            20 |
|         205 | Higgins   |           110 |
|         206 | Gietz     |           110 |
+-------------+-----------+---------------+

Possible Solució

SELECT *
FROM employees_vu;

Selecciona el nom de la vista i el texte de la vista del diccionari de dades information_schema.VIEWS.

Si utilitzes la shell i la comanda mysql...

en comptes de finalitzar la comanda amb punt i coma (;) pots acabar-la amb contrabarra G majúscula (\G) i així ens mostra els resultats en linies en comptes de columnes (així es veu millor el camp VIEW_DEFINITION)
Possible sortida
```
TABLE_SCHEMA: empresa
TABLE_NAME: employees_vu
VIEW_DEFINITION: select `empresa`.`employees`.`EMPLOYEE_ID` AS `EMPLOYEE_ID`,`empresa`.`employees`.`LAST_NAME` AS `EMPLOYEE`,`empresa`.`employees`.`DEPARTMENT_ID` AS `DEPARTMENT_ID` from `empresa`.`employees`
```
Possible solució
```
SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
```
Utilizant la vista EMPLOYEES_VU, executa una consulta per mostrar només els noms d’empleat i els codis de departament.
Possible solució
```
SELECT EMPLOYEE, DEPARTMENT_ID
FROM EMPLOYEES_VU;
```
Crea una vista anomenada DEPT50 que contingui els codis i cognoms dels empleats i els codis del departament de tots els empleats del departament 50. Etiqueta les columnes de la vista com EMPNO, EMPLOYEE i DEPTNO. No permitis que es reassigni un empleat a un altre departament a través de la vista.
Possible Solució
```
CREATE OR REPLACE VIEW DEPT50 AS
    SELECT EMPLOYEE_ID AS EMPNO,
        LAST_NAME AS EMPLOYEE,
        DEPARTMENT_ID AS DEPTNO
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 50
    WITH CHECK OPTION;
```
Visualitza l’estructura i el contingut de la vista DEPT50.
Possible solució
```
DESCRIBE DEPT50;
```
```
SELECT *
FROM DEPT50;
```

Intenta reassignar a Matos al departament 190.

Possible solució

UPDATE DEPT50
    SET DEPTNO = 190
    WHERE EMPLOYEE = 'Mourgos';

Dona un error ja que amb el WITH CHECK OPTION l'empleat desapareixeria de la vista

ERROR 1369 (44000): CHECK OPTION failed `empresa`.`dept50`

Crea una vista anomenada SALARY_VU mostrant els cognoms dels empleats, els noms de departament, els sous i els graus de sou de tots els empleats. Utilitza les taules EMPLOYEES, DEPARTMENTS i JOB_GRADES. Etiqueta les columnes Employee, Department, Salary i Grade, respectivament.

Possible solució

CREATE OR REPLACE VIEW SALARY_VU AS
    SELECT LAST_NAME Employee, 
           DEPARTMENT_NAME Department, 
           SALARY Salary, GRADE_LEVEL Grade
    FROM EMPLOYEES 
    LEFT JOIN DEPARTMENTS USING (DEPARTMENT_ID) 
         JOIN JOB_GRADES 
              ON (SALARY BETWEEN LOWEST_SAL AND HIGHEST_SAL);

Hem utilitzat LEFT JOIN per tal que apareguin tots els empleats, fins i tot els que no tenen departament assignat

Mostra els empleats de grau A ordenats per sou de major a menor.
Possible solució
```
SELECT *
FROM SALARY_VU
WHERE Grade = 'A'
ORDER BY Salary DESC;
```
Mostra el grau, el sou mínim, el sou màxim la diferència entre el sou mínim i el sou màxim, la mitjana de sou i el nombre d’empleats dels empleats per cada grau. Etiqueta les columnes com Grau, Mínim, Màxim, Diferència, Mitjana i Empleats
Possible solució
```
SELECT Grade Grau, 
       MIN(Salary) "Mínim", MAX(Salary) "Màxim", 
       MAX(Salary) - MIN(Salary) "Diferència", 
       AVG(Salary) "Mitjana", COUNT(*) "Empleats"
FROM SALARY_VU
GROUP BY Grade;
```