T12 Soluciones a los ejercicios

Soluciones

T12.001- Días que han pasado entre el primer y último pedido.

select datediff(
   (select max(fecha) from pedido), 
   (select min(fecha) from pedido)
) dias;

Solución alternativa:

select datediff(max(fecha), min(fecha)) dias from pedido;

T12.002- Calcula y muestra la cantidad de televisores, cámaras y objetivos almacenados en la base de datos.

select
(select count(*) from tv) tv,
(select count(*) from camara) camaras,
(select count(*) from objetivo) objetivos;

T12.003- Calcula y muestra los porcentajes, sobre el total de artículos almacenados en la base de datos, de televisores, cámaras y objetivos.

select
(select count(*) from tv)/(select count(*) from articulo)*100 tvPC,
(select count(*) from camara)/(select count(*) from articulo)*100 camarasPC,
(select count(*) from objetivo)/(select count(*) from articulo)*100 objetivosPC;

Solución alternativa:

select
(select count(*) from tv)/t.total*100 tvPC,
(select count(*) from camara)/t.total*100 camarasPC,
(select count(*) from objetivo)/t.total*100 objetivosPC
from (select count(*) total from articulo) t;

La solución alternativa es más eficiente, solo utiliza ARTICULO 1 vez. En la solución principal se recorre 3 veces para obtener el mismo dato cada vez.

T12.004- Email, nombre y apellidos de los usuarios de la provincia 03, y si tienen un pedido cuyo importe total sea mayor que 10000€, mostrar también el número de pedido y ese importe; ordena la salida descendentemente por el valor del pedido.

Comienza resolviendo número de pedido, usuario e importe total de los pedidos valorados en más de 10000€ y utiliza el resultado como tabla temporal.

select email,nombre, apellidos,numpedido, valor
from usuario u left join
  (select p.numpedido,usuario, sum(cantidad*importe) valor
   from pedido p, linped l
   where p.numpedido=l.numpedido
   group by numpedido,usuario
   having sum(cantidad*importe)>10000) calculo
on (email=usuario)
where provincia='03'
order by valor desc

T12.005- De los usuarios que tengan algún pedido sin líneas de pedido y artículos pendientes de solicitud en alguna cesta, mostrar su email, nombre, apellidos, número del pedido sin líneas, y valor total de su cesta (PVP).

Comienza resolviendo usuarios de pedidos sin líneas; después, valor de la cesta por usuario. Utiliza esos resultados como subconsultas.

select email,nombre,apellidos,numpedido,pendiente
FROM usuario u
JOIN (select numpedido,usuario from pedido  
      where numpedido not in (select numpedido from linped)) pedidos 
ON email=pedidos.usuario  -- pedidos sin líneas
JOIN (select usuario,sum(pvp) pendiente
      from cesta c, articulo a
      where c.articulo=a.cod
      group by usuario) cestas 
ON email=cestas.usuario; -- valor de sus cestas

T12.006- Para aquellos usuarios que tengan más de un pedido en 2010, obtener una tabla donde cada columna se corresponda con un mes del año y muestre la cantidad de pedidos realizada por ese usuario en ese mes. Cada fila empieza por el email, nombre y apellidos del usuario.

WITH contados AS 
(SELECT p.usuario, u.apellidos, u.nombre, MONTH(fecha) mes, COUNT(*) pedidos
 FROM pedido p JOIN usuario u ON p.usuario=u.email
 WHERE YEAR(fecha)=2010
 GROUP BY usuario, u.apellidos, u.nombre, MONTH(fecha)
)
SELECT 
    usuario, 
    apellidos, 
    nombre,
    MAX(IF(mes = 1, pedidos, 0)) AS Ene,
    MAX(IF(mes = 2, pedidos, 0)) AS Feb,
    MAX(IF(mes = 3, pedidos, 0)) AS Mar,
    MAX(IF(mes = 4, pedidos, 0)) AS Abr,
    MAX(IF(mes = 5, pedidos, 0)) AS May,
    MAX(IF(mes = 6, pedidos, 0)) AS Jun,
    MAX(IF(mes = 7, pedidos, 0)) AS Jul,
    MAX(IF(mes = 8, pedidos, 0)) AS Ago,
    MAX(IF(mes = 9, pedidos, 0)) AS Sep,
    MAX(IF(mes = 10, pedidos, 0)) AS Oct,
    MAX(IF(mes = 11, pedidos, 0)) AS Nov,
    MAX(IF(mes = 12, pedidos, 0)) AS Dic
FROM contados
GROUP BY usuario, apellidos, nombre
HAVING SUM(pedidos) > 1;

Solución alternativa:

select *
from
  (select email,nombre,apellidos,
   (select count(*) from pedido where month(fecha)=1 and year(fecha)=2010 and usuario=email) enero,
   (select count(*) from pedido where month(fecha)=2 and year(fecha)=2010 and usuario=email) febrero,
   (select count(*) from pedido where month(fecha)=3 and year(fecha)=2010 and usuario=email) marzo,
   (select count(*) from pedido where month(fecha)=4 and year(fecha)=2010 and usuario=email) abril,
   (select count(*) from pedido where month(fecha)=5 and year(fecha)=2010 and usuario=email) mayo,
   (select count(*) from pedido where month(fecha)=6 and year(fecha)=2010 and usuario=email) junio,
   (select count(*) from pedido where month(fecha)=7 and year(fecha)=2010 and usuario=email) julio,
   (select count(*) from pedido where month(fecha)=8 and year(fecha)=2010 and usuario=email) agosto,
   (select count(*) from pedido where month(fecha)=9 and year(fecha)=2010 and usuario=email) septiembre,
   (select count(*) from pedido where month(fecha)=10 and year(fecha)=2010 and usuario=email) octubre,
   (select count(*) from pedido where month(fecha)=11 and year(fecha)=2010 and usuario=email) noviembre,
   (select count(*) from pedido where month(fecha)=12 and year(fecha)=2010 and usuario=email) diciembre
   from usuario) pormeses
where enero+febrero+marzo+abril+mayo+junio+julio+agosto+septiembre+octubre+noviembre+diciembre > 1;

La solución aprovecha que la CTE genera una fila por cada usuario y mes en el que tenga pedidos.

La solución alternativa, siendo más "entendible", es mucho más ineficiente que la proporcionada, "recorre" la tabla pedido 12 veces. En nuestra base de datos, pequeña, no es importante, pero no se aconseja recurrir a soluciones así.

T12.007- Provincias y cantidad de pedidos servidos en ellas, para aquellas provincias con un promedio de importe total de sus pedidos por encima de 2500 €.

select provincia,count(*) pedidos
from usuario u, 
 (select usuario,p.numpedido,sum(importe*cantidad) total
  from pedido p join linped l on (p.numpedido=l.numpedido)
  group by usuario,numpedido) pd
where u.email=pd.usuario 
group by provincia
having avg(total)>2500;
Solución alternativa:
WITH PD AS 
  (select usuario,p.numpedido,sum(importe*cantidad) total
  from pedido p join linped l on (p.numpedido=l.numpedido)
  group by usuario,numpedido)
  
select provincia,count(*) pedidos
from usuario u,PD
where u.email=PD.usuario 
group by provincia
having avg(total)>2500;
El problema aquí reside en calcular el importe medio de los pedidos por cada provincia. Piensa que si planteas:

select provincia,count(*),avg(importe*cantidad)
from usuario u,pedido p,linped l
where u.email=p.usuario and p.numpedido=l.numpedido
group by provincia

Lo que está calculando es el promedio de importe*cantidad de todas las líneas de pedidos en cada provincia.

Plantéalo como un problema por etapas. Primero, usuario (lo necesitaremos después), numpedido e importe total de los pedidos:

select usuario,p.numpedido,sum(importe*cantidad) totalpedido
from pedido p,linped l
where p.numpedido=l.numpedido
group by usuario,p.numpedido

Supongamos que llamamos a lo anterior PD. Ahora lo integramos en la consulta que realmente queremos hacer:

select provincia,count(*) pedidos
from usuario u, PD
where u.email=PD.usuario
group by provincia
having avg(totalpedido)>2500;

La solución alternativa, usando CTE, es la sintaxis más cercana a esta explicación.

T12.008- Nombre de las localidades de los usuarios que han hecho 2 pedidos o más. Elimina duplicados.

select distinct l.pueblo 
from usuario u, localidad l 
where u.pueblo = l.codm and u.provincia=l.provincia
  and email in (select usuario
                from pedido p
                group by usuario
                having count(*)>=2);

Solución alternativa:

select distinct l.pueblo 
from usuario u, localidad l, pedido p 
where u.pueblo = l.codm and u.provincia=l.provincia AND u.email=p.usuario
group by usuario,l.pueblo,u.provincia
having count(*)>=2;
-- ----
select l.pueblo 
from localidad l,
(SELECT distinct u.pueblo,u.provincia
 from pedido p JOIN usuario u ON p.usuario=u.email
 group by p.usuario,u.pueblo,u.provincia
 having count(*)>=2) pp 
where pp.pueblo = l.codm and pp.provincia=l.provincia;

T12.009- Obtén el valor promedio de "pantalla" de los televisores con resolución "1920 x 1080".

De los televisores de marca SONY, muestra todos sus datos como televisor, pero cambiando la pantalla a ese valor promedio calculado previamente.

select tv.cod,panel,(select round(avg(pantalla),0) from tv where resolucion='1920 x 1080') pantalla, resolucion, hdreadyfullhd, tdt
from tv, articulo a 
where tv.cod=a.cod and marca='SONY';

Solución alternativa:

select tv.cod,panel,t.pantalla, resolucion, hdreadyfullhd, tdt
from tv, articulo a,(select round(avg(pantalla),0) pantalla from tv where resolucion='1920 x 1080') t 
where tv.cod=a.cod and marca='SONY';

T12.010- Calcula la cantidad de televisores, cámaras y objetivos que tenemos en la base de datos. Con esos valores, calcula la proporción de cada tipo contra el total de artículos de esos tipos (tvs+camaras+objetivos).

SELECT tvs/(tvs+cams+objs) promtv,
cams/(tvs+cams+objs) promcam,
objs/(tvs+cams+objs) promtv,
(tvs+cams+objs) total
FROM  
  (SELECT  COUNT(*) tvs FROM tv) t,
  (SELECT  COUNT(*) cams FROM camara) c,
  (SELECT  COUNT(*) objs FROM objetivo) o

Solución alternativa:

WITH
  t AS (SELECT  COUNT(*) tvs FROM tv),
  c AS (SELECT  COUNT(*) cams FROM camara),
  o AS (SELECT  COUNT(*) objs FROM objetivo)
SELECT tvs/(tvs+cams+objs) promtv,
cams/(tvs+cams+objs) promcam,
objs/(tvs+cams+objs) promtv,
(tvs+cams+objs) total
FROM  t, c, o;

T12.011- Muestra la cantidad de usuarios de la provincia de Alicante ('03'), la cantidad que son de Agost (provincia '03', codm '0020'), y el porcentaje que suponen estos últimos sobre el total de alicantinos, redondeado a 2 decimales.

select totalAlicante, deAgost, round(deAgost/totalALicante*100,2) '% agostenses sobre alicantinos'
from
(select count(*) totalAlicante from usuario where provincia='03') t1,
(select count(*) deAgost from usuario where provincia='03' and pueblo='0020') t2;
Fíjate que hacemos un producto cartesiano de las subconsultas t1 y t2, pero ambas obtienen una única fila y una única columna, por lo que no es costoso para el motor de base de datos.

T12.012- Por cada provincia, calcula el promedio de la máxima cantidad pedida en cada articulo. Necesitas calcular, por cada pedido, el máximo de linped.cantidad; de ese resultado intermedio ya puedes calcular lo solicitado.

WITH maximos AS (
SELECT numpedido,MAX(cantidad) cants
FROM linped
GROUP BY numpedido
)
SELECT provincia, AVG(cants)
FROM maximos m
JOIN pedido p ON m.numpedido=p.numpedido
JOIN usuario u ON p.usuario=u.email
GROUP BY provincia

T12.013- Obtener, para cada marca, el pvp medio de sus artículos expresado como porcentaje respecto al precio máximo existente en toda la tabla (promedioMarca/pvpMáximo*100), mostrando ese valor con dos decimales, y ordenando las marcas de mayor a menor según dicho porcentaje.

SELECT marca,round(AVG(pvp)/(SELECT MAX(pvp) FROM articulo)*100,2) prom
FROM articulo
GROUP BY marca
ORDER BY prom desc;

Solución alternativa:

WITH maximo AS (
  SELECT MAX(pvp) AS max_pvp
  FROM articulo
)
SELECT 
  a.marca,
  ROUND(AVG(a.pvp) / m.max_pvp * 100, 2) AS prom
FROM articulo a
CROSS JOIN maximo m
GROUP BY a.marca, m.max_pvp
ORDER BY prom DESC;

T12.014- Obteniendo cuál es la marca del artículo más caro en catálogo (PVP), listar los códigos de articulo de esa marca y el importe pagado por ellos en cualquier pedido.

WITH mmax AS (
SELECT marca
FROM articulo
WHERE pvp = (SELECT MAX(pvp) FROM articulo)
)
SELECT articulo, importe
FROM linped l 
JOIN articulo a ON l.articulo=a.cod
CROSS JOIN mmax
WHERE a.marca = mmax.marca;

Solución alternativa:

SELECT articulo, importe
FROM linped l 
JOIN articulo a ON l.articulo=a.cod
WHERE a.marca = (SELECT marca
                 FROM articulo
                 WHERE pvp = (SELECT MAX(pvp) FROM articulo))

T12.015- Listar los códigos de artículo, su marca, su PVP y el importe pagado en pedidos, solo para aquellos artículos cuyo PVP sea superior al PVP medio de los artículos de su propia marca.

WITH media_marca AS (
  SELECT marca, AVG(pvp) AS media_pvp
  FROM articulo
  GROUP BY marca
)
SELECT a.cod, a.marca, a.pvp, l.importe
FROM articulo a
JOIN media_marca mm ON a.marca = mm.marca
JOIN linped l ON l.articulo = a.cod
WHERE a.pvp > mm.media_pvp;

T12.016- Obtener la marca o marcas cuyos artículos suman la mayor cantidad total vendida.

WITH ventas_marca AS (
  SELECT a.marca, SUM(l.cantidad) AS unidades
  FROM articulo a
  JOIN linped l ON l.articulo = a.cod
  GROUP BY a.marca
)
SELECT marca, unidades
FROM ventas_marca
WHERE unidades = (
  SELECT MAX(unidades)
  FROM ventas_marca
);

T12.017- Obtén un listado de todos los packs que incluya el código del pack, su nombre, su precio de venta actual y la suma total de los precios individuales de todos los artículos que lo integran.

WITH packtotal AS (
  SELECT pack, SUM(pvp) AS coste
  FROM ptienea p
  JOIN articulo a ON p.articulo=a.cod
  GROUP BY pack
)
SELECT a.cod,a.nombre,a.pvp,t.coste
FROM pack k
JOIN articulo a ON k.cod=a.cod
JOIN packtotal t ON t.pack=a.cod

Soluciones

T12.001- Días que han pasado entre el primer y último pedido.

T12.002- Calcula y muestra la cantidad de televisores, cámaras y objetivos almacenados en la base de datos.

T12.003- Calcula y muestra los porcentajes, sobre el total de artículos almacenados en la base de datos, de televisores, cámaras y objetivos.

T12.006- Para aquellos usuarios que tengan más de un pedido en 2010, obtener una tabla donde cada columna se corresponda con un mes del año y muestre la cantidad de pedidos realizada por ese usuario en ese mes. Cada fila empieza por el email, nombre y apellidos del usuario.

T12.007- Provincias y cantidad de pedidos servidos en ellas, para aquellas provincias con un promedio de importe total de sus pedidos por encima de 2500 €.

T12.008- Nombre de las localidades de los usuarios que han hecho 2 pedidos o más. Elimina duplicados.

T12.009- Obtén el valor promedio de "pantalla" de los televisores con resolución "1920 x 1080". De los televisores de marca SONY, muestra todos sus datos como televisor, pero cambiando la pantalla a ese valor promedio calculado previamente.

T12.010- Calcula la cantidad de televisores, cámaras y objetivos que tenemos en la base de datos. Con esos valores, calcula la proporción de cada tipo contra el total de artículos de esos tipos (tvs+camaras+objetivos).

T12.011- Muestra la cantidad de usuarios de la provincia de Alicante ('03'), la cantidad que son de Agost (provincia '03', codm '0020'), y el porcentaje que suponen estos últimos sobre el total de alicantinos, redondeado a 2 decimales.

T12.012- Por cada provincia, calcula el promedio de la máxima cantidad pedida en cada articulo. Necesitas calcular, por cada pedido, el máximo de linped.cantidad; de ese resultado intermedio ya puedes calcular lo solicitado.

T12.013- Obtener, para cada marca, el pvp medio de sus artículos expresado como porcentaje respecto al precio máximo existente en toda la tabla (promedioMarca/pvpMáximo*100), mostrando ese valor con dos decimales, y ordenando las marcas de mayor a menor según dicho porcentaje.

T12.014- Obteniendo cuál es la marca del artículo más caro en catálogo (PVP), listar los códigos de articulo de esa marca y el importe pagado por ellos en cualquier pedido.

T12.015- Listar los códigos de artículo, su marca, su PVP y el importe pagado en pedidos, solo para aquellos artículos cuyo PVP sea superior al PVP medio de los artículos de su propia marca.

T12.016- Obtener la marca o marcas cuyos artículos suman la mayor cantidad total vendida.

T12.017- Obtén un listado de todos los packs que incluya el código del pack, su nombre, su precio de venta actual y la suma total de los precios individuales de todos los artículos que lo integran.

T12.009- Obtén el valor promedio de "pantalla" de los televisores con resolución "1920 x 1080".

De los televisores de marca SONY, muestra todos sus datos como televisor, pero cambiando la pantalla a ese valor promedio calculado previamente.