Resoluciones de ejercicios de parcial

parciales-bd.md

SQL

Dados los siguientes esquemas sobre el envío de productos a clientes:

• clientes (cod_cliente, nombre, tipo_cliente)
• productos (nro_prod, descripción, unidad_medida, precio)
• envíos (cod_envio, cod cliente, dirección, ciudad, provincia)
• detalles envios (cod_envio, nro_prod, cantidad_enviada)

1. Obtenga el número (columna nro prod) y descripción de aquellos productos que fueron enviados al cliente de código 1000 (columna cod cliente) y no fueron enviados ni al cliente de código 2000 ni al de código 3000.

select pr.nro_prod, descripcion
from productos pr
inner join detalles_envios de using(nro_prod)
where exists (
    select 1 from envios e
    where e.cod_envio = de.cod_envio 
    and e.cod_cliente = 1000 
    and not exists (
    	select 1 from envios e2
        where e.cod_envio = e2.cod_envio 
        and e2.cod_cliente = 2000 or e2.cod_cliente = 3000
    )
);

2. Para cada cliente al que se le hayan efectuado al menos tres envíos, devuelva su código de cliente, nombre y cantidad de distintos productos que se le enviaron. Ordene el listado por esta última cantidad descendente.

select cs.cod_cliente, nombre, count(distinct nro_prod) as productos_enviados
from clientes cs 
inner join envios e using (cod_cliente)
inner join detalle_envios de on (e.cod_envio = de.cod_envio)
group by cs.cod_cliente
having count(e.cod_envio) >= 3
order by productos_enviados desc;

3. Para aquellos clientes a los que se les haya enviado al menos uno de los productos de mayor precio, devuelva su código de cliente y nombre.

select cs.cod_cliente, cs.nombre
from clientes cs
inner join envios e using (cod_cliente)
inner join detalle_envios de using (cod_envio)
inner join productos pr using (nro_producto)
group by cs.cod_cliente
having max(pr.precio) >= all (select max(precio) from productos);

4. Para cada tipo de cliente, devuelva el tipo de cliente, cuántos clientes son de dicho tipo y cuántos envíos se les hizo a clientes de ese tipo.

select tipo_cliente, count(distinct cs.cod_cliente) as cant_clientes, count(distinct cod_envio) as cant_envios
from clientes cs
left join envios e using(cod_cliente)
group by tipo_cliente;

5. Para aquellos productos que hayan sido enviados al menos una vez a todos los clientes, devuelva su número de producto (columna nro_prod) y descripción.

select pr.nro_prod, pr.descripcion
from productos pr
where not exists (
	(select cod_cliente from clientes)
    except
    (select cod_cliente 
     from envios e
     where pr.nro_prod = e.nro_prod
    )
);

6. Para cada envío hecho al cliente de código 1000 (columna cod_cliente), indique el código de envío, la dirección a la que debe ser enviado, cuántos distintos productos se enviaron en el envío y la cantidad total enviada de todos los productos que conforman el envío.

select e.cod_envio, e.direccion, count(distinct de.nro_prod) as cant_prod, sum(de.cantidad_enviada) as cant_total
from envios e
inner join detalle_envios de using(cod_envio)
where e.cod_cliente = 1000
group by e.cod_envio, e.direccion

Algebra Relacional

max_prec = π precio (productos - (σ p1.precio < p2.precio ((ρ p1 productos) ⨯ (ρ p2 productos))))
max_prod = π nro_prod (max_prec ⨝ productos)
env = π cod_envio (max_prod ⨝ detalles_envios)
cli = π cod_cliente (env ⨝ envios)
cli ⨝ clientes

A = π cod_envio (π cod_envio, nro_producto (detalles_envios) ÷ π nro_producto productos)
A ⨝ clientes

A = π cod_envio (σ productos.nro_prod = detalles_envios.nro_prod ∧ productos.unidad_medida = 'Ton' (productos ⨯ detalles_envios))
SF = π cod_cliente (σ envios.direccion = 'Santa Fe' ∧ envios.cod_envio = A.cod_envio (A ⨯ envios))
NQ = π cod_cliente (σ envios.direccion = 'Neuquen' ∧ envios.cod_envio = A.cod_envio (A ⨯ envios))
clientes ⨝ (SF ∩ NQ)

Pasaje de modelo

1. Parcial
   • A(idA)
   • B(idB, B1)
   • D(idD, D1, D2)
   • E(idE, E1, E2)
   • C(idD, discC, H1, H2)
   • G(idD, discC, idA, idB)
   • J(idB1, idB2, J1)
   • I(idD, idE, I1, I2), CK = { idE }
2. Primer Recuperatorio
   • A(idA, A1)
   • B(idA)
   • C(idA, C1, C2)
   • D(idA, D1)
   • I(idI1, idI2, I1)
   • F(idA, discF, F1, F2, E1)
   • G(idA1, discF1, idA2, discF2, G1, G2)
   • H(idA1, idA2, idI1, idI2), CK = { { idA1, {idI1, idI2} }, { idA2, {idI1, idI2} } }
3. Segundo Recuperatorio
   • E(idE1, idE2)
   • F(idF, F1)
   • G(idF)
   • H(idF, H1, H2, idI)
   • I(idI, I1, I2, idF)
   • A(idF1, idF2, A1)
   • C(idE1_1, idE2_1, idE1_2, idE2_2, idF, C1, C2), CK = { {idE1_2, idE2_2}, idF }