Pt31. Inserció de dades com a resultat d'un select

De Lordwektabyte Wiki
Salta a la navegació Salta a la cerca


Guillem Solà i Boeck (host A10BPC05)

1. Crear una taula, per obtenir l'idioma (oficial) que a mes països es parla, per continent

MariaDB [world]> create table Intermitja select Continent, Language, count(*) as compte from CountryLanguage, Country where Country.Code=CountryLanguage.CountryCode and IsOfficial = "T" group by Continent, Language order by compte desc;

Query OK, 120 rows affected (0.67 sec)
Records: 120  Duplicates: 0  Warnings: 0


 
MariaDB [world]> 
MariaDB [world]> MariaDB [world]> select * from Intermitja
+---------------+------------------+--------+
| Continent     | Language         | compte |
+---------------+------------------+--------+
| Oceania       | English          |     18 |
| North America | English          |     15 |
| Asia          | Arabic           |     12 |
| North America | Spanish          |     10 |
| Africa        | Arabic           |     10 |
| South America | Spanish          |      9 |
| Europe        | German           |      6 |
| Africa        | English          |      5 |
| North America | French           |      5 |
| Africa        | French           |      5 |
| Europe        | French           |      5 |
| Asia          | Malay            |      4 |
| Europe        | Italian          |      4 |
| Europe        | English          |      4 |
| Oceania       | French           |      3 |
| Europe        | Serbo-Croatian   |      3 |
| Europe        | Danish           |      2 |
| Europe        | Swedish          |      2 |
| Asia          | Portuguese       |      2 |
| South America | Aimará           |      2 |
| Oceania       | Samoan           |      2 |
| Europe        | Dutch            |      2 |
| Asia          | Chinese          |      2 |
| Asia          | Korean           |      2 |
| Europe        | Romanian         |      2 |
| South America | Ketšua           |      2 |
| Europe        | Norwegian        |      2 |
| Africa        | Portuguese       |      2 |
| Asia          | Tamil            |      2 |
| Europe        | Russian          |      2 |
| North America | Dutch            |      2 |
| Asia          | Turkish          |      2 |
| Oceania       | Kiribati         |      1 |
| Africa        | Wolof            |      1 |
| Africa        | Ewe              |      1 |
| Asia          | Azerbaijani      |      1 |
| Oceania       | Tongan           |      1 |
| Asia          | Bengali          |      1 |
| Asia          | Georgiana        |      1 |
| Europe        | Macedonian       |      1 |
| Asia          | Uzbek            |      1 |
| Asia          | English          |      1 |
| Africa        | Zulu             |      1 |
| Europe        | Icelandic        |      1 |
| Asia          | Pilipino         |      1 |
| Asia          | Dari             |      1 |
| Africa        | Comorian         |      1 |
| Asia          | Russian          |      1 |
| Europe        | Romani           |      1 |
| North America | Papiamento       |      1 |
| Europe        | Czech            |      1 |
| Asia          | Singali          |      1 |
| Europe        | Slovene          |      1 |
| Europe        | Estonian         |      1 |
| Europe        | Luxembourgish    |      1 |
| Asia          | Tadzhik          |      1 |
| Europe        | Faroese          |      1 |
| Asia          | Dhivehi          |      1 |
| Africa        | Swahili          |      1 |
| Europe        | Belorussian      |      1 |
| North America | Greenlandic      |      1 |
| Asia          | Mongolian        |      1 |
| Africa        | Afrikaans        |      1 |
| Europe        | Irish            |      1 |
| Oceania       | Nauru            |      1 |
| Asia          | Kazakh           |      1 |
| Europe        | Portuguese       |      1 |
| Europe        | Albaniana        |      1 |
| Asia          | Greek            |      1 |
| Africa        | Somali           |      1 |
| Asia          | Armenian         |      1 |
| Africa        | Tigrinja         |      1 |
| Africa        | Sotho            |      1 |
| Africa        | Kabyé            |      1 |
| Oceania       | Fijian           |      1 |
| Oceania       | Tuvalu           |      1 |
| Europe        | Bulgariana       |      1 |
| Europe        | Greek            |      1 |
| Europe        | Maltese          |      1 |
| Asia          | Vietnamese       |      1 |
| Europe        | Hungarian        |      1 |
| Asia          | Hebrew           |      1 |
| Oceania       | Palau            |      1 |
| Asia          | Pashto           |      1 |
| Asia          | Khmer            |      1 |
| Africa        | Rwanda           |      1 |
| Africa        | Swazi            |      1 |
| Europe        | Finnish          |      1 |
| Europe        | Latvian          |      1 |
| Asia          | Turkmenian       |      1 |
| Oceania       | Marshallese      |      1 |
| Europe        | Ukrainian        |      1 |
| Oceania       | Chamorro         |      1 |
| Africa        | Chichewa         |      1 |
| Africa        | Xhosa            |      1 |
| Asia          | Dzongkha         |      1 |
| Asia          | Persian          |      1 |
| Asia          | Urdu             |      1 |
| Oceania       | Maori            |      1 |
| Asia          | Kirgiz           |      1 |
| South America | Guaraní          |      1 |
| Europe        | Catalan          |      1 |
| Asia          | Lao              |      1 |
| Europe        | Slovak           |      1 |
| Europe        | Spanish          |      1 |
| Europe        | Lithuanian       |      1 |
| Asia          | Thai             |      1 |
| Africa        | Kirundi          |      1 |
| South America | English          |      1 |
| Africa        | Malagasy         |      1 |
| Asia          | Mandarin Chinese |      1 |
| North America | Danish           |      1 |
| Asia          | Burmese          |      1 |
| Oceania       | Bislama          |      1 |
| South America | Portuguese       |      1 |
| Asia          | Hindi            |      1 |
| Asia          | Nepali           |      1 |
| Europe        | Romansh          |      1 |
| Asia          | Japanese         |      1 |
| Europe        | Polish           |      1 |
+---------------+------------------+--------+
120 rows in set (0.00 sec)


 
MariaDB [world]> 
MariaDB [world]> MariaDB [world]> select Language from CountryLanguage group by Language order by Percentage asc Limit 1;
+---------------+----------+--------+
| Continent     | Language | compte |
+---------------+----------+--------+
| Oceania       | English  |     18 |
| North America | English  |     15 |
| Asia          | Arabic   |     12 |
| Africa        | Arabic   |     10 |
| South America | Spanish  |      9 |
| Europe        | German   |      6 |
+---------------+----------+--------+
6 rows in set (0.00 sec)

 
MariaDB [world]> 

2. Per conèixer les dades dels països que no son una República

MariaDB [world]> MariaDB [world]> MariaDB [world]> create table NoRepublicans select * from Country where GovernmentForm not like '%republic%';
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+----------------------------------+---------+-------+
| Code | Name                                         | Continent     | Region                    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP        | GNPOld     | LocalName                                    | GovernmentForm                               | HeadOfState                      | Capital | Code2 |
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+----------------------------------+---------+-------+
| ABW  | Aruba                                        | North America | Caribbean                 |      193.00 |      NULL |     103000 |           78.4 |     828.00 |     793.00 | Aruba                                        | Nonmetropolitan Territory of The Netherlands | Beatrix                          |     129 | AW    |
| AFG  | Afghanistan                                  | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 |    5976.00 |       NULL | Afganistan/Afqanestan                        | Islamic Emirate                              | Mohammad Omar                    |       1 | AF    |
| AIA  | Anguilla                                     | North America | Caribbean                 |       96.00 |      NULL |       8000 |           76.1 |      63.20 |       NULL | Anguilla                                     | Dependent Territory of the UK                | Elisabeth II                     |      62 | AI    |
| AND  | Andorra                                      | Europe        | Southern Europe           |      468.00 |      1278 |      78000 |           83.5 |    1630.00 |       NULL | Andorra                                      | Parliamentary Coprincipality                 |                                  |      55 | AD    |
| ANT  | Netherlands Antilles                         | North America | Caribbean                 |      800.00 |      NULL |     217000 |           74.7 |    1941.00 |       NULL | Nederlandse Antillen                         | Nonmetropolitan Territory of The Netherlands | Beatrix                          |      33 | AN    |
| ARE  | United Arab Emirates                         | Asia          | Middle East               |    83600.00 |      1971 |    2441000 |           74.1 |   37966.00 |   36846.00 | Al-Imarat al-´Arabiya al-Muttahida           | Emirate Federation                           | Zayid bin Sultan al-Nahayan      |      65 | AE    |
| ASM  | American Samoa                               | Oceania       | Polynesia                 |      199.00 |      NULL |      68000 |           75.1 |     334.00 |       NULL | Amerika Samoa                                | US Territory                                 | George W. Bush                   |      54 | AS    |
| ATA  | Antarctica                                   | Antarctica    | Antarctica                | 13120000.00 |      NULL |          0 |           NULL |       0.00 |       NULL | –                                            | Co-administrated                             |                                  |    NULL | AQ    |
| ATF  | French Southern territories                  | Antarctica    | Antarctica                |     7780.00 |      NULL |          0 |           NULL |       0.00 |       NULL | Terres australes françaises                  | Nonmetropolitan Territory of France          | Jacques Chirac                   |    NULL | TF    |
| ATG  | Antigua and Barbuda                          | North America | Caribbean                 |      442.00 |      1981 |      68000 |           70.5 |     612.00 |     584.00 | Antigua and Barbuda                          | Constitutional Monarchy                      | Elisabeth II                     |      63 | AG    |
| AUS  | Australia                                    | Oceania       | Australia and New Zealand |  7741220.00 |      1901 |   18886000 |           79.8 |  351182.00 |  392911.00 | Australia                                    | Constitutional Monarchy, Federation          | Elisabeth II                     |     135 | AU    |
| BEL  | Belgium                                      | Europe        | Western Europe            |    30518.00 |      1830 |   10239000 |           77.8 |  249704.00 |  243948.00 | België/Belgique                              | Constitutional Monarchy, Federation          | Albert II                        |     179 | BE    |
| BHR  | Bahrain                                      | Asia          | Middle East               |      694.00 |      1971 |     617000 |           73.0 |    6366.00 |    6097.00 | Al-Bahrayn                                   | Monarchy (Emirate)                           | Hamad ibn Isa al-Khalifa         |     149 | BH    |
| BHS  | Bahamas                                      | North America | Caribbean                 |    13878.00 |      1973 |     307000 |           71.1 |    3527.00 |    3347.00 | The Bahamas                                  | Constitutional Monarchy                      | Elisabeth II                     |     148 | BS    |
| BLZ  | Belize                                       | North America | Central America           |    22696.00 |      1981 |     241000 |           70.9 |     630.00 |     616.00 | Belize                                       | Constitutional Monarchy                      | Elisabeth II                     |     185 | BZ    |
| BMU  | Bermuda                                      | North America | North America             |       53.00 |      NULL |      65000 |           76.9 |    2328.00 |    2190.00 | Bermuda                                      | Dependent Territory of the UK                | Elisabeth II                     |     191 | BM    |
| BRB  | Barbados                                     | North America | Caribbean                 |      430.00 |      1966 |     270000 |           73.0 |    2223.00 |    2186.00 | Barbados                                     | Constitutional Monarchy                      | Elisabeth II                     |     174 | BB    |
| BRN  | Brunei                                       | Asia          | Southeast Asia            |     5765.00 |      1984 |     328000 |           73.6 |   11705.00 |   12460.00 | Brunei Darussalam                            | Monarchy (Sultanate)                         | Haji Hassan al-Bolkiah           |     538 | BN    |
| BTN  | Bhutan                                       | Asia          | Southern and Central Asia |    47000.00 |      1910 |    2124000 |           52.4 |     372.00 |     383.00 | Druk-Yul                                     | Monarchy                                     | Jigme Singye Wangchuk            |     192 | BT    |
| BVT  | Bouvet Island                                | Antarctica    | Antarctica                |       59.00 |      NULL |          0 |           NULL |       0.00 |       NULL | Bouvetøya                                    | Dependent Territory of Norway                | Harald V                         |    NULL | BV    |
| CAN  | Canada                                       | North America | North America             |  9970610.00 |      1867 |   31147000 |           79.4 |  598862.00 |  625626.00 | Canada                                       | Constitutional Monarchy, Federation          | Elisabeth II                     |    1822 | CA    |
| CCK  | Cocos (Keeling) Islands                      | Oceania       | Australia and New Zealand |       14.00 |      NULL |        600 |           NULL |       0.00 |       NULL | Cocos (Keeling) Islands                      | Territory of Australia                       | Elisabeth II                     |    2317 | CC    |
| CHE  | Switzerland                                  | Europe        | Western Europe            |    41284.00 |      1499 |    7160400 |           79.6 |  264478.00 |  256092.00 | Schweiz/Suisse/Svizzera/Svizra               | Federation                                   | Adolf Ogi                        |    3248 | CH    |
| COK  | Cook Islands                                 | Oceania       | Polynesia                 |      236.00 |      NULL |      20000 |           71.1 |     100.00 |       NULL | The Cook Islands                             | Nonmetropolitan Territory of New Zealand     | Elisabeth II                     |     583 | CK    |
| CXR  | Christmas Island                             | Oceania       | Australia and New Zealand |      135.00 |      NULL |       2500 |           NULL |       0.00 |       NULL | Christmas Island                             | Territory of Australia                       | Elisabeth II                     |    1791 | CX    |
| CYM  | Cayman Islands                               | North America | Caribbean                 |      264.00 |      NULL |      38000 |           78.9 |    1263.00 |    1186.00 | Cayman Islands                               | Dependent Territory of the UK                | Elisabeth II                     |     553 | KY    |
| DNK  | Denmark                                      | Europe        | Nordic Countries          |    43094.00 |       800 |    5330000 |           76.5 |  174099.00 |  169264.00 | Danmark                                      | Constitutional Monarchy                      | Margrethe II                     |    3315 | DK    |
| ESH  | Western Sahara                               | Africa        | Northern Africa           |   266000.00 |      NULL |     293000 |           49.8 |      60.00 |       NULL | As-Sahrawiya                                 | Occupied by Marocco                          | Mohammed Abdel Aziz              |    2453 | EH    |
| ESP  | Spain                                        | Europe        | Southern Europe           |   505992.00 |      1492 |   39441700 |           78.8 |  553233.00 |  532031.00 | España                                       | Constitutional Monarchy                      | Juan Carlos I                    |     653 | ES    |
| FLK  | Falkland Islands                             | South America | South America             |    12173.00 |      NULL |       2000 |           NULL |       0.00 |       NULL | Falkland Islands                             | Dependent Territory of the UK                | Elisabeth II                     |     763 | FK    |
| FRO  | Faroe Islands                                | Europe        | Nordic Countries          |     1399.00 |      NULL |      43000 |           78.4 |       0.00 |       NULL | Føroyar                                      | Part of Denmark                              | Margrethe II                     |     901 | FO    |
| GBR  | United Kingdom                               | Europe        | British Islands           |   242900.00 |      1066 |   59623400 |           77.7 | 1378330.00 | 1296830.00 | United Kingdom                               | Constitutional Monarchy                      | Elisabeth II                     |     456 | GB    |
| GIB  | Gibraltar                                    | Europe        | Southern Europe           |        6.00 |      NULL |      25000 |           79.0 |     258.00 |       NULL | Gibraltar                                    | Dependent Territory of the UK                | Elisabeth II                     |     915 | GI    |
| GLP  | Guadeloupe                                   | North America | Caribbean                 |     1705.00 |      NULL |     456000 |           77.0 |    3501.00 |       NULL | Guadeloupe                                   | Overseas Department of France                | Jacques Chirac                   |     919 | GP    |
| GRD  | Grenada                                      | North America | Caribbean                 |      344.00 |      1974 |      94000 |           64.5 |     318.00 |       NULL | Grenada                                      | Constitutional Monarchy                      | Elisabeth II                     |     916 | GD    |
| GRL  | Greenland                                    | North America | North America             |  2166090.00 |      NULL |      56000 |           68.1 |       0.00 |       NULL | Kalaallit Nunaat/Grønland                    | Part of Denmark                              | Margrethe II                     |     917 | GL    |
| GUF  | French Guiana                                | South America | South America             |    90000.00 |      NULL |     181000 |           76.1 |     681.00 |       NULL | Guyane française                             | Overseas Department of France                | Jacques Chirac                   |    3014 | GF    |
| GUM  | Guam                                         | Oceania       | Micronesia                |      549.00 |      NULL |     168000 |           77.8 |    1197.00 |    1136.00 | Guam                                         | US Territory                                 | George W. Bush                   |     921 | GU    |
| HKG  | Hong Kong                                    | Asia          | Eastern Asia              |     1075.00 |      NULL |    6782000 |           79.5 |  166448.00 |  173610.00 | Xianggang/Hong Kong                          | Special Administrative Region of China       | Jiang Zemin                      |     937 | HK    |
| HMD  | Heard Island and McDonald Islands            | Antarctica    | Antarctica                |      359.00 |      NULL |          0 |           NULL |       0.00 |       NULL | Heard and McDonald Islands                   | Territory of Australia                       | Elisabeth II                     |    NULL | HM    |
| IOT  | British Indian Ocean Territory               | Africa        | Eastern Africa            |       78.00 |      NULL |          0 |           NULL |       0.00 |       NULL | British Indian Ocean Territory               | Dependent Territory of the UK                | Elisabeth II                     |    NULL | IO    |
| JAM  | Jamaica                                      | North America | Caribbean                 |    10990.00 |      1962 |    2583000 |           75.2 |    6871.00 |    6722.00 | Jamaica                                      | Constitutional Monarchy                      | Elisabeth II                     |    1530 | JM    |
| JOR  | Jordan                                       | Asia          | Middle East               |    88946.00 |      1946 |    5083000 |           77.4 |    7526.00 |    7051.00 | Al-Urdunn                                    | Constitutional Monarchy                      | Abdullah II                      |    1786 | JO    |
| JPN  | Japan                                        | Asia          | Eastern Asia              |   377829.00 |      -660 |  126714000 |           80.7 | 3787042.00 | 4192638.00 | Nihon/Nippon                                 | Constitutional Monarchy                      | Akihito                          |    1532 | JP    |
| KHM  | Cambodia                                     | Asia          | Southeast Asia            |   181035.00 |      1953 |   11168000 |           56.5 |    5121.00 |    5670.00 | Kâmpuchéa                                    | Constitutional Monarchy                      | Norodom Sihanouk                 |    1800 | KH    |
| KNA  | Saint Kitts and Nevis                        | North America | Caribbean                 |      261.00 |      1983 |      38000 |           70.7 |     299.00 |       NULL | Saint Kitts and Nevis                        | Constitutional Monarchy                      | Elisabeth II                     |    3064 | KN    |
| KWT  | Kuwait                                       | Asia          | Middle East               |    17818.00 |      1961 |    1972000 |           76.1 |   27037.00 |   30373.00 | Al-Kuwayt                                    | Constitutional Monarchy (Emirate)            | Jabir al-Ahmad al-Jabir al-Sabah |    2429 | KW    |
| LBY  | Libyan Arab Jamahiriya                       | Africa        | Northern Africa           |  1759540.00 |      1951 |    5605000 |           75.5 |   44806.00 |   40562.00 | Libiya                                       | Socialistic State                            | Muammar al-Qadhafi               |    2441 | LY    |
| LCA  | Saint Lucia                                  | North America | Caribbean                 |      622.00 |      1979 |     154000 |           72.3 |     571.00 |       NULL | Saint Lucia                                  | Constitutional Monarchy                      | Elisabeth II                     |    3065 | LC    |
| LIE  | Liechtenstein                                | Europe        | Western Europe            |      160.00 |      1806 |      32300 |           78.8 |    1119.00 |    1084.00 | Liechtenstein                                | Constitutional Monarchy                      | Hans-Adam II                     |    2446 | LI    |
| LSO  | Lesotho                                      | Africa        | Southern Africa           |    30355.00 |      1966 |    2153000 |           50.8 |    1061.00 |    1161.00 | Lesotho                                      | Constitutional Monarchy                      | Letsie III                       |    2437 | LS    |
| LUX  | Luxembourg                                   | Europe        | Western Europe            |     2586.00 |      1867 |     435700 |           77.1 |   16321.00 |   15519.00 | Luxembourg/Lëtzebuerg                        | Constitutional Monarchy                      | Henri                            |    2452 | LU    |
| MAC  | Macao                                        | Asia          | Eastern Asia              |       18.00 |      NULL |     473000 |           81.6 |    5749.00 |    5940.00 | Macau/Aomen                                  | Special Administrative Region of China       | Jiang Zemin                      |    2454 | MO    |
| MAR  | Morocco                                      | Africa        | Northern Africa           |   446550.00 |      1956 |   28351000 |           69.1 |   36124.00 |   33514.00 | Al-Maghrib                                   | Constitutional Monarchy                      | Mohammed VI                      |    2486 | MA    |
| MCO  | Monaco                                       | Europe        | Western Europe            |        1.50 |      1861 |      34000 |           78.8 |     776.00 |       NULL | Monaco                                       | Constitutional Monarchy                      | Rainier III                      |    2695 | MC    |
| MNP  | Northern Mariana Islands                     | Oceania       | Micronesia                |      464.00 |      NULL |      78000 |           75.5 |       0.00 |       NULL | Northern Mariana Islands                     | Commonwealth of the US                       | George W. Bush                   |    2913 | MP    |
| MSR  | Montserrat                                   | North America | Caribbean                 |      102.00 |      NULL |      11000 |           78.0 |     109.00 |       NULL | Montserrat                                   | Dependent Territory of the UK                | Elisabeth II                     |    2697 | MS    |
| MTQ  | Martinique                                   | North America | Caribbean                 |     1102.00 |      NULL |     395000 |           78.3 |    2731.00 |    2559.00 | Martinique                                   | Overseas Department of France                | Jacques Chirac                   |    2508 | MQ    |
| MYS  | Malaysia                                     | Asia          | Southeast Asia            |   329758.00 |      1957 |   22244000 |           70.8 |   69213.00 |   97884.00 | Malaysia                                     | Constitutional Monarchy, Federation          | Salahuddin Abdul Aziz Shah Alhaj |    2464 | MY    |
| MYT  | Mayotte                                      | Africa        | Eastern Africa            |      373.00 |      NULL |     149000 |           59.5 |       0.00 |       NULL | Mayotte                                      | Territorial Collectivity of France           | Jacques Chirac                   |    2514 | YT    |
| NCL  | New Caledonia                                | Oceania       | Melanesia                 |    18575.00 |      NULL |     214000 |           72.8 |    3563.00 |       NULL | Nouvelle-Calédonie                           | Nonmetropolitan Territory of France          | Jacques Chirac                   |    3493 | NC    |
| NFK  | Norfolk Island                               | Oceania       | Australia and New Zealand |       36.00 |      NULL |       2000 |           NULL |       0.00 |       NULL | Norfolk Island                               | Territory of Australia                       | Elisabeth II                     |    2806 | NF    |
| NIU  | Niue                                         | Oceania       | Polynesia                 |      260.00 |      NULL |       2000 |           NULL |       0.00 |       NULL | Niue                                         | Nonmetropolitan Territory of New Zealand     | Elisabeth II                     |    2805 | NU    |
| NLD  | Netherlands                                  | Europe        | Western Europe            |    41526.00 |      1581 |   15864000 |           78.3 |  371362.00 |  360478.00 | Nederland                                    | Constitutional Monarchy                      | Beatrix                          |       5 | NL    |
| NOR  | Norway                                       | Europe        | Nordic Countries          |   323877.00 |      1905 |    4478500 |           78.7 |  145895.00 |  153370.00 | Norge                                        | Constitutional Monarchy                      | Harald V                         |    2807 | NO    |
| NPL  | Nepal                                        | Asia          | Southern and Central Asia |   147181.00 |      1769 |   23930000 |           57.8 |    4768.00 |    4837.00 | Nepal                                        | Constitutional Monarchy                      | Gyanendra Bir Bikram             |    2729 | NP    |
| NZL  | New Zealand                                  | Oceania       | Australia and New Zealand |   270534.00 |      1907 |    3862000 |           77.8 |   54669.00 |   64960.00 | New Zealand/Aotearoa                         | Constitutional Monarchy                      | Elisabeth II                     |    3499 | NZ    |
| OMN  | Oman                                         | Asia          | Middle East               |   309500.00 |      1951 |    2542000 |           71.8 |   16904.00 |   16153.00 | ´Uman                                        | Monarchy (Sultanate)                         | Qabus ibn Sa´id                  |    2821 | OM    |
| PCN  | Pitcairn                                     | Oceania       | Polynesia                 |       49.00 |      NULL |         50 |           NULL |       0.00 |       NULL | Pitcairn                                     | Dependent Territory of the UK                | Elisabeth II                     |    2912 | PN    |
| PNG  | Papua New Guinea                             | Oceania       | Melanesia                 |   462840.00 |      1975 |    4807000 |           63.1 |    4988.00 |    6328.00 | Papua New Guinea/Papua Niugini               | Constitutional Monarchy                      | Elisabeth II                     |    2884 | PG    |
| PRI  | Puerto Rico                                  | North America | Caribbean                 |     8875.00 |      NULL |    3869000 |           75.6 |   34100.00 |   32100.00 | Puerto Rico                                  | Commonwealth of the US                       | George W. Bush                   |    2919 | PR    |
| PSE  | Palestine                                    | Asia          | Middle East               |     6257.00 |      NULL |    3101000 |           71.4 |    4173.00 |       NULL | Filastin                                     | Autonomous Area                              | Yasser (Yasir) Arafat            |    4074 | PS    |
| PYF  | French Polynesia                             | Oceania       | Polynesia                 |     4000.00 |      NULL |     235000 |           74.8 |     818.00 |     781.00 | Polynésie française                          | Nonmetropolitan Territory of France          | Jacques Chirac                   |    3016 | PF    |
| QAT  | Qatar                                        | Asia          | Middle East               |    11000.00 |      1971 |     599000 |           72.4 |    9472.00 |    8920.00 | Qatar                                        | Monarchy                                     | Hamad ibn Khalifa al-Thani       |    2973 | QA    |
| REU  | Réunion                                      | Africa        | Eastern Africa            |     2510.00 |      NULL |     699000 |           72.7 |    8287.00 |    7988.00 | Réunion                                      | Overseas Department of France                | Jacques Chirac                   |    3017 | RE    |
| SAU  | Saudi Arabia                                 | Asia          | Middle East               |  2149690.00 |      1932 |   21607000 |           67.8 |  137635.00 |  146171.00 | Al-´Arabiya as-Sa´udiya                      | Monarchy                                     | Fahd ibn Abdul-Aziz al-Sa´ud     |    3173 | SA    |
| SGS  | South Georgia and the South Sandwich Islands | Antarctica    | Antarctica                |     3903.00 |      NULL |          0 |           NULL |       0.00 |       NULL | South Georgia and the South Sandwich Islands | Dependent Territory of the UK                | Elisabeth II                     |    NULL | GS    |
| SHN  | Saint Helena                                 | Africa        | Western Africa            |      314.00 |      NULL |       6000 |           76.8 |       0.00 |       NULL | Saint Helena                                 | Dependent Territory of the UK                | Elisabeth II                     |    3063 | SH    |
| SJM  | Svalbard and Jan Mayen                       | Europe        | Nordic Countries          |    62422.00 |      NULL |       3200 |           NULL |       0.00 |       NULL | Svalbard og Jan Mayen                        | Dependent Territory of Norway                | Harald V                         |     938 | SJ    |
| SLB  | Solomon Islands                              | Oceania       | Melanesia                 |    28896.00 |      1978 |     444000 |           71.3 |     182.00 |     220.00 | Solomon Islands                              | Constitutional Monarchy                      | Elisabeth II                     |    3161 | SB    |
| SPM  | Saint Pierre and Miquelon                    | North America | North America             |      242.00 |      NULL |       7000 |           77.6 |       0.00 |       NULL | Saint-Pierre-et-Miquelon                     | Territorial Collectivity of France           | Jacques Chirac                   |    3067 | PM    |
| SWE  | Sweden                                       | Europe        | Nordic Countries          |   449964.00 |       836 |    8861400 |           79.6 |  226492.00 |  227757.00 | Sverige                                      | Constitutional Monarchy                      | Carl XVI Gustaf                  |    3048 | SE    |
| SWZ  | Swaziland                                    | Africa        | Southern Africa           |    17364.00 |      1968 |    1008000 |           40.4 |    1206.00 |    1312.00 | kaNgwane                                     | Monarchy                                     | Mswati III                       |    3244 | SZ    |=Guillem Solà i Boeck (Ordinador PC05 (host A10BPC05))=

==1. Continent amb menys població==

{{QWorld
|<nowiki>MariaDB [world]> select Continent from Country group by Continent order by Population asc limit 1;;
 
MariaDB [world]> 

2. Llengua menys parlada

MariaDB [world]> MariaDB [world]> select Language from CountryLanguage group by Language order by Percentage asc Limit 1;
+----------+
| Language |
+----------+
| Soqutri  |
+----------+
1 row in set (0.00 sec)

 
MariaDB [world]> 

3. No sé quina pregunta fer, és divendres a la tarda i porto tota la setmana treballant 4h i no dóno per més

MariaDB [world]> consulta


 
MariaDB [world]> 

| TCA | Turks and Caicos Islands | North America | Caribbean | 430.00 | NULL | 17000 | 73.3 | 96.00 | NULL | The Turks and Caicos Islands | Dependent Territory of the UK | Elisabeth II | 3423 | TC | | THA | Thailand | Asia | Southeast Asia | 513115.00 | 1350 | 61399000 | 68.6 | 116416.00 | 153907.00 | Prathet Thai | Constitutional Monarchy | Bhumibol Adulyadej | 3320 | TH | | TKL | Tokelau | Oceania | Polynesia | 12.00 | NULL | 2000 | NULL | 0.00 | NULL | Tokelau | Nonmetropolitan Territory of New Zealand | Elisabeth II | 3333 | TK | | TMP | East Timor | Asia | Southeast Asia | 14874.00 | NULL | 885000 | 46.0 | 0.00 | NULL | Timor Timur | Administrated by the UN | José Alexandre Gusmão | 1522 | TP | | TON | Tonga | Oceania | Polynesia | 650.00 | 1970 | 99000 | 67.9 | 146.00 | 170.00 | Tonga | Monarchy | Taufa'ahau Tupou IV | 3334 | TO | | TUV | Tuvalu | Oceania | Polynesia | 26.00 | 1978 | 12000 | 66.3 | 6.00 | NULL | Tuvalu | Constitutional Monarchy | Elisabeth II | 3424 | TV | | UMI | United States Minor Outlying Islands | Oceania | Micronesia/Caribbean | 16.00 | NULL | 0 | NULL | 0.00 | NULL | United States Minor Outlying Islands | Dependent Territory of the US | George W. Bush | NULL | UM | | 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 | | VCT | Saint Vincent and the Grenadines | North America | Caribbean | 388.00 | 1979 | 114000 | 72.3 | 285.00 | NULL | Saint Vincent and the Grenadines | Constitutional Monarchy | Elisabeth II | 3066 | VC | | VGB | Virgin Islands, British | North America | Caribbean | 151.00 | NULL | 21000 | 75.4 | 612.00 | 573.00 | British Virgin Islands | Dependent Territory of the UK | Elisabeth II | 537 | VG | | VIR | Virgin Islands, U.S. | North America | Caribbean | 347.00 | NULL | 93000 | 78.1 | 0.00 | NULL | Virgin Islands of the United States | US Territory | George W. Bush | 4067 | VI | | WLF | Wallis and Futuna | Oceania | Polynesia | 200.00 | NULL | 15000 | NULL | 0.00 | NULL | Wallis-et-Futuna | Nonmetropolitan Territory of France | Jacques Chirac | 3536 | WF | | WSM | Samoa | Oceania | Polynesia | 2831.00 | 1962 | 180000 | 69.2 | 141.00 | 157.00 | Samoa | Parlementary Monarchy | Malietoa Tanumafili II | 3169 | WS | +------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+----------------------------------+---------+-------+ 96 rows in set (0.00 sec) </nowiki> }}

3. Crea una taula amb les dades del país on el percentatge d'ús de l'idioma Spanish és més elevat

MariaDB [world]> MariaDB [world]>create table Intermitja 2 select * from CountryLanguage where Language="Spanish" and IsOfficial='T' order by percentage desc;
Query OK, 20 rows affected (0.37 sec)
Records: 20  Duplicates: 0  Warnings: 0


 
MariaDB [world]> 
MariaDB [world]> MariaDB [world]>MariaDB [world]> create table MesCastellanoparlant select * from Country,intermitja2 where Country.Code=intermitja2.CountryCode and intermitja2.Percentage=100;
Query OK, 2 rows affected (0.31 sec)
Records: 2  Duplicates: 0  Warnings: 0


 
MariaDB [world]> 
MariaDB [world]> MariaDB [world]>MariaDB [world]> select * from MesCastellanoparlant;
+------+-------------+---------------+-----------------+-------------+-----------+------------+----------------+----------+----------+-------------+----------------------+-----------------------------------+---------+-------+-------------+----------+------------+------------+
| Code | Name        | Continent     | Region          | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP      | GNPOld   | LocalName   | GovernmentForm       | HeadOfState                       | Capital | Code2 | CountryCode | Language | IsOfficial | Percentage |
+------+-------------+---------------+-----------------+-------------+-----------+------------+----------------+----------+----------+-------------+----------------------+-----------------------------------+---------+-------+-------------+----------+------------+------------+
| SLV  | El Salvador | North America | Central America |    21041.00 |      1841 |    6276000 |           69.7 | 11863.00 | 11203.00 | El Salvador | Republic             | Francisco Guillermo Flores Pérez  |     645 | SV    | SLV         | Spanish  | T          |      100.0 |
| CUB  | Cuba        | North America | Caribbean       |   110861.00 |      1902 |   11201000 |           76.2 | 17843.00 | 18862.00 | Cuba        | Socialistic Republic | Fidel Castro Ruz                  |    2413 | CU    | CUB         | Spanish  | T          |      100.0 |
+------+-------------+---------------+-----------------+-------------+-----------+------------+----------------+----------+----------+-------------+----------------------+-----------------------------------+---------+-------+-------------+----------+------------+------------+
2 rows in set (0.01 sec)

 
MariaDB [world]>