UF2. Llenguatges SQL : DML i DDL¶
A01. Introducció al DML¶
1.1. Consultes simples¶
1.1.1. Exercicis¶
Base de dades ``peces``
Nom de les peces que es troben a Granollers
select pnom from p where ciutat="Granollers"; +---------+ | pnom | +---------+ | peca 1 | | peca 10 | | peca 11 | | peca 14 | | peca 15 | | peca 18 | | peca 7 | | peca 8 | | peca 9 | +---------+ 9 rows in set (0.00 sec)
Mostrar totes les dades dels enviaments amb quantitat inferior a 200 i superior a 100
select * from sp where cant<200 and cant>100; +----+-----+------+ | s | p | cant | +----+-----+------+ | s1 | p7 | 130 | | s2 | p9 | 130 | | s4 | p14 | 130 | | s5 | p14 | 130 | | s6 | p14 | 130 | | s7 | p14 | 130 | +----+-----+------+ 6 rows in set (0.00 sec)
Mostrar el nom dels proveïdors que no són de la ciutat de Granollers
select snom from s where ciutat!="Granollers"; +--------------+ | snom | +--------------+ | proveidor 10 | | proveidor 11 | | proveidor 12 | | proveidor 3 | | proveidor 6 | | proveidor 7 | | proveidor 8 | | proveidor 9 | +--------------+ 8 rows in set (0.00 sec)
Base de dades ``biblioteca``
Mostrar el nom dels proveïdors que no són de la ciutat de Granollers
select * from socis order by nom; +--------+------+------------+------------+-------+ | numsoc | dni | nom | adreca | cp | +--------+------+------------+------------+-------+ | 5 | 5 | el 5 | camp | 08227 | | 6 | 6 | el 6 | de les | 08200 | | 7 | 7 | el 7 | moreres | 08400 | | 8 | 8 | el 8 | granollers | 08400 | | 4 | 4 | el cinqué | aquella | 08000 | | 9 | 9 | el nou | barna | 08227 | | 1 | 1 | El primer | esta | 08400 | | 2 | 2 | el segon | esta | 08400 | | 3 | 3 | el tercer | la otra | 08400 | | 10 | 10 | l'últim | catalunya | 08000 | +--------+------+------------+------------+-------+ 10 rows in set (0.00 sec)
1.1.2. Pràctica¶
Nom de les poblacions d’espanya amb mes d’un milió d’habitants
select Name from City WHERE CountryCode = 'ESP' and Population>1000000; +-----------+ | Name | +-----------+ | Madrid | | Barcelona | +-----------+ 2 rows in set (0.00 sec)
Nom dels paisos d’Europe amb mes 30 milions d’habitants
select Name from Country where Continent = 'Europe' and Population>30000000; +--------------------+ | Name | +--------------------+ | Germany | | Spain | | France | | United Kingdom | | Italy | | Poland | | Russian Federation | | Ukraine | +--------------------+ 8 rows in set (0.00 sec)
Les 10 ciutats més poblades del món
select Name from Country order by Population Desc limit 10; +--------------------+ | Name | +--------------------+ | China | | India | | United States | | Indonesia | | Brazil | | Pakistan | | Russian Federation | | Bangladesh | | Japan | | Nigeria | +--------------------+ 10 rows in set (0.00 sec)
1.2. Consultes amb funcions d’agregat¶
1.2.1. Exercicis¶
N/A
1.2.2. Pràctica¶
En quants països es parla spanish?
select count(*) as Castellanoparlants from CountryLanguage where Language='Spanish'; +--------------------+ | Castellanoparlants | +--------------------+ | 28 | +--------------------+ 1 row in set (0.00 sec)
Quantitat de ciutats del districte Andalusia
select count(*) as Ciutats_andaluses from City where district='Andalusia'; +-------------------+ | Ciutats_andaluses | +-------------------+ | 12 | +-------------------+ 1 row in set (0.00 sec)
A02. Consultes amb múltiples taules¶
2.1. Consultes amb múltiples taules¶
2.1.1. Exercicis¶
N/A
2.1.2. Pràctica¶
Nom dels països de parla catalana
MariaDB [world]> select Name from Country,CountryLanguage where Country.Code=CountryLanguage.CountryCode and CountryLanguage.Language="Catalan"; +---------+ | Name | +---------+ | Andorra | | Spain | +---------+ 2 rows in set (0.00 sec)
Nom dels països amb 10 o més llengües
N/A
Mostra el nom del país on el percentatge d’ús d’anglès és més elevat
MariaDB [world]> select Name from Country,CountryLanguage where Country.Code=CountryLanguage.CountryCode and CountryLanguage.Language="English" order by CountryLanguage.Percentage desc limit 1; +---------+ | Name | +---------+ | Bermuda | +---------+ 1 row in set (0.00 sec)
2.2. Consultes amb subconsultes¶
2.2.1. Exercicis¶
N/A
2.2.2. Pràctica¶
Continent amb menys població
MariaDB [world]> select Continent from Country group by Continent order by Population asc limit 1; +------------+ | Continent | +------------+ | Antarctica | +------------+ 1 row in set (0.00 sec)
Llengua menys parlada
MariaDB [world]> select Language from CountryLanguage group by Language order by Percentage asc Limit 1; +----------+ | Language | +----------+ | Soqutri | +----------+ 1 row in set (0.00 sec)
A03. Inserció, modificació i eliminació de dades¶
3.1. Inserció de dades com a resultat d’un select¶
3.1.1. Exercicis¶
N/A
3.1.2. Pràctica¶
Crear una taula, per obtenir l’idioma (oficial) que a més països es parla, per continent
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]> 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]> 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)
Per conèixer les dades dels països que no son una República
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 |
Llengua menys parlada
MariaDB [world]> select Language from CountryLanguage group by Language order by Percentage asc Limit 1; +----------+ | Language | +----------+ | Soqutri | +----------+ 1 row in set (0.00 sec)
Crea una taula amb les dades del país on el percentatge d’ús de l’idioma Spanish és més elevat
MariaDB [world]>create table intermitja2 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]> 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]> 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)
3.2. Inserció, modificació i eliminació de dades¶
3.2.1. Exercicis¶
N/A
3.2.2. Pràctica¶
Inserir 2 ciutats a City
MariaDB [world]> insert into City (Name,CountryCode,District,Population) Values ("Guillemlandia", "AND", "Boeck", 1996), ("No man's Land", "AND", "COD", 25000); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0
Modificar els registres de EuropeCityMitja, posant a tots la mitja de la població de les ciutats
MariaDB [world]> update EuropeCityMitja set mitja=(select avg(population) from City); Query OK, 0 rows affected (0.09 sec) Rows matched: 46 Changed: 0 Warnings: 0
Afegeix un idioma que es parli en un 50% a Andorra i que no sigui oficial
MariaDB [world]> insert into CountryLanguage (CountryCode, Language, IsOfficial,Percentage) values ('AND', 'Binari', 'F','50'); Query OK, 1 row affected (0.06 sec)
A04. Ampliació del DML¶
4.1. Consultes amb unió, intersecció i diferència de consultes¶
4.1.1. Exercicis¶
Base de dades ``peces``
Ciutats dels proveïdors i de les peces
SELECT ciutat FROM s UNION SELECT ciutat FROM p; +------------+ | ciutat | +------------+ | granollers | | terrassa | | barcelona | | granada | +------------+ 4 rows in set (0.00 sec)
Codi de les peces que es troben a Granada i que s’han enviat
select p from p where ciutat="GRANADA" and p in (select p from sp); +-----+ | p | +-----+ | p16 | | p17 | +-----+ 2 rows in set (0.03 sec)
Base de dades ``ies``
Codi dels crèdits de DAI que es fan a ASI
select codi_credit from credits where codi_pla="dai" and codi_credit in (select codi_credit from credits where codi_pla="asi"); +-------------+ | codi_credit | +-------------+ | c1 | | c2 | | c3 | | c4 | | c5 | | c6 | | c7 | | c8 | +-------------+ 8 rows in set (0.03 sec)
Codi dels crèdits de DAI que no es fan a ASI
select codi_credit from credits where codi_pla="dai" and codi_credit not in (select codi_credit from credits where codi_pla="asi"); +-------------+ | codi_credit | +-------------+ | c9 | +-------------+ 1 row in set (0.00 sec)
Codi dels crèdits de DAI i els d’ASI
select codi_credit from credits where codi_pla="dai" union select codi_credit from credits where codi_pla="asi"; +-------------+ | codi_credit | +-------------+ | c1 | | c2 | | c3 | | c4 | | c5 | | c6 | | c7 | | c8 | | c9 | +-------------+ 9 rows in set (0.00 sec)
Base de dades ``ciclisme``
Dorsal dels ciclistes guanyadors d’etapes que han guanyat algun port
select distinct dorsal from etapa where dorsal in (select dorsal from port); +--------+ | dorsal | +--------+ | 1 | | 2 | | 4 | +--------+ 3 rows in set (0.04 sec)
Dorsal dels ciclistes guanyadors d’etapes, més els guanyadors d’algun port
select distinct dorsal from etapa union select dorsal from port; +--------+ | dorsal | +--------+ | 1 | | 2 | | 4 | | 5 | | 8 | | 10 | | 12 | | 22 | | 27 | | 35 | | 36 | | 52 | | 65 | | 83 | | 86 | | 93 | | 7 | | 9 | | 20 | | 24 | | 25 | | 26 | | 30 | | 42 | +--------+ 24 rows in set (0.00 sec)
Dorsal dels ciclistes guanyadors d’etapes que no han guanyat cap port
select distinct dorsal from etapa where dorsal not in( select dorsal from port); +--------+ | dorsal | +--------+ | 5 | | 8 | | 10 | | 12 | | 22 | | 27 | | 35 | | 36 | | 52 | | 65 | | 83 | | 86 | | 93 | +--------+ 13 rows in set (0.00 sec)
Dorsal dels ciclistes guanyadors de ports que no han guanyat cap etapa
select distinct dorsal from port where dorsal not in( select dorsal from etapa); +--------+ | dorsal | +--------+ | 7 | | 9 | | 20 | | 24 | | 25 | | 26 | | 30 | | 42 | +--------+ 8 rows in set (0.00 sec)
Dorsal dels ciclistes guanyadors de ports que han guanyat alguna etapa
select distinct dorsal from port where dorsal in (select dorsal from etapa); +--------+ | dorsal | +--------+ | 1 | | 2 | | 4 | +--------+ 3 rows in set (0.00 sec)
4.1.2. Pràctica¶
Ciutats d’Espanya i ciutats de Sud Amèrica
select City.Name from City, Country where City.CountryCode=Country.Code and Country.Code="ESP" union select City.Name from City, Country where City.CountryCode=Country.Code and Country.Continent="South America"; +---------------------------------+ | Name | +---------------------------------+ | Madrid | | Barcelona | | Valencia | | Sevilla | | Zaragoza | | Málaga | | Bilbao | | Las Palmas de Gran Canaria | | Murcia | | Palma de Mallorca | | Valladolid | | Córdoba | | Vigo | | Alicante [Alacant] | | Gijón | | L´Hospitalet de Llobregat | | Granada | | A Coruña (La Coruña) | | Vitoria-Gasteiz | | Santa Cruz de Tenerife | | Badalona | | Oviedo | | Móstoles | | Elche [Elx] | | Sabadell | | Santander | | Jerez de la Frontera | | Pamplona [Iruña] | | Donostia-San Sebastián | | Cartagena | | Leganés | | Fuenlabrada | | Almería | | Terrassa | | Alcalá de Henares | | Burgos | | Salamanca | | Albacete | | Getafe | | Cádiz | | Alcorcón | | Huelva | | León | | Castellón de la Plana [Castell | | Badajoz | | [San Cristóbal de] la Laguna | | Logroño | | Santa Coloma de Gramenet | | Tarragona | | Lleida (Lérida) | | Jaén | | Ourense (Orense) | | Mataró | | Algeciras | | Marbella | | Barakaldo | | Dos Hermanas | | Santiago de Compostela | | Torrejón de Ardoz | | Buenos Aires | | La Matanza | | Rosario | | Lomas de Zamora | | Quilmes | | Almirante Brown | | La Plata | | Mar del Plata | | San Miguel de Tucumán | | Lanús | | Merlo | | General San Martín | | Salta | | Moreno | | Santa Fé | | Avellaneda | | Tres de Febrero | | Morón | | Florencio Varela | | San Isidro | | Tigre | | Malvinas Argentinas | | Vicente López | | Berazategui | | Corrientes | | San Miguel | | Bahía Blanca | | Esteban Echeverría | | Resistencia | | José C. Paz | | Paraná | | Godoy Cruz | | Posadas | | Guaymallén | | Santiago del Estero | | San Salvador de Jujuy | | Hurlingham | | Neuquén | | Ituzaingó | | San Fernando | | Formosa | | Las Heras | | La Rioja | | San Fernando del Valle de Cata | | Río Cuarto | | Comodoro Rivadavia | | Mendoza | | San Nicolás de los Arroyos | | San Juan | | Escobar | | Concordia | | Pilar | | San Luis | | Ezeiza | | San Rafael | | Tandil | | Santa Cruz de la Sierra | | La Paz | | El Alto | | Cochabamba | | Oruro | | Sucre | | Potosí | | Tarija | | São Paulo | | Rio de Janeiro | | Salvador | | Belo Horizonte | | Fortaleza | | Brasília | | Curitiba | | Recife | | Porto Alegre | | Manaus | | Belém | | Guarulhos | | Goiânia | | Campinas | | São Gonçalo | | Nova Iguaçu | | São Luís | | Maceió | | Duque de Caxias | | São Bernardo do Campo | | Teresina | | Natal | | Osasco | | Campo Grande | | Santo André | | João Pessoa | | Jaboatão dos Guararapes | | Contagem | | São José dos Campos | | Uberlândia | | Feira de Santana | | Ribeirão Preto | | Sorocaba | | Niterói | | Cuiabá | | Juiz de Fora | | Aracaju | | São João de Meriti | | Londrina | | Joinville | | Belford Roxo | | Santos | | Ananindeua | | Campos dos Goytacazes | | Mauá | | Carapicuíba | | Olinda | | Campina Grande | | São José do Rio Preto | | Caxias do Sul | | Moji das Cruzes | | Diadema | | Aparecida de Goiânia | | Piracicaba | | Cariacica | | Vila Velha | | Pelotas | | Bauru | | Porto Velho | | Serra | | Betim | | Jundíaí | | Canoas | | Franca | | São Vicente | | Maringá | | Montes Claros | | Anápolis | | Florianópolis | | Petrópolis | | Itaquaquecetuba | | Vitória | | Ponta Grossa | | Rio Branco | | Foz do Iguaçu | | Macapá | | Ilhéus | | Vitória da Conquista | | Uberaba | | Paulista | | Limeira | | Blumenau | | Caruaru | | Santarém | | Volta Redonda | | Novo Hamburgo | | Caucaia | | Santa Maria | | Cascavel | | Guarujá | | Ribeirão das Neves | | Governador Valadares | | Taubaté | | Imperatriz | | Gravataí | | Embu | | Mossoró | | Várzea Grande | | Petrolina | | Barueri | | Viamão | | Ipatinga | | Juazeiro | | Juazeiro do Norte | | Taboão da Serra | | São José dos Pinhais | | Magé | | Suzano | | São Leopoldo | | Marília | | São Carlos | | Sumaré | | Presidente Prudente | | Divinópolis | | Sete Lagoas | | Rio Grande | | Itabuna | | Jequié | | Arapiraca | | Colombo | | Americana | | Alvorada | | Araraquara | | Itaboraí | | Santa Bárbara d´Oeste | | Nova Friburgo | | Jacareí | | Araçatuba | | Barra Mansa | | Praia Grande | | Marabá | | Criciúma | | Boa Vista | | Passo Fundo | | Dourados | | Santa Luzia | | Rio Claro | | Maracanaú | | Guarapuava | | Rondonópolis | | São José | | Cachoeiro de Itapemirim | | Nilópolis | | Itapevi | | Cabo de Santo Agostinho | | Camaçari | | Sobral | | Itajaí | | Chapecó | | Cotia | | Lages | | Ferraz de Vasconcelos | | Indaiatuba | | Hortolândia | | Caxias | | São Caetano do Sul | | Itu | | Nossa Senhora do Socorro | | Parnaíba | | Poços de Caldas | | Teresópolis | | Barreiras | | Castanhal | | Alagoinhas | | Itapecerica da Serra | | Uruguaiana | | Paranaguá | | Ibirité | | Timon | | Luziânia | | Macaé | | Teófilo Otoni | | Moji-Guaçu | | Palmas | | Pindamonhangaba | | Francisco Morato | | Bagé | | Sapucaia do Sul | | Cabo Frio | | Itapetininga | | Patos de Minas | | Camaragibe | | Bragança Paulista | | Queimados | | Araguaína | | Garanhuns | | Vitória de Santo Antão | | Santa Rita | | Barbacena | | Abaetetuba | | Jaú | | Lauro de Freitas | | Franco da Rocha | | Teixeira de Freitas | | Varginha | | Ribeirão Pires | | Sabará | | Catanduva | | Rio Verde | | Botucatu | | Colatina | | Santa Cruz do Sul | | Linhares | | Apucarana | | Barretos | | Guaratinguetá | | Cachoeirinha | | Codó | | Jaraguá do Sul | | Cubatão | | Itabira | | Itaituba | | Araras | | Resende | | Atibaia | | Pouso Alegre | | Toledo | | Crato | | Passos | | Araguari | | São José de Ribamar | | Pinhais | | Sertãozinho | | Conselheiro Lafaiete | | Paulo Afonso | | Angra dos Reis | | Eunápolis | | Salto | | Ourinhos | | Parnamirim | | Jacobina | | Coronel Fabriciano | | Birigui | | Tatuí | | Ji-Paraná | | Bacabal | | Cametá | | Guaíba | | São Lourenço da Mata | | Santana do Livramento | | Votorantim | | Campo Largo | | Patos | | Ituiutaba | | Corumbá | | Palhoça | | Barra do Piraí | | Bento Gonçalves | | Poá | | Águas Lindas de Goiás | | Santiago de Chile | | Puente Alto | | Viña del Mar | | Valparaíso | | Talcahuano | | Antofagasta | | San Bernardo | | Temuco | | Concepción | | Rancagua | | Arica | | Talca | | Chillán | | Iquique | | Los Angeles | | Puerto Montt | | Coquimbo | | Osorno | | La Serena | | Calama | | Valdivia | | Punta Arenas | | Copiapó | | Quilpué | | Curicó | | Ovalle | | Coronel | | San Pedro de la Paz | | Melipilla | | Santafé de Bogotá | | Cali | | Medellín | | Barranquilla | | Cúcuta | | Bucaramanga | | Ibagué | | Pereira | | Santa Marta | | Manizales | | Bello | | Pasto | | Neiva | | Soledad | | Armenia | | Villavicencio | | Soacha | | Valledupar | | Montería | | Itagüí | | Palmira | | Buenaventura | | Floridablanca | | Sincelejo | | Popayán | | Barrancabermeja | | Dos Quebradas | | Tuluá | | Envigado | | Cartago | | Girardot | | Buga | | Tunja | | Florencia | | Maicao | | Sogamoso | | Giron | | Guayaquil | | Quito | | Cuenca | | Machala | | Santo Domingo de los Colorados | | Portoviejo | | Ambato | | Manta | | Duran [Eloy Alfaro] | | Ibarra | | Quevedo | | Milagro | | Loja | | Ríobamba | | Esmeraldas | | Stanley | | Cayenne | | Georgetown | | Lima | | Arequipa | | Trujillo | | Chiclayo | | Callao | | Iquitos | | Chimbote | | Huancayo | | Piura | | Cusco | | Pucallpa | | Tacna | | Ica | | Sullana | | Juliaca | | Huánuco | | Ayacucho | | Chincha Alta | | Cajamarca | | Puno | | Ventanilla | | Castilla | | Asunción | | Ciudad del Este | | San Lorenzo | | Lambaré | | Fernando de la Mora | | Paramaribo | | Montevideo | | Caracas | | Maracaíbo | | Barquisimeto | | Ciudad Guayana | | Petare | | Maracay | | Maturín | | San Cristóbal | | Ciudad Bolívar | | Cumaná | | Mérida | | Cabimas | | Barinas | | Turmero | | Baruta | | Puerto Cabello | | Santa Ana de Coro | | Los Teques | | Punto Fijo | | Guarenas | | Acarigua | | Puerto La Cruz | | Ciudad Losada | | Guacara | | Valera | | Guanare | | Carúpano | | Catia La Mar | | El Tigre | | Guatire | | Calabozo | | Pozuelos | | Ciudad Ojeda | | Ocumare del Tuy | | Valle de la Pascua | | Araure | | San Fernando de Apure | | San Felipe | | El Limón | +---------------------------------+ 525 rows in set (0.02 sec)
De les ciutats de Sud Amèrica, dir quines es troben a Espanya
select City.Name from City, Country where City.Name in (select City.Name from City, Country where City.CountryCode = Country.Code and City.CountryCode="ESP") and City.CountryCode = Country.Code and Country.Continent="South America"; +-----------+ | Name | +-----------+ | Córdoba | | Cartagena | | Valencia | | Barcelona | +-----------+ 4 rows in set (0.04 sec)
De les ciutats d’Espanya, dir quines no es troben sun Sud Amèrica
select City.Name from City, Country where City.Name not in (select City.Name from City, Country where City.CountryCode = Country.Code and Country.Continent="South America") and City.CountryCode = Country.Code and City.CountryCode="ESP"; +---------------------------------+ | Name | +---------------------------------+ | Madrid | | Sevilla | | Zaragoza | | Málaga | | Bilbao | | Las Palmas de Gran Canaria | | Murcia | | Palma de Mallorca | | Valladolid | | Vigo | | Alicante [Alacant] | | Gijón | | L´Hospitalet de Llobregat | | Granada | | A Coruña (La Coruña) | | Vitoria-Gasteiz | | Santa Cruz de Tenerife | | Badalona | | Oviedo | | Móstoles | | Elche [Elx] | | Sabadell | | Santander | | Jerez de la Frontera | | Pamplona [Iruña] | | Donostia-San Sebastián | | Leganés | | Fuenlabrada | | Almería | | Terrassa | | Alcalá de Henares | | Burgos | | Salamanca | | Albacete | | Getafe | | Cádiz | | Alcorcón | | Huelva | | León | | Castellón de la Plana [Castell | | Badajoz | | [San Cristóbal de] la Laguna | | Logroño | | Santa Coloma de Gramenet | | Tarragona | | Lleida (Lérida) | | Jaén | | Ourense (Orense) | | Mataró | | Algeciras | | Marbella | | Barakaldo | | Dos Hermanas | | Santiago de Compostela | | Torrejón de Ardoz | +---------------------------------+ 55 rows in set (0.01 sec)
Dels llenguatges que es parlen a Europa, dir quins es parlen a Nord Amèrica
select distinct Language from CountryLanguage cl, Country where cl.Language in (select Language from CountryLanguage, Country where CountryLanguage.CountryCode = Country.Code and Country.Continent="North America") and cl.CountryCode = Country.Code and Country.Continent="Europe"; +------------+ | Language | +------------+ | French | | Portuguese | | Spanish | | German | | Polish | | Arabic | | Dutch | | Italian | | Ukrainian | | Danish | | English | +------------+ 11 rows in set (0.00 sec)
A05. Optimització de consultes¶
5.1. Optimització de consultes¶
5.1.1. Exercicis¶
N/A
5.1.2. Pràctica¶
Nom de la capital del país amb més població
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)
Nom dels països amb el nom de la seva capital i llengua oficial
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)
Quantes ciutats hi ha a spain?
SELECT count(ID) as Espanyoles FROM City WHERE CountryCode='ESP'; +------------+ | Espanyoles | +------------+ | 59 | +------------+ 1 row in set (0.00 sec)
A06. Transaccions i gestió de la concurrència¶
6.1. Transaccions i gestió de la concurrència¶
6.1.1. Exercicis¶
Transaccions
Insertar un registre a la taula ``sp`` de ``peces``, (el valors dels camps ``s`` i ``p`` han d’estar a les seves taules)
Insertar un registre a la taula ``sp`` de ``peces``, (el valors dels camp ``s`` i ``p`` NO han d’estar a les seves taules)
Esborrar un registre de la taula ``sp`` i sortir abans de fer el commit
Esborrar un registre de la taula ``sp`` i fer un rollback
Bloquejos
N/A
6.1.2. Pràctica¶
Transaccions
Esborrar un registre de la taula ``city`` i fer un rollback
Bloquejos
1. Bloquejar només la modificació de la ciutat “Barcelona” de la taula ``city``
A07. Introducció al DDL¶
7.1. Eina gràfica per al SGBD¶
7.1.1. Exercicis¶
N/A
7.1.2. Pràctica¶
Pràctica eina gràfica SGBD
-- phpMyAdmin SQL Dump
-- version 4.7.6
-- https://www.phpmyadmin.net/
--
-- Servidor: localhost
-- Temps de generació: 18-12-2017 a les 16:32:16
-- Versió del servidor: 10.1.29-MariaDB
-- Versió de PHP: 7.1.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Base de dades: `PtBD`
--
-- --------------------------------------------------------
--
-- Estructura de la taula `botiga`
--
CREATE TABLE `botiga` (
`codi` varchar(5) NOT NULL,
`nom` varchar(50) NOT NULL,
`adreca` varchar(75) NOT NULL,
`telefon` varchar(9) NOT NULL,
`email` varchar(70) NOT NULL,
`cp` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Estructura de la taula `camio`
--
CREATE TABLE `camio` (
`codi` varchar(5) NOT NULL,
`pes` int(11) NOT NULL,
`capacitat` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Estructura de la taula `CP`
--
CREATE TABLE `CP` (
`cp` varchar(5) NOT NULL,
`poblacio` varchar(40) NOT NULL,
`provincia` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Estructura de la taula `enviament`
--
CREATE TABLE `enviament` (
`codi` varchar(5) NOT NULL,
`pes` int(11) NOT NULL,
`volum` int(11) NOT NULL,
`codiMagatzem` varchar(5) NOT NULL,
`codiCamio` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Estructura de la taula `enviamentViatge`
--
CREATE TABLE `enviamentViatge` (
`codiEnv` varchar(5) NOT NULL,
`codiVia` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Estructura de la taula `Magatzem`
--
CREATE TABLE `Magatzem` (
`codi` varchar(5) NOT NULL,
`nom` varchar(50) NOT NULL,
`adreca` varchar(50) NOT NULL,
`telefon` varchar(9) NOT NULL,
`email` varchar(75) NOT NULL,
`cp` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Estructura de la taula `viatge`
--
CREATE TABLE `viatge` (
`codi` varchar(5) NOT NULL,
`nom` varchar(50) NOT NULL,
`data` date NOT NULL,
`codiCamio` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexos per taules bolcades
--
--
-- Index de la taula `botiga`
--
ALTER TABLE `botiga`
ADD PRIMARY KEY (`codi`),
ADD KEY `FK_CP` (`cp`);
--
-- Index de la taula `camio`
--
ALTER TABLE `camio`
ADD PRIMARY KEY (`codi`);
--
-- Index de la taula `CP`
--
ALTER TABLE `CP`
ADD PRIMARY KEY (`cp`);
--
-- Index de la taula `enviament`
--
ALTER TABLE `enviament`
ADD PRIMARY KEY (`codi`),
ADD KEY `FK_codiCamio` (`codiCamio`);
--
-- Index de la taula `enviamentViatge`
--
ALTER TABLE `enviamentViatge`
ADD PRIMARY KEY (`codiEnv`,`codiVia`),
ADD KEY `FK_viatge` (`codiVia`);
--
-- Index de la taula `Magatzem`
--
ALTER TABLE `Magatzem`
ADD PRIMARY KEY (`codi`),
ADD KEY `FK_CP-Magatzem` (`cp`);
--
-- Index de la taula `viatge`
--
ALTER TABLE `viatge`
ADD PRIMARY KEY (`codi`),
ADD KEY `FK_codiCamio-Viatge` (`codiCamio`);
--
-- Restriccions per taules bolcades
--
--
-- Restriccions per la taula `botiga`
--
ALTER TABLE `botiga`
ADD CONSTRAINT `FK_CP` FOREIGN KEY (`cp`) REFERENCES `CP` (`cp`) ON UPDATE CASCADE;
--
-- Restriccions per la taula `enviament`
--
ALTER TABLE `enviament`
ADD CONSTRAINT `FK_codiCamio` FOREIGN KEY (`codiCamio`) REFERENCES `camio` (`codi`) ON UPDATE CASCADE;
--
-- Restriccions per la taula `enviamentViatge`
--
ALTER TABLE `enviamentViatge`
ADD CONSTRAINT `FK_enviament` FOREIGN KEY (`codiEnv`) REFERENCES `enviament` (`codi`) ON UPDATE CASCADE,
ADD CONSTRAINT `FK_viatge` FOREIGN KEY (`codiVia`) REFERENCES `viatge` (`codi`) ON UPDATE CASCADE;
--
-- Restriccions per la taula `Magatzem`
--
ALTER TABLE `Magatzem`
ADD CONSTRAINT `FK_CP-Magatzem` FOREIGN KEY (`cp`) REFERENCES `CP` (`cp`) ON UPDATE CASCADE;
--
-- Restriccions per la taula `viatge`
--
ALTER TABLE `viatge`
ADD CONSTRAINT `FK_codiCamio-Viatge` FOREIGN KEY (`codiCamio`) REFERENCES `camio` (`codi`) ON UPDATE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
A08. Creació de bases de dades¶
8.1. Bases de dades¶
8.1.1. Exercicis¶
N/A
8.1.2. Pràctica¶
Crear una base de dades on es puguin emmagatzemar els fitxers pdf’s i tenir-los classificats. Aquesta base de dades s’anomenarà ``documents``
Creem la base de dades:
create database documents; Query OK, 1 row affected (0.00 sec)
Comprovem que s’hagi creat correctament:
show databases; +--------------------+ | Database | +--------------------+ | documents | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.10 sec)
8.2. Taules¶
8.2.1. Exercicis¶
N/A
8.2.2. Pràctica¶
Crear les taules ``Pdf``, ``Document``, ``Tema`` i ``TemaDoc``
Primer de tot, canviem la BBDD en ús per la que hem creat
MariaDB [(none)]> use documents; Database changed MariaDB [documents]>
Creem les taules
MariaDB [documents]> CREATE TABLE IF NOT EXISTS Pdf ( -> idPdf varchar(5) PRIMARY KEY, -> Nom varchar(25) UNIQUE); MariaDB [documents]> CREATE TABLE IF NOT EXISTS Tema ( -> idTema varchar(5) PRIMARY KEY, -> Descripcio varchar(25) UNIQUE, -> SubTema varchar(5)); MariaDB [documents]> CREATE TABLE IF NOT EXISTS Document ( -> idDoc varchar(5) PRIMARY KEY, -> Nom varchar(25) UNIQUE, -> Tipus varchar(15)); MariaDB [documents]> CREATE TABLE IF NOT EXISTS TemaDoc ( -> idTema varchar(5), -> idDoc varchar(5), -> PRIMARY KEY (idTema,idDoc) -> ); Query OK, 0 rows affected (0.28 sec)
Comprovem la creació
MariaDB [documents]> show tables; +---------------------+ | Tables_in_documents | +---------------------+ | Document | | Pdf | | Tema | | TemaDoc | +---------------------+ 4 rows in set (0.00 sec)
Modificar les taules afegint les FK [2]_s corresponents
MariaDB [documents]> ALTER TABLE TemaDoc ADD CONSTRAINT FOREIGN KEY (idTema) REFERENCES Tema (idTema) ON UPDATE CASCADE ON DELETE RESTRICT; Query OK, 0 rows affected (0.68 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [documents]> ALTER TABLE TemaDoc ADD CONSTRAINT FOREIGN KEY (idDoc) REFERENCES Document (idDoc) ON UPDATE CASCADE ON DELETE RESTRICT; Query OK, 0 rows affected (0.87 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [documents]> ALTER TABLE Tema ADD CONSTRAINT FOREIGN KEY (SubTema) REFERENCES Tema (idTema) ON UPDATE CASCADE ON DELETE RESTRICT; Query OK, 0 rows affected (0.95 sec) Records: 0 Duplicates: 0 Warnings: 0
Eliminar la taula ``Pdf``
MariaDB [documents]> DROP TABLE Pdf; Query OK, 0 rows affected (0.12 sec)
MariaDB [documents]> SHOW TABLES; +---------------------+ | Tables_in_documents | +---------------------+ | Document | | Tema | | TemaDoc | +---------------------+ 3 rows in set (0.00 sec)
Comprovar el resultat dels ``SHOW CREATE TABLE``
Taula
Document
MariaDB [documents]> show create table Document; +---------+-----------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------+ | Document | CREATE TABLE `Document` ( `idDoc` varchar(5) NOT NULL, `Nom` varchar(25) DEFAULT NULL, `Tipus` varchar(15) DEFAULT NULL, PRIMARY KEY (`idDoc`), UNIQUE KEY `Nom` (`Nom`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-----------------------------------------------------------------------+ 1 row in set (0.00 sec)
Taula
Tema
MariaDB [documents]> show create table Tema; +---------+-----------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------+ | Tema | CREATE TABLE `Tema` ( `idTema` varchar(5) NOT NULL, `Descripcio` varchar(25) DEFAULT NULL, `SubTema` varchar(5) DEFAULT NULL, PRIMARY KEY (`idTema`), UNIQUE KEY `Descripcio` (`Descripcio`), KEY `SubTema` (`SubTema`), CONSTRAINT `Tema_ibfk_1` FOREIGN KEY (`SubTema`) REFERENCES `Tema` (`idTema`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Taula
TemaDoc
MariaDB [documents]> show create table TemaDoc; +---------+-----------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------+ | TemaDoc | CREATE TABLE `TemaDoc` ( `idTema` varchar(5) NOT NULL, `idDoc` varchar(5) NOT NULL, PRIMARY KEY (`idTema`,`idDoc`), KEY `idDoc` (`idDoc`), CONSTRAINT `TemaDoc_ibfk_1` FOREIGN KEY (`idTema`) REFERENCES `Tema` (`idTema`), CONSTRAINT `TemaDoc_ibfk_2` FOREIGN KEY (`idDoc`) REFERENCES `Document` (`idDoc`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-----------------------------------------------------------------------+ 1 row in set (0.00 sec)
8.3. Índexs¶
8.3.1. Exercicis¶
N/A
8.3.2. Pràctica¶
Crear un índex a cada taula per un camp diferent a les PK [3]_s de la base de dades ``documents``
Creo un índex a la taula
Documentpel campTipus
MariaDB [documents]> CREATE INDEX IF NOT EXISTS IX_Tipus ON Document (Tipus); Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0
Creo un índex a la taula
Temapel campSubtema:
MariaDB [documents]> CREATE INDEX IF NOT EXISTS IX_SubTema ON Tema (SubTema); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0
Per a la taula
TemaDocno creo cap Index perquè els 2 camps que formen la taula formen la PK i, per tant, ja són índex.
Modificar els indexs dels camps nom per a que siguin tipus DESC
Com que no existeix la instrucció
ALTER INDEXhem d’eliminar-los i tornar-los a crear.Per a la taula
Document
MariaDB [documents]> ALTER TABLE Document DROP INDEX Nom; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
MariaDB [documents]> CREATE INDEX IF NOT EXISTS Nom ON Document (Nom DESC); Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0
Per a la taula
Tema
MariaDB [documents]> ALTER TABLE Tema DROP INDEX Descripcio; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0
MariaDB [documents]> CREATE INDEX IF NOT EXISTS Descripcio ON Tema (Descripcio DESC); Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0
Comprovem el resultat amb
SHOW INDEX
MariaDB [documents]> show index from Document; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Document | 0 | PRIMARY | 1 | idDoc | A | 0 | NULL | NULL | | BTREE | | | | Document | 1 | IX_Tipus | 1 | Tipus | A | 0 | NULL | NULL | YES | BTREE | | | | Document | 1 | Nom | 1 | Nom | A | 0 | NULL | NULL | YES | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
MariaDB [documents]> show index from Tema; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Tema | 0 | PRIMARY | 1 | idTema | A | 0 | NULL | NULL | | BTREE | | | | Tema | 1 | IX_SubTema | 1 | SubTema | A | 0 | NULL | NULL | YES | BTREE | | | | Tema | 1 | Descripcio | 1 | Descripcio | A | 0 | NULL | NULL | YES | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
A09. Extensions del llenguatge DDL¶
9.1. Vistes¶
9.1. Exercicis¶
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 OPTIONque hem definit al moment de crear la vista. Com que el registre nou no compleix la condició delWHEREde 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’
INSERTes 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
Creo la vista
vPecesGranollers
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 OPTIONperquè el camp ciutat inserit no és granollers.Introduiré un valor que compleixi el
CHECK OPTIONperò 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)
9.2. Pràctica¶
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 | +------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+ 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]> CREATE VIEW vPractica91 AS SELECT * FROM Country WHERE Continent="Europe" WITH CHECK OPTION; Query OK, 0 rows affected (0.02 sec)
Verifico que s’ha introduït
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 | +------+-------------------------------+-----------+------------------+-------------+-----------+------------+----------------+------------+------------+--------------------------------+-------------------------------------+--------------------------+---------+-------+ 46 rows 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 OR REPLACEperquè es modifiqui la vista creada anteriorment i també modifico elSELECTperquè correspongui amb l’enunciat
MariaDB [world]> CREATE OR REPLACE VIEW vPractica91 AS SELECT * FROM Country WHERE Continent="South America" WITH CHECK OPTION; 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 | +------+------------------+---------------+---------------+-------------+-----------+------------+----------------+-----------+-----------+-------------------+-------------------------------+------------------------------+---------+-------+ 14 rows in set (0.00 sec)
A10. Examen M02 UF2¶
Descripció dels articles en stock superior a més de 3 vegades el seu stock mínim i preu superior a 6€
MariaDB [examenuf2]> SELECT descrip as Descripció FROM articles WHERE preu>6 AND (stock>3*stock_min); +-----------------------------------+ | Descripció | +-----------------------------------+ | PROLONGADOR SENCILLO 10.0 M | | TUBO FLUORESCENTE 18W TLD | | CLAVIJA LEGRAND II POLOS 20A | | CRUZAMIENTO 1 M. LEGRAND SERIE MO | | BASE ENCHUFE NORMAL 2 M. LEGRAND | | CAJA SUPERFICIE VILAPLANA 100 | | CAJA SUPERFICIE VILAPLANA 80 | +-----------------------------------+ 7 rows in set (0.01 sec)
Codi, data i descompte de les factures sense IVA (``iva`` null o zero)
MariaDB [examenuf2]> SELECT codfac, data, dto FROM factures WHERE iva IS NULL OR iva=0; +--------+------------+------+ | codfac | data | dto | +--------+------------+------+ | 3 | 1998-10-03 | 20 | | 11 | 2000-09-28 | 50 | | 12 | 2001-07-21 | 10 | | 15 | 1999-09-21 | 10 | | 19 | 2000-02-18 | 0 | | 25 | 1998-02-08 | 0 | | 27 | 1998-10-06 | 10 | | 29 | 2001-02-05 | 10 | | 30 | 1999-12-19 | NULL | | 31 | 2000-01-25 | 25 | | 33 | 2000-12-02 | 50 | | 36 | 2001-03-02 | 0 | | 39 | 2001-08-11 | 50 | | 41 | 1998-10-14 | 0 | | 47 | 2000-06-07 | 20 | | 49 | 1999-10-04 | 0 | | 51 | 1998-04-01 | 25 | | 52 | 2002-08-11 | 20 | | 55 | 2000-01-29 | 25 | | 56 | 2001-07-13 | 10 | | 57 | 1999-05-04 | 10 | | 58 | 1998-10-02 | 10 | | 59 | 2001-04-09 | 50 | | 63 | 2002-10-17 | 10 | | 66 | 1998-02-23 | 50 | | 73 | 2001-04-28 | 10 | | 76 | 1998-03-26 | 0 | | 88 | 2000-08-16 | 25 | | 89 | 1998-08-12 | 50 | | 90 | 1999-03-19 | 10 | | 91 | 2002-07-20 | 50 | | 95 | 2000-04-09 | 0 | | 97 | 1998-08-20 | 25 | | 99 | 2000-12-19 | 10 | | 100 | 2002-08-31 | 20 | | 101 | 2002-06-30 | 20 | | 102 | 2002-02-25 | 10 | | 105 | 2001-01-11 | 25 | | 112 | 2002-10-02 | 20 | | 113 | 1999-07-12 | 10 | | 114 | 2002-10-09 | NULL | | 115 | 1999-04-21 | 20 | | 116 | 2000-09-09 | 20 | | 118 | 1999-04-29 | 10 | | 121 | 2000-02-25 | 10 | | 122 | 2001-04-11 | 50 | | 124 | 2002-02-15 | 50 | | 127 | 2001-11-22 | 25 | | 131 | 2001-09-25 | 20 | | 136 | 2000-01-15 | 20 | | 143 | 2000-09-19 | 10 | | 149 | 1998-02-02 | 0 | | 155 | 1998-10-17 | 0 | | 160 | 2001-08-10 | 20 | | 165 | 2000-11-20 | 20 | | 167 | 1998-09-23 | 25 | | 170 | 2001-12-10 | 10 | | 177 | 1999-11-03 | 20 | | 183 | 2001-09-07 | 0 | | 185 | 1998-12-05 | 50 | | 189 | 2001-02-13 | 20 | | 190 | 2001-09-14 | 50 | | 191 | 2001-11-21 | 25 | | 196 | 2001-01-18 | 20 | | 197 | 2002-10-01 | 0 | | 203 | 2001-01-17 | 20 | | 212 | 2000-05-30 | 20 | | 213 | 1999-02-26 | 20 | | 215 | 2002-02-17 | 50 | | 235 | 2002-01-26 | 0 | | 236 | 2000-07-17 | 50 | | 238 | 1998-05-28 | NULL | | 243 | 2001-09-27 | 20 | | 245 | 2000-10-06 | 20 | | 248 | 1999-09-08 | 0 | | 251 | 2002-07-16 | 10 | | 254 | 2002-03-07 | 20 | | 255 | 2002-11-24 | 25 | | 257 | 1999-07-24 | 20 | | 258 | 2001-05-21 | 10 | | 261 | 1998-09-09 | 10 | | 263 | 1998-05-04 | 50 | | 264 | 2000-03-01 | 20 | | 265 | 2002-02-06 | 25 | | 266 | 2001-06-14 | 25 | | 278 | 1998-09-17 | 10 | | 279 | 1998-05-22 | 20 | | 286 | 1998-08-26 | 10 | | 287 | 1998-10-06 | 10 | | 288 | 2001-02-08 | 50 | | 291 | 2002-09-20 | 25 | | 296 | 2001-07-09 | NULL | | 297 | 2001-03-29 | 0 | | 309 | 2000-04-10 | 25 | | 312 | 1998-08-17 | 20 | | 313 | 2000-06-04 | 10 | | 316 | 1999-01-02 | 50 | | 317 | 2000-02-11 | 25 | | 322 | 1999-10-21 | 25 | | 330 | 2002-07-17 | 20 | | 331 | 2002-10-30 | 20 | | 332 | 2001-07-30 | 20 | | 334 | 1999-11-25 | 10 | | 335 | 2002-10-04 | 25 | | 338 | 1998-06-21 | 10 | | 339 | 2001-02-10 | 10 | | 340 | 2000-10-08 | 50 | | 343 | 2000-02-13 | 0 | | 350 | 1998-08-19 | 0 | | 352 | 2001-01-29 | 25 | | 354 | 2001-04-12 | 0 | | 355 | 2000-03-22 | 50 | | 358 | 2002-03-20 | 0 | | 366 | 2002-01-17 | 20 | | 367 | 1998-01-24 | 20 | | 376 | 1999-03-07 | 20 | | 380 | 2000-04-03 | 10 | | 382 | 1999-07-29 | 20 | | 387 | 2001-08-14 | 10 | | 390 | 1998-04-13 | 0 | | 392 | 2002-09-20 | 20 | | 393 | 2001-12-26 | 0 | | 395 | 1999-01-01 | 10 | | 397 | 1999-02-23 | 20 | | 398 | 2000-02-01 | 50 | | 406 | 1999-11-02 | 0 | | 410 | 2002-04-30 | NULL | | 426 | 2000-04-29 | 20 | | 428 | 2001-11-23 | 20 | | 429 | 2000-02-09 | 50 | | 432 | 2001-12-02 | 0 | | 439 | 1999-10-27 | 20 | | 445 | 1998-04-04 | 0 | | 449 | 2000-12-19 | 10 | | 450 | 2001-12-31 | 50 | | 451 | 2002-01-25 | 10 | | 453 | 2001-08-05 | 25 | | 455 | 2002-10-25 | 50 | | 459 | 2000-06-10 | 20 | | 462 | 2001-07-08 | 0 | | 465 | 1998-07-14 | NULL | | 466 | 2000-02-04 | NULL | | 469 | 2000-03-07 | 0 | | 470 | 2001-10-05 | 50 | | 472 | 1998-06-13 | 25 | | 475 | 2000-12-27 | NULL | | 478 | 1998-12-30 | 10 | | 480 | 1998-03-22 | 25 | | 482 | 2002-10-03 | 20 | | 484 | 1998-12-24 | 10 | | 485 | 1998-06-07 | 10 | | 487 | 1999-07-01 | 50 | | 489 | 1999-11-14 | 0 | | 508 | 1998-08-03 | 25 | | 510 | 2002-02-09 | 0 | | 511 | 2002-08-04 | 25 | | 512 | 2001-05-14 | 50 | | 513 | 2000-03-26 | 25 | | 518 | 2000-02-01 | 10 | | 521 | 2000-04-27 | 20 | | 527 | 1999-10-12 | 25 | | 529 | 2001-10-22 | NULL | | 532 | 2001-06-21 | 25 | | 536 | 2000-06-21 | 10 | | 537 | 2001-10-10 | 10 | | 539 | 1998-06-30 | 10 | | 541 | 2000-11-10 | 50 | | 554 | 1999-06-29 | 0 | | 569 | 2001-12-10 | 25 | | 571 | 1999-11-13 | 0 | | 572 | 2001-04-29 | 10 | | 573 | 2001-02-17 | 50 | | 576 | 1999-10-15 | 20 | | 578 | 1998-06-05 | 25 | | 581 | 1998-08-20 | 0 | | 583 | 1998-06-27 | 10 | | 586 | 2000-02-24 | 25 | | 587 | 2002-09-29 | 10 | | 593 | 1999-04-05 | 0 | | 599 | 1998-05-17 | NULL | | 602 | 2001-11-06 | 0 | | 604 | 2001-10-10 | 0 | | 606 | 2000-12-29 | 10 | | 613 | 1998-09-19 | 20 | | 622 | 1999-07-27 | 20 | | 625 | 1998-12-21 | 25 | | 626 | 2002-11-29 | NULL | | 628 | 2001-04-06 | 10 | | 631 | 2002-06-27 | 10 | | 634 | 2000-04-25 | 25 | | 642 | 2001-09-04 | NULL | | 646 | 2001-03-20 | 25 | | 653 | 2000-09-29 | 20 | | 655 | 2000-06-12 | 0 | | 656 | 2002-10-04 | 0 | | 657 | 2002-10-07 | 0 | | 659 | 2002-03-28 | 20 | | 662 | 2002-04-27 | 25 | | 665 | 1998-01-22 | 0 | | 666 | 2002-01-05 | NULL | | 667 | 1999-05-26 | 50 | | 668 | 1999-06-22 | NULL | | 674 | 1998-02-15 | 25 | | 680 | 2002-03-31 | 10 | | 694 | 1998-10-26 | 20 | | 697 | 1998-11-11 | 0 | | 702 | 2001-05-14 | 20 | | 705 | 2002-02-10 | 20 | | 709 | 2001-10-12 | 20 | | 710 | 2001-07-06 | NULL | | 711 | 2001-01-06 | 20 | | 712 | 1998-08-19 | 10 | | 723 | 1999-12-03 | 0 | | 726 | 2002-10-02 | NULL | | 728 | 2001-02-22 | 20 | | 731 | 1999-03-25 | 25 | | 732 | 2002-07-10 | 25 | | 733 | 1998-02-25 | 0 | | 734 | 2001-10-03 | 50 | | 735 | 1998-05-21 | 0 | | 736 | 2001-10-27 | 20 | | 746 | 2000-10-04 | 50 | | 749 | 2002-09-25 | 10 | | 750 | 2000-09-20 | 0 | | 751 | 2000-07-16 | 25 | | 759 | 2002-06-25 | 25 | | 762 | 2001-08-06 | 0 | | 763 | 2002-07-28 | 10 | | 770 | 2002-11-03 | 0 | | 771 | 2002-05-24 | 25 | | 773 | 2002-03-05 | 20 | | 782 | 2002-06-14 | 20 | | 786 | 1999-08-31 | 0 | | 788 | 2002-12-12 | 20 | | 794 | 2002-12-27 | 25 | | 795 | 2000-07-02 | 10 | | 797 | 1999-08-29 | 10 | | 810 | 2000-08-19 | 25 | | 812 | 2000-08-03 | 10 | | 813 | 1998-02-27 | 0 | | 814 | 2000-08-01 | 20 | | 815 | 1999-05-20 | NULL | | 821 | 2001-08-21 | 25 | | 826 | 1999-12-05 | 10 | | 830 | 2002-03-25 | 50 | | 839 | 1999-11-26 | 20 | | 843 | 2000-04-30 | NULL | | 849 | 2000-11-23 | 25 | | 853 | 2001-12-08 | 20 | | 855 | 2002-08-26 | 0 | | 856 | 1999-02-14 | NULL | | 857 | 2001-11-19 | 10 | | 858 | 2001-06-14 | 10 | | 861 | 2000-05-21 | 0 | | 863 | 2002-03-07 | 10 | | 864 | 1998-05-04 | 20 | | 867 | 1998-10-27 | 20 | | 880 | 2002-09-08 | NULL | | 884 | 2000-02-05 | 25 | | 892 | 1998-04-24 | 0 | | 894 | 2001-12-06 | 50 | | 899 | 2001-02-27 | NULL | | 902 | 2000-01-12 | 25 | | 906 | 1999-08-01 | 0 | | 909 | 1998-12-19 | 0 | | 910 | 2001-01-09 | NULL | | 917 | 2002-10-24 | 50 | | 919 | 1999-05-02 | 0 | | 921 | 2002-05-26 | 25 | | 923 | 2002-08-31 | 10 | | 925 | 1998-10-04 | 50 | | 930 | 1998-07-07 | 25 | | 932 | 2001-10-15 | 10 | | 933 | 2000-02-19 | 10 | | 938 | 2000-12-08 | 25 | | 942 | 1999-06-16 | 25 | | 945 | 2002-12-10 | NULL | | 946 | 2002-03-05 | NULL | | 951 | 2000-05-17 | 0 | | 958 | 2001-09-29 | NULL | | 962 | 2000-12-02 | 10 | | 965 | 2000-04-29 | 20 | | 966 | 2000-04-18 | 10 | | 970 | 2001-12-19 | 50 | | 973 | 2000-10-31 | 20 | | 976 | 1999-04-09 | 20 | | 977 | 2001-05-13 | 20 | | 978 | 2000-07-13 | 25 | | 981 | 2002-11-08 | 0 | | 991 | 2001-03-30 | 10 | | 994 | 1999-05-29 | 0 | | 995 | 1998-09-23 | 10 | | 997 | 1998-04-17 | 10 | | 999 | 2002-04-09 | 50 | +--------+------------+------+ 294 rows in set (0.00 sec)
Descompte mitjà aplicat a les factures
MariaDB [examenuf2]> SELECT avg(dto) FROM factures; +----------+ | avg(dto) | +----------+ | 17.8263 | +----------+ 1 row in set (0.02 sec)
Nom de les poblacions dels clients en el que el seu codi postal comença per 08
MariaDB [examenuf2]> SELECT pobles.nom FROM pobles JOIN clients ON clients.codpob=pobles.codpob WHERE clients.codpostal LIKE '08%'; +--------------------+ | nom | +--------------------+ | CALLUS | | CINT (EL) | | MAS D'EN ROCA | | PLANASSA (LA) | | VALLS DE TORRUELLA | +--------------------+ 5 rows in set (0.01 sec)
Import mig per factura (amb 2 decimals) sense considerar descomptes ni impostos. L’import es calcula sumant la quantitat del producte pel seu preu
N/A
Quantitats totals venudes per cada article, dels que el seu codi comença per F. La quantitat total es calcula sumant les quantitats de totes les línies de factura
MariaDB [examenuf2]> SELECT articles.codart, articles.descrip, sum(linees.quantitat) as "suma articles" FROM articles JOIN linees on articles.codart=linees.codart WHERE linees.codart LIKE "F%" GROUP BY articles.codart; +---------+----------------------------------+---------------+ | codart | descrip | suma articles | +---------+----------------------------------+---------------+ | FAF36L | LAMPARA ESF R14 60W CLARA | 31 | | FELM3 | PLACA CIEGA TICINO TEKNE | 28 | | FO5/140 | INTERRUPTOR DIFERENCIAL 4 P, 25 | 10 | | FO5/141 | INTERRUPTOR DIFERENCIAL 4 P, 25 | 57 | | FO5/143 | INTERRUPTOR DIFERENCIAL 4 P, 40 | 43 | | FO5/16 | INTERRUPTOR DIFERENCIAL 4 P, 40 | 35 | | FO5/17 | INTERRUPTOR DIFERENCIAL 4 P, 63 | 35 | | FO5/50 | INTERRUPTOR DIFERENCIAL 4 P, 63 | 32 | +---------+----------------------------------+---------------+ 8 rows in set (0.01 sec)
Nom de client i nom de població dels clients de la província de Barcelona
MariaDB [examenuf2]> SELECT clients.nom AS Client, pobles.nom AS Poble FROM clients JOIN pobles on clients.codpob=pobles.codpob WHERE pobles.codpro = (SELECT codpro FROM provincies WHERE nom="Barcelona"); +------------------------------+--------------------+ | Client | Poble | +------------------------------+--------------------+ | CASTELLANOS ANDRES, JULIO | CALLUS | | CUBEDO ANICETO, JAVIER RUBEN | CINT (EL) | | FORTUNYO GRANGEL, JOSE | MAS D'EN ROCA | | LOPEZ DOBON, VICENTE RAMON | PLANASSA (LA) | | FERNANDEZ MARTIN, CRISTINA | VALLS DE TORRUELLA | +------------------------------+--------------------+ 5 rows in set (0.03 sec)
Nom de les poblacions de Barcelona que tenen un nom igual al d’alguna altra població d’una altra província
MariaDB [examenuf2]> SELECT DISTINCT pobles.nom FROM pobles JOIN provincies ON pobles.codpro=provincies.codpro WHERE provincies.nom="Barcelona" AND pobles.nom IN (SELECT altres.nom FROM pobles AS altres JOIN provincies ON altres.codpro=provincies.codpro WHERE provincies.nom!="Barcelona"); +---------------------+ | nom | +---------------------+ | ASPA | | BELLAVISTA | | BONAVISTA | | BORGONYA | | CALELLA | | CAN FONT | | CANTALLOPS | | CANYELLES | | CASC ANTIC | | CASC URBA | | CASETES (LES) | | CASTELL (EL) | | CASTELLET (EL) | | COMA (LA) | | COSTA (LA) | | COSTES (LES) | | ESTACION (LA) | | ESTANY (L') | | FLIX | | FLORESTA (LA) | | GARRIGA (LA) | | GRANOLLERS | | GUARDIA (LA) | | GUARDIOLA | | MALLORQUINES | | MARATA | | MARTORELL | | MASSANES | | MIRAMBELL | | MOLI (EL) | | MOLINA (LA) | | MONTCLAR | | MONTJUIC | | MONTNEGRE | | NUCLEO | | PINEDA (LA) | | PLA (EL) | | PLANA (LA) | | POAL (EL) | | POBLE SEC | | POLIGONO INDUSTRIAL | | PUIG (EL) | | PUJALT | | RAPITA (LA) | | RATERA | | REMEI | | RIERA (LA) | | RIERAL (EL) | | ROCA (LA) | | RUBIO | | SALINES (LES) | | SALLENT | | SALUT (LA) | | SAMUNTA | | SANT BARTOMEU | | SANT CRISTOFOL | | SANT GENIS | | SANT JULIA | | SANT MIQUEL | | SANT PERE | | SANT RAMON | | SANT ROC | | SANTA CREU | | SANTA EUGENIA | | SANTA LLUCIA | | SANTA MARGARIDA | | SERRA (LA) | | TORDERA | | TORRE (LA) | | TORREBLANCA | | TORRENTS (ELS) | | VALL (LA) | | VALLDEPERAS | | VALLS | | VILA-SECA | | VIVER | +---------------------+ 76 rows in set (0.02 sec)
Nombre de client de cada població de Catalunya (codis provincia 08, 17, 43, 25) amb el nomb de població i província
N/A
Número de clients que no tenen cap factura
MariaDB [examenuf2]> SELECT nom FROM clients WHERE NOT EXISTS (SELECT codcli FROM factures WHERE clients.codcli = factures.codcli); +-------------------------------------+ | nom | +-------------------------------------+ | MIRAVET SALA, MARIA MERCEDES | | SAMPEDRO SIMO, MARIA MERCEDES | | GISBERT MIRALLES, BEATRIZ LAURA | | DE LA CRUZ AZNAR, CONCHITA PATRICIA | | VILLALONGA SANCHIS, MILAGROS | | PITARCH MONSONIS, MARIA CARMEN | | GARCIA CASADO, YOLANDA | | ADELL GALMES, MERCEDES ROSARIO | | HERRERA SALA, ANA | | MARTI MOLTO, CONCHITA | | SOS CARRETERO, JESUS | | MIGUEL ARCHILES, OSCAR RAMON | | CHALER SORIANO, MANUEL DIEGO | | PINEL HUERTA, VICENTE | | LOPEZ BOTELLA, MAURO | | PALAU MARTINEZ, JORGE | | RINCON VERNIA, DAVID | | MURIA VINAIZA, JOSE | | HUGUET PERIS, JUAN ANGEL | | FABREGA GARGORI, JAVIER SEBASTIAN | | VILLALONGA RAMIREZ, DIEGO SERGIO | | BADENES CEPRIA, ANDRES RICARDO | | BOTELLA CATALA, JUAN | | LOPEZ LLORENS, SANCHEZ MARCOS | | LOPEZ RINCON, LUIS MIGUEL | | GUIMERA AGOST, LUIS | | GUILLOT BELDA, FRANCISCO JOSE | | LOPEZ GUITART, XAVIER | | TUR MARTIN, MANUEL FRANCISCO | | AZNAR MONFERRER, ADRIAN | +-------------------------------------+ 30 rows in set (0.00 sec)