Salta el contingut

DAM - DAW - MP 0484 Bases de Dades

Pràctica 11: - Base de dades empresa - Manipulació de dades

Aquesta pràctica, com que requereix treballar amb transaccions, no pot realitzar-se des del phpmyadmin. Utilitzeu amb la comanda mysql, des de l'ordre de comandes, o el programa DBeaver.

  1. Executa la següent sentència per a crear la taula MY_EMPLOYEE.

    CREATE TABLE my_employee(
       id INT(4)  NOT NULL,
       last_name VARCHAR(25),
       first_name VARCHAR(25),
       userid VARCHAR(8),
       salary DECIMAL(9,2));

  2. Descriu l'estructura de la taula MY_EMPLOYEE.

    Possible solució 
    DESCRIBE MY_EMPLOYEE;

    +------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(4)       | NO   |     | NULL    |       |
| last_name  | varchar(25)  | YES  |     | NULL    |       |
| first_name | varchar(25)  | YES  |     | NULL    |       |
| userid     | varchar(8)   | YES  |     | NULL    |       |
| salary     | decimal(9,2) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

  3. Afegeix la primera fila de dades a la taula MY_EMPLOYEE amb les següents dades d'exemple. No enumeris les columnes a la clàusula INSERT.

    ID LAST_NAME FIRST_NAME USERID SALARY
    1 Patel Ralph rpatel 895.00
    Possible solució 
    INSERT INTO MY_EMPLOYEE
VALUES(1, 'Patel', 'Ralph', 'rpatel', 895);

    i fins i tot

    INSERT INTO MY_EMPLOYEE
VALUES(2, 'Patel', 'Ralph', LOWER(CONCAT(LEFT(FIRST_NAME, 1), LAST_NAME)), 895);

  4. Afegeix a la taula MY_EMPLOYEE una segona fila amb les dades de l'exemple. Aquesta vegada, enumera les columnes explícitament a la clàusula INSERT.

    ID LAST_NAME FIRST_NAME USERID SALARY
    2 Dancs Betty bdancs 860.00
    Possible solució 
    INSERT INTO MY_EMPLOYEE(id, last_name, first_name, userid, salary)
VALUES(2, 'Dancs', 'Betty', 'bdancs', 860);

    i fins i tot

    INSERT INTO MY_EMPLOYEE(id, last_name, first_name, userid, salary)
VALUES((SELECT IFNULL(MAX(M.id), 0) + 1 FROM MY_EMPLOYEE M),
       'Dancs', 'Betty',
       LOWER(CONCAT(LEFT(FIRST_NAME, 1), LAST_NAME)),
       860);

    En aquest cas, la subconsulta per trobar el valor màxim està entre parèntesis i utilitza un àlies.

  5. Escriu dues sentències INSERT per afegir 2 files a la taula MY_EMPLOYEE. Concatena, amb funcions, la primera lletra del nom amb els primers set caràcters del cognom per crear el USERID.

    ID LAST_NAME FIRST_NAME USERID SALARY
    3 Biri Ben bbiri 1100.00
    4 Ropeburn Audrey aropebur 750.00
    Possible solució 
    INSERT INTO MY_EMPLOYEE(id, last_name, first_name, userid, salary)
VALUES( 3,
       'Biri', 'Ben',
       LOWER(CONCAT(LEFT(FIRST_NAME, 1), LAST_NAME)),
       1100);

    INSERT INTO MY_EMPLOYEE(id, last_name, first_name, userid, salary)
VALUES((SELECT IFNULL(MAX(M.id), 0) + 1 FROM MY_EMPLOYEE M),
       'Ropeburn', 'Audrey',
       LOWER(CONCAT(LEFT(FIRST_NAME, 1), LEFT(LAST_NAME, 7))),
       750);

    Cal vigilar el tamany màxim de USERID ja que és menor que el LAST_NAME i pot provocar problemes.

  6. Comprova els següents afegits a la taula

    ID LAST_NAME FIRST_NAME USERID SALARY
    3 Biri Ben bbiri 1100.00
    4 Ropeburn Audrey aropebur 750.00
    Possible solució 
    SELECT *
FROM MY_EMPLOYEE;

    +----+-----------+------------+----------+---------+
| id | last_name | first_name | userid   | salary  |
+----+-----------+------------+----------+---------+
|  1 | Patel     | Ralph      | rpatel   |  895.00 |
|  2 | Dancs     | Betty      | bdancs   |  860.00 |
|  3 | Biri      | Ben        | bbiri    | 1100.00 |
|  4 | Ropeburn  | Audrey     | aropebur |  750.00 |
+----+-----------+------------+----------+---------+

    Cal vigilar el tamany màxim de USERID ja que és menor que el LAST_NAME i pot provocar problemes.

  7. Canvia el cognom de l'empleat 3 per Drexler.

    Possible solució 
    UPDATE MY_EMPLOYEE
SET LAST_NAME = 'Drexler'
WHERE ID = 3;

    No hem modificat el camp USERID

  8. Canvia el sou a 1000 a tots els empleats amb un sou menor a 900.

    Possible solució 
    UPDATE MY_EMPLOYEE
SET SALARY = 1000
WHERE SALARY < 900;

  9. Verifica que s'han realitzat els canvis a la taula.

    Possible solució 
    SELECT *
FROM MY_EMPLOYEE;

    +----+-----------+------------+----------+---------+
| id | last_name | first_name | userid   | salary  |
+----+-----------+------------+----------+---------+
|  1 | Patel     | Ralph      | rpatel   | 1000.00 |
|  2 | Dancs     | Betty      | bdancs   | 1000.00 |
|  3 | Drexler   | Ben        | bbiri    | 1100.00 |
|  4 | Ropeburn  | Audrey     | aropebur | 1000.00 |
+----+-----------+------------+----------+---------+

  10. Suprimeix a Betty Dancs i l'empleat amb ID = 4 de la taula MY_EMPLOYEES.

    Possible solució 
    DELETE FROM MY_EMPLOYEE
WHERE (FIRST_NAME = 'Betty' AND last_name = 'Dancs') OR USERID = 4;

  11. Comprova els canvis realitzats a la taula.

    Possible solució 
    SELECT *
FROM MY_EMPLOYEE;

    +----+-----------+------------+----------+---------+
| id | last_name | first_name | userid   | salary  |
+----+-----------+------------+----------+---------+
|  1 | Patel     | Ralph      | rpatel   | 1000.00 |
|  3 | Drexler   | Ben        | bbiri    | 1100.00 |
+----+-----------+------------+----------+---------+

A partir d'aquí treballarem amb TRANSACCIONS

Treballarem amb transaccions (START TRANSACTION + COMMIT/ROLLBACK). Ja s'indicarà quan cal fer el COMMIT/ROLLBACK (confirmant/anul·lant les sentències).

  1. Comença una transacció.

    Possible solució 
    START TRANSACTION;

  2. Afegeix la següent fila.

    ID LAST_NAME FIRST_NAME USERID SALARY
    5 Ropeburn Audrey aropebur 1550.00
    Possible solució 
    INSERT INTO MY_EMPLOYEE
VALUES(5, 'Ropeburn', 'Audrey', LOWER(CONCAT(LEFT(FIRST_NAME, 1), LEFT(LAST_NAME, 7))), 1550);

  3. Comprova que els canvis s'han realitzat correctament.

    Possible solució 
    SELECT *
FROM MY_EMPLOYEE;

    +----+-----------+------------+----------+---------+
| id | last_name | first_name | userid   | salary  |
+----+-----------+------------+----------+---------+
|  1 | Patel     | Ralph      | rpatel   | 1000.00 |
|  3 | Drexler   | Ben        | bbiri    | 1100.00 |
|  5 | Ropeburn  | Audrey     | aropebur | 1550.00 |
+----+-----------+------------+----------+---------+

  4. Crea un punt de restauració – SAVEPOINT, per exemple amb el nom P1, i suprimeix totes les files de la taula.

    Possible solució 
    SAVEPOINT P1;

    DELETE FROM MY_EMPLOYEE;

    Compte que els noms del SAVEPOINT són CaseSensitive.

  5. Comprova que ara la taula està buida.

    Possible solució 
    SELECT * FROM MY_EMPLOYEE;
Empty set (0.001 sec)

  6. Desfés l'operació DELETE més recent (sense desfer l'operació INSERT anterior).

    Possible solució 
    ROLLBACK TO P1;
Empty set (0.001 sec)

  7. Comprova que la nova fila, i les altres, segueixen intactes.

    Possible solució 
    SELECT * FROM MY_EMPLOYEE;

    +----+-----------+------------+----------+---------+
| id | last_name | first_name | userid   | salary  |
+----+-----------+------------+----------+---------+
|  1 | Patel     | Ralph      | rpatel   | 1000.00 |
|  3 | Drexler   | Ben        | bbiri    | 1100.00 |
|  5 | Ropeburn  | Audrey     | aropebur | 1550.00 |
+----+-----------+------------+----------+---------+

    Cal haver fet un ROLLBACK TO P1 ja que si executem ROLLBACK tornarem a l'inici de la transacció, on no teniem l'empleat 5.

  8. Fes que la inserció de dades sigui permanent.

    Possible solució 
    COMMIT;

Treballem amb la taula empresa.EMPLOYEES

  1. Comença una nova transacció.

    Possible solució 
    START TRANSACTION;

  2. A la taula EMPLOYEES. Fes una actualització per augmentar en un 10% el sou de tots els empleats que tenen gent a càrrec seu. Comprova-ho.

    Possible solució 
    UPDATE EMPLOYEES
SET SALARY = SALARY * 1.10
WHERE EMPLOYEE_ID IN (SELECT DISTINCT MANAGER_ID FROM EMPLOYEES);

    Tot i sortir dues vegades la taula EMPLOYEES NO CAL utilitzar àlies.

    SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME, SALARY, MANAGER_ID
FROM EMPLOYEES;

    +-------------+-----------+------------+----------+------------+
| EMPLOYEE_ID | LAST_NAME | FIRST_NAME | SALARY   | MANAGER_ID |
+-------------+-----------+------------+----------+------------+
|         100 | King      | Steven     | 26400.00 |       NULL |
|         101 | Kochhar   | Neena      | 18700.00 |        100 |
|         102 | De Haan   | Lex        | 18700.00 |        100 |
|         103 | Hunold    | Alexander  |  9900.00 |        102 |
|         104 | Ernst     | Bruce      |  6000.00 |        103 |
|         107 | Lorentz   | Diana      |  4200.00 |        103 |
|         124 | Mourgos   | Kevin      |  6380.00 |        100 |
|         141 | Rajs      | Trenna     |  3500.00 |        124 |
|         142 | Davies    | Curtis     |  3100.00 |        124 |
|         143 | Matos     | Randall    |  2600.00 |        124 |
|         144 | Vargas    | Peter      |  2500.00 |        124 |
|         149 | Zlotkey   | Eleni      | 11550.00 |        100 |
|         174 | Abel      | Ellen      | 11000.00 |        149 |
|         176 | Taylor    | Jonathan   |  8600.00 |        149 |
|         178 | Grant     | Kimberely  |  7000.00 |        149 |
|         200 | Whalen    | Jennifer   |  4400.00 |        101 |
|         201 | Hartstein | Michael    | 14300.00 |        100 |
|         202 | Fay       | Pat        |  6000.00 |        201 |
|         205 | Higgins   | Shelley    | 13200.00 |        101 |
|         206 | Gietz     | William    |  8300.00 |        205 |
+-------------+-----------+------------+----------+------------+

  3. A la taula EMPLOYEES. Fes una actualització per tal que els empleats que tenen gent al seu càrrec cobrin 2 vegades la mitja del sou que cobren els empleats a càrrec seu. Comprova-ho.

    Possible solució 
    UPDATE EMPLOYEES E
SET SALARY = 2 * (SELECT AVG(S.SALARY) FROM EMPLOYEES S WHERE S.MANAGER_ID = E.EMPLOYEE_ID)
WHERE EMPLOYEE_ID IN (SELECT DISTINCT MANAGER_ID FROM EMPLOYEES);

    Aquí, al sortir dues vegades la taula EMPLOYEES SÍ CAL utilitzar àlies.

    Cal diferenciar quin camp fa referència la taula principal o a la de la mateixa subconsulta.

    SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME, SALARY, MANAGER_ID
FROM EMPLOYEES;

    +-------------+-----------+------------+----------+------------+
| EMPLOYEE_ID | LAST_NAME | FIRST_NAME | SALARY   | MANAGER_ID |
+-------------+-----------+------------+----------+------------+
|         100 | King      | Steven     | 27852.00 |       NULL |
|         101 | Kochhar   | Neena      | 17600.00 |        100 |
|         102 | De Haan   | Lex        | 19800.00 |        100 |
|         103 | Hunold    | Alexander  | 10200.00 |        102 |
|         104 | Ernst     | Bruce      |  6000.00 |        103 |
|         107 | Lorentz   | Diana      |  4200.00 |        103 |
|         124 | Mourgos   | Kevin      |  5850.00 |        100 |
|         141 | Rajs      | Trenna     |  3500.00 |        124 |
|         142 | Davies    | Curtis     |  3100.00 |        124 |
|         143 | Matos     | Randall    |  2600.00 |        124 |
|         144 | Vargas    | Peter      |  2500.00 |        124 |
|         149 | Zlotkey   | Eleni      | 17733.33 |        100 |
|         174 | Abel      | Ellen      | 11000.00 |        149 |
|         176 | Taylor    | Jonathan   |  8600.00 |        149 |
|         178 | Grant     | Kimberely  |  7000.00 |        149 |
|         200 | Whalen    | Jennifer   |  4400.00 |        101 |
|         201 | Hartstein | Michael    | 12000.00 |        100 |
|         202 | Fay       | Pat        |  6000.00 |        201 |
|         205 | Higgins   | Shelley    | 16600.00 |        101 |
|         206 | Gietz     | William    |  8300.00 |        205 |
+-------------+-----------+------------+----------+------------+

  4. Desfés els canvis que has fet.

    Possible solució 
    ROLLBACK;

    SELECT EMPLOYEE_ID, LAST_NAME, FIRST_NAME, SALARY, MANAGER_ID
FROM EMPLOYEES;

    +-------------+-----------+------------+----------+------------+
| EMPLOYEE_ID | LAST_NAME | FIRST_NAME | SALARY   | MANAGER_ID |
+-------------+-----------+------------+----------+------------+
|         100 | King      | Steven     | 24000.00 |       NULL |
|         101 | Kochhar   | Neena      | 17000.00 |        100 |
|         102 | De Haan   | Lex        | 17000.00 |        100 |
|         103 | Hunold    | Alexander  |  9000.00 |        102 |
|         104 | Ernst     | Bruce      |  6000.00 |        103 |
|         107 | Lorentz   | Diana      |  4200.00 |        103 |
|         124 | Mourgos   | Kevin      |  5800.00 |        100 |
|         141 | Rajs      | Trenna     |  3500.00 |        124 |
|         142 | Davies    | Curtis     |  3100.00 |        124 |
|         143 | Matos     | Randall    |  2600.00 |        124 |
|         144 | Vargas    | Peter      |  2500.00 |        124 |
|         149 | Zlotkey   | Eleni      | 10500.00 |        100 |
|         174 | Abel      | Ellen      | 11000.00 |        149 |
|         176 | Taylor    | Jonathan   |  8600.00 |        149 |
|         178 | Grant     | Kimberely  |  7000.00 |        149 |
|         200 | Whalen    | Jennifer   |  4400.00 |        101 |
|         201 | Hartstein | Michael    | 13000.00 |        100 |
|         202 | Fay       | Pat        |  6000.00 |        201 |
|         205 | Higgins   | Shelley    | 12000.00 |        101 |
|         206 | Gietz     | William    |  8300.00 |        205 |
+-------------+-----------+------------+----------+------------+