Exercicis de creació, manipulació i eliminació de vistes

Peces

Crear una vista dels enviaments del proveïdor s2

Creo la vista vEnviamentsS2:

MariaDB [peces]> CREATE VIEW vEnviamentsS2 as
    -> SELECT * FROM sp where s=2 WITH CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)

Comprovo el contingut que em mostra aquesta vista

MariaDB [peces]> select * from vEnviamentsS2;
+----+-----+------+
| s  | p   | cant |
+----+-----+------+
| s2 | p10 |  300 |
| s2 | p4  |  200 |
| s2 | p5  |  200 |
| s2 | p6  |  100 |
| s2 | p7  |  400 |
| s2 | p8  |  200 |
| s2 | p9  |  130 |
+----+-----+------+
7 rows in set (0.00 sec)

Intento introduïr dades a la vista

MariaDB [peces]> INSERT INTO vEnviamentsS2 (s,p,cant) VALUES ("s2","p5",50);
ERROR 1062 (23000): Duplicate entry 's2-p5' for key 'PRIMARY'

L'error que apareix és de duplicat de clau primària perquè el registre amb s2 i p5 ja existeix.

Introduiré un registre que sigui vàlid

MariaDB [peces]> INSERT INTO vEnviamentsS2 (s,p,cant) VALUES ("s3","p5",50);
ERROR 1369 (HY000): CHECK OPTION failed 'peces.vEnviamentsS2'

En aquest cas, l'error està relacionat amb l'opció de CHECK OPTION que hem definit al moment de crear la vista. Com que el registre nou no compleix la condició del WHERE de la vista, no ens el deixa inserir.

Introduiré un registre vàlid, i que compleixi el CHECK OPTION

MariaDB [peces]> INSERT INTO vEnviamentsS2 (s,p,cant) VALUES ("s2","p3",50);
Query OK, 1 row affected (0.05 sec)

S'ha introduït correctament.

Comprovo que l'INSERT es vegi reflectit tant a la vista com a la taula original

MariaDB [peces]> select * from vEnviamentsS2;
+----+-----+------+
| s  | p   | cant |
+----+-----+------+
| s2 | p10 |  300 |
| s2 | p3  |   50 | <-- Registre nou
| s2 | p4  |  200 |
| s2 | p5  |  200 |
| s2 | p6  |  100 |
| s2 | p7  |  400 |
| s2 | p8  |  200 |
| s2 | p9  |  130 |
+----+-----+------+
8 rows in set (0.00 sec)

MariaDB [peces]> select * from sp WHERE s="s2";
+----+-----+------+
| s  | p   | cant |
+----+-----+------+
| s2 | p10 |  300 |
| s2 | p3  |   50 | <-- Registre nou
| s2 | p4  |  200 |
| s2 | p5  |  200 |
| s2 | p6  |  100 |
| s2 | p7  |  400 |
| s2 | p8  |  200 |
| s2 | p9  |  130 |
+----+-----+------+
8 rows in set (0.00 sec)

---

Crear una vista de les peces que es troben a granollers

MariaDB [peces]> CREATE VIEW vPecesGranollers AS SELECT * FROM p WHERE ciutat="granollers" WITH CHECK OPTION;
Query OK, 0 rows affected (0.02 sec)}}

Comprovo el contingut de la vista vPecesGranollers

MariaDB [peces]> select * from vPecesGranollers;
+-----+---------+---------+------+------------+
| p   | pnom    | color   | pes  | ciutat     |
+-----+---------+---------+------+------------+
| p1  | peca 1  | vermell | 10   | granollers |
| p10 | peca 10 | gris    | 10   | granollers |
| p11 | peca 11 | negre   | 14   | granollers |
| p14 | peca 14 | groc    | 11   | granollers |
| p15 | peca 15 | blanc   | 11   | granollers |
| p18 | peca 18 | vermell | 11   | granollers |
| p7  | peca 7  | taronja | 10   | granollers |
| p8  | peca 8  | taronja | 12   | granollers |
| p9  | peca 9  | gris    | 14   | granollers |
+-----+---------+---------+------+------------+
9 rows in set (0.00 sec)

Introduiré dades a la vista que no compleixin la condició

MariaDB [peces]> INSERT INTO vPecesGranollers (p,pnom,color,pes,ciutat) VALUES ("p69","peca 69","taronja ubuntu",54,"tona");
ERROR 1369 (HY000): CHECK OPTION failed 'peces.vPecesGranollers'

Falla el CHECK OPTION perquè el camp ciutat inserit no és granollers.

Introduiré un valor que compleixi el CHECK OPTION però que repeteixi clau

MariaDB [peces]> INSERT INTO vPecesGranollers (p,pnom,color,pes,ciutat) VALUES ("p10","peca repetida","taronja ubuntu",54,"granollers");
ERROR 1062 (23000): Duplicate entry 'p10' for key 'PRIMARY'

Faré un insert d'un registre correcte i comprovaré el contingut de la taula original i la vista

MariaDB [peces]> INSERT INTO vPecesGranollers (p,pnom,color,pes,ciutat) VALUES ("p0","peca zero","taronja ubuntu",54,"granollers");
Query OK, 1 row affected, 1 warning (0.04 sec)

MariaDB [peces]> SELECT * from vPecesGranollers;
+-----+-----------+------------+------+------------+
| p   | pnom      | color      | pes  | ciutat     |
+-----+-----------+------------+------+------------+
| p0  | peca zero | taronja ub | 54   | granollers | <-- registre nou
| p1  | peca 1    | vermell    | 10   | granollers |
| p10 | peca 10   | gris       | 10   | granollers |
| p11 | peca 11   | negre      | 14   | granollers |
| p14 | peca 14   | groc       | 11   | granollers |
| p15 | peca 15   | blanc      | 11   | granollers |
| p18 | peca 18   | vermell    | 11   | granollers |
| p7  | peca 7    | taronja    | 10   | granollers |
| p8  | peca 8    | taronja    | 12   | granollers |
| p9  | peca 9    | gris       | 14   | granollers |
+-----+-----------+------------+------+------------+
10 rows in set (0.00 sec)

MariaDB [peces]> SELECT * from p WHERE ciutat="granollers";
+-----+-----------+------------+------+------------+
| p   | pnom      | color      | pes  | ciutat     |
+-----+-----------+------------+------+------------+
| p0  | peca zero | taronja ub | 54   | granollers | <-- registre nou
| p1  | peca 1    | vermell    | 10   | granollers |
| p10 | peca 10   | gris       | 10   | granollers |
| p11 | peca 11   | negre      | 14   | granollers |
| p14 | peca 14   | groc       | 11   | granollers |
| p15 | peca 15   | blanc      | 11   | granollers |
| p18 | peca 18   | vermell    | 11   | granollers |
| p7  | peca 7    | taronja    | 10   | granollers |
| p8  | peca 8    | taronja    | 12   | granollers |
| p9  | peca 9    | gris       | 14   | granollers |
+-----+-----------+------------+------+------------+
10 rows in set (0.00 sec)

---

Autors amb més de 2 llibres:

MariaDB [biblioteca]> SELECT * FROM autors where autor in (SELECT autor FROM autors GROUP BY autor HAVING count(autor)>2);
+------+----------+
| isbn | autor    |
+------+----------+
| 1    | Yo mismo |
| 2    | Anonimo  |
| 3    | Anonimo  |
| 4    | Yo mismo |
| 6    | Anonimo  |
| 8    | Yo mismo |
| 9    | Anonimo  |
+------+----------+
7 rows in set (0.03 sec)