WIXLIB

A.Introducción breve a SQLite.En esta práctica sobre SQL utilizaremos el software SQLite.SQLite es un sistema de gestión de bases relacional, famoso por su pequeñotamaño. Es un proyecto de dominio público creado por Richard Hipp. A diferencia de otros sistemas de gestión cliente-servidor el motor de SQLite no es unproceso independiente lo que hace que la latencia sea menor y más el accesomás eficiente. SQLite también soporta operaciones ACID [9].La versión 3 permite bases de datos de hasta 2 Terabytes de tamaño y lainclusión de campos tipo BLOB [2]. Esta es la versión que utilizaremos para lapráctica.Debido a su facilidad de uso, su pequeño tamaño y su versatilidad SQLite esutilizado en una gran variedad de aplicaciones. Entre ellas podemos encontrarMozilla Firefox Skype o XBMC [8]. Es especialmente adecuado para los sistemasintegrados y su uso ha sido muy popular en las aplicaciones para smartphonescon SO Android y iOS [1].En la página web de SQLite podemos encontrar el código fuente y los binarios para los principales sistemas operativos [4]. El programa funciona por lı́neade comandos y no tiene ninguna interfaz gráfica. Sin embargo es fácil encontrar interfaces gráficas en Internet que podemos utilizar. Entre ellas tenemosSQLiteStudio [7], SQLite Administrator [6] o DB Browser for SQLite [3].Para el desarrollo de la práctica podeı́s apoyaros en cualquier herramientagráfica que queráis utilizar pero se recomienda utilizar, al menos en los primerospasos, la interfaz de comandos. Además, los resultados de la práctica siemprese evaluarán sobre la interfaz de la lı́nea de comandos por lo que os debéis asegurar que el código se ejecuta. También se valorará la claridad de las consultas,consultas demasiado largas o innecesariamente complejas serán penalizadas.B.Repaso básico a SQL y SQLiteEn SQLite lo primero que debemos hacer es iniciar nuestra base de datos, elmismo comando que nos servirá para abrirla lo utilizamos para crearla ya quedetecta que la base de datos no existe y la crea. Simplemente debemos escribirel nombre del ejecutable en la consola seguido del nombre de nuestra base dedatos “sqlite3 nombreBaseDatos.db”.B.1.Tipos de datosCada gestor de bases de datos tiene unos tipos predefinidos. En SQLitetenemos tan solo los siguientes:NULLINTEGERREALTEXTBLOB10

Los nombres son autoexplicativos en su mayor parte, pero para mayor información podemos consultar el manual [5]. Allı́ explica como por ejemplo lasfechas no tienen un tipo especial sino que se almacenan o como texto o comoreal o como entero.B.2.ComandosEn SQLite los comandos van precedidos por punto y son similares a loscomandos de otros gestores de bases de datos. A continuación repasamos losmás importantes, para un listado completo tan solo tenemos que utilizar elcomando ayuda.help Muestra los comandos disponibles.quit Nos permite salir del programa.tables Muestra una lista de las tablas creadas en la base de datos.output ?FILENAME? Envia la salida estándar a un fichero.schema Muestra el código SQL necesario para crear los elementos presentesB.3.Creación de tablasLa creación de tablas sigue la misma sintaxis que cualquier motor de SQL,utilizando el comando CREATE table.CREATE TABLE c i u d a d e s (i d INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,nombre VARCHAR ( 2 5 5 ) NOT NULL,nombre Largo VARCHAR ( 2 5 5 ) ,i d p a i s INTEGER NOT NULL);Posteriormente debemos insertar datos en las tablas que hemos creado, paraello utilizamos la orden INSERT INTO table.i n s e r t into c i u d a d e s values ( 1 , ” S e v i l l a ” , ”Luz de l a s Gentes ” , 3 ) ;i n s e r t into c i u d a d e s values ( 2 , ” Madrid ” , NULL, 3 ) ;B.4.Consultas en la base de datosUna vez tenemos la información en la base de datos es fundamental quepodamos acceder a ella, para ello utilizamos las consultas. Las consultas serealizaran utilizando la orden SELECT ? FROM table.s e l e c t from c i u d a d e s ;s e l e c t nombre from c i u d a d e s ;s e l e c t nombre from c i u d a d e s where i d p a i s ” 3 ” ;11

Las consultas se realizan con toda la potencia de la sintaxis de SQL, pudiendose realizar consultas a varias tablas, con varias condiciones, agrupadas, pororden, etc.El formato completo serı́a el siguiente:SELECT [ALL DISTINCT ] nombre campo [{ , nombre campo }]FROM nombre tabla n o m b r e v i s t a [{ , nombre tabla n o m b r e v i s t a }][WHERE c o n d i c i o n [ { AND OR c o n d i c i o n } ] ][GROUP BY nombre campo [{ , nombre campo }]][HAVING c o n d i c i o n [{ AND OR c o n d i c i o n } ] ][ORDER BY nombre campo i n d i c e c a m p o [ASC DESC][{ , nombre campo i n d i c e c a m p o [ASC DESC ] } ] ]Dónde los corchetes indican todas las opciones que pueden o no aparecer.B.5.Otros comandosOtras operaciones SQL que se pueden realizar son las modificaciones de datoso el borrado.Para modificar un dato ya guardado utilizaremos el comando UPDATE table.update c i u d a d e s set nombre Largo ” Madrid La l l a n a ” wherenombre ” Madrid ” ;Para borrar un registro de la base de datos utilizaremos el comando DELETEFROM tabledelete from c i u d a d e s where nombre ” Madrid ” ;C.Explicación sobre la base de datos.En esta práctica vamos a crear una base de datos para almacenar información de los resultados de los mundiales de futbol. Necesitaremos almacenar lasselecciones que participan, a que paı́s pertenecen, en que evento participan y losdetalles de cada uno de los partidos.También es necesario que sepamos el desarrollo de la competición, por lotanto tenemos que saber a que ronda pertenece cada uno de los partidos, si esla final o si son los preliminares.En la figura 1 adjunta se muestra un diagrama de entidad relación de lapráctica pedida.D.Ejercicios de la prácticaEn esta práctica se te pedirá que realicéis ciertas operaciones utilizandoSQLite.12

Figura 1: Diagrama de Entidad RelaciónCualquier comando que se haya ejecutado debe quedar reflejado en los ficheros que se entreguen.Por cada ejercicio se deben entregar dos ficheros diferentes:NombreApellidosEjercicioXCodigo.sqlEste fichero deberá poder ser interpretado por SQLite y contendrá todoslos comandos utilizados para el Ejercicio correspondiente. Para diferenciarentre los apartados del ejercicio utiliza un comentario en el fichero./ Apartado 1 /Select from c i t i e s ;/ Apartado 2 /CREATE TABLE c i t i e s (idINTEGERPRIMARY KEY AUTOINCREMENTNOT NULL,nameVARCHAR ( 2 5 5 ) NOT NULL,codeVARCHAR ( 2 5 5 ) ,c o u n t r y i d INTEGERNOT NULL);/ Apartado 3 /Select from p l a c e s ;NombreApellidosEjercicioResultado.txtEste fichero contendrá el resultado de los comandos ejecutados. En el casode consultas el resultado de la misma y en el caso de una vista el resultadode una consulta de todos los elementos de la vista.Donde X es el número de ejercicio que corresponda.D.1.Ejercicio 1Este ejercicio consiste en la creación de las tablas que representen la información que se pide almacenar, además de poblarlas con datos. No se pide que escomplete toda la información de todas las copas del mundo, pero en cada tabladeben aparecer un número de registros aceptable, al menos unos diez y con la13

mayor variedad posible. Tanto la creación de datos como la inserción de datosdeben quedar perfectamente reflejadas en el archivo correspondiente, de formaque se pueda reproducir a la exactitud la base de datos.Las tablas que se piden elaborar son como mı́nimo las siguientes;Selecciones: que represente las distintas selecciones que hay.Paises: los distintos paises que existen y que estan representados por suselección.Eventos: las competiciones que se han disputadoPartidos: que equipos jugaron, cuando y el resultado.Cualquier otra tabla que se considere necesaria se puede y debe añadir.D.2.Ejercicio 2Este ejercicio consiste en realizar varias consultas. Se pide que haya datossuficientes en la base de datos para poder observar el correcto funcionamientode las consultas, si hacen falta más datos se pueden añadir, reflejandolo en elarchivo de comandos.Realiza las siguientes consultas:1. Enumera el nombre de todos los equipos que han participado alguna vezen el mundial.2. Muestra la fecha de inicio de todas las ediciones del mundial.3. Para un mundial muestra en dos columnas para cada partido el nombrede los dos paı́ses que se enfrentaron entre sı́.4. A la consulta anterior añade además la puntuación final de cada uno delos dos equipos.5. Muestra el nombre de los equipos que participaron en la primera ediciónde la que tenemos datos.6. Lista los partidos que se hayan realizado entre el 1900 y el 2010 en los quehaya habido al menos un gol.7. Ya sabemos que los Españoles somos muy buenos al fútbol pero ahoramuestra en que ediciones del mundial ha estado la selección española.8. Lista los partidos en los ha jugado la selección española y no ha empatado.9. Ahora vamos a ver cual ha sido la mayor cantidad de goles que ha conseguido marcar nuestra selección10. Muestra el número de veces que cada equipo ha participado en el mundial,ordénalos desde el que más veces haya participado hasta el que menos.11. Los nombres de los equipos que participan en la copa del mundo no siemprecoinciden con el nombre del equipo, en una consulta muestra para cadapaı́s los equipos que tiene asociados.14

12. Muestra en una consulta los resultados de las finales de la copa del mundo.Debes mostrar en la misma consulta la competición que fue, el nombre delos dos equipos que participaban y el resultado final de cada uno de ellos.D.3.Ejercicio 3Sobre la base de datos que se te ha entregado se te pide que crees algunastablas. Ten en cuenta las posibles limitaciones y la estructura de la base dedatos existente. Razona las decisiones que tomes, si tienes algún comentario oaclaración la puedes añadir en el fichero .sql como un comentario.En los casos que se pida que se inserten datos en las tablas también debemosañadir el código que lo hace en el fichero .sql en el apartado correspondiente.1. Hay jugadores que han marcado la historia de los mundiales, añade unatabla para almacenar los nombres de estos jugadores. En el caso que puedastambién debes dejar espacio para añadir la fecha de nacimiento en casode que este disponible.Inserta algún valor en la tabla de tus jugadores favoritos.2. Pero los jugadores sin sus equipos no serı́an nada. Ahora queremos saberde nuestras leyendas con que equipos jugaron, crea las tablas necesariase inserta los datos apropiados en ellas para que podamos consultar estainformación.3. Ahora vamos a introducir nuestro nombre en la historia, en este caso vamosa sustituir el nombre de uno de los grandes “Luis Monti” por el nuestro.Introduce por lo tanto tu nombre en la tabla con las leyendas del fútbolpero con los datos de este jugador. Añade también en los equipos en losque participó este jugador.4. Vamos a mirar nuestro registro de partidos, haz una consulta que muestretodos los partidos que nos corresponden con nuestra nueva identidad futbolı́stica en los datos de las copas del mundo. No es necesario diferenciarentre los partidos en los que jugamos o no, lo importante es participar,mientras que formáramos parte del equipo cuenta para el listado.D.4.Ejercicio 4En este último ejercicio se te pedirá que crees ciertas vistas sobre la base dedatos.1. Crea una vista dónde se muestren todos los partidos jugados, con el nombre del evento y de los dos equipos.2. Crea una vista que muestre el número de copas del mundo en las que haparticipado cada equipo, una fila por cada equipo en la que una columnaponga el número de veces que ha participado. Ordena el resultado de formaque en la primera linea salga el equipo que más veces ha participado.3. Repite la vista anterior pero en este caso en vez de por equipos por paı́ses.15

E.Resultados que se deben entregar.Se deben comprimir todos los ficheros en un archivo sin meterlos en ningunacarpeta, nombra al fichero:NombreApellidosBD.zipAdjunta también la base de datos que has creado:BDCompleteNombreApellidos.dbAntes de entregarlo revisa que la nomenclatura de los ficheros es correcta yque los ficheros .sql se pueden ejecutar.16

Anexo: Solución documentada dela práctica17

E.1.Ejercicio 1Este ejercicio consiste en la creación de las tablas que representen la información que se pide almacenar, además de poblarlas con datos. No se pide que escomplete toda la información de todas las copas del mundo, pero en cada tabladeben aparecer un número de registros aceptable, al menos unos diez y con lamayor variedad posible. Tanto la creación de datos como la inserción de datosdeben quedar perfectamente reflejadas en el archivo correspondiente, de formaque se pueda reproducir a la exactitud la base de datos.Las tablas que se piden elaborar son como mı́nimo las siguientes;Selecciones: que represente las distintas selecciones que hay.Paises: los distintos paises que existen y que estan representados por suselección.Eventos: las competiciones que se han disputadoPartidos: que equipos jugaron, cuando y el resultado.Cualquier otra tabla que se considere necesaria se puede y debe añadir.CREATE TABLE ” c o u n t r i e s ”( ” i d ” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,”name” varchar ( 2 5 5 ) NOT NULL) ;CREATE TABLE ” e v e n t s ”( ” i d ” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,” key ” varchar ( 2 5 5 ) NOT NULL, ” s t a r t a t ” date NOT NULL, ” e n d a t ” date ) ;CREATE TABLE ” e v e n t s t e a m s ”( ” i d ” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,” e v e n t i d ” integer NOT NULL, ” t e a m i d ” integer NOT NULL) ;CREATE TABLE ” games ”( ” i d ” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,” e v e n t i d ” integer NOT NULL, ” round ” varchar ( 2 5 5 ) NOT NULL,” t e a m 1 i d ” integer NOT NULL, ” t e a m 2 i d ” integer NOT NULL,” p l a y a t ” d a t e t i m e NOT NULL, ” s c o r e 1 ” integer , ” s c o r e 2 ” integer ,” winner ” integer ) ;CREATE TABLE ” teams ”( ” i d ” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,” key ” varchar ( 2 5 5 ) NOT NULL, ” t i t l e ” varchar ( 2 5 5 ) NOT NULL,” c o u n t r y i d ” integer NOT NULL) NTOINTOINTO” countries ”” countries ”” countries ”” countries ”” countries ”” countries ”VALUES( 6 3 , ’ China ’ ) ;VALUES( 1 0 9 , ’ Germany ’ ) ;VALUES( 1 1 0 , ’ E s t o n i a ’ ) ;VALUES( 1 1 1 , ’ Spain ’ ) ;VALUES( 1 1 2 , ’ F i n l a n d ’ ) ;VALUES( 1 1 3 , ’ France ’ ) ;18

INTOINTOINTOINTOINTO” countries ”” countries ”” countries ”” countries ”” countries ”” countries ”” countries NTO” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events ”” events OINTOINTOINTO” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”” teams ”VALUES( 1 1 4 ,VALUES( 1 1 5 ,VALUES( 1 1 6 ,VALUES( 1 2 0 ,VALUES( 1 2 7 ,VALUES( 1 8 6 ,VALUES( 1 9 4 ,’ Greece ’ ) ;’ Irland ’ );’ Italy ’ );’ Portugal ’ ) ;’ Poland ’ ) ;’ Argentina ’ ) ;’ Uruguay ’ ) ;VALUES( 1 , ’ world . 1 9 3 0 ’VALUES( 2 , ’ world . 1 9 3 4 ’VALUES( 3 , ’ world . 1 9 3 8 ’VALUES( 4 , ’ world . 1 9 5 0 ’VALUES( 5 , ’ world . 1 9 5 4 ’VALUES( 6 , ’ world . 1 9 5 8 ’VALUES( 7 , ’ world . 1 9 6 2 ’VALUES( 8 , ’ world . 1 9 6 6 ’VALUES( 9 , ’ world . 1 9 7 0 ’VALUES( 1 0 , ’ world . 1 9 7 4VALUES( 1 1 , ’ world . 1 9 7 8VALUES( 1 2 , ’ world . 1 9 8 2VALUES( 1 3 , ’ world . 1 9 8 6VALUES( 1 4 , ’ world . 1 9 9 0VALUES( 1 5 , ’ world . 1 9 9 4VALUES( 1 6 , ’ world . 1 9 9 8VALUES( 1 7 , ’ world . 2 0 0 2VALUES( 1 8 , ’ world . 2 0 0 6VALUES( 1 9 , ’ world . 2 0 1 0VALUES( 2 0 , ’ world . 2 0 1 4VALUES( 7 0 , ’ chn ’ ,VALUES( 2 2 4 , ’ f r g ’VALUES( 2 2 5 , ’ gdr ’VALUES( 1 2 7 , ’ g e r ’VALUES( 1 2 8 , ’ e s t ’VALUES( 1 2 9 , ’ e s p ’VALUES( 1 3 0 , ’ f i n ’VALUES( 1 3 1 , ’ f r a ’VALUES( 1 3 2 , ’ g r e ’VALUES( 1 3 3 , ’ i r l ’VALUES( 1 3 4 , ’ i t a ’VALUES( 1 3 8 , ’ por ’VALUES( 1 4 5 , ’ p o l ’VALUES( 2 1 0 , ’ a r g ’VALUES( 2 1 4 , ’ uru ’VALUES( 1 3 7 , ’ ned ’,,,,,,,,,’’’’’’’’’’’’ 1930 07 13 ’ ,NULL) ;’ 1934 05 27 ’ ,NULL) ;’ 1938 06 04 ’ ,NULL) ;’ 1950 06 24 ’ ,NULL) ;’ 1954 06 16 ’ ,NULL) ;’ 1958 06 08 ’ ,NULL) ;’ 1962 05 30 ’ ,NULL) ;’ 1966 07 11 ’ ,NULL) ;’ 1970 05 31 ’ ,NULL) ;, ’ 1974 06 13 ’ ,NULL) ;, ’ 1978 05 01 ’ ,NULL) ;, ’ 1982 06 13 ’ ,NULL) ;, ’ 1986 05 31 ’ ,NULL) ;, ’ 1990 06 08 ’ ,NULL) ;, ’ 1994 06 17 ’ ,NULL) ;, ’ 1998 06 10 ’ ,NULL) ;, ’ 2002 05 31 ’ ,NULL) ;, ’ 2006 06 09 ’ ,NULL) ;, ’ 2010 06 11 ’ ,NULL) ;, ’ 2014 06 12 ’ ,NULL) ;’ China ’ , 6 3 ) ;, ’ West Germany ( 1989) ’ , 1 0 9 ) ;, ’ East Germany ( 1989) ’ , 1 0 9 ) ;, ’ Germany ’ , 1 0 9 ) ;, ’ Estonia ’ ,110);, ’ Spain ’ , 1 1 1 ) ;, ’ Finland ’ , 1 1 2 ) ;, ’ France ’ , 1 1 3 ) ;, ’ Greece ’ , 1 1 4 ) ;, ’ Ireland ’ ,115);, ’ Italy ’ ,116);, ’ Portugal ’ , 12 0) ;, ’ Poland ’ , 1 2 7 ) ;, ’ Argentina ’ , 1 8 6 ) ;, ’ Uruguay ’ , 1 9 4 ) ;, ’ Netherlands ’ , 11 9) ;INSERT INTO ” e v e n t s t e a m s ” VALUES( 3 0 3 , 1 7 , 7 0 ) ;INSERT INTO ” e v e n t s t e a m s ” VALUES( 6 4 , 5 , 2 2 4 ) ;INSERT INTO ” e v e n t s t e a m s ” VALUES( 7 8 , 6 , 2 2 4 ) ;19

TOINTOINTO” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” events” eventsteams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”teams ”VALUES( 9 3 , 7 , 2 2 4 ) ;VALUES( 1 1 0 , 8 , 2 2 4 ) ;VALUES( 1 2 7 , 9 , 2 2 4 ) ;VALUES( 1 4 1 , 1 0 , 2 2 4 ) ;VALUES( 1 5 8 , 1 1 , 2 2 4 ) ;VALUES( 1 7 8 , 1 2 , 2 2 4 ) ;VALUES( 2 0 3 , 1 3 , 2 2 4 ) ;VALUES( 2 2 6 , 1 4 , 2 2 4 ) ;VALUES( 1 4 2 , 1 0 , 2 2 5 ) ;VALUES( 3 4 , 3 , 1 2 7 ) ;VALUES( 2 1 , 2 , 1 2 7 ) ;VALUES( 2 4 9 , 1 5 , 1 2 7 ) ;VALUES( 2 8 2 , 1 6 , 1 2 7 ) ;VALUES( 3 1 2 , 1 7 , 1 2 7 ) ;VALUES( 3 3 4 , 1 8 , 1 2 7 ) ;VALUES( 3 6 3 , 1 9 , 1 2 7 ) ;VALUES( 4 1 4 , 2 0 , 1 2 7 ) ;VALUES( 2 2 , 2 , 1 2 9 ) ;VALUES( 4 7 , 4 , 1 2 9 ) ;VALUES( 9 7 , 7 , 1 2 9 ) ;VALUES( 1 1 5 , 8 , 1 2 9 ) ;VALUES( 1 6 4 , 1 1 , 1 2 9 ) ;VALUES( 1 8 5 , 1 2 , 1 2 9 ) ;VALUES( 2 1 1 , 1 3 , 1 2 9 ) ;VALUES( 2 3 3 , 1 4 , 1 2 9 ) ;VALUES( 2 5 7 , 1 5 , 1 2 9 ) ;VALUES( 2 8 8 , 1 6 , 1 2 9 ) ;VALUES( 3 1 9 , 1 7 , 1 2 9 ) ;VALUES( 3 4 0 , 1 8 , 1 2 9 ) ;VALUES( 3 7 0 , 1 9 , 1 2 9 ) ;VALUES( 4 2 0 , 2 0 , 1 2 9 ) ;INSERT INTO ” games ” VALUES( 1 , 1 , ’ Matchday 1 ’ , 1 3 1 , 1 9 0, ’ 1930 07 13 1 2 : 0 0 : 0 0 . 0 0 0 0 0 0 ’ , 4 , 1 , 1 ) ;INSERT INTO ” games ” VALUES( 1 5 0 , 7 , ’ Matchday 2 ’ , 2 2 3 , 1 2 9, ’ 1962 05 31 1 2 : 0 0 : 0 0 . 0 0 0 0 0 0 ’ , 1 , 0 , 1 ) ;INSERT INTO ” games ” VALUES( 1 5 1 , 7 , ’ Matchday 3 ’ , 2 1 1 , 2 2 3, ’ 1962 06 02 1 2 : 0 0 : 0 0 . 0 0 0 0 0 0 ’ , 0 , 0 , 0 )

En esta pr actica sobre SQL utilizaremos el software SQLite. SQLite es un sistema de gesti on de bases relacional, famoso por su pequeno tamano. Es un proyecto de dominio pub lico creado por Richard Hipp. A dife-rencia de otros sistemas de gesti on cliente-servidor el motor de SQLite no es un