ASIX/M02/UF2/A09/E1

Guillem Solà i Boeck (host A10BPC05)

Crear una vista pels paisos de Europe

MariaDB [world]> CREATE VIEW vPractica91 AS SELECT * FROM Country WHERE Continent="Europe" WITH CHECK OPTION;
Query OK, 0 rows affected (0.02 sec)

MariaDB [world]> SELECT * FROM vPractica91;
+------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+
| Code | Name                          | Continent | Region           | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP        | GNPOld     | LocalName                      | GovernmentForm                      | HeadOfState              | Capital | Code2 |
+------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+
| ALB  | Albania                       | Europe    | Southern Europe  |    28748.00 |      1912 |    3401200 |           71.6 |    3205.00 |    2500.00 | Shqipëria                      | Republic                            | Rexhep Mejdani           |      34 | AL    |
| AND  | Andorra                       | Europe    | Southern Europe  |      468.00 |      1278 |      78000 |           83.5 |    1630.00 |       NULL | Andorra                        | Parliamentary Coprincipality        |                          |      55 | AD    |
| AUT  | Austria                       | Europe    | Western Europe   |    83859.00 |      1918 |    8091800 |           77.7 |  211860.00 |  206025.00 | Österreich                     | Federal Republic                    | Thomas Klestil           |    1523 | AT    |
| BEL  | Belgium                       | Europe    | Western Europe   |    30518.00 |      1830 |   10239000 |           77.8 |  249704.00 |  243948.00 | België/Belgique                | Constitutional Monarchy, Federation | Albert II                |     179 | BE    |
| BGR  | Bulgaria                      | Europe    | Eastern Europe   |   110994.00 |      1908 |    8190900 |           70.9 |   12178.00 |   10169.00 | Balgarija                      | Republic                            | Petar Stojanov           |     539 | BG    |
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| VAT  | Holy See (Vatican City State) | Europe    | Southern Europe  |        0.40 |      1929 |       1000 |           NULL |       9.00 |       NULL | Santa Sede/Città del Vaticano  | Independent Church State            | Johannes Paavali II      |    3538 | VA    |
| YUG  | Yugoslavia                    | Europe    | Southern Europe  |   102173.00 |      1918 |   10640000 |           72.4 |   17000.00 |       NULL | Jugoslavija                    | Federal Republic                    | Vojislav Koštunica       |    1792 | YU    |
+------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+
46 rows in set (0.00 sec)

Introduir un país en aquesta vista. Per comprovar que està introduït a Country

Afegeixo un registre amb uns quants valors
MariaDB [world]> INSERT INTO vPractica91 (Code, Name, Continent, Region, Population) VALUES ("GS", "Guillemland", "Europe", "Wektabyte", 1);
Query OK, 1 row affected (0.04 sec)

Verifico que s'ha introduït

MariaDB [world]> SELECT * FROM Country WHERE Name = "Guillemland";
+------+-------------+------------+-----------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
| Code | Name        | Continent  | Region    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP  | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+-------------+------------+-----------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
| GS   | Guillemland | Europe | Wektabyte |        0.00 |      NULL |          1 |           NULL | NULL |   NULL |           |                | NULL        |    NULL |       |
+------+-------------+------------+-----------+-------------+-----------+------------+----------------+------+--------+-----------+----------------+-------------+---------+-------+
1 row in set (0.00 sec)

Eliminar aquest país des de la taula Country

Elimino el registre anterior

MariaDB [world]> DELETE FROM Country WHERE Name = "Guillemland";
Query OK, 1 row affected (0.06 sec)

Verifico que s'ha eliminat

MariaDB [world]> SELECT * FROM Country WHERE Name = "Guillemland";
Empty set (0.00 sec)

Canviar la vista, per a que siguin els paisos de South America

Executo l'ordre de CREATE VIEW amb REPLACE perquè es modifiqui la vista creada anteriorment i també modifico el SELECT perquè correspongui amb l'enunciat:

MariaDB [world]> CREATE OR REPLACE VIEW vPractica91 AS SELECT * FROM Country WHERE Continent="South America";
Query OK, 0 rows affected (0.04 sec)

Verifico que la vista sigui correcta (mostri els països de South America:

MariaDB [world]> SELECT * FROM vPractica91;
+------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+
| Code | Name             | Continent     | Region        | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP       | GNPOld    | LocalName         | GovernmentForm                | HeadOfState                  | Capital | Code2 |
+------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+
| ARG  | Argentina        | South America | South America |  2780400.00 |      1816 |   37032000 |           75.1 | 340238.00 | 323310.00 | Argentina         | Federal Republic              | Fernando de la Rúa           |      69 | AR    |
| BOL  | Bolivia          | South America | South America |  1098581.00 |      1825 |    8329000 |           63.7 |   8571.00 |   7967.00 | Bolivia           | Republic                      | Hugo Bánzer Suárez           |     194 | BO    |
| BRA  | Brazil           | South America | South America |  8547403.00 |      1822 |  170115000 |           62.9 | 776739.00 | 804108.00 | Brasil            | Federal Republic              | Fernando Henrique Cardoso    |     211 | BR    |
| CHL  | Chile            | South America | South America |   756626.00 |      1810 |   15211000 |           75.7 |  72949.00 |  75780.00 | Chile             | Republic                      | Ricardo Lagos Escobar        |     554 | CL    |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SUR  | Suriname         | South America | South America |   163265.00 |      1975 |     417000 |           71.4 |    870.00 |    706.00 | Suriname          | Republic                      | Ronald Venetiaan             |    3243 | SR    |
| URY  | Uruguay          | South America | South America |   175016.00 |      1828 |    3337000 |           75.2 |  20831.00 |  19967.00 | Uruguay           | Republic                      | Jorge Batlle Ibáñez          |    3492 | UY    |
| VEN  | Venezuela        | South America | South America |   912050.00 |      1811 |   24170000 |           73.1 |  95023.00 |  88434.00 | Venezuela         | Federal Republic              | Hugo Chávez Frías            |    3539 | VE    |
+------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+
14 rows in set (0.00 sec)