T11 Soluciones a los ejercicios

Soluciones

T11.001- Listado de los códigos de los artículos Samsung que han sido pedidos.

SELECT a.cod 
FROM articulo a 
WHERE marca = 'Samsung' AND cod IN
	(SELECT articulo FROM linped);

Solución alternativa:

SELECT a.cod 
FROM articulo a 
WHERE marca = 'Samsung' AND EXISTS
	(SELECT 1 FROM linped l WHERE a.cod = l.articulo);

-- otra solución
SELECT DISTINCT a.cod 
FROM articulo a, linped l 
WHERE a.cod = articulo AND marca = 'Samsung';

T11.002- Utilizando operadores de conjuntos obtener los nombres de los artículos que sean cámaras compactas con visor electrónico o televisores CRT.

SELECT nombre FROM camara c, articulo a 
	WHERE c.cod = a.cod and tipo LIKE '%compacta%visor%electrónico%' 
UNION 
SELECT nombre FROM tv t,articulo a 
	WHERE t.cod = a.cod and panel LIKE '%televisor%CRT%';

Solución alternativa:

SELECT nombre FROM camara c 
	JOIN articulo a ON (c.cod = a.cod) 
	WHERE tipo LIKE '%compacta%visor%electrónico%' 
UNION 
SELECT nombre FROM tv t 
	JOIN articulo a ON (t.cod = a.cod) 
	WHERE panel LIKE '%televisor%CRT%';

T11.003- Utilizando operadores de conjuntos obtener el nombre de los usuarios, la localidad y la provincia de los usuarios que sean de un pueblo que contenga 'San Vicente' o que sean de la provincia de 'Valencia'.

SELECT u.nombre, p.nombre, l.pueblo 
   FROM usuario u, localidad l, provincia p 
	WHERE u.pueblo = l.codm and u.provincia = l.provincia and l.provincia = p.codp and l.pueblo LIKE '%San Vicente%' 
UNION 
SELECT u.nombre, p.nombre, l.pueblo 
   FROM usuario u, localidad l, provincia p
	WHERE u.pueblo = l.codm AND u.provincia = l.provincia and l.provincia = p.codp and p.nombre LIKE '%Valencia%';

Solución alternativa:

SELECT u.nombre, p.nombre, l.pueblo FROM usuario u 
	JOIN localidad l ON (u.pueblo = l.codm AND u.provincia = l.provincia) 
	JOIN provincia p ON l.provincia = p.codp 
	WHERE l.pueblo LIKE '%San Vicente%' 
UNION 
SELECT u.nombre, p.nombre, l.pueblo FROM usuario u 
	JOIN localidad l ON (u.pueblo = l.codm AND u.provincia = l.provincia)
	JOIN provincia p ON l.provincia = p.codp
	WHERE p.nombre LIKE '%Valencia%';

T11.004- Nombre y email de los usuarios de Asturias que tengan la misma dirección de envió que de residencia (que no tengan dirección de envío).

SELECT u.nombre, email 
FROM usuario u, provincia p
WHERE u.provincia = codp 
  AND p.nombre = 'Asturias' 
  AND email NOT IN (SELECT email FROM direnvio)
Solución alternativa:
SELECT u.nombre, email 
FROM usuario u 
JOIN provincia p ON (u.provincia = codp)
WHERE p.nombre = 'Asturias' 
  AND email NOT IN (SELECT email FROM direnvio);
En realidad habría que comparar también dirección de residencia y dirección de envío de aquellos que sí la tienen pero lo dejamos simplemente en que "no tienen dirección de envío".

T11.005- Código, nombre y marca de los objetivos con focales de 500 o 600 mm para las marcas de las que no se ha solicitó ningún artículo en el mes de noviembre de 2010.

SELECT a.cod, nombre, marca 
FROM objetivo o, articulo a 
WHERE a.cod = o.cod AND (focal = '500 mm' OR focal = '600 mm') 
	AND marca NOT IN 
		(SELECT marca 
		FROM pedido p, linped l, articulo 
		where p.numpedido=l.numpedido and articulo= cod 
		      and year(fecha)=2010 and month(fecha)=11)

T11.006- Código y pvp de los artículos 'Samsung' que tengan pvp y que no tengan pedidos.

SELECT a.cod, pvp 
	FROM articulo a 
	WHERE marca = 'Samsung' AND pvp IS NOT NULL AND a.cod NOT IN 
		(select articulo from linped);

T11.007- Utilizando operadores de conjuntos, muestra los nombres de los artículos que estén en un pack.

SELECT nombre 
FROM articulo 
WHERE cod IN 
	(SELECT articulo FROM ptienea);

Solución alternativa:

SELECT nombre 
FROM articulo 
WHERE EXISTS 
	(SELECT 1 FROM ptienea WHERE cod = articulo);

T11.008- Utilizando el producto cartesiano, obtener los nombres de las localidades con 2 o más usuarios (sin usar group by).

SELECT distinct l.pueblo 
FROM usuario u1, usuario u2, localidad l 
WHERE u1.email != u2.email 
  AND u1.pueblo = u2.pueblo AND u1.provincia = u2.provincia 
  AND u1.pueblo=l.codm AND u1.provincia=l.provincia;

Solución alternativa:

Compáralo con la solución usando group by-having.

SELECT l.pueblo 
FROM localidad l, usuario u 
WHERE u.pueblo = l.codm AND u.provincia=l.provincia
GROUP BY l.codm, l.provincia, l.pueblo 
HAVING COUNT(*) >= 2;

T11.009- Los códigos de los artículos que están en stock, en la cesta y han sido pedidos.

SELECT DISTINCT s.articulo 
FROM stock s, cesta c, linped l 
WHERE s.articulo = c.articulo AND s.articulo = l.articulo;

T11.010- Código y nombre de los artículos, aunque estén repetidos, que aparezcan en un pack o en una cesta.

SELECT p.articulo, a.nombre 
FROM ptienea p, articulo a 
WHERE p.articulo = a.cod 
UNION ALL 
SELECT c.articulo, a.nombre 
FROM cesta c, articulo a 
WHERE c.articulo = a.cod;

Solución alternativa:

SELECT p.articulo, a.nombre 
FROM ptienea p 
	JOIN articulo a ON (p.articulo = a.cod) 
UNION ALL 
SELECT c.articulo, a.nombre 
FROM cesta c 
	JOIN articulo a ON (c.articulo = a.cod);

T11.011- Códigos de artículos que están en alguna cesta o en alguna línea de pedido.

select  articulo from cesta
union
select  articulo from linped;

Solución alternativa:

select cod 
from articulo
where cod in (select articulo from cesta) 
   or cod in (select articulo from linped);

select cod 
from articulo
where exists (select 1 from cesta where articulo=cod) 
   or exists (select 1 from linped where articulo=cod);

T11.012- Email y nombre de los usuarios que no han hecho ningún pedido o que han hecho sólo uno.

select email, nombre
from usuario
where email not in (select usuario from pedido)
union
select email, nombre
from usuario, pedido
where email=usuario
group by email, nombre
having count(*)=1;

T11.013- Apellidos que se repitan en más de un usuario (sin utilizar group by).

select distinct u1.apellidos
from usuario u1, usuario u2
where u1.email <> u2.email and u1.apellidos= u2.apellidos;

Solución alternativa:

Compáralo con la solución usando group by-having.

select apellidos
from usuario
group by apellidos
having count(*) > 1;

T11.014- Parejas de nombres de provincia que tienen algún pueblo que se llama igual, junto con el nombre del pueblo.

select p1.nombre, p2.nombre, l1.pueblo
from provincia p1, provincia p2, localidad l1, localidad l2
where p1.codp<>p2.codp 
  and p1.codp=l1.provincia 
  and p2.codp=l2.provincia 
  and l1.pueblo=l2.pueblo;

Observad lo que tarda en realizar la consulta comparado con otras.

T11.015- Código y nombre de los artículos que en stock están "Descatalogado" o que no se han solicitado en ningún pedido.

select cod, nombre
from articulo
where cod in(
  select articulo from stock where entrega='Descatalogado'
  union
  select cod
  from articulo where cod not in (select articulo from linped))

T11.016- Email, nombre y apellidos de los usuarios que han solicitado televisores pero nunca han solicitado cámaras.

select email, nombre, apellidos
from usuario
where email in
  (select usuario from pedido p, linped l, tv 
   where p.numpedido=l.numpedido and articulo=cod)
 and email not in 
  (select usuario from pedido p, linped l, camara 
   where p.numpedido=l.numpedido and articulo=cod);

T11.017- Usuarios que han solicitado pedidos de coste total superior a 10000 (por pedido) o que han solicitado más de 5 artículos distintos entre todos sus pedidos.

select usuario
from linped l, pedido p where l.numpedido=p.numpedido
group by p.numpedido, usuario
having sum(cantidad*importe)>10000
union 
select usuario
from linped l, pedido p where l.numpedido=p.numpedido
group by usuario
having count(distinct articulo)>5;

T11.018- Obtener un listado en el que figuren para todos los usuarios: su email, su nombre y sus apellidos junto con una frase en la que se muestre lo que a continuación se indica:
- para los usuarios con un coste total entre todos sus pedidos superior a 10000 mostraremos GRAN CLIENTE
- para los que el coste total está entre 6000 y 10000 mostraremos CLIENTE MEDIO
- para los que el coste total es inferior a 6000 mostraremos COMPRA POCO
- para los que no han hecho ningún pedido mostraremos ** NO HA COMPRADO NUNCA.

El listado se ordenará por apellidos.

select email, nombre, apellidos, '  GRAN CLIENTE'
from usuario, linped l, pedido p where l.numpedido=p.numpedido and p.usuario=email
group by email, nombre, apellidos
having sum(cantidad*importe)>10000
UNION
select email, nombre, apellidos, '  CLIENTE MEDIO'
from usuario, linped l, pedido p where l.numpedido=p.numpedido and p.usuario=email
group by email, nombre, apellidos
having sum(cantidad*importe) between 6000 and 10000
UNION
select email, nombre, apellidos, '  COMPRA POCO'
from usuario, linped l, pedido p where l.numpedido=p.numpedido and p.usuario=email
group by email, nombre, apellidos
having sum(cantidad*importe)<6000
UNION
select email, nombre, apellidos, '  ** NO HA COMPRADO NUNCA'
from usuario
where email not in (select usuario from pedido)
order by 3;

T11.019- ¿Hay alguna fila en la tabla marca?

select exists (select * from marca)

T11.020- Email y nombre de los usuarios que no han pedido ninguna cámara.

select email, nombre
from usuario u
where email not in 
  (select usuario 
   from linped l, pedido p, camara c
   where l.numpedido=p.numpedido and l.articulo = c.cod);

Solución alternativa:

select email, nombre
from usuario u
where not exists 
 (select 1 
  from linped l, pedido p, camara c
  where email=p.usuario 
    and l.numpedido=p.numpedido and c.cod=l.articulo);

T11.021- Email y nombre de los usuarios que, habiendo realizado algún pedido, no han pedido ninguna cámara.

select email, nombre
from usuario u
where email in (select usuario from pedido) 
and email not in 
  (select usuario 
   from linped l, pedido p, camara c
   where l.numpedido=p.numpedido and l.articulo = c.cod);

Solución alternativa:

select email, nombre
from usuario u 
where email in (select usuario from pedido) 
and not exists 
 (select 1 
  from linped l, pedido p, camara c
  where email=p.usuario 
    and l.numpedido=p.numpedido and c.cod=l.articulo);

Obsérvese el uso de usuario.email en la subconsulta del exists.

T11.022- Código y nombre del artículo que ha sido incluído en todos los pedidos.

select cod,nombre
from articulo a 
where not exists 
  (select 1 
   from pedido p
   where not exists 
      (select 1 
       from linped l
       where l.numpedido=p.numpedido
         and l.articulo=a.cod))

Solución alternativa:

select articulo
from linped l
group by articulo
having count(distinct numpedido) = 
  (select count(*) from pedido);

"Artículo tal que NO existe pedido que NO lo incluya".

T11.024- ¿Hay alguna fila en la tabla marca? Si la respuesta es positiva, que muestre la palabra "sí".

select 'sí' respuesta
from dual
where exists (select 1 from marca);
"Dual" es una tabla ficticia del sistema que sirve precisamente para esto, para consultas que no utilizan ninguna tabla.

T11.025- ¿Hay alguna fila en la tabla memoria? Si la respuesta es negativa, que muestre la palabra "no".

select 'no' respuesta
from dual
where not exists (select 1 from memoria);
Solución alternativa:
select 'sí' respuesta
where exists (select 1 from marca);
"Dual" es una tabla ficticia del sistema que sirve precisamente para esto, para consultas que no utilizan ninguna tabla. En realidad, se utiliza por compatibilidad con otros motores de BD, en MariaDB/MySQL no es imprescindible (véase la solución alternativa).

T11.026- Pedidos (sin duplicados) que incluyen cámaras y televisiones.

select * from pedido p
where numpedido in 
 (select numpedido from linped l, camara c 
  where l.articulo=c.cod)
and numpedido in 
 (select numpedido from linped l, tv  
  where l.articulo=tv.cod);

Solución alternativa:

select * from pedido p
where exists (select 1 from linped l where l.numpedido=p.numpedido
                                       and articulo in (select cod from tv))
  and exists (select 1 from linped l where l.numpedido=p.numpedido
                                       and articulo in (select cod from camara));

T11.027- Pedidos (sin duplicados) que incluyen cámaras y objetivos.

select * from pedido p
where numpedido in 
 (select numpedido from linped l, camara c 
  where l.articulo=c.cod)
and numpedido in 
 (select numpedido from linped l, objetivo o  
  where l.articulo=o.cod);

Solución alternativa:

select * from pedido p
where exists (select 1 from linped l where l.numpedido=p.numpedido
                                       and articulo in (select cod from camara))
  and exists (select 1 from linped l where l.numpedido=p.numpedido
                                       and articulo in (select cod from objetivo));

T11.028- Concatenación natural de artículos y memorias.

select * from articulo natural join memoria;

T11.029- Código de artículo, nombre, pvp, marca y tipo de la concatenación natural de artículos y memorias.

select cod,nombre,pvp,marca,tipo from articulo natural join memoria;

T11.030- Código de artículo, nombre, pvp, marca y tipo de la concatenación natural de artículos y memorias, si el tipo es "Compact Flash".

select cod,nombre,pvp,marca,tipo 
from articulo natural join memoria
where tipo='Compact Flash';

Solución alternativa:

select a.cod,a.nombre,a.pvp,a.marca,m.tipo 
from articulo a, memoria m
where a.cod=m.cod and tipo='Compact Flash';

T11.031- Concatenación natural de pedido y linped, ordenado por fecha de pedido.

select * 
from pedido natural join linped
order by fecha;

Solución alternativa:

select * 
from pedido natural join linped
order by fecha;

T11.032- Comprueba que la concatenación natural de cesta y pack produce un producto cartesiano.

select * 
from cesta natural join pack;
Solución alternativa:
select *
from cesta, pack;
En CESTA se utiliza el nombre de columna "articulo" mientras que en pack se utiliza "cod": no hay columnas comunes.

T11.034- ¿Por qué la concatenación natural de usuario y direnvio resulta en una tabla vacía?

select * 
from usuario natural join direnvio;
Entre USUARIO y DIRENVIO hay varias columnas comunes: "email", "calle", "calle2", "codpos", "pueblo" y "provincia".
Más útil sería concatenar únicamente por "email"; si nos empeñamos en utilizar la concatenación natural la única solución es renombrar las columnas:
select *
from usuario
natural join
(select email,calle dcalle,calle2 dcalle2,codpos dcodpos,pueblo dpueblo,provincia dprovincia from direnvio) as d