Repaso Simulacro

select * from clase;
--mostrar cuantos estudiantes estan en cada nivel mediante el nombre de nivel:
SELECT n.nom_nivel,
COUNT (c.dni) FROM clase c
INNER JOIN idioma i ON c.cod_idioma=i.cod_idioma
INNER JOIN nivel n ON i.cod_nivel=n.cod_nivel
GROUP BY n.nom_nivel ORDER BY n.nom_nivel desc;

SELECT DISTINCT c.dia FROM clase c
INNER JOIN profesor p ON c.legajo=p.legajo
INNER JOIN alumno a ON c.dni=a.dni
INNER JOIN idioma i ON c.cod_idioma=i.cod_idioma
WHERE i.precio BETWEEN 1000 AND 1100;

--¿Qué consulta proporciona los nombres y domicilios de los alumnos que asistieron a clases del
--profesor "Juarez" en el nivel "Básico"?

select a.nom_alumno,a.domicilio from alumno a
inner join clase c on a.dni = c.dni
inner join profesor p on c.legajo = p.legajo
inner join idioma i on c.cod_idioma = i.cod_idioma
inner join nivel n on i.cod_nivel = n.cod_nivel
where p.legajo = '89' and n.nom_nivel = 'basico';

select * from nivel;

--A.2) Realice un cursor que reciba como parámetro un código de nivel y guarde todas las clases para ese
--código de nivel. Luego recorra el cursor y las clases cuyo precio son menores a 1500 tienen que ser
--guardados en la tabla “auditoría”. El cursor tiene los siguientes campos: cod_idioma, nom_idioma, precio,
--nom_nivel, día. La tabla "auditoria" no posee clave primaria y tiene la misma estructura del cursor.

select * from auditoria;

create or replace function guardarClase(codigo_nivel varchar)
returns void as $$
declare
    guardar cursor for
        select i.cod_idioma,i.nom_idioma,i.precio,n.nom_nivel,c.dia from clase c
        inner join idioma i on c.cod_idioma = i.cod_idioma
        inner join nivel n on i.cod_nivel = n.cod_nivel
        where n.cod_nivel = codigo_nivel;

    v_cod_idioma varchar(20);
    v_nom_idioma varchar(20);
    v_precio numeric;
    v_nom_nivel varchar(20);
    v_dia date;

begin
     open guardar;
     loop
         fetch guardar into v_cod_idioma,v_nom_idioma,v_precio,v_nom_nivel,v_dia;
         exit when not found;

    if v_precio < 1500 then
     insert into auditoria(cod_idioma,nom_idioma,precio,nom_nivel,dia)
     values(v_cod_idioma,v_nom_idioma,v_precio,v_nom_nivel,v_dia);

     end if;
     end loop;

     close guardar;
end;
$$language plpgsql;

select guardarClase('INT');
select * from auditoria;


--A.1) Realice un trigger que antes de registrar una nueva clase, controle si el idioma es “Alemán” no debe
--permitir la inserción, además mostrar el mensaje “curso suspendido”.

create or replace function control()
returns trigger as $$
begin
    if(select nom_idioma from idioma
        where cod_idioma = new.cod_idioma)  = 'AL' then
        raise exception 'curso suspendido';

    end if;
    return new;
end;
$$language plpgsql

create trigger insercion
before insert on clase
for each row
execute function control();

insert into clase(dni,cod_idioma,legajo,dia,aula)
values(555,'AL',78,'07-02-2024',14);

insert into clase(dni,cod_idioma,legajo,dia,aula)
values(555,'PO',78,'07-02-2024',14);

SELECT * FROM CLASE;

--1) Utilice pareo de tablas y muestre la cantidad de idiomas que dicta cada profesor, agrupados por nombre de profesor.
select  p.nombre, count(c.cod_idioma) from clase c
inner join profesor p on c.legajo = p.legajo
inner join idioma i on c.cod_idioma = i.cod_idioma
group by p.nombre;

--) Utilice inner join y muestre la duración, el nombre de nivel, el nombre del idioma y el precio de las clases dictadas el día
--26/10/2024.
select n.duracion,n.nom_nivel,i.nom_idioma,i.precio,c.dia from clase c
inner join idioma i on c.cod_idioma = i.cod_idioma
inner join nivel n on i.cod_nivel = n.cod_nivel
where c.dia = '26-10-2024';

--3) Utilice subconsultas y muestre para el profesor “Castro” las aulas donde dicta clases.
select p.nombre, c.aula from clase c
inner join profesor p on  c.legajo = p.legajo
where c.legajo = 87;

--4) Utilice subconsultas y muestre para el alumno “Maria Rocha” los días y aulas asignados para su clase de “Inglés”.
select a.nom_alumno,c.dia,c.aula,i.nom_idioma from clase c
inner join alumno a on c.dni = a.dni
inner join idioma i on c.cod_idioma = i.cod_idioma
where a.dni = 111 and i.nom_idioma = 'ingles';

--5) Muestre el nombre del profesor y el precio de los idiomas cuyo precio son menores a 1100,00.
select p.nombre,i.precio,i.nom_idioma from clase c
inner join profesor p on c.legajo = p.legajo
inner join idioma i on c.cod_idioma  = i.cod_idioma
where i.precio <= 1100.00;

--6) Realice una función que permita ingresar el Código de idioma y muestre la cantidad de clases que se dictan para ese
--idioma.
create or replace function cantidadClase(codigo_idioma varchar)
returns integer as $$
declare
    cantidad int;
begin
    select count (*)
    into cantidad from clase c
    inner join idioma i on c.cod_idioma = i.cod_idioma
    where i.cod_idioma = codigo_idioma;

    return cantidad;

end;
$$language plpgsql;

DROP FUNCTION cantidadClase(varchar);

select cantidadClase('IN');

--7) Realice una función que reciba como parámetro el Legajo de Profesor, cargue un cursor con los códigos y precios de los
--idiomas que tiene a su cargo y muestre el total recaudado. Considere que se realiza el 20% de descuento cuando el idioma
--es “Alemán”
create or replace function contar (legajopro int)
returns numeric as $$
declare
    cursorsito cursor for
    select i.cod_idioma,i.precio
    from clase c
    inner join idioma i on c.cod_idioma = i.cod_idioma
    inner join profesor p on c.legajo = p.legajo
    where p.legajo = legajopro;

    v_cod_idioma varchar;
    v_precio numeric;

    total numeric := 0;

begin
    open cursorsito;
    loop
        fetch cursorsito into v_cod_idioma,v_precio;
        exit when not found;

    if(select nom_idioma from idioma where cod_idioma = v_cod_idioma) = 'AL' THEN
        total := total + (v_precio * 0.80);
    else
        total := total + v_precio;
    end if;

    end loop;

    close cursorsito;

    return total;
end;
$$language plpgsql;

drop function contar(int);

SELECT contar(87);