¡Descarga Ejercicios Resueltos de Álgebra Relacional y SQL y más Guías, Proyectos, Investigaciones en PDF de Programación de Bases de Datos solo en Docsity!
EJERCICIOS RESUELTOS
Presentamos a continuación una serie de ejercicios de consulta sobre la base de datos
formada por las tablas de PROVEEDORES, COMPONENTES, ARTICULOS y ENVIOS. En
dicha base de datos está almacenada la siguiente información:
PROVEEDORES ENVIOS
P# PNOMBRE CATEGORIA CIUDAD
P1 CARLOS 20 SEVILLA
P2 JUAN 10 MADRID
P3 JOSE 30 SEVILLA
P4 INMA 20 SEVILLA
P5 EVA 30 CACERES
COMPONENTES
C# CNOMBRE COLOR PESO CIUDAD
C1 X3A ROJO 12 SEVILLA
C2 B85 VERDE 17 MADRID
C3 C4B AZUL 17 MALAGA
C4 C4B ROJO 14 SEVILLA
C5 VT8 AZUL 12 MADRID
C6 C30 ROJO 19 SEVILLA
ARTICULOS
T# TNOMBRE CIUDAD
T1 CLASIFICADORA MADRID
T2 PERFORADORA MALAGA
T3 LECTORA CACERES
T4 CONSOLA CACERES
T5 MEZCLADORA SEVILLA
T6 TERMINAL BARCELONA
T7 CINTA SEVILLA
PROVEEDORES .- Representa los datos de proveedores de componentes para la fabricación de
artículos y su ciudad de residencia.
COMPONENTES.- Indica la información de piezas utilizadas en la fabricación de diferentes
artículos, indicándose el lugar de fabricación de dichos componentes.
ARTICULOS.- Información sobre los diferentes artículos que se fabrican y el lugar de montaje
del mismo.
ENVIOS.- Suministros realizados por los diferentes proveedores de determinadas cantidades de
componentes asignadas para la elaboración del artículo correspondiente.
Para resolver cada consulta, debes seguir los siguientes pasos:
a) Lee atentamente el enunciado, hasta que hayas creído entender qué se pide.
P# C# T# CANTIDAD
P1 C1 T1 200
P1 C1 T4 700
P2 C3 T1 400
P2 C3 T2 200
P2 C3 T3 200
P2 C3 T4 500
P2 C3 T5 600
P2 C3 T6 400
P2 C3 T7 800
P2 C5 T2 100
P3 C3 T1 200
P3 C4 T2 500
P4 C6 T3 300
P4 C6 T7 300
P5 C2 T2 200
P5 C2 T4 100
P5 C5 T4 500
P5 C5 T7 100
P5 C6 T2 200
P5 C1 T4 100
P5 C3 T4 200
P5 C4 T4 800
P5 C5 T5 400
P5 C6 T4 500
b) Obtén manualmente los resultados para los datos de ejemplo.
c) Intenta representar la consulta mediante una expresión algebraica relacional. (Pueden
existir numerosas expresiones algebraicas relacionales equivalentes que respondan a la
misma consulta).
c) Expresa la consulta mediante una sentencia SELECT de SQL.
d) Pueden existir distintas formas de escribir una consulta SQL que resuelva el apartado
anterior. ¿Se te ocurre alguna otra forma de expresión de la sentencia en SQL para
representar la consulta?.
No te fíes de los datos particulares del ejemplo; puede que obtengas el mismo resultado que
la solución correcta, pero que sea errónea tu consulta para otros datos diferentes. Tu solución
debe trabajar correctamente para cualquier relación consistente que pueda almacenar nuestra
base de datos.
Para cada ejercicio se aporta una posible solución pero es posible que existan varias que
sean también correctas.
EJERCICIOS
1. Obtener todos los detalles de todos los artículos de CACERES.
2. Obtener todos los valores de P# para los proveedores que abastecen el artículo T1.
3. Obtener la lista de pares de atributos ( COLOR, CIUDAD ) de la tabla componentes
eliminando los pares duplicados.
4. Obtener de la tabla de artículos los valores de T# y CIUDAD donde el nombre de la
ciudad acaba en D o contiene al menos una E.
5. Obtener los valores de P# para los proveedores que suministran para el artículo T1 el
componente C1.
6. Obtener los valores de TNOMBRE en orden alfabético para los artículos abastecidos por
el proveedor P1.
7. Obtener los valores de C# para los componentes suministrados para cualquier artículo de
MADRID.
8. Obtener todos los valores de C# de los componentes tales que ningún otro componente
tenga un valor de peso inferior.
9. Obtener los valores de P# para los proveedores que suministren los artículos T1 y T2.
10. Obtener los valores de P# para los proveedores que suministran para un artículo de
SEVILLA o MADRID un componente ROJO.
11. Obtener, mediante subconsultas, los valores de C# para los componentes suministrados
para algún artículo de SEVILLA por un proveedor de SEVILLA.
12. Obtener los valores de T# para los artículos que usan al menos un componente que se
puede obtener con el proveedor P1.
13. Obtener todas las ternas ( CIUDAD, C#, CIUDAD ) tales que un proveedor de la primera
ciudad suministre el componente especificado para un artículo montado en la segunda
ciudad.
14. Repetir el ejercicio anterior pero sin recuperar las ternas en los que los dos valores de
15. Obtener el número de suministros, el de artículos distintos suministrados y la cantidad
total de artículos suministrados por el proveedor P2.
16. Para cada artículo y componente suministrado obtener los valores de C#, T# y la cantidad
total correspondiente.
17. Obtener los valores de T# de los artículos abastecidos al menos por un proveedor que no
viva en MADRID y que no esté en la misma ciudad en la que se monta el artículo.
18. Obtener los valores de P# para los proveedores que suministran al menos un componente
suministrado al menos por un proveedor que suministra al menos un componente ROJO.
19. Obtener los identificadores de artículos, T#, para los que se ha suministrado algún
componente del que se haya suministrado una media superior a 320 artículos.
20. Seleccionar los identificadores de proveedores que hayan realizado algún envío con
Cantidad mayor que la media de los envíos realizados para el componente a que
corresponda dicho envío.
21. Seleccionar los identificadores de componentes suministrados para el artículo 'T2' por el
proveedor 'P2'.
22. Seleccionar todos los datos de los envíos realizados de componentes cuyo color no sea
'ROJO'.
23. Seleccionar los identificadores de componentes que se suministren para los artículos 'T1'
y 'T2'.
24. Seleccionar el identificador de proveedor y el número de envíos de componentes de color
'ROJO' llevados a cabo por cada proveedor.
25. Seleccionar los colores de componentes suministrados por el proveedor 'P1'.
26. Seleccionar los datos de envío y nombre de ciudad de aquellos envíos que cumplan que
el artículo, proveedor y componente son de la misma ciudad.
27. Seleccionar los nombres de los componentes que son suministrados en una cantidad total
superior a 500.
28. Seleccionar los identificadores de proveedores que residan en Sevilla y no suministren
más de dos artículos distintos.
29. Seleccionar los identificadores de artículos para los cuales todos sus componentes se
fabrican en una misma ciudad.
30. Seleccionar los identificadores de artículos para los que se provean envíos de todos los
componentes existentes en la base de datos.
31. Seleccionar los códigos de proveedor y artículo que suministran al menos dos
componentes de color 'ROJO'.
32. Propón tu mismo consultas que puedan realizarse sobre esta base de datos de ejemplo.
Intenta responderla, y si te parece un problema interesante o no estás seguro de su
solución, puedes exponerlos en la clases prácticas para su resolución en grupo.
SOLUCIONES A LOS EJERCICIOS
1. Obtener todos los detalles de todos los artículos de CACERES.
ciudad=´CACERES´
( Artículos)
SELECT *
FROM artículos
WHERE ciudad = 'CACERES';
2. Obtener todos los valores de P# para los proveedores que abastecen el artículo T1.
p#
( t#='T1'
(Envíos))
SELECT DISTINCT p#
FROM envios
WHERE t#='T1';
3. Obtener la lista de pares de atributos ( COLOR, CIUDAD ) de la tabla componentes
eliminando los pares duplicados.
color, ciudad
( Componentes)
SELECT DISTINCT color , ciudad
FROM componentes;
4. Obtener de la tabla de artículos los valores de T# y CIUDAD donde el nombre de la
ciudad acaba en D o contiene al menos una E.
t#, ciudad
( (ciudad LIKE '%D') OR (ciudad LIKE '%E%')
(Artículos))
SELECT t#, ciudad
FROM artículos
WHERE ciudad LIKE '%D' OR
ciudad LIKE '%E%';
5. Obtener los valores de P# para los proveedores que suministran para el artículo T
el componente C1.
p#
( (^) (t#='T1') AND (c#='C1')
(Envíos))
SELECT p#
FROM envios
WHERE t#='T1' AND c#='C1';
P#
P
T#
TNOMBRE CIUDAD
T3 LECTORA CACERES
T4 CONSOLA CACERES
P#
P
P
P
COLOR CIUDAD
AZUL MADRID
AZUL MALAGA
ROJO SEVILLA
T#
CIUDAD
T1 MADRID
T3 CACERES
T4 CACERES
T5 SEVILLA
T6 BARCELONA
externo por la derecha / y si es un join externo completo //
10. Obtener los valores de P# para los proveedores que suministran para un artículo de
SEVILLA o MADRID un componente ROJO.
p#
( ( Color='ROJO'
(Componentes))* (Envíos* (^) t#
(( (ciudad='MADRID' OR Ciudad='SEVILLA')
(Artículos))
SELECT p#
FROM envios e, componentes c, artículos a
WHERE e.c#=c.c# AND e.t#=a.t# AND
c.color='ROJO' AND
a.ciudad IN('SEVILLA', 'MADRID');
SELECT p#
FROM componentes c JOIN
2 (envios e
NATURAL JOIN artículos a) ON c.c#=e.c#
WHERE c.color='ROJO' AND
a.ciudad IN('SEVILLA', 'MADRID');
11. Obtener, mediante subconsultas, los valores de C# para los componentes
suministrados para algún artículo de SEVILLA por un proveedor de SEVILLA.
c#
( ( Ciudad='SEVILLA'
(Proveedores)) * c#,p#
(Envíos * ( Ciudad='SEVILLA'
(Artículos))) )
SELECT c#
FROM envios
WHERE t# IN ( SELECT t#
FROM artículos
WHERE ciudad='SEVILLA')
AND p# IN ( SELECT p#
FROM proveedores
WHERE ciudad='SEVILLA');
12. Obtener los valores de T# para los artículos que usan al menos un componente que
se puede obtener con el proveedor P1.
t#
( Envíos * c#
( p#='P1'
(Envíos)) )
SELECT DISTINCT t#
FROM envios
WHERE c# IN ( SELECT DISTINCT c#
FROM envios
WHERE p#='P1' );
13. Obtener todas las ternas ( CIUDAD, C#, CIUDAD ) tales que un proveedor de la
primera ciudad suministre el componente especificado para un artículo montado en
la segunda ciudad.
2 Observese que se ha hecho un JOIN en lugar de un NATURAL JOIN para que se realice sólo por el
P#
P
P
T#
T
T
C#
C
Articulo_ciudad (p#, c#, ciudada) p#,c#,ciudad
(Envíos * Artículos)
ciudad, c#, ciudada
( Proveedores * Articulo_ciudad )
SELECT p.ciudad ,e.c#, a.ciudad
FROM envios e, proveedores p , artículos a
WHERE e.p#=p.p# AND e.t#=a.t#;
SELECT p.ciudad ,e.c#, a.ciudad
FROM proveedores p JOIN (envios e NATURAL
JOIN artículos a) ON p.p#=e.p#;
14. Repetir el ejercicio anterior pero sin recuperar las ternas en los que los dos valores
de ciudad sean los mismos.
Articulo_ciudad (p#, c#, ciudada) p#,c#,ciudad
(Envíos * Artículos)
ciudad, c#, ciudada
( ciudad<> ciudada
(Proveedores * Articulo_ciudad ))
SELECT p.ciudad ,c#, a.ciudad
FROM envios e, proveedores p , articulos a
WHERE e.p#=p.p# AND
e.t#=a.t# AND
p.ciudad <> a.ciudad;
SELECT p.ciudad ,e.c#, a.ciudad
FROM proveedores p JOIN (envios e NATURAL JOIN
artículos a) ON p.p#=e.p# and p.ciudad <>a.ciudad;
CIUDAD C# CIUDAD
SEVILLA C1 MADRID
SEVILLA C1 CACERES
MADRID C3 MADRID
MADRID C3 MALAGA
MADRID C3 CACERES
MADRID C3 CACERES
MADRID C3 SEVILLA
MADRID C3 BARCELONA
MADRID C3 SEVILLA
MADRID C5 MALAGA
SEVILLA C3 MADRID
SEVILLA C4 MALAGA
SEVILLA C6 CACERES
SEVILLA C6 SEVILLA
CACERES C2 MALAGA
CACERES C2 CACERES
CACERES C5 CACERES
CACERES C5 SEVILLA
CACERES C6 MALAGA
CACERES C1 CACERES
CACERES C3 CACERES
CACERES C4 CACERES
CACERES C5 SEVILLA
CIUDAD C# CIUDAD
SEVILLA C1 MADRID
SEVILLA C1 CACERES
MADRID C3 MALAGA
MADRID C3 CACERES
MADRID C3 CACERES
MADRID C3 SEVILLA
MADRID C3 BARCELONA
MADRID C3 SEVILLA
MADRID C5 MALAGA
SEVILLA C3 MADRID
SEVILLA C4 MALAGA
SEVILLA C6 CACERES
CACERES C2 MALAGA
CACERES C5 SEVILLA
CACERES C6 MALAGA
15. Obtener el número de suministros, el de artículos distintos suministrados y la
cantidad total de artículos suministrados por el proveedor P2.
18. Obtener los valores de P# para los proveedores que suministran al menos un
componente suministrado al menos por un proveedor que suministra al menos un
componente ROJO.
p#
(Envios * ( c#
(Envíos * ( p#
(Envíos * ( Color='ROJO'
(Componentes)))))))
SELECT distinct p#
FROM envios
WHERE c# IN
(SELECT c#
FROM envios
WHERE p# IN
( SELECT p#
FROM envios NATURAL JOIN componentes
WHERE color='ROJO') );
19. Obtener los identificadores de artículos, T#, para los que se ha suministrado algún
componente del que se haya suministrado una media superior a 320 artículos.
t#
( Envios * ( c#
( A > 320
( (c#) AVG(cantidad)
A (Envíos) ) ) ) )
SELECT DISTINCT t#
FROM envios
WHERE c# IN ( SELECT c#
FROM envios
GROUP BY c#
HAVING AVG(cantidad) > 320 );
20. Seleccionar los identificadores de proveedores que hayan realizado algún envío con
Cantidad mayor que la media de los envíos realizados para el componente a que
corresponda dicho envío.
p#
( (Cantidad > A)
(Envíos *( (c#) AVG(cantidad)
A (Envíos) ) ))
SELECT distinct p#
FROM envios a
WHERE cantidad > (SELECT AVG(cantidad)
FROM envios b
WHERE b.c#=a.c# );
21. Seleccionar los identificadores de componentes suministrados para el artículo 'T2'
por el proveedor 'P2'.
c#
( (^) t#='T2' AND p#='P2'
(Envíos))
Nótese que es un error grave dar como solución la expresión:
c#
( t#='T2'
(Envíos)) c#
( p#='P2'
(Envíos))
SELECT c#
FROM envios
WHERE t# = 'T2' AND p# = 'P2';
P#
P
P
P
P
P
T#
T
T
T
T
T
T
P#
P
P
P
C#
C
C
22. Seleccionar todos los datos de los envíos realizados de componentes cuyo color no
sea 'ROJO'.
Envíos.*
(Envíos* ( Color <> 'ROJO'
(Componentes)) )
SELECT e.*
FROM envios e, componentes c
WHERE e.c# = c.c# AND color <> 'ROJO';
SELECT e.*
FROM envios e NATURAL JOIN componentes
WHERE color <> 'ROJO';
23. Seleccionar los identificadores de componentes que se suministren para los artículos
'T1' y 'T2'.
( c#
( t#='T1'
(Envíos)) ) ( c#
( t#='T2'
(Envíos))
Nótese que es un error grave dar como solución:
c#
( (^) t#='T1' AND t#='T2'
(Envíos))
SELECT c#
FROM envios
WHEREt# ='T1'
INTERSECT
SELECT c#
FROM envios
WHERE t# ='T2';
24. Seleccionar el identificador de proveedor y el número de envíos de componentes de
color 'ROJO' llevados a cabo por cada proveedor.
(p#) COUNT(*)
A ( Envíos * ( Color = 'ROJO'
(Componentes)) )
SELECT p#, count(*) A
FROM envios
WHEREc# IN ( SELECT c#
FROM componentes
WHERE color = 'ROJO')
GROUP BY p# ;
25. Seleccionar los colores de componentes suministrados por el proveedor 'P1'.
color
(Componentes * ( p# = 'P1'
(Envíos)) )
SELECT DISTINCT color
FROM componentes
WHEREc# IN ( SELECT DISTINCT c#
FROM envios
WHERE p# = 'P1');
C#
C
COLOR
ROJO
P# C# T# CANTIDAD
P5 C2 T2 200
P5 C2 T4 100
P2 C3 T1 400
P3 C3 T1 200
P2 C3 T2 200
P2 C3 T3 200
P2 C3 T4 500
P5 C3 T4 200
P2 C3 T5 600
P2 C3 T6 400
P2 C3 T7 800
P2 C5 T2 100
P5 C5 T4 500
P5 C5 T5 400
P# A
P1 2
P3 1
P4 2
P5 4
29. Seleccionar los identificadores de artículos para los cuales todos sus componentes se
fabrican en una misma ciudad.
t#
( ( C = 1)
( (t#) COUNT(DISTINCT ciudad)
C (Componentes * Envíos ) ) )
SELECT t#
FROM envios e,componentes c
WHERE e.c# = c.c#
GROUP BY t#
HAVING COUNT(DISTINCT ciudad) = 1 ;
SELECT t#
FROM envios NATURAL JOIN componentes
GROUP BY t#
HAVING COUNT(DISTINCT ciudad) = 1 ;
30. Seleccionar los identificadores de artículos para los que se provean envíos de todos
los componentes existentes en la base de datos.
t#
( Envíos c#
(Componentes))
SELECT t#
FROM Envíos
GROUP BY t#
HAVING COUNT(DISTINCT c#) = ( SELECT COUNT(*)
FROM Componentes) ;
31. Seleccionar los códigos de proveedor y artículo que suministran al menos dos
componentes de color 'ROJO'.
p#,t#
( (C > 1)
( (p#, t#) COUNT(*)
C ( Envíos * ( color='ROJO'
(Componentes))))
SELECT p#, t#
FROM envios e,componentes c
WHERE e.c# = c.c# AND c.color='ROJO'
GROUP BY p#, t#
HAVING COUNT (*) > 1;
SELECT p#, t#
FROM envios NATURAL JOIN componentes
WHERE .color='ROJO'
GROUP BY p#, t#
HAVING COUNT (*) > 1;
T#
T
T#
T
P# T#
P5 T
