ASIX/M02/UF2/A05/E1

Guillem Solà i Boeck (host A10BPC05)

1. Nom de la capital del país amb més població

MariaDB [world]> SELECT City.Name FROM City JOIN Country ON City.ID=Country.Capital ORDER BY Country.Population DESC LIMIT 1;

+--------+
| Name   |
+--------+
| Peking |
+--------+
1 row in set (0.00 sec)

 
MariaDB [world]> 

2. Nom dels països amb el nom de la seva capital i llengua oficial

MariaDB [world]> SELECT Country.Name as País, City.Name as Capital, CountryLanguage.Language as Llengua_Oficial FROM Country JOIN City ON City.ID=Country.Capital JOIN CountryLanguage ON CountryLanguage.CountryCode=Country.Code WHERE CountryLanguage.IsOfficial='T';

+----------------------------------+------------------------------------+------------------+
| País                             | Capital                            | Llengua_Oficial  |
+----------------------------------+------------------------------------+------------------+
| Aruba                            | Oranjestad                         | Dutch            |
| Afghanistan                      | Kabul                              | Dari             |
| Afghanistan                      | Kabul                              | Pashto           |
| Anguilla                         | The Valley                         | English          |
| Albania                          | Tirana                             | Albaniana        |
| Andorra                          | Andorra la Vella                   | Catalan          |
| Netherlands Antilles             | Willemstad                         | Dutch            |
| Netherlands Antilles             | Willemstad                         | Papiamento       |
| United Arab Emirates             | Abu Dhabi                          | Arabic           |
| Argentina                        | Buenos Aires                       | Spanish          |
| Armenia                          | Yerevan                            | Armenian         |
| American Samoa                   | Fagatogo                           | English          |
| American Samoa                   | Fagatogo                           | Samoan           |
| Antigua and Barbuda              | Saint John´s                       | English          |
| Australia                        | Canberra                           | English          |
| Austria                          | Wien                               | German           |
| Azerbaijan                       | Baku                               | Azerbaijani      |
| Burundi                          | Bujumbura                          | French           |
| Burundi                          | Bujumbura                          | Kirundi          |
| Belgium                          | Bruxelles [Brussel]                | Dutch            |
| Belgium                          | Bruxelles [Brussel]                | French           |
| Belgium                          | Bruxelles [Brussel]                | German           |
| Bangladesh                       | Dhaka                              | Bengali          |
| Bulgaria                         | Sofija                             | Bulgariana       |
| Bahrain                          | al-Manama                          | Arabic           |
| Bosnia and Herzegovina           | Sarajevo                           | Serbo-Croatian   |
| Belarus                          | Minsk                              | Belorussian      |
| Belarus                          | Minsk                              | Russian          |
| Belize                           | Belmopan                           | English          |
| Bermuda                          | Hamilton                           | English          |
| Bolivia                          | La Paz                             | Aimará           |
| Bolivia                          | La Paz                             | Ketšua           |
| Bolivia                          | La Paz                             | Spanish          |
| Brazil                           | Brasília                           | Portuguese       |
| Barbados                         | Bridgetown                         | English          |
| Brunei                           | Bandar Seri Begawan                | Malay            |
| Bhutan                           | Thimphu                            | Dzongkha         |
| Canada                           | Ottawa                             | English          |
| Canada                           | Ottawa                             | French           |
| Cocos (Keeling) Islands          | West Island                        | English          |
| Switzerland                      | Bern                               | French           |
| Switzerland                      | Bern                               | German           |
| Switzerland                      | Bern                               | Italian          |
| Switzerland                      | Bern                               | Romansh          |
| Chile                            | Santiago de Chile                  | Spanish          |
| China                            | Peking                             | Chinese          |
| Cook Islands                     | Avarua                             | Maori            |
| Colombia                         | Santafé de Bogotá                  | Spanish          |
| Comoros                          | Moroni                             | Comorian         |
| Cape Verde                       | Praia                              | Portuguese       |
| Costa Rica                       | San José                           | Spanish          |
| Cuba                             | La Habana                          | Spanish          |
| Christmas Island                 | Flying Fish Cove                   | English          |
| Cayman Islands                   | George Town                        | English          |
| Cyprus                           | Nicosia                            | Greek            |
| Cyprus                           | Nicosia                            | Turkish          |
| Czech Republic                   | Praha                              | Czech            |
| Germany                          | Berlin                             | German           |
| Djibouti                         | Djibouti                           | Arabic           |
| Denmark                          | København                          | Danish           |
| Dominican Republic               | Santo Domingo de Guzmán            | Spanish          |
| Algeria                          | Alger                              | Arabic           |
| Ecuador                          | Quito                              | Spanish          |
| Egypt                            | Cairo                              | Arabic           |
| Eritrea                          | Asmara                             | Tigrinja         |
| Western Sahara                   | El-Aaiún                           | Arabic           |
| Spain                            | Madrid                             | Spanish          |
| Estonia                          | Tallinn                            | Estonian         |
| Finland                          | Helsinki [Helsingfors]             | Finnish          |
| Finland                          | Helsinki [Helsingfors]             | Swedish          |
| Fiji Islands                     | Suva                               | Fijian           |
| Falkland Islands                 | Stanley                            | English          |
| France                           | Paris                              | French           |
| Faroe Islands                    | Tórshavn                           | Danish           |
| Faroe Islands                    | Tórshavn                           | Faroese          |
| United Kingdom                   | London                             | English          |
| Georgia                          | Tbilisi                            | Georgiana        |
| Gibraltar                        | Gibraltar                          | English          |
| Guadeloupe                       | Basse-Terre                        | French           |
| Guinea-Bissau                    | Bissau                             | Portuguese       |
| Greece                           | Athenai                            | Greek            |
| Greenland                        | Nuuk                               | Danish           |
| Greenland                        | Nuuk                               | Greenlandic      |
| Guatemala                        | Ciudad de Guatemala                | Spanish          |
| Guam                             | Agaña                              | Chamorro         |
| Guam                             | Agaña                              | English          |
| Hong Kong                        | Victoria                           | English          |
| Honduras                         | Tegucigalpa                        | Spanish          |
| Croatia                          | Zagreb                             | Serbo-Croatian   |
| Haiti                            | Port-au-Prince                     | French           |
| Hungary                          | Budapest                           | Hungarian        |
| Indonesia                        | Jakarta                            | Malay            |
| India                            | New Delhi                          | Hindi            |
| Ireland                          | Dublin                             | English          |
| Ireland                          | Dublin                             | Irish            |
| Iran                             | Teheran                            | Persian          |
| Iraq                             | Baghdad                            | Arabic           |
| Iceland                          | Reykjavík                          | Icelandic        |
| Israel                           | Jerusalem                          | Arabic           |
| Israel                           | Jerusalem                          | Hebrew           |
| Italy                            | Roma                               | Italian          |
| Jordan                           | Amman                              | Arabic           |
| Japan                            | Tokyo                              | Japanese         |
| Kazakstan                        | Astana                             | Kazakh           |
| Kyrgyzstan                       | Bishkek                            | Kirgiz           |
| Kyrgyzstan                       | Bishkek                            | Russian          |
| Cambodia                         | Phnom Penh                         | Khmer            |
| Kiribati                         | Bairiki                            | Kiribati         |
| Saint Kitts and Nevis            | Basseterre                         | English          |
| South Korea                      | Seoul                              | Korean           |
| Kuwait                           | Kuwait                             | Arabic           |
| Laos                             | Vientiane                          | Lao              |
| Lebanon                          | Beirut                             | Arabic           |
| Libyan Arab Jamahiriya           | Tripoli                            | Arabic           |
| Saint Lucia                      | Castries                           | English          |
| Liechtenstein                    | Vaduz                              | German           |
| Sri Lanka                        | Colombo                            | Singali          |
| Sri Lanka                        | Colombo                            | Tamil            |
| Lesotho                          | Maseru                             | English          |
| Lesotho                          | Maseru                             | Sotho            |
| Lithuania                        | Vilnius                            | Lithuanian       |
| Luxembourg                       | Luxembourg [Luxemburg/Lëtzebuerg]  | French           |
| Luxembourg                       | Luxembourg [Luxemburg/Lëtzebuerg]  | German           |
| Luxembourg                       | Luxembourg [Luxemburg/Lëtzebuerg]  | Luxembourgish    |
| Latvia                           | Riga                               | Latvian          |
| Macao                            | Macao                              | Portuguese       |
| Morocco                          | Rabat                              | Arabic           |
| Monaco                           | Monaco-Ville                       | French           |
| Moldova                          | Chisinau                           | Romanian         |
| Madagascar                       | Antananarivo                       | French           |
| Madagascar                       | Antananarivo                       | Malagasy         |
| Maldives                         | Male                               | Dhivehi          |
| Mexico                           | Ciudad de México                   | Spanish          |
| Marshall Islands                 | Dalap-Uliga-Darrit                 | English          |
| Marshall Islands                 | Dalap-Uliga-Darrit                 | Marshallese      |
| Macedonia                        | Skopje                             | Macedonian       |
| Malta                            | Valletta                           | English          |
| Malta                            | Valletta                           | Maltese          |
| Myanmar                          | Rangoon (Yangon)                   | Burmese          |
| Mongolia                         | Ulan Bator                         | Mongolian        |
| Northern Mariana Islands         | Garapan                            | English          |
| Montserrat                       | Plymouth                           | English          |
| Martinique                       | Fort-de-France                     | French           |
| Malawi                           | Lilongwe                           | Chichewa         |
| Malaysia                         | Kuala Lumpur                       | Malay            |
| Mayotte                          | Mamoutzou                          | French           |
| New Caledonia                    | Nouméa                             | French           |
| Norfolk Island                   | Kingston                           | English          |
| Nicaragua                        | Managua                            | Spanish          |
| Niue                             | Alofi                              | English          |
| Netherlands                      | Amsterdam                          | Dutch            |
| Norway                           | Oslo                               | Norwegian        |
| Nepal                            | Kathmandu                          | Nepali           |
| Nauru                            | Yaren                              | English          |
| Nauru                            | Yaren                              | Nauru            |
| New Zealand                      | Wellington                         | English          |
| Oman                             | Masqat                             | Arabic           |
| Pakistan                         | Islamabad                          | Urdu             |
| Panama                           | Ciudad de Panamá                   | Spanish          |
| Peru                             | Lima                               | Aimará           |
| Peru                             | Lima                               | Ketšua           |
| Peru                             | Lima                               | Spanish          |
| Philippines                      | Manila                             | Pilipino         |
| Palau                            | Koror                              | English          |
| Palau                            | Koror                              | Palau            |
| Poland                           | Warszawa                           | Polish           |
| Puerto Rico                      | San Juan                           | Spanish          |
| North Korea                      | Pyongyang                          | Korean           |
| Portugal                         | Lisboa                             | Portuguese       |
| Paraguay                         | Asunción                           | Guaraní          |
| Paraguay                         | Asunción                           | Spanish          |
| French Polynesia                 | Papeete                            | French           |
| Qatar                            | Doha                               | Arabic           |
| Romania                          | Bucuresti                          | Romani           |
| Romania                          | Bucuresti                          | Romanian         |
| Russian Federation               | Moscow                             | Russian          |
| Rwanda                           | Kigali                             | French           |
| Rwanda                           | Kigali                             | Rwanda           |
| Saudi Arabia                     | Riyadh                             | Arabic           |
| Sudan                            | Khartum                            | Arabic           |
| Senegal                          | Dakar                              | Wolof            |
| Singapore                        | Singapore                          | Chinese          |
| Singapore                        | Singapore                          | Malay            |
| Singapore                        | Singapore                          | Tamil            |
| Saint Helena                     | Jamestown                          | English          |
| Svalbard and Jan Mayen           | Longyearbyen                       | Norwegian        |
| El Salvador                      | San Salvador                       | Spanish          |
| San Marino                       | San Marino                         | Italian          |
| Somalia                          | Mogadishu                          | Arabic           |
| Somalia                          | Mogadishu                          | Somali           |
| Saint Pierre and Miquelon        | Saint-Pierre                       | French           |
| Slovakia                         | Bratislava                         | Slovak           |
| Slovenia                         | Ljubljana                          | Slovene          |
| Sweden                           | Stockholm                          | Swedish          |
| Swaziland                        | Mbabane                            | Swazi            |
| Seychelles                       | Victoria                           | English          |
| Seychelles                       | Victoria                           | French           |
| Syria                            | Damascus                           | Arabic           |
| Turks and Caicos Islands         | Cockburn Town                      | English          |
| Chad                             | N´Djaména                          | Arabic           |
| Togo                             | Lomé                               | Ewe              |
| Togo                             | Lomé                               | Kabyé            |
| Thailand                         | Bangkok                            | Thai             |
| Tajikistan                       | Dushanbe                           | Tadzhik          |
| Tokelau                          | Fakaofo                            | English          |
| Turkmenistan                     | Ashgabat                           | Turkmenian       |
| East Timor                       | Dili                               | Portuguese       |
| Tonga                            | Nuku´alofa                         | English          |
| Tonga                            | Nuku´alofa                         | Tongan           |
| Tunisia                          | Tunis                              | Arabic           |
| Turkey                           | Ankara                             | Turkish          |
| Tuvalu                           | Funafuti                           | English          |
| Tuvalu                           | Funafuti                           | Tuvalu           |
| Taiwan                           | Taipei                             | Mandarin Chinese |
| Tanzania                         | Dodoma                             | Swahili          |
| Ukraine                          | Kyiv                               | Ukrainian        |
| Uruguay                          | Montevideo                         | Spanish          |
| United States                    | Washington                         | English          |
| Uzbekistan                       | Toskent                            | Uzbek            |
| Holy See (Vatican City State)    | Città del Vaticano                 | Italian          |
| Saint Vincent and the Grenadines | Kingstown                          | English          |
| Venezuela                        | Caracas                            | Spanish          |
| Virgin Islands, British          | Road Town                          | English          |
| Virgin Islands, U.S.             | Charlotte Amalie                   | English          |
| Vietnam                          | Hanoi                              | Vietnamese       |
| Vanuatu                          | Port-Vila                          | Bislama          |
| Vanuatu                          | Port-Vila                          | English          |
| Vanuatu                          | Port-Vila                          | French           |
| Samoa                            | Apia                               | English          |
| Samoa                            | Apia                               | Samoan           |
| Yemen                            | Sanaa                              | Arabic           |
| Yugoslavia                       | Beograd                            | Serbo-Croatian   |
| South Africa                     | Pretoria                           | Afrikaans        |
| South Africa                     | Pretoria                           | English          |
| South Africa                     | Pretoria                           | Xhosa            |
| South Africa                     | Pretoria                           | Zulu             |
| Zimbabwe                         | Harare                             | English          |
+----------------------------------+------------------------------------+------------------+
237 rows in set (0.01 sec)

 
MariaDB [world]> 

3. Quantes ciutats hi ha a spain?

MariaDB [world]> SELECT count(ID) as Espanyoles FROM City WHERE CountryCode='ESP';

+------------+
| Espanyoles |
+------------+
|         59 |
+------------+
1 row in set (0.00 sec)

 
MariaDB [world]>