PDF de programación - Basesdatos prac 4 combinar

Sistemas de Información II – Práctica IV: Consultas multi-tabla

Carlos Castillo / Victor Pascual – [email protected]

1. Base de datos de ejemplo

Figura 1: Base de datos

1

Esta base de datos está compuesta de varias tablas que se muestran en la Figura 1. Impriman esa hoja,

porque vamos a utilizar varias tablas simultáneamente y no es fácil seguirlo en la pantalla.

En la cuenta SSH que usamos hay un fichero instpubs.txt que contiene una base de datos. Miren el
fichero instpubs.sql. Este fichero (convertido del SQL de SQL server al SQL de MySQL) contiene varias
instrucciones de creación de tablas que incluyen algúnas cláusulas especiales, ejemplo:

CREATE TABLE titles
(

char(6),
varchar(80)

title_id
title
...
PRIMARY KEY(title_id),
FOREIGN KEY(pub_id) REFERENCES publishers(pub_id)

NOT NULL,

) TYPE=INNODB;

La cláusula PRIMARY KEY indica, por ahora, que la columna debe tener un valor y que no se pueden

repetir valores, es decir, esta columna será una llave primaria en esa tabla.

La cláusula FOREING KEY indica, por ahora, que la columna apunta a una columna de otra tabla, y que

en esa otra tabla debe existir una fila con ese valor. Esto se llama una llave foránea.

La cláusula TYPE=INNODB tiene que ver con la forma en que MySQL almacena las tablas. Por motivos de
velocidad lo normal es que MySQL almacene las tablas en un formato que no permite llaves foráneas, y esto
es para que si lo permita.

Para cargar la base de datos, ejecutar:

cat instpubs.txt | mysql -f si2_XX -u si2_XX -p

Esta práctica son solamente consultas, algunas de ellas bastante complejas.

2. Actividades

Las actividades son sólo consultas, una consulta para cada pregunta, con un sólo SELECT, usando

cualquiera de las instrucciones que hemos usado hasta ahora.

Importante: no es válido incluir una constante en la consulta que no esté provista en el enunciado. Por
ejemplo, si les pido todos los libros escritos por el autor “Charlene Locksley”, obviamente no es válido buscar
manualmente su au id, en este caso “486-29-1786” y ponerlo en la consulta.

Título y número de unidades vendidas de todos los libros, ordenados por título del libro. Verificar: “Is
Anger...” tiene 4 unidades.

Nombre de la ciudad y número de unidades vendidas de todos los libros, ordenados de mayor venta a
menor venta. Debe poner un nombre a la columna para ordenar: “... SUM(qty) AS s ... ORDER BY
s” Verificar: “Remulade” es la mayor con 130 unidades.

Título y ventas totales (precio × cantidad) de todos los libros, ordenados por ventas totales. Verificar:
“Secrets ...” es el segundo mayor con 1000.

Autor (nombre y apellido), título y ventas totales (precio × cantidad) de todos los libros, ordenados
por ventas totales. Verificar: “Secrets ...” es el segundo mayor con 1000.

Nombre y número de empleados de las tres descripciones de trabajo que tienen más empleados. Verificar:
la segunda y la tercera son dos tipos de “manager” que están empatados en número de empleados.

Nombre y apellido de todos los empleados que tienen como descripción de su trabajo “Publisher”.
Verificar: son siete.

2

Nombres de las editoriales con las cuales ha trabajado “Marjorie Green”. Esta consulta involucra
cuatro tablas, pero es fácil, simplemente hay que incluir cláusulas WHERE para cada par de tablas que
compartan una columna. Verificar: son dos editoriales.

Apellido, nombre y precio promedio de sus libros de todos los autores. Verificar: el menor precio
promedio lo tiene “DeFrance”.

Apellido, nombre y ventas totales de sus libros de todos los autores, ordenados por ventas totales
(cantidad de libros multiplicada por precio) de mayor a menor. Verificar: “Albert Ringer’ es el de
mayor ventas.

Nombre de cada editorial y cada tienda que ha vendido algún libro de esa editorial. Es necesario usar
distinct. Verificar: sólo hay 1 editorial que ha vendido libros en 5 tiendas.

Nombre de cada editorial y número de tiendas en que ha vendido libros esa editorial. Es necesario
usar distinct, puede usar distinct dentro del count, ejemplo count(distinct stor name). Verificar:
los números son 4, 4 y 5.

Consejo: (1) lee la pregunta (2) anota en un papel las tablas y columnas involucradas (3) si no te resulta
escribe una consulta más simple que la pedida – ej.: si preguntamos por precio, preguntar por cantidad (4)
agrega la condición de ordenamiento al final (5) resuelve las preguntas fáciles primero. Recuerda siempre
unir con una condición WHERE los pares de tablas que tengan elementos en común: si hay K tablas que
unir, se necesitan al menos K − 1 condiciones para unirlas.

Inglés: publisher editorial, author author, sales ventas, qty cantidad, price precio, job trabajo, job desc

descripción del trabajo.

3. RESULTADO

Lo que se debe entregar son los comandos SQL que ejecuten todas las actividades de esta sesión.
El resultado de esta práctica debe entregarse hoy vía Campus Global.

3