T09 Soluciones a los ejercicios

Soluciones

T09.001- ¿Cuántos artículos de cada marca hay?

select marca,count(*) 
from articulo 
group by marca;

T09.004- Número de cámaras que tienen sensor CMOS

select count(*) from camara where sensor like '%CMOS%'
Si miramos la columna camara.sensor veremos que los valores son descripciones largas de texto que en alguna parte incluyen la cadena 'CMOS'.

T09.012- Ha habido un error en Tiendaonline y se han colado varios artículos sin stock, con cero unidades disponibles, en la cesta. Averigua el código de esos artículos y las veces que aparece cada uno en cesta.

select c.articulo, count(*) 
from cesta c, stock s 
where c.articulo=s.articulo
  and disponible=0  
group by c.articulo;

T09.016- Cantidad de artículos con stock 0

select count(*) from stock where disponible=0;

T09.017- Cantidad de artículos que no son ni memoria, ni tv, ni objetivo, ni cámara ni pack.

select  count(*) 
from articulo 
where cod not in (select cod from camara) 
  and cod not in (select cod from tv) 
  and cod not in (select cod from memoria) 
  and cod not in (select cod from objetivo) 
  and cod not in (select cod from pack);

Solución alternativa:

select  count(*) 
from articulo 
where cod not in (select cod from camara 
            union select cod from tv 
            union select cod from memoria 
            union select cod from objetivo 
            union select cod from pack);

T09.021- Cantidad de líneas por cada pedido.

select numpedido, count(*)
from linped
group by numpedido

Solución alternativa:

-- esta solución incluye los pedidos que no tienen líneas (0 líneas)
select numpedido,0 lineas from pedido where numpedido not in (select numpedido from linped)
UNION
select numpedido, count(*) from linped group by numpedido

-- o esta otra, equivalente a la anterior
select p.numPedido, count(*) 
from pedido p 
LEFT JOIN linped l on (p.numPedido = l.numPedido) 
group by p.numPedido

Contamos filas (count(*)) porque en la tabla LINPED, cada fila es una línea de pedido.

T09.022- Cantidad de líneas por cada pedido, eliminando aquellas filas con cantidades de artículo pedidas menores a 3.

select numpedido, count(*)
from linped
where cantidad >= 3
group by numpedido
Where filtra primero las filas a contar, y después se hace el cálculo.

T09.023- Para los pedidos anteriores a septiembre de 2010, cantidad de líneas por cada pedido.

select l.numpedido, count(*)
from linped l, pedido p
where l.numpedido=p.numpedido 
  and fecha < '2010-09-01'
group by numpedido
Where filtra primero las filas a contar, y después se hace el cálculo.

T09.024- Por cada artículo pedido, cantidad total de unidades pedidas.

select articulo, sum(cantidad)
from linped
group by articulo;
Estamos sumando los valores almacenados en la columna LINPED.cantidad.

T09.025- Por cada artículo pedido, cantidad total de unidades pedidas y promedio de importe unitario.

select articulo, sum(cantidad) cantidad, avg(importe) impmedio
from linped
group by articulo;

T09.026- Para las cámaras pedidas, código de artículo, cantidad total de unidades pedidas y promedio de importe unitario.

select articulo, sum(cantidad) cantidad, avg(importe) impmedio
from linped
where articulo in (select cod from camara)
group by articulo;

Solución alternativa:

select l.articulo, sum(cantidad) cantidad, avg(importe) impmedio
from linped l, camara c
where l.articulo = c.cod
group by articulo;

T09.027- Para las cámaras pedidas, código, nombre, marca del artículo, y cantidad total de unidades pedidas y promedio de importe unitario.

select l.articulo, a.nombre, a.marca, sum(cantidad) cantidad, avg(importe) impmedio
from linped l, articulo a
where l.articulo=a.cod
  and articulo in (select cod from camara)
group by l.articulo, a.nombre, a.marca;

Todas las columnas que no sean una expresión de agregación deben aparecer en el group by.

T09.028- Para todo usuario cuyo primer apellido sea Martínez, comprobar si hay algún otro usuario con los mismos apellidos y nombre.

select apellidos,nombre,count(*)
from usuario
where apellidos like 'Martínez%'
group by apellidos,nombre;

Solución alternativa:

-- Si el enunciado hubiera sido más genérico, 
-- y asumiendo que sabemos utilizar having:
-- Apellidos y nombre repetidos para varios usuarios
select apellidos,nombre
from usuario
group by apellidos,nombre
having count(*)>1;

Lo que vamos a ver en el resultado es que Martínez Fernández, Javier, aparece en dos filas de la tabla USUARIO.

T09.029- Por cada mes y año, cantidad de pedidos realizados.

select month(fecha) mes, year(fecha) año, count(*)
from pedido
group by month(fecha), year(fecha);

T09.030- Para los artículos 'A0233' y 'A1085', código de artículo y cantidad de pedidos distintos en los que ha sido solicitado.

select articulo, count(distinct numpedido)
from linped
where articulo in ('A0233','A1085')
group by articulo;
Estamos obligados a utilizar count(distinct numpedido) porque un artículo puede aparecer en varias líneas del mismo pedido, como es el caso de los dos códigos de artículo exigidos.

T09.031- ¿Cuántas veces se ha pedido cada artículo? Deben mostrarse todos los artículos, incluso los que no se han pedido nunca. La salida contendrá el código y nombre del artículo, junto con las veces que ha sido incluido en un pedido (solo si ha sido incluido, no se trata de la "cantidad").

select cod, nombre, count(numpedido) veces
from articulo a 
left join linped l on (a.cod=l.articulo) 
group by cod, nombre;
Aquí no nos vale count(*). Contando filas, todos los artículos tendrían una cuenta de "1 o más filas". Examina el resultado de "select cod, nombre, numpedido from articulo a left join linped l on (a.cod=l.articulo)". Cada uno de los artículos aparece al menos una vez.

El "truco" consiste en contar los números de pedido asociados a algunos artículos. El left join hace que ciertos artículos aparezcan una vez junto con NULL como numpedido. El resto de artículos aparecen tantas veces como líneas tengan en LINPED. Como count(numpedido) solo cuenta los valores distintos de NULL, por eso conseguimos que la cuenta sea 0 en los artículos no pedidos nunca.

T09.032- Por cada usuario, email y total de precio de venta al público acumulado en sus cestas actuales.

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

T09.033- Por cada usuario, email y total de precio de venta al público acumulado en sus cestas actuales, eliminando previamente los artículos sin PVP, y ordenando de mayor a menor por ese total.

select usuario,sum(pvp) total
from cesta c, articulo a
where c.articulo=a.cod and pvp is not null
group by usuario
order by total;