Download SQL-query-I

DML en SQL
Consultas sencillas usando el DML
de SQL
Base Datos de Películas
?
?
?
?
Para rellenar la base se ha utilizado
información proveniente de “The internet
Movie- Database”
http://www.imdb.com/list
Todos los datos son anteriores a 1997
Todas las películas tienen al menos 200
votos
Los actores deben aparecer en más de
una película
2
Esquema de una base de datos
de Peliculas
copy
3
TABLAS
CREATE TABLE PELICULA(
ID INTEGER,
TITULO CHAR(70),
AGNO DECIMAL(4),
PUNTUACION FLOAT,
VOTOS INTEGER,
PRIMARY KEY (ID));
-CREATE TABLE ACTOR (
ID INTEGER,
NOMBRE CHAR(35),
PRIMARY KEY (ID));
-CREATE TABLE REPARTO(
PELICULA_ID INTEGER,
ACTOR_ID INTEGER,
ORD INTEGER,
------
Identificador único
Titulo de la película
Año de estreno
Puntuación media
Numero de votos
-- Identificador Único
-- Nombre del actor/actriz
-----
referencia a la tabla PELICULA
referencia a la tabla ACTOR_ID
Orden en el reparto
La estrella es 1, ...
-FOREIGN KEY (PELICULA_ID ) REFERENCES PELICULA(ID),
FOREIGN KEY (ACTOR_ID) REFERENCES ACTOR(ID),
PRIMARY KEY (PELICULA_ID, ACTOR_ID));
4
Consultas sencillas en SQL
SELECT
FROM
WHERE
ORDER BY
? Lista los atributos (SELECT) pertenecientes a
una (o más relaciones) (FROM) que satisfagan
una condición (WHERE), ordenar la salida
(ORDEN BY).
?
La principal diferencia entre los resultado
proporcionados por SQL y álgebra relacional
es que SQL admite el duplicado de tuplas
5
SQL vs. Rel. Álgebra
SELECT A1, A2, ..., An
FROM R1, R2, ..., Rm
WHERE condición
es equivalente a la consulta en álgebra relacional :
? A1, A2, ..., An (? condición (R1 X R2 X ... X Rm))
Devuelve una relación (sin nombre) cuyo esquema es (A1,
A2, ..., An)
?
SQL no distingue entre mayúsculas y minúsculas
?
Excepto cuando se usan comillas.
6
Selección y Proyección
Selección:
? Encontrar las mejores películas (puntuación mayor que
9.0)
SELECT *
FROM pelicula
WHERE puntuacion > 9.0 ;
Proyección:
? Encontrar las mejores películas (puntuación mayor que
9.0) e imprimir el título y el año del estreno
SELECT titulo, agno
FROM pelicula
WHERE puntuacion > 9.0 ;
7
Duplicados
?
Para eliminarlos se usa DISTINCT
?
Es una operación cara
SELECT DISTINCT agno
FROM pelicula
WHERE puntuacion > 9.0;
8
Comparación de caracteres
?
?
Se pueden utilizar las expresiones algebraicas
“usuales” ‘< ’, ‘>’, ‘= ’, …
o la instrucción “like” que permite el uso de
“comodines”
?
?
Dos “wildcards”: ‘_’ (.) and ‘%’ (*)
Encontrar todas las películas (y su puntuación) que
empiezan por ‘Star’
SELECT titulo, puntuacion
FROM pelicula
WHERE titulo LIKE 'Star%';
?
Encontrar todas las películas con ‘s en su titulo
SELECT titulo
FROM pelicula
WHERE titulo LIKE '%''s%';
9
[NOT] SIMILAR TO
?
Tiene todas las funcionalidades de LIKE y
además es capaz de usar expresiones regulares
(bueno, un subconjunto)
?
?
?
?
?
| una de dos alternativas
* repetición de lo anterior cero o más veces.
+ repeticion de lo anterior una o más veces.
() se usa para agrupar creando una único
objeto.
[...] especifica una clase.
10
Ejemplo: SIMILAR
?
Encontrar todas las películas (y su
puntuación) que empiezan por ‘Star’ y no
sean de la saga “Star Treck”
SELECT titulo, puntuacion
FROM pelicula
WHERE titulo NOT SIMILAR TO
'%(S|s)tar [A-z]rek%' AND
titulo SIMILAR TO '%Star%';
11
Ordenar la salida
?
?
?
Tras una consulta, la relación resultante está
ordenada al azar.
ORDER BY especifica en que orden se
presentan las duplas :
Encontrar las mejores películas (puntuación
mayor que 9.0) e imprimir el título y su
puntuación. El resultado debe estar ordenado
por puntuación.
SELECT titulo, puntuacion
FROM pelicula
WHERE puntuacion > 9.0
ORDER BY puntuacion;
12
Ordenar la salida
?
Vaya, por defecto ordena de forma
ascendente
SELECT titulo, puntuacion
FROM pelicula
WHERE puntuacion > 9.0
ORDER BY puntuacion DESC;
?
¿Cuál es la peor película?
13
Ordenar la Salida
SELECT select_list
FROM table_expression
WHERE
ORDER BY column1 [ASC |
DESC] [, column2 [ASC | DESC]
...];
14
Renombrar Atributos
?
Se usa la palabra AS en el comando SELECT
SELECT titulo AS Title, agno AS Year,puntuacion
AS kk
FROM pelicula
WHERE kk > 9.0;
SELECT titulo AS Title, agno*365.25 AS Day
FROM pelicula
WHERE puntuacion > 9.0;
15
Producto(s) Cartesiano(s)
?
?
?
Gran parte de la potencia de las bases relacionales se
basa en la posibilidad de combinar dos (o más)
relaciones.
El producto cartesiano de dos relaciones se consigue
enumerando cada relación en la orden FROM
Obtener el reparto de 'Pulp Fiction' (el identificador
de esta película es el 2)
SELECT nombre
FROM actor, reparto
WHERE pelicula_id=2 AND actor_id=id;
16
Natural Join
?
Obtener el reparto de 'Pulp Fiction' (el
identificador de esta película es el 2)
SELECT nombre
FROM actor NATURAL INNER JOIN reparto
WHERE pelicula_id=2 AND actor_id=id; ;
17
Producto(s) Cartesiano(s)
?
Obtener el reparto de ‘Blade Runner'
SELECT nombre
FROM actor, reparto, pelicula
WHERE titulo = 'Blade Runner'
AND pelicula.id = pelicula_id
AND actor_id = actor.id;
18
Producto Cartesiano
Películas de John Travolta ordenadas por popularidad
SELECT titulo, puntuacion
FROM reparto,pelicula,actor
WHERE actor.nombre='John Travolta'
AND actor_id=actor.id
AND pelicula_id=pelicula.id
ORDER BY puntuacion desc;
?
19
Ambigüedad en los atributos
?
Método 1: Utilizar el nombre de la
relación
Películas de John Travolta
SELECT titulo, puntuacion
FROM reparto,pelicula,actor
WHERE actor.nombre='John Travolta'
AND actor_id=actor.id
AND pelicula_id=pelicula.id
ORDER BY puntuacion desc;
?
20
Ambigüedad en los atributos
?
?
Método 2: variable tupla
Haz un chequeo de consistencia, Si hay dos actores
con el mismo nombre y distinto ID puede ser un
error
SELECT Star1.nombre, Star1.id,
Star2.nombre, Star2.id
FROM actor Star1, actor Star2
WHERE Star1.nombre = Star2.nombre
AND Star1.id < Star2.id;
21
Ambigüedad en los atributos
?
pi
En que se diferencia esta consulta de la
anterior
SELECT Star1.nombre, Star1.id,
Star2.nombre, Star2.id
FROM actor Star1, actor Star2
WHERE Star1.nombre = Star2.nombre
AND Star1.id <> Star2.id;
22
NATURAL join etc
?
Dejar esto para subqueries
23
Combinando Consultas
?
?
?
?
Union: unión
Intersect: interseccion
Except: resta
Estos operadores eliminan los dupicados
?
?
?
Si se usa ALL los duplicados no se eliminan: e.g., UNION ALL
Las subconsultas deben ser compatibles
Actores comunes a las películas Star Trek IV y Star Trek V
(SELECT nombre FROM pelicula,actor,reparto WHERE
titulo LIKE 'Star Trek V:%' AND
pelicula_id=pelicula.id AND actor_id=actor.id)
INTERSECT (SELECT nombre FROM pelicula,actor,reparto
WHERE titulo LIKE 'Star Trek IV:%' AND
pelicula_id=pelicula.id AND actor_id=actor.id );
24
Ejemplos
copiar tablas
?
Lista de películas con al menos 5000 votos.
SELECT titulo
FROM pelicula
WHERE votos > 5000;
?
Año de estreno de ‘Citizen Kane’.
SELECT agno
FROM pelicula
WHERE titulo = 'Citizen Kane';
25
Ejemplos
?
Titulo y puntuación de las películas de la ‘Police
Academy…’.
SELECT titulo, puntuacion
FROM pelicula
WHERE titulo LIKE 'Police Academy%';
?
Nombre y puntuación de las películas que
contienen la palabra 'Dog' .
(similar to)
SELECT titulo, puntuacion
FROM pelicula
WHERE (titulo LIKE '%dog%') OR (titulo
LIKE '%Dog%');
26
Ejemplos!!!!!!!
?
Películas en las que aparece 'Harrison Ford' y
no es protagonista.
SELECT titulo
FROM actor, pelicula, reparto
WHERE nombre = 'Harrison Ford' AND
actor.id=actor_id AND
pelicula_id=pelicula.id AND ord <> 1;
?
Reparto de 'Alien' .
SELECT nombre
FROM actor, reparto, pelicula
WHERE titulo = 'Alien'
AND pelicula.id = pelicula_id
AND actor_id = actor.id;
27
Ejemplos
?
Películas en las que aparece 'Harrison Ford'
pero no es el protagonista.
SELECT titulo
FROM actor, pelicula, reparto
WHERE nombre = 'Harrison Ford'
AND actor.id=actor_id
AND pelicula_id=pelicula.id
AND ord <> 1;
28
Ejemplos
?
Películas filmadas en 1962 y sus
protagonistas
SELECT titulo, nombre
FROM pelicula, reparto, actor
WHERE agno=1962 AND
pelicula.id=pelicula_id AND
ord=1 AND
actor_id=actor.id;
29
Ejemplo del Capítulo Siguiente
?
Año en que ‘John Travolta’ extrenó más peliculas
?
?
Primero encontremos los años y títulos de sus películas
SELECT titulo, agno
FROM pelicula, reparto, actor
WHERE pelicula.id=pelicula_id AND
actor.id=actor_id AND
nombre= 'John Travolta';
Luego agrupémoslas.
SELECT COUNT(titulo), agno
FROM pelicula, reparto, actor
WHERE pelicula.id=pelicula_id AND
actor.id=actor_id AND
nombre= 'John Travolta'
GROUP BY agno ORDER BY 1;
30
Ejemplo del Capítulo Siguiente
?
Nombres y estrellas de todas las películas en las que
actuó 'Julie Andrews'
?
?
Primero encontremos los id de sus películas
SELECT pelicula_id
FROM reparto, actor
WHERE actor_id=actor.id AND
nombre='Julie Andrews';
y luego los nombres de las películas y las estrellas
SELECT titulo, nombre
FROM pelicula, reparto, actor
WHERE pelicula_id=pelicula.id AND
actor_id=actor.id AND ord=1
AND pelicula.id IN (SELECT pelicula_id
FROM reparto, actor
WHERE actor_id=actor.id AND
nombre='Julie Andrews');
31
Ejemplos
?
Actores que han trabajado con 'Orson
Welles'
?
Primero encontremos los id de sus películas
SELECT pelicula_id
FROM reparto, actor
WHERE actor_id=actor.id AND
nombre= 'Orson Welles';
y luego los nombres de quien a trabajado en esas películas
SELECT nombre, titulo
FROM actor, reparto, pelicula
WHERE actor.id=actor_id AND
pelicula_id=pelicula.id AND
pelicula_id IN (SELECT pelicula_id
FROM reparto, actor WHERE actor_id=actor.id AND
nombre='Orson Welles');
?
32