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;

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.

Comienza resolviendo pedidos sin líneas y valor de la cesta por usuario y utiliza los resultados como tablas temporales.

select email,nombre, apellidos,numpedido, pendiente
from usuario u,
  (select numpedido,usuario
   from pedido  
   where numpedido not in (select numpedido from linped)
  ) pedidos,
  (select usuario,sum(pvp) pendiente
   from cesta c, articulo a
   where c.articulo=a.cod
   group by usuario
  ) cestas
where email=pedidos.usuario and email=cestas.usuario;
Date cuenta que el último where utiliza columnas de las tablas temporales.

Para entender mejor qué hace esta consulta, ejecuta las subconsultas por separado:
select numpedido,usuario
from pedido
where numpedido not in (select numpedido from linped)

select usuario,sum(pvp) pendiente
from cesta c, articulo a
where c.articulo=a.cod
group by usuario

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.

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
where email in (select usuario from pedido group by usuario having count(*) > 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;

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,
(select usuario
 from pedido p
 group by usuario
 having count(*)>=2) pp 
where u.pueblo = l.codm and u.provincia=l.provincia
  and pp.usuario=u.email;

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, pero mostrando esos datos en una única fila.

select 
(select count(*) from tv) televisores,
(select count(*) from camara) camaras,
(select count(*) from objetivo) 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.

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;

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, pero mostrando esos datos en una única fila.

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.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.