Taller 4 y 5 de BDD

taller4-5.sql

-- 2
select
	a.padron
from alumnos a
where exists(select 1 from notas n where n.padron = a.padron and n.codigo = 71 and n.numero = 14 and n.nota >= 4)
	and not exists(select 1 from notas n where n.padron = a.padron and n.codigo = 71 and n.numero = 15 and n.nota >= 4)

-- 3
select 
	a.padron,
    a.apellido
from alumnos a
join notas n on n.padron = a.padron
where a.intercambio
group by 1,2
having count(distinct(n.codigo, n.numero, n.fecha)) = (select count(*) from materias)

-- 4
select 
	a.padron,
    a.apellido,
    array_agg(n.nota)
from alumnos a
join notas n on n.padron = a.padron and n.codigo = 75
group by 1,2
having count(distinct(n.codigo, n.numero, n.fecha)) = (select count(*) from materias where codigo = 75)

-- 5
select
	count(distinct a.padron)
from alumnos a
join notas n on n.padron = a.padron and n.nota >= 7

-- 6
select
	padron
from(
select
	a.padron,
	rank() over (partition by n.numero order by n.nota desc) as row
from alumnos a
join notas n on n.padron = a.padron and n.codigo = 75
) a where row = 1

-- 7
select
	padron
from(
select
	a.padron,
    rank() over (order by count(*) desc) as row,
    count(*)
from alumnos a
join notas n on n.padron = a.padron and n.nota >= 4
group by 1
order by 2 desc) a where row = 1