T10 Soluciones a los ejercicios

Soluciones

T10.001- Email, nombre y apellidos de usuario, y nombre de provincia y localidad donde vive, ordenado de mayor a menor total en euros comprado en nuestra tienda.

select u.email,u.nombre,u.apellidos,l.pueblo,p.nombre
from usuario u join pedido pd on u.email=pd.usuario
join linped lp on lp.numpedido = pd.numpedido
join localidad l on u.pueblo = l.codm and u.provincia = l.provincia
join provincia p on l.provincia = p.codp
group by u.email,u.nombre,u.apellidos,l.pueblo,p.nombre
order by sum(importe*cantidad) desc;
Fíjate que no pedimos el resultado de la operación de agregación, solo que se ordene por ella.
No obstante, daríamos por buena también "select u.email,u.nombre,u.apellidos,l.pueblo,p.nombre, sum(importe*cantidad)..."

En todo caso, es un ejemplo de un group-by forzado por el tipo de ordenación que queremos, no porque el cálculo vaya a verse en la tabla resultado.

T10.002- Email, nombre y apellidos de usuario, y nombre de provincia y localidad donde vive, de los 5 primeros que más han pedido a nuestra tienda en total de euros.

select u.email,u.nombre,u.apellidos,l.pueblo,p.nombre
from usuario u join pedido pd on u.email=pd.usuario
join linped lp on lp.numpedido = pd.numpedido
join localidad l on u.pueblo = l.codm and u.provincia = l.provincia
join provincia p on l.provincia = p.codp
group by u.email,u.nombre,u.apellidos,l.pueblo,p.nombre
order by sum(importe*cantidad) desc limit 5;
El enunciado es un tanto ambiguo en cuanto a lo que pide: "...que más han pedido...". La solución opta por interpretarlo como "que mayor cantidad en euros ha pedido".
La novedad de este ejercicio, si puede llamársela así, es el uso de "limit n", mostrar solo las n primeras filas.

T10.003- Email, nombre y apellidos de usuario, y nombre de provincia y localidad donde vive, de los cinco que menos han pedido a nuestra tienda en total de euros.

select u.email,u.nombre,u.apellidos,l.pueblo,p.nombre
from usuario u join pedido pd on u.email=pd.usuario
join linped lp on lp.numpedido = pd.numpedido
join localidad l on u.pueblo = l.codm and u.provincia = l.provincia
join provincia p on l.provincia = p.codp
group by u.email,u.nombre,u.apellidos,l.pueblo,p.nombre
order by sum(importe*cantidad) limit 5;

T10.004- Cantidad de pedidos de usuarios con direcciones de envío alternativas.

select count(*) cantidad
from direnvio d, pedido p
where d.email=p.usuario

T10.005- Provincias y cantidad de pedidos servidos en ellas, para aquellas provincias con un promedio de importe total de sus líneas de pedidos por encima de los 1500 €.

select provincia,count(distinct p.numpedido) pedidos
from usuario u, pedido p, linped l
where u.email=p.usuario and p.numpedido=l.numpedido
group by provincia
having avg(importe*cantidad)>=1500;
En este caso, al necesitar las líneas de pedido (LINPED), con count(*) estaríamos contando líneas de pedido, los números de pedido (numpedido) se pueden repetir para la misma provincia. Por ello es necesario count(distinct numpedido), nos piden únicamente cantidad de pedidos.

T10.006- Marcas y cantidad de cámaras con disponible superior a 0 unidades, si esa cantidad es inferior a 20.

select a.marca,count(*) cantidad
from articulo a, camara c, stock s
where a.cod=c.cod and c.cod=s.articulo
  and s.disponible>0
group by marca
having count(*)<20;

T10.007- Códigos de provincia y localidad de los usuarios que han hecho más de 1 pedido. Sin duplicados.

select distinct u.provincia,u.pueblo
from usuario u, pedido p
where p.usuario=u.email
group by u.email,u.provincia,u.pueblo
having count(*)>1;
Solución alternativa:
select distinct provincia,pueblo
from usuario
where email in (select usuario 
                from pedido 
                group by usuario
                having count(*)>1);
Ten cuidado, lo que te están pidiendo es "cuántos pedidos por usuario". Lo que pasa es que solo queremos mostrar su provincia y localidad. Por eso metemos email en el group by.
Dicho de otra forma, si agrupamos solo por (provincia,pueblo) no estarías obteniendo lo que se te pide.

T10.008- Marcas y promedio de precios de venta al público de sus artículos, exceptuando los packs, y teniendo esas marcas una cantidad de artículos mayor que 100, ordenado descendentemente por promedio.

select marca,avg(pvp) promedio
from articulo
where cod not in (select cod from pack)
group by marca
having count(*) > 100
order by promedio desc;

T10.009- Códigos de artículo y marca de los televisores cuyo promedio de importe unitario en pedidos supera en un 10 % al precio de venta al público actual.

select a.cod,a.marca
from articulo a, tv, linped l
where a.cod=tv.cod and l.articulo=a.cod
group by a.cod,a.marca,a.pvp
having avg(l.importe)>1.10*a.pvp;
Solución alternativa:
select a.cod,a.marca
from articulo a 
join tv on a.cod=tv.cod
join (select articulo,avg(importe) mimporte 
      from linped group by articulo) l on l.articulo=a.cod
where l.mimporte>1.10*a.pvp;
En el group by hemos tenido que añadir a.pvp y a.marca para que nos permitiera ejecutar, el primero porque se usa en el having, y el segundo por ser un dato a mostrar en la solución. Date cuenta de que cada artículo de ARTICULO tiene un único pvp y una única marca, con lo que no afecta al cálculo del promedio.
También habría valido, aprovechándonos en este caso de que max(pvp) para cada artículo sigue siendo un valor único:
...
group by a.cod,a.marca
having avg(l.importe)>1.10*max(a.pvp);

La solución alternativa convierte la consulta de agregación en una subconsulta o consulta temporal. En próximas sesiones aprenderemos a trabajar con subconsultas como si fueran tablas temporales.

T10.010- Código de artículo, marca e importe medio de las cámaras que han sido pedidos en más de dos líneas de pedido, ordenado por marca y código de artículo.

select a.cod,a.marca,avg(importe) mimporte
from articulo a, camara c, linped l
where a.cod=c.cod and l.articulo=a.cod
group by a.cod,a.marca
having count(*)>2
order by a.marca,a.cod;
Utilizamos count(*) porque sabemos que la cuenta de filas en LINPED equivale a en cuántas líneas de pedido está cada artículo. Count(linea) valdría también, sería la cuenta de valores no nulos en esa columna, que coincide en este caso con count(*), LINPED.linea no permite nulos. Count(distinct linea) ya no, hay valores repetidos en la columna.

T10.011- Fechas de pedido y cantidad de pedidos efectuados en ellas, sin tener en cuenta las líneas de pedido con importes superiores a 1000 €.

select fecha, count(distinct l.numpedido) cuantos
from pedido p, linped l
where p.numpedido=l.numpedido
  and l.importe <=1000
group by fecha;
Cuidado, aquí es obligatorio utilizar count(distinct numpedido). ¿Sabes por qué?

T10.012- Fechas de pedido (solo fecha) e importe total pagado en sus pedidos, ordenado por ese importe total.

select date(fecha), sum(l.cantidad*l.importe) total
from pedido p, linped l
where p.numpedido=l.numpedido
group by fecha
order by 2;
"Order by 2" es "ordena por la segunda columna", y equivalente en este caso a "order by sum(l.cantidad*l.immporte)".

T10.013- Número de pedido y cantidad de líneas de pedido por cada uno, ordenado de mayor a menor por esa cantidad.

select numpedido, count(*) lineas
from linped
group by numpedido
order by count(*) desc;

T10.014- Apellidos de personas que están repetidos.

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

Solución alternativa:

select distinct u1.apellidos
from usuario u1, usuario u2
where u1.email != u2.email
  and u1.apellidos = u2.apellidos
order by u1.apellidos;

T10.015- Nombre y apellidos de usuarios que están repetidos.

select apellidos, nombre
from usuario
group by apellidos,nombre
having count(*) >= 2;

Solución alternativa:

select distinct u1.apellidos,u1.nombre
from usuario u1, usuario u2
where u1.email != u2.email
  and u1.apellidos = u2.apellidos
  and u1.nombre = u2.nombre
order by u1.apellidos;

T10.016- Nombres de localidad y cantidad de provincias en las que se encuentra un pueblo con ese nombre, para nombres con 2 o más provincias.

select pueblo, count(*) provincias
from localidad
group by pueblo
having count(*) >= 2;

T10.017- Cantidad de artículos sin PVP.

select count(*) sinPVP
from articulo
where pvp is null;

T10.018- Cantidad de artículos sin PVP y descatalogados.

select count(*)  'Descatalogados y sinPVP'
from articulo a, stock s
where a.cod=s.articulo
  and pvp is null
  and entrega='Descatalogado';

T10.019- Marca y cantidad de artículos cuyo nombre indique que son objetivos con focales "18-200 mm" o "28-200 mm", ordenado de mayor a menor por esa cantidad.

select marca,count(*) cuantos
from articulo
where nombre like '%18-200%' or nombre like '%28-200%'
group by marca
order by count(*) desc;

T10.020- Código y nombre de provincias, y media y desviación estandar de cantidad de artículos pedidos desde esas provincias.

select p.codp,p.nombre,avg(cantidad) media, std(cantidad) desvest
from provincia p, usuario u,pedido pd,linped l
where p.codp=u.provincia 
  and u.email=pd.usuario
  and pd.numpedido=l.numpedido
group by p.codp,p.nombre;

T10.022- Número de pedido, fecha, email, apellidos y nombre de usuario, e IVA (21 %, incluido en los importes, redondeado a 2 decimales) a repercutir en cada factura derivada de la hoja de pedido, ordenado por fecha y email.

select p.numpedido,p.fecha,u.email,u.apellidos,u.nombre,
       round(sum(cantidad*importe/1.21*.21),2) iva
from usuario u,pedido p,linped l
where u.email=p.usuario
  and p.numpedido=l.numpedido
group by p.numpedido,p.fecha,u.email,u.apellidos,u.nombre
order by p.fecha,u.email;
En importe*cantidad ya está incluido el IVA. Por lo tanto necesitamos calcular la base imponible para determinar cuánto hemos pagado de IVA.
base imponible = importe*cantidad/1.21
IVA = base_imponible*0.21, o también IVA = importe*cantidad-base_imponible

Luego, IVA = importe*cantidad/1.21*0.21

T10.023- Número de pedido, fecha, email, apellidos y nombre de usuario, y base imponible (asume 21 % IVA) de cada factura derivada de la hoja de pedido, ordenado por fecha y email. Cálculos redondeados a 2 decimales.

select p.numpedido,p.fecha,u.email,u.apellidos,u.nombre,
       round(sum(cantidad*importe/1.21),2) base
from usuario u,pedido p,linped l
where u.email=p.usuario
  and p.numpedido=l.numpedido
group by p.numpedido,p.fecha,u.email,u.apellidos,u.nombre
order by p.fecha,u.email;
Supongamos x el coste antes de aplicar el IVA (base imponible):
importe*cantidad = x+x*.21
Luego, x = importe*cantidad/1.21

T10.024- Año, mes y total de IVA (21 %, incluido en los importes) repercutido, ordenado por año y mes. Cálculos redondeados a 2 decimales.

select year(fecha) año, month(fecha) mes, round(sum(importe*cantidad)/1.21*0.21,2) IVA 
from pedido p join linped l on (l.numpedido = p.numpedido)
group by year(fecha), month(fecha)
order by year(fecha), month(fecha);

base imponible = importe*cantidad/1.21
IVA = base_imponible*0.21

T10.025- Año, mes y base imponible total (IVA 21 %, incluido en los importes) de sus pedidos, ordenado por año y mes. Cálculos redondeados a 2 decimales.

select year(fecha) año, month(fecha) mes, round(sum(importe*cantidad)/1.21,2) base 
from pedido p join linped l on (l.numpedido = p.numpedido)
group by year(fecha), month(fecha)
order by year(fecha), month(fecha);

base imponible = importe*cantidad/1.21
IVA = base_imponible*0.21

T10.026- Por cada año y mes, base imponible, IVA (21 %, incluido en los importes) repercutido e importe total de todos sus pedidos, ordenado por año y mes. Cálculos redondeados a 2 decimales.

select year(fecha) año, month(fecha) mes, 
 round(sum(importe*cantidad)/1.21,2) base, 
 round(sum(importe*cantidad)/1.21*0.21,2)IVA,
 round(sum(importe*cantidad),2) total  
from pedido p join linped l on (l.numpedido = p.numpedido)
group by year(fecha), month(fecha)
order by year(fecha), month(fecha);

base imponible = importe*cantidad/1.21
IVA = base_imponible*0.21

T10.027- EL IVA está incluido en los importes. Por cada año y mes, base imponible (x/1.21), IVA repercutido (x*0.21) e importe total de todos sus pedidos, ordenado por año y mes. Solo los meses cuya cantidad de pedidos sea mayor que 10. Cálculos redondeados a 2 decimales.

select year(fecha) año, month(fecha) mes, 
 round(sum(importe*cantidad)/1.21,2) base, 
 round(sum(importe*cantidad)/1.21*.21,2) IVA,
 round(sum(importe*cantidad),2) total  
from pedido p join linped l on (l.numpedido = p.numpedido)
group by year(fecha), month(fecha)
having count(distinct l.numpedido) > 10
order by year(fecha), month(fecha);

base imponible = importe*cantidad/1.21
IVA = base_imponible*0.21

T10.028- Por cada año y mes, base imponible, IVA (21 %, incluido en los importes) repercutido e importe total de todos sus pedidos, ordenado por año y mes. Eliminando pedidos de la provincia 33 y solo los meses cuya cantidad de pedidos sea mayor que 10. Cálculos redondeados a 2 decimales.

select year(fecha) año, month(fecha) mes, 
 round(sum(importe*cantidad/1.21),2) base, 
 round(sum(importe*cantidad/1.21*0.21),2) IVA,
 round(sum(importe*cantidad),2) total  
from pedido p join linped l on (l.numpedido = p.numpedido)
join usuario u on (p.usuario = u.email)
where u.provincia != '33'
group by year(fecha), month(fecha)
having count(distinct l.numpedido) > 10
order by year(fecha), month(fecha);

T10.029- Fechas de pedido y cantidad de pedidos efectuados en ellas, exceptuando los pedidos que contengan líneas con importes inferiores a 1000 €.

select fecha, count(distinct l.numpedido)
from pedido p join linped l on p.numpedido=l.numpedido
where p.numpedido not in (select numpedido from linped where importe<1000)
group by fecha;
Cuidado, aquí es obligatorio utilizar count(distinct numpedido). ¿Sabes por qué?