Usar el Modelo de Datos (SQL)

• Objetivos
• Clases »
  1. Manejar Datos
  2. Sintaxis SQL
  3. Mejorar Consultas
  4. Ejercicio Completo
  5. Más SQL
• Actividades
• Duración
• Para aprender más

Objetivos

• Descripción de las acciones sobre la base de datos (DML)
• Descripciones de consultas, uso avanzado de SELECT

Clases

1. Manejar Datos
   1. Inserción de Datos: Sentencia INSERT INTO
   2. Modificando y consultando datos:
      1. Modificación de Datos (con cuidado): Sentencia UPDATE
      2. Consultas Básicas: Sentencia SELECT * | expression [ AS output_name ] [, ...] [ FROM from_item [, ...] [ WHERE condition ] ]. Por ejemplo select nombre as "El nombre", apellidos from alumnos where id_alumno = 25 ó select 25*8 AS "Operacion"
      3. Condiciones (condition): expresiones que devuelvan valor true. Por ejemplo: nombre = 'Pepe' o nombre = 'Paco' AND edad < 25
      4. Borrado de Datos (con cuidado): Sentencia DELETE
   3. Consultas con varias tablas
      1. Uso de varias Tablas (producto cartesiano de todas las tablas): Clausula FROM tabla1, tabla2, tabla3
      2. Se utilizan las claves ajenas (foreign key) para generar condiciones (WHERE) sobre ese producto cartesiano.
      3. El uso de Alias en Campos y Tablas, permite claridad y facilidad en la generación de consultas complejas
2. Sintaxis completa de SELECT
   1. Cláusulas:
      • Para ordenar la salida: ORDER BY
      • Para limitar la salida: LIMIT y OFFSET
   2. Uso de JOIN en las consultas (uso en cláusula FROM)
      1. CROSS Join: equivalente a separar por comas
      2. Requieren Condiciones (qualifications): INNER Join y OUTER Join (right y left)
      3. Las condiciones se añaden con la palabra ON y una expresión ON (join_condition)
      4. Ejemplo de Sesión SQL
3. Mejorando las consultas
   1. Utilizar Expresiones (devuelven un valor de un tipo de datos concreto).
   2. Funciones y Operadores
      1. Matemáticas:
         • Operadores: +, - , /, *, ^, etc
         • Funciones: log(x), random(), sqrt(), random(), etc
      2. Manejo de Caracteres:
         • Operadores: ||
         • Funciones : lower('cadena'), char_length('cadena'), substring ('cadena' from 'REGEXP'etc
      3. Tiempo y Fecha:
         • Constantes: current_timestamp, current_date, now()
         • Funciones y Operadores: +, -, etc
         • Funciones de formateo: to_char(timestamp, text), to_date(text, text), etc
           Nota: text es un formato específico, por ejemplo HH24:MI:SS ó SS (More Examples)
      4. Expresiones con Patrones (Pattern Matching):
         • Operador LIKE: string [NOT] LIKE pattern
         • Operador ~: Regular Expressions
   3. Agregación de Registros:
      1. Cláusulas GROUP BY: agrupar según campo y utilizar funciones de agregación
      2. Cláusula HAVING: condiciones sobre las filas agrupadas
      3. De agregación: count(*),sum (expression), max(expression), min(expression), avg(expression), stddev(expression). Ejemplo: select empresa, min(salario)::integer from tabla_ejemplo group by empresa;
4. Ejercicio Completo :
   1. Revisar un modelo SQL y consultas SQL de otros
   2. Hacer un Modelo de Datos (dos clases)
5. Más SQL:
   • Tipo de datos Serial: valores de una serie (útil para id)
   • Operador IN o NOT IN:
     • Sirve para ver la pertenencia a una lista o conjunto de valores
     • expression IN (subquery)
     • Ejemplos:
       • nombre IN ('hola','adios') ó
       • (nombre,ciudad) IN (select nombre, ciudad from tabla1, tabla2 where tabla1.id=tabla2.codigo);
   • Subconsultas (subqueries)
     • Uso de SELECT dentro de otro SELECT
     • Se utilizan paréntesis dentro del cuerpo de la sentencia
     • Ejemplos:

       		  select titulo from carla_peliculas where id > (select 5);
       		  select titulo from carla_peliculas where duracion=(select max(duracion) from carla_peliculas);
       		

   • Vistas (views)
     • Es COMO una tabla-resultado de una consulta
     • Sintaxis: CREATE view AS query
     • Ejemplo de uso
       • Crear la consulta: create view vista as vista_ejemplo as select titulo as Titulo, anio as anho from carla_peliculas;
       • Usar la consulta: select anho from vista_ejemplo;
       • Borrar la consulta (no borra datos!!): drop view vista_ejemplo;
   • Funciones
     • Extensión de la funcionalidad
     • Uso de un lenguaje: SQL u otros como c, tcl, plperl, plpgsql
     • Ejemplo de uso
       • Crear la función: create function dame_titulo(integer) returns varchar(80) as 'select titulo from carla_peliculas where id=$1' language 'SQL';
       • Usar la función: select dame_titulo(2);
       • Borrar la función: drop function dame_titulo(integer);
     • Lista de funciones: \df

Actividades

1. Manejando Datos (Select Básico, UPDATE, DELETE)
   • Ejecutar Consultas Básicas (SELECT) (Ver el contenido de la tabla, ver datos, FROM)
   • Introducir datos, todos los campos o por separado (INSERT INTO)
   • Probar cómo protege la base de datos su consistencia (forzar errores)
   • Modifificar Filas / Datos (UPDATE)
   • Borrar Filas (DELETE)
2. Manejando Datos (SELECT avanzado).
   • Uso de JOIN
   • Uso de funciones de agregación
3. Consultas con Funciones y Operadores (Crear Ejemplos)
4. Revisar archivos SQL de otros

Modelo de Datos completo

Se busca construir un sistema de información para gestionar una base de datos de películas. Básicamente se requiere gestionar la información de las películas, las estrellas (actores / actrices) que participan en ella y los estudios de producción a las que pertenecen.

Además se requiere que las consultas que tendrá que soportar el modelo de datos sean:

1. Encontrar la dirección de un estudio
2. Encontrar todas las estrellas que participaron en películas realizadas en el año TAL o en alguna película que contenga la palabra TAL en el título.
3. ¿Quíen fue la estrella de la película TAL?
4. ¿Qué películas tienen mayor duración que la película TAL?
5. Encontrar el título y la duración de todas las películas producidas por los estudios TAL en el año TAL, ordenando la salida por su duración
6. Encontrar todas las estrellas que son hombres o viven en TAL ciudad
7. ¿Qué estrellas distintas aparecen en las películas producidas por el estudio TAL, en TAL año? Si las películas salen repetidas, mostrarlas una sóla vez
8. ¿Cuál es la media, en años, de publicación de las películas?
9. Encontrar las películas que empiecen por L, que tengan más de una palabra y que contengan en el título las letras a,p y m
10. Encontrar la suma de la duración de todas las películas de cada estudio
11. Además, cada alumno deberá, añadir tres ejemplos de consultas más, para vender su producto.

El trabajo se hará utilizando la base de datos del colegio y las tablas de la base de datos serán precedidas por el username del alumno. Recordad que todos tenéis el mismo usuario, por lo tanto cuidado con las tablas que modificáis y pensad la manera de tener copias de seguridad del modelo de datos. Se dispondrá de dos clases (7 horas) para el trabajo y en la última clase iré revisando el trabajo.

Necesito un plan escrito breve, que revisaré en la primera clase. Del resto podéis utilizar cualquier herramienta y/o técnica de diseño y trabajo.

Tiempo Estimado

4 semanas

Recursos a Utilizar

• Recursos de la Unidad Anterior
• Construyendo una base de datos, desde cero (no utiliza la consola)