¶ Northwind
Examen 2024 NorthWind
Explicación del E/R.
Aquí encontraréis un esquema que representa todas las entidades que tiene esta empresa que se dedica a la venta de productos (generales).
Como se puede observar, en este sistema se almacenan datos de los clientes, y todo lo referente a su dirección, con bastante detalle. Además, de los clientes almacenamos sus contactos. Estos contactos son los que se comunican con la empresa a través de sus empleados. Es por ello por lo que guardamos un registro de todas las interacciones que mantienen los contactos con los empleados de la empresa y de qué manera lo hacen (a través de qué medio).
Como no puede ser de otra manera, la empresa almacena también los datos de los pedidos. Los pedidos están realizados por un empleado. Además, ese pedido tendrá un cliente asociado. El pedido puede tener una factura asociada y puede estar en estado entregado o no. Además, también se le puede asignar un transportista a dicho pedido.
De los empleados necesitamos saber sus datos personales, pero además saber si tiene o no un encargado.
Los pedidos estarán formados por las líneas de pedido, donde se especificará qué producto se está pidiendo, así como la cantidad y el precio de este.
Si analizamos en detalle la entidad productos, podemos ver que tenemos datos como su nombre, quién nos lo suministra (proveedor), la categoría que tiene, la unidad de medida y su precio. Como los precios fluctúan mucho, se ha optado por tener un histórico de los precios de los productos para posterior análisis de los datos.
Por lo que respecta a la categoría del producto, se puede apreciar que un producto tiene una categoría, y esa categoría a su vez puede estar o no estar dentro de otra categoría o englobar a otras categorías.
De los proveedores, además de los datos básicos almacenamos los contactos de estos y cómo contactan con nuestra empresa (a través de qué medios).
En cuanto al producto, puede estar compuesto por piezas o no. Si lo estuviera, almacenamos la cantidad de cada pieza para cada producto. Las piezas que conforman el producto a su vez pueden estar compuestas por otras piezas.
Pasando al tema económico, nuestro sistema almacena las facturas (que pueden tener uno o muchos pedidos). Para tener registrado los cobros de estas facturas, tenemos una tabla que almacena el banco a través del cual se ha realizado el cobro, con qué fecha y el importe de dicho cobro.
Si analizamos en detalle la entidad productos, podemos ver que tenemos datos como su nombre, quién nos lo suministra (proveedor), la categoría que tiene y com ohemos comentado anteriormente la unidad de medida (unidades, metros, kilos, …).
- Di cuántos clientes han realizado pedidos de productos suministrados por al menos nueve proveedores diferentes. Solución: 33
SELECT COUNT(*) FROM (SELECT
c.*
FROM clientes c
JOIN pedidos p ON c.id = p.fk_cliente
JOIN lineas_pedido dp ON p.id = dp.fk_pedido
JOIN productos pr ON dp.fk_producto = pr.id
GROUP BY c.id
HAVING COUNT(DISTINCT pr.fk_proveedor) >= 9)t;
- Escribe el número de clientes que han recibido todos sus pedidos. Teniendo en cuenta que entregado es igual a 1 cuando es verdadero y 0 cuando es falso. Solución 9114
SELECT COUNT(DISTINCT c.id)
FROM clientes c
JOIN (
SELECT fk_cliente, COUNT(*) AS total_pedidos
FROM pedidos
GROUP BY fk_cliente
HAVING SUM(entregado) = COUNT(*)
) AS pedidos_entregados ON c.id = pedidos_entregados.fk_cliente;
SELECT * from
(SELECT COUNT(id) c1, fk_cliente clt1 from pedidos
GROUP BY fk_cliente) todos_pedidos,
(SELECT COUNT(id) c2, fk_cliente clt2 from pedidos
WHERE entregado
GROUP BY fk_cliente) entregado_pedidos
WHERE clt1=clt2 AND c1=c2;
- Cuenta el número de contactos que tiene el proveedor que más productos tiene. Solución 17
SELECT COUNT(*) FROM contactos WHERE fk_proveedor =
(SELECT pr.id FROM categorias c JOIN productos p
ON c.id = p.fk_categoria JOIN proveedores pr
ON p.fk_proveedor = pr.id
GROUP BY pr.id
ORDER BY COUNT(pr.id) DESC LIMIT 1);
- A partir del CRM indica cual es la hora (del 0 al 23) en que se ha hecho más acciones comerciales. Solución 16
SELECT HOUR(fechahora), COUNT(*) FROM crm
GROUP BY HOUR(fechahora)
ORDER BY 2 desc;
- Determina cuántos empleados tienen 3 o más subordinados directos a su cargo. Solución 2
SELECT COUNT(*)
FROM (
SELECT e.id
FROM empleados e
JOIN empleados sub ON e.id = sub.fk_empleado
GROUP BY e.id
HAVING COUNT(*) >= 3
) AS supervisores;
- Indica el mayor importe de cada año (ejercicio) que no se ha cobrado.
SELECT sum(lp.precio), YEAR(pe.fecha) FROM
lineas_pedido lp left JOIN pedidos pe
ON lp.fk_pedido = pe.id left JOIN facturas fa
ON pe.fk_factura = fa.id left JOIN cobros
ON fa.id = cobros.fk_factura
WHERE cobros.fk_factura IS NULL
GROUP BY YEAR(pe.fecha)
ORDER BY 1 desc;
6585541
SELECT sum(lp.precio*lp.cantidad), YEAR(pe.fecha) FROM
lineas_pedido lp left JOIN pedidos pe
ON lp.fk_pedido = pe.id left JOIN facturas fa
ON pe.fk_factura = fa.id left JOIN cobros
ON fa.id = cobros.fk_factura
WHERE cobros.fk_factura IS NULL
GROUP BY YEAR(pe.fecha)
ORDER BY 1 desc;
39702160
- ¿Cuánto se ha cobrado por el banco ING.
SELECT sum(importe)
FROM cobros where
fk_banco IN (SELECT id FROM bancos WHERE nombre LIKE "%ing%");
3819191
- Cuenta el número de piezas que están formadas por otras piezas
SELECT COUNT(DISTINCT fk_pieza) FROM piezas;
O BIEN ....
SELECT COUNT(*) FROM piezas
WHERE id IN (SELECT DISTINCT fk_pieza FROM piezas);
234
- Cuenta el número de piezas que forman parte de otras piezas.
SELECT COUNT(DISTINCT id) FROM piezas WHERE fk_pieza is not null;
245
Cuenta cuantos productos hay que tengan un precio mayor a la media del precio histórico de dicho producto tras haber incrementado en un 5% (x 1,05) el precio medio histórico de dicho producto.
SELECT * FROM productos p
WHERE p.precio > (SELECT AVG(precio) FROM historico_precios
WHERE fk_producto = p.id)*1.05;
SELECT * FROM productos p WHERE p.precio > (SELECT AVG(precio*1.05) FROM historico_precios WHERE fk_producto = p.id);
3
- Determina cuántos productos están compuestos directamente por más de 5 piezas diferentes
SELECT COUNT(*)
FROM (
SELECT fk_producto
FROM piezas_productos
GROUP BY fk_producto
HAVING COUNT(fk_pieza) > 5
) AS productos_con_mas_de_cinco_piezas;
89
- Encuentra cuántos productos no han tenido un cambio de precio en el último mes
SELECT COUNT(*)
FROM (
SELECT pr.id
FROM productos pr
JOIN historico_precios hp ON pr.id = hp.fk_producto
GROUP BY pr.id
HAVING MAX(hp.fecha) < "2024/03/18" - INTERVAL 1 MONTH
) AS productos_con_precio_antiguo;
186 (día del examen)
- Cuenta el número de proveedores que hay de España (ES)
SELECT COUNT(pr.id) FROM proveedores pr JOIN localidades l
ON pr.fk_localidad = l.id JOIN municipios m
ON l.fk_municipio = m.id JOIN provincias p
ON m.fk_provincia = p.id JOIN regiones r
ON r.id = p.fk_region JOIN paises pa
ON r.fk_pais = pa.id
WHERE pa.iso2="ES"
0
- Cuenta cuantos proveedores tienen un precio medio de sus productos mayor o igual al precio medio de los productos de la categoría de bebidas subcaterías de ésta
SET @pm:=(SELECT AVG(p.precio) FROM productos p JOIN categorias c
ON p.fk_categoria = c.id
WHERE c.nombre ="Bebidas" OR c.fk_categoria = c.id);
select count(*) FROM (SELECT AVG(pt.precio) pp FROM proveedores pr JOIN productos pt
ON pr.id = pt.fk_proveedor
GROUP BY pr.id
HAVING AVG(pt.precio)>=@pm) t;
13
- Suma el número de paises del continente que más paises tiene, al número de regiones del país que más regiones tiene y réstale el número de provincias que tiene España.
SELECT SUM(campo) from
(SELECT MAX(t1.cont) AS campo FROM (SELECT COUNT(p.id) cont, co.nombre
FROM continentes co JOIN paises p
ON co.id = p.fk_continente
GROUP BY co.nombre) t1
UNION
SELECT MAX(t2.cont) FROM (SELECT COUNT(r.id) cont, p.nombre
FROM paises p JOIN regiones r
ON r.fk_pais = p.id
GROUP BY p.nombre) t2
UNION
SELECT -1*COUNT(p.id) cont
FROM paises p JOIN regiones r
ON r.fk_pais = p.id JOIN provincias pr
ON pr.fk_region = r.id
WHERE p.iso2 = "ES") t3
212
- Cuenta cuantas categorías tienen tienen más de 25 productos con un precio superior a 290 €.
SELECT
cat.nombre AS nombre_categoria
FROM categorias cat
JOIN productos prod ON cat.id = prod.fk_categoria
WHERE prod.precio > 290
GROUP BY cat.id
HAVING COUNT(*) > 25;
1
Encuentra cuántos clientes han adquirido en total 50 o más unidades de productos.
SELECT COUNT(*)
FROM (
SELECT c.id
FROM clientes c
JOIN pedidos pe ON c.id = pe.fk_cliente
JOIN lineas_pedido lp ON pe.id = lp.fk_pedido
GROUP BY c.id
HAVING SUM(lp.cantidad) >= 50
) t;
144
- Cuenta el número de facturas que todavía no se han cobrado.
SELECT COUNT(DISTINCT facturas.id) FROM facturas left JOIN cobros
ON facturas.id = cobros.fk_factura
WHERE cobros.fk_factura IS NULL;
6758
- Indica cuantos empleados tienen más de 55 años.
SELECT COUNT(*) FROM empleados WHERE
DATE_SUB(NOW(), INTERVAL 55 YEAR) >= empleados.fechanac
22
- Indica cuantas veces se ha utilizado el medio (medio de comunicación) más utilizado durante el primer y cuarto trimestre del año 2023 para las comunicaciones en el CRM excluyendo el «fax» y la «carta postal»
SELECT count(fk_medio), nombre FROM crm JOIN medios
ON crm.fk_medio = medios.id
WHERE YEAR(fechahora)=2023 AND QUARTER(fechahora) IN (1,4) AND
medios.nombre !="fax" AND medios.nombre !="carta postal"
GROUP BY fk_medio
ORDER BY 1 desc;
84
- Para cada proveedor de Estados Unidos (iso2=«US»), cuenta el número total de productos que suministra cada uno. Luego busca el nombre del proveedor que suministra 22 productos, e indica el id de la localidad a la que pertenece dicho proveedor. (No pongas separador de miles en el número si los hubiera)
/*SELECT COUNT(pro.id), p.nombre, pro.nombre FROM productos pro join proveedores p
ON pro.fk_proveedor= p.id JOIN localidades l
ON p.fk_localidad = l.id JOIN municipios m
on l.fk_municipio = m.id JOIN provincias pr
ON pr.id = m.fk_provincia JOIN regiones r
ON r.id = pr.fk_region JOIN paises pa
ON pa.id = r.fk_pais
WHERE pa.iso2="us"
GROUP BY p.id; */
SELECT l.* FROM localidades l JOIN proveedores p
ON l.id = p.fk_localidad
WHERE p.nombre="Epson Corporation"
40474
- Calcula cuántos pedidos han incluido productos con un precio superior a 1000 euros durante el año 2023.
SELECT COUNT(DISTINCT p.id)
FROM pedidos p
JOIN lineas_pedido lp ON p.id = lp.fk_pedido
JOIN productos pr ON lp.fk_producto = pr.id
WHERE pr.precio > 1000
AND year(p.fecha) = 2023;
3252
- Indica cual es el precio más repetido en todos los productos
SELECT COUNT(PR.ID), PR.PRECIO
FROM productos pr
GROUP BY PR.PRECIO
ORDER BY 1 DESC;
1319
- Determina cuántos productos en la base de datos contienen al menos una pieza que tenga la palabra «motor» en su nombre
SELECT COUNT(DISTINCT p.id)
FROM productos p
JOIN piezas_productos pp ON p.id = pp.fk_producto
JOIN piezas pz ON pp.fk_pieza = pz.id
WHERE pz.nombre LIKE '%motor%';
182
- Calcula cuántos productos han sido pedidos más de 250 veces y entregados a los clientes.
SELECT sum(lp.cantidad)
FROM productos pr
JOIN lineas_pedido lp ON pr.id = lp.fk_producto
JOIN pedidos p ON lp.fk_pedido = p.id
WHERE p.entregado = true
GROUP BY pr.id
HAVING sum(lp.cantidad) > 250;
2
- Cuenta el número de productos servidos desde cada continente, e indica únicamente el número del mayor número de ellos, es decir, únicamente el mayor número de productos ofrecidos desde cada continente.
SELECT COUNT(pd.id), c.nombre FROM productos pd join proveedores pr
on pd.fk_proveedor = pr.id JOIN localidades l
ON pr.fk_localidad = l.id JOIN municipios m
ON l.fk_municipio = m.id JOIN provincias p
ON m.fk_provincia = p.id JOIN regiones r
ON r.id = p.fk_region JOIN paises pa
ON r.fk_pais = pa.id JOIN continentes c
ON pa.fk_continente = c.id
GROUP BY c.nombre
ORDER BY 1 desc
299
- Cuenta el número de productos que hay por categoría y a la categoría que menos productos tenga súmale el número de piezas del producto que más piezas tenga. Ten en cuenta que un producto está formado por un número X de piezas y que esas piezas pueden ser más de una (cantidad)
SELECT SUM(cont) FROM (
select MIN(cont) cont FROM (SELECT COUNT(id) cont FROM productos
GROUP BY fk_categoria) t
UNION
SELECT MAX(cont) FROM (SELECT SUM(cantidad) cont FROM piezas_productos
GROUP BY fk_producto) t) t
29
- Indica el nombre tal y como aparece en la base de datos de la séptima provincia que más clientes tenemos en nuestra base de datos.’ style=‘blue’ collapse_link=‘true’]
SELECT COUNT(cl.id), p.nombre FROM clientes cl JOIN localidades l
ON cl.fk_localidad = l.id JOIN municipios m
ON l.fk_municipio = m.id JOIN provincias p
ON m.fk_provincia = p.id JOIN regiones r
ON r.id = p.fk_region JOIN paises pa
ON r.fk_pais = pa.id
WHERE pa.iso2="ES"
GROUP BY p.nombre
ORDER BY 1 DESC LIMIT 1 OFFSET 6
Valencia
- Indica el nombre, tal como aparezca en la base de datos, del segundo país donde más proveedores tenemos registrados, excluyendo los continentes asiático (AS), sur americano (SA) y africano (AF).
SELECT COUNT(pr.id), pa.nombre FROM proveedores pr JOIN localidades l
ON pr.fk_localidad = l.id JOIN municipios m
ON l.fk_municipio = m.id JOIN provincias p
ON m.fk_provincia = p.id JOIN regiones r
ON r.id = p.fk_region JOIN paises pa
ON r.fk_pais = pa.id JOIN continentes c
ON pa.fk_continente = c.id
WHERE c.cod !="AS" AND c.cod !="SA" AND c.cod !="AF"
GROUP BY pa.nombre
ORDER BY 1 desc
Italy
Cuenta cuantas categorías no pertenecen a ninguna otra categoría, y de esas categorías que no pertenecen a ninguna otra categoría, cuenta las que no tienen ninguna subcategoría. (Sol. 18)
select count(id) from categorias where fk_categoria is NULL
AND id not IN (SELECT DISTINCT fk_categoria FROM categorias
WHERE fk_categoria IS NOT null)
Cuenta cuantas categorías no pertenecen a ninguna otra categoría, y de esas categorías que no pertenecen a ninguna otra categoría, cuenta las que sí tienen alguna subcategoría. (Sol. 6)
select count(id)
from categorias where fk_categoria is NULL AND
id IN (SELECT DISTINCT fk_categoria FROM categorias);
Cuenta cuantos productos se han ofertado (aparecen en los pedidos) cuya cantidad ofertada de ese producto ha sido mayor a 300. (Sol. 236)
SELECT COUNT(*) FROM
(SELECT distinct fk_producto FROM lineas_pedido
GROUP BY fk_producto
having sum(cantidad) > 300) t;
o bien
SELECT COUNT(id) FROM productos
WHERE id IN (SELECT DISTINCT fk_producto FROM lineas_pedido
GROUP BY fk_producto
having sum(cantidad) > 300);
Dado el precio medio histórico de TODOS los productos, indica cuantos productos actualmente están a un precio mayor a ese precio medio histórico. (Sol. 509)
SELECT COUNT(id) FROM productos
WHERE precio > (SELECT AVG(precio) FROM historico_precios);
Dado el precio medio actual de los productos, de los productos que no pertenecen a ninguna categoría o que pertenecen a la categoría de “Tecnología” o bien subcategorías directas de la categoría “Tecnología” (es decir fk_categoria = id de la categoría de “Tecnología”), indica cuantos productos actualmente están a un precio menor al precio medio actual de todos los productos. (Sol. 19)
SET @miid := (SELECT id FROM categorias WHERE nombre="Tecnología");
SELECT count(id) FROM productos
WHERE
(fk_categoria IS NULL OR
fk_categoria in (
SELECT id FROM categorias WHERE id=@miid or
fk_categoria IN (SELECT id FROM categorias WHERE fk_categoria=@miid)
)
) and
precio < (SELECT AVG(precio) FROM productos);
Teniendo en cuenta el que el horario laboral oficial es de 8:00:00 am a 15:30:00 pm, ambas horas includas, indica cuantas acciones comerciales (crm) se hicieron fuera de horario laboral. (Sol. 3507)
SELECT SUM(h) FROM
(SELECT COUNT(*) h FROM crm
UNION all
SELECT COUNT(*)*-1 h FROM crm
WHERE time(fechahora) BETWEEN '08:00:00' AND '15:30,00') t
Teniendo en cuenta que el año (ejercicio fiscal) lo obtenemos de la fecha de un pedido, indica el mayor número medio de días redondeado sin decimales y por cada año, que han pasado desde que se hace el pedido hasta que se hace su factura. Sol 553.
SELECT round(avg(DATEDIFf(F.fecha,p.fecha)),0), year(p.fecha)
FROM pedidos p LEFT JOIN facturas f
ON p.fk_factura = f.id
GROUP BY 2
ORDER BY 1 desc
Escribe el total de ventas (precio x cantidad) para la categoría que más vendió en el año 2021 — sol 1392591
SELECT c.nombre AS categoria, SUM(lp.precio * lp.cantidad) AS total_ventas
FROM productos p
JOIN categorias c ON p.fk_categoria = c.id
JOIN lineas_pedido lp ON p.id = lp.fk_producto
JOIN pedidos pe ON lp.fk_pedido = pe.id
JOIN facturas f ON pe.fk_factura = f.id
WHERE YEAR(f.fecha) = 2021
GROUP BY c.nombre
order by 2 desc;
Cuenta el número de clientes que no pidieron nada en el 2023. Sol-192102.
SELECT count(distinct c.id)
FROM clientes c
left JOIN pedidos p
ON c.id = p.fk_cliente AND year(p.fecha) = 2023
WHERE p.id IS NULL;
Cuenta el número de productos que han sido pedidos al menos una vez y que tienen un precio superior a 191. Sol: 954
SELECT COUNT(distinct pr.id)
FROM productos pr
JOIN lineas_pedido lp ON pr.id = lp.fk_producto
WHERE pr.precio > 191;
Indica el número de proveedores que tienen al menos un producto asociado a las categorías que tienen más de 29 productos diferentes. Sol: 22
SELECT distinct pr.nombre
FROM proveedores pr
JOIN productos pd ON pr.id = pd.fk_proveedor
and pd.fk_categoria in (
SELECT fk_categoria
FROM productos
GROUP BY fk_categoria
HAVING COUNT(*) > 29
);
Indica el máximo número de ventas por año y por cliente, indicando únicamente el importe del más alto. Sol 97513
SELECT c.id, c.nombre, YEAR(f.fecha) AS year, SUM(lp.cantidad * lp.precio) AS total_ventas
FROM clientes c
JOIN pedidos pe ON c.id = pe.fk_cliente
JOIN lineas_pedido lp ON pe.id = lp.fk_pedido
JOIN productos p ON lp.fk_producto = p.id
JOIN facturas f ON pe.fk_factura = f.id
GROUP BY c.id, c.nombre, YEAR(f.fecha)
order by 4 desc;
Número de empleados que han realizado pedidos por encima de la media de todos los empleados. Recordad que la media es el total de todos los pedidos divido por el número de empleados. Sol: 54
SET @totempl:=(SELECT COUNT(id) FROM empleados);
SET @totpedidos:=(SELECT COUNT(id) FROM pedidos);
SET @media:=@totpedidos/@totempl;
SELECT COUNT(*) FROM (SELECT COUNT(p.id) FROM pedidos p
GROUP BY (p.fk_empleado)
HAVING COUNT(p.id)>@media)t;
Cuenta el número de clientes que han realizado pedidos en una fecha posterior al 2023-01-01 y que pertenezcan a la región de Andalucía. Sol: 540
SELECT count(distinct c.id)
FROM pedidos p
JOIN clientes c ON c.id = p.fk_cliente
JOIN localidades l ON c.fk_localidad = l.id
JOIN municipios m ON l.fk_municipio = m.id
JOIN provincias pv ON m.fk_provincia = pv.id
JOIN regiones r ON pv.fk_region = r.id
WHERE p.fecha > '2023-01-01' AND
r.nombre LIKE "%andalucia%";
Indica el ID del producto que más se ha pedido en los trimestres impares del año 2022. Sol: 376
SELECT p.id, SUM(lp.cantidad) AS total_cantidad
FROM productos p
INNER JOIN lineas_pedido lp ON p.id = lp.fk_producto
JOIN pedidos pd ON lp.fk_pedido = pd.id
WHERE quarter(pd.fecha) IN (1,3) AND YEAR(pd.fecha)= 2022
GROUP BY p.id
order BY 2 desc;
Cuenta el número de productos que no han sido pedidos en el último trimestre de 2022: Sol: 921
SELECT count distinct (pr.id)
FROM productos pr
LEFT JOIN lineas_pedido lp ON pr.id = lp.fk_producto
LEFT JOIN pedidos pe ON lp.fk_pedido = pe.id
WHERE pe.fecha IS NULL OR (YEAR(pe.fecha) = 2022 AND QUARTER(pe.fecha) = 4);
Cuenta el número de proveedores que tienen al menos un producto con un precio superior a 500 y que están asociados a una categoría con nombre “Electrónica”. Sol: 7
SELECT DISTINCT pr.nombre, pr.telefono
FROM proveedores pr
JOIN productos pd ON pr.id = pd.fk_proveedor
JOIN categorias cat ON pd.fk_categoria = cat.id
WHERE pd.precio > 500 AND cat.nombre = 'Electrónica';