Soluciones

English version (when available)

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); -- sin problema, linped.articulo no admite nulos
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 JOIN linped l ON a.cod = articulo 
WHERE marca = 'Samsung';

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

SELECT nombre FROM articulo WHERE cod IN (
SELECT cod FROM camara WHERE tipo LIKE '%compacta%visor%electrónico%' 
UNION 
SELECT cod FROM tv WHERE panel LIKE '%televisor%CRT%');
Solución alternativa: 
SELECT nombre 
FROM articulo a
WHERE EXISTS (
    SELECT 1 FROM camara c 
    WHERE c.cod = a.cod AND c.tipo LIKE '%compacta%visor%electrónico%'
)
OR EXISTS (
    SELECT 1 FROM tv t 
    WHERE t.cod = a.cod AND t.panel LIKE '%televisor%CRT%'
);

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

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%' OR p.nombre LIKE '%Valencia%';
No siempre es necesario UNION

T11.004- Nombre y email de los usuarios de Asturias que no tengan dirección de envío alternativa.

SELECT u.nombre, email 
FROM usuario u 
join provincia p on u.provincia = p.codp 
where p.nombre = 'Asturias' 
  AND email NOT IN (SELECT email FROM direnvio) -- sin problema, email no admite nulos
Solución alternativa: 
SELECT u.nombre, email 
FROM usuario u 
join provincia p on u.provincia = p.codp 
where p.nombre = 'Asturias' 
  AND NOT EXISTS (SELECT 1 FROM direnvio d WHERE u.email=d.email)

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 solicitó ningún artículo en el mes de noviembre de 2010.

SELECT a.cod, nombre, marca 
FROM objetivo o join articulo a on a.cod = o.cod 
WHERE (focal = '500 mm' OR focal = '600 mm') 
  AND marca IN ( -- marcas de las que no hay líneas de pedido del 11/2010 
	SELECT marca FROM marca
	except
	SELECT marca 
	FROM pedido p 
	JOIN linped l ON l.numpedido=p.numpedido 
	JOIN articulo a ON l.articulo=a.cod
        WHERE p.fecha BETWEEN '2010-11-01' and '2010-11-30'
);
Solución alternativa: 
SELECT a.cod, nombre, marca 
FROM objetivo o join articulo a on a.cod = o.cod 
WHERE (focal = '500 mm' or focal = '600 mm') 
  AND NOT exists (
     SELECT 1 
     FROM pedido p 
     JOIN linped l ON p.numpedido=l.numpedido
     JOIN articulo a2 ON l.articulo= a2.cod 
     WHERE fecha>='2010-11-01' and p.fecha<='2010-11-30'
       AND a.marca=a2.marca
  );
En este caso, el uso de IN no supone ningún problema porque el operando izquierdo del EXCEPT es MARCA.marca, clave primaria, por lo que la operación no devolverá nulos.
En cualquier caso, la forma de "olvidarse" de los nulos y el operador IN es recurrir a la solucíon alternativa con EXISTS.

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); -- sin problema, linped.articulo no permite nulos
Solución alternativa: 
SELECT a.cod, pvp 
FROM articulo a 
WHERE marca = 'Samsung' AND pvp IS NOT NULL 
  AND NOT EXISTS (SELECT 1 from linped l WHERE a.cod=l.articulo);

T11.007- Muestra los nombres de los artículos contenidos en algún 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);
-- -------------------
SELECT distinct nombre
FROM articulo a,ptienea p
where a.cod=p.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 -- que sean usuarios diferentes
  AND u1.pueblo = u2.pueblo AND u1.provincia = u2.provincia -- de la misma localidad
  AND u1.pueblo=l.codm AND u1.provincia=l.provincia; 
-- enlazado con LOCALIDAD para conseguir el nombre, me da igual si esde un usuario o del otro
Solución alternativa: 
Compáralo con la solución usando group by-having.

SELECT l.pueblo 
FROM localidad l JOIN usuario u ON 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. Ordénalos.

select articulo from stock
INTERSECT
select articulo from cesta
INTERSECT
select articulo from linped
order by articulo;
Solución alternativa: 
SELECT DISTINCT s.articulo 
FROM stock s
join cesta c on s.articulo = c.articulo 
join linped l on s.articulo = l.articulo
order by s.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 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;
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;

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 u
where NOT EXISTS (select 1 from pedido p where p.usuario=u.email)
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;
Solución alternativa: 
SELECT p1.nombre, p2.nombre, l1.pueblo
FROM localidad l1 JOIN localidad l2 ON l1.pueblo=l2.pueblo -- nombres iguales
                                   AND l1.provincia < l2.provincia -- códigos de provincia diferentes
JOIN provincia p1 ON p1.codp=l1.provincia -- necesitamos el nombre
JOIN provincia p2 ON p2.codp=l2.provincia -- y el otro nombre
Observad lo que tarda en realizar la consulta comparado con otras.
Se han comparado las provincias con "menor estricto": p1.codp < p2.codp. Con esto se consigue filtrar las filas que, en realidad, son información duplicada: Cuenca-Palmas (Las)-Moya y Palmas (Las)-Cuenca-Moya.

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 cod FROM articulo
    EXCEPT 
    SELECT articulo FROM linped
    UNION
    SELECT articulo FROM stock WHERE entrega = 'Descatalogado'
);
De la suboconsulta.
Observa que los operadores de conjunto se aplican en orden: primero "artículos no solicitados en ningún pedido" (EXCEPT) y, después, (UNION) "artículo descatalogados en STOCK".
Además, el nombre de la columna resultado es el de la primera consulta (ARTICULO.cod)

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 u
join pedido p ON u.email=p.usuario
join linped l ON l.numpedido=p.numpedido
join tv ON tv.cod=l.articulo 
EXCEPT
select email, nombre, apellidos
from usuario u
join pedido p ON u.email=p.usuario
join linped l ON l.numpedido=p.numpedido
join camara c ON c.cod=l.articulo

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 join pedido p on l.numpedido=p.numpedido
group by p.numpedido, usuario
having sum(cantidad*importe)>10000
UNION
select usuario
from linped l join pedido p on 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' tipo
from usuario join pedido p on p.usuario=email join linped l on l.numpedido=p.numpedido 
group by email, nombre, apellidos
having sum(cantidad*importe)>10000
UNION ALL -- los conjuntos son disjuntos, no hace falta buscar filas duplicadas
select email, nombre, apellidos, '  CLIENTE MEDIO'
from usuario join pedido p on p.usuario=email join linped l on l.numpedido=p.numpedido 
group by email, nombre, apellidos
having sum(cantidad*importe) between 6000 and 10000
UNION ALL
select email, nombre, apellidos, '  COMPRA POCO'
from usuario join pedido p on p.usuario=email join linped l on l.numpedido=p.numpedido 
group by email, nombre, apellidos
having sum(cantidad*importe)<6000
UNION ALL
select email, nombre, apellidos, '  ** NO HA COMPRADO NUNCA'
from usuario
where email not in (select usuario from pedido) -- PERO SÍ HAY PEDIDOS SIN LÍNEAS DE PEDIDOS
                                                -- Aquí los estamos eliminando de la respuesta
order by 3; -- también order by tipo
Solución alternativa: 
WITH tt AS (
	SELECT email, nombre, apellidos, SUM(importe*cantidad) total
	FROM usuario
	LEFT join pedido p on p.usuario=email 
	LEFT JOIN linped l ON l.numpedido=p.numpedido
	GROUP BY email, nombre, apellidos
)
SELECT email, nombre, apellidos, ' GRAN CLIENTE' FROM tt WHERE total>10000
UNION ALL -- el group by no genera filas duplicadas, UNION ALL no necesita compararlas
SELECT email, nombre, apellidos, ' CLIENTE MEDIO' FROM tt WHERE total BETWEEN 6000 AND 10000
UNION ALL
SELECT email, nombre, apellidos, ' COMPRA POCO' FROM tt WHERE total<6000
UNION ALL
SELECT email, nombre, apellidos, ' ** NO HA COMPRADO NUNCA' FROM tt WHERE total IS NULL 
-- puede haber pedidos sin líneas de pedido, asumimos que esos casos entran en esta categoría
ORDER BY apellidos;


-------------------

SELECT u.email, u.nombre, u.apellidos,
  CASE
    WHEN SUM(l.importe * l.cantidad) IS NULL THEN '** NO HA COMPRADO NUNCA'
    WHEN SUM(l.importe * l.cantidad) < 6000 THEN 'COMPRA POCO'
    WHEN SUM(l.importe * l.cantidad) <= 10000 THEN 'CLIENTE MEDIO'
    ELSE 'GRAN CLIENTE'
  END AS `Tipo de cliente`
FROM usuario u
LEFT JOIN pedido p  ON p.usuario = u.email
LEFT JOIN linped l  ON l.numPedido = p.numPedido
GROUP BY u.email, u.nombre, u.apellidos
ORDER BY apellidos
UNION ALL devuelve todas las filas sin eliminar duplicados. Si sabemos que no los va a haber, UNION ALL es menos pesado para el motor de base de datos, hace una cosa menos, buscar y eliminar duplicados.

Tal y cómo está diseñada la base de datos puede haber pedidos sin líneas de pedido. El enunciado es ambiguo en este dato, hay que decidir en qué categoría ("compra poco", "no ha comprado")

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
EXCEPT
SELECT u.email, u.nombre
FROM usuario u
JOIN pedido p ON u.email = p.usuario
JOIN linped l ON p.numpedido = l.numpedido
JOIN camara c ON l.articulo = c.cod;
Solución alternativa: 
SELECT u.email,u.nombre from usuario u 
WHERE NOT exists (
  SELECT 1
  FROM pedido p 
  JOIN linped l ON p.numpedido = l.numpedido
  JOIN camara c ON l.articulo = c.cod
  WHERE p.usuario=u.email
);

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 JOIN pedido p ON u.email=p.usuario
EXCEPT
select email, nombre 
from usuario u JOIN pedido p ON u.email=p.usuario
JOIN linped l ON l.numPedido=p.numPedido
JOIN camara c ON c.cod=l.articulo;
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 u.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 
WHERE numpedido IN (
    SELECT numpedido FROM linped JOIN camara ON articulo = cod
    INTERSECT
    SELECT numpedido FROM linped JOIN tv ON articulo = cod
);
Solución alternativa: 
select * from pedido p
where EXISTS 
	(select 1 
	 from linped l 
	 join tv ON l.articulo=tv.cod
	 where l.numpedido=p.numpedido)
  and EXISTS 
  	(select 1 
	 from linped l 
	 join camara c ON l.articulo=c.cod
	 where l.numpedido=p.numpedido);

T11.027- Pedidos (sin duplicados) que incluyen cámaras o televisores.

SELECT * FROM pedido 
WHERE numpedido IN (
    SELECT numpedido FROM linped JOIN camara ON articulo = cod
    UNION
    SELECT numpedido FROM linped JOIN tv ON articulo = 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))
   OR exists (select 1 from linped l 
              where l.numpedido=p.numpedido
                and articulo in (select cod from tv));

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

T11.035- Códigos de artículo que están almacenados simulatáneamente en STOCK y CESTA.

SELECT articulo FROM stock
INTERSECT
SELECT articulo FROM cesta;
Solución alternativa: 
SELECT distinct articulo 
FROM stock s CROSS JOIN cesta c ON s.articulo=c.articulo;

T11.036- Códigos de artículo que podemos encontrar en CESTA pero no en STOCK.

SELECT articulo FROM cesta
EXCEPT
SELECT articulo FROM stock;

T11.037- Códigos de artículo que no son PACK ni han sido pedidos nunca.

select cod from articulo
EXCEPT
select cod from pack 
EXCEPT
select articulo from linped

T11.038- Códigos y nombres de artículo que no son PACK ni han sido pedidos nunca.

select cod, nombre from articulo 
where cod IN (
  select cod from articulo
  EXCEPT
  select cod from pack 
  EXCEPT
  select articulo from linped
)
En este caso es seguro utilizar IN porque trabaja con columnas que sabemos que no almacenan nulos.

T11.039- Email de usuario, y códigos de artículo en cesta ya comprados por el mismo usuario.

SELECT usuario, articulo FROM cesta
INTERSECT
SELECT p.usuario, l.articulo 
FROM pedido p JOIN linped l ON p.numPedido = l.numPedido;

T11.040- Email de usuario con pedidos pero sin dirección de envío alternativa.

SELECT usuario FROM pedido
EXCEPT
SELECT email FROM direnvio;

T11.041- Fechas de pedido que no coinciden con ninguna fecha de nacimiento de usuario. Ordena las fechas obtenidas.

select fecha from pedido
EXCEPT
select nacido from usuario
order by fecha;
Este es un caso del uso "peligroso" de NOT IN. Esta consulta

select fecha from pedido
WHERE fecha NOT IN (select nacido from usuario)
order by fecha;

Obtiene cero filas porque en la columna usuario.nacido hay valores nulos.

T11.042- Lista código, y PVP de los artículos que están en alguna cesta, y código e importe de los artículos pedidos. Añade una columna adicional que informe del origen de los datos, "reservado" para cestas, "vendido" para los pedidos.

SELECT articulo,'vendido' etq, importe FROM linped
UNION
SELECT articulo, 'de cesta', pvp FROM cesta c JOIN articulo a ON c.articulo=a.cod

T11.043- Obtener un listado de artículos destacados que muestre, para cada fila, el código y el nombre del artículo junto con una etiqueta de tipo de destacado, considerando como “MAS VENDIDO” los artículos cuya suma total de unidades vendidas sea al menos 30 unidades y como “MUY EN CESTA” los artículos que hayan sido añadidos a la cesta al menos 3 veces, incluyendo todos ellos en un único resultado.

-- Más vendidos
SELECT  a.cod, a.nombre,'MAS VENDIDO' tipo_destacado
FROM articulo a JOIN linped l ON l.articulo = a.cod
GROUP BY a.cod, a.nombre
HAVING SUM(l.cantidad) >= 30

UNION ALL

-- Muy añadidos a cesta
SELECT  a.cod, a.nombre, 'MUY EN CESTA'
FROM articulo a JOIN cesta c ON c.articulo = a.cod
GROUP BY a.cod, a.nombre
HAVING COUNT(*) >= 3;
Usamos UNION ALL porque estamos seguros de que no va a haber filas duplicadas.

T11.044- Obtén un listado de productos fotográficos que muestre exactamente cinco columnas: código de artículo, nombre de artículo, tipo (CAMARA u OBJETIVO) y dos columnas de detalle con características técnicas (resolución y sensor para las cámaras, focal y apertura para los objetivos), incluyendo únicamente artículos con pvp conocido y reuniendo ambos tipos en un único resultado.

SELECT  a.cod, a.nombre,'CAMARA' AS tipo, 
        c.resolucion AS detalle1, c.sensor AS detalle2
FROM articulo a JOIN camara c ON c.cod = a.cod
WHERE pvp IS NOT NULL 

UNION ALL

SELECT  a.cod,a.nombre,'OBJETIVO',o.focal,o.apertura
FROM articulo a JOIN objetivo o ON o.cod = a.cod
WHERE pvp IS NOT NULL
Claramente, los conjuntos de cámaras y objetivos van a ser disjuntos: aunque solo sea por el tipo, no habrá filas duplicadas entre ambos, por lo que podemos usar UNION ALL.

¡Ojo! Eso no quiere decir que no haya artículos con el mismo código en las tablas CAMARA y OBJETIVO, cosa que sí podría darse.