simulacro2024RepasoNuevo

SELECT nom_alumno, domicilio FROM alumno WHERE dni IN
(SELECT dni FROM clase WHERE cod_idioma IN
(SELECT cod_idioma FROM idioma WHERE cod_nivel =
(SELECT cod_nivel FROM nivel where nom_nivel = 'basico')) AND legajo IN
(SELECT legajo FROM profesor WHERE nombre = 'juarez'));

SELECT nom_alumno, domicilio FROM alumno WHERE dni IN
(SELECT dni FROM clase WHERE cod_idioma IN
(SELECT cod_idioma FROM idioma WHERE cod_nivel='BAS') AND legajo IN
(SELECT legajo FROM profesor WHERE nombre = 'juarez'));

SELECT a.nom_alumno, a.domicilio FROM alumno a
INNER JOIN clase c ON a.dni=c.dni
INNER JOIN idioma i ON c.cod_idioma=i.cod_idioma
INNER JOIN nivel n ON i.cod_nivel=n.cod_nivel
INNER JOIN profesor p ON c.legajo=p.legajo
WHERE n.nom_nivel='basico' AND p.nombre='juarez';

select * from nivel;

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


--2) 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 p.nombre = 'castro';

--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.nom_alumno = 'maria rocha' 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.nom_idioma, i.precio from clase c
inner join profesor p on c.legajo = p.legajo
inner join idioma i on i.cod_idioma = c.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 cantidad_clases(codidioma varchar)
returns integer as $$
declare
    cantidad integer;
begin
    select count(*) into cantidad from clase c
    inner join idioma i on c.cod_idioma = i.cod_idioma
    where c.cod_idioma = codidioma;
    return cantidad;
end;
$$language plpgsql;

select cantidad_clases('AL');


--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 recaudacion(legajjo integer)
returns numeric as $$
declare
    cursorr 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 = c.legajo
    where p.legajo = legajjo;

    v_codigo varchar;
    v_precio numeric;
    total numeric :=0;

begin
    open cursorr;
    loop
        fetch cursorr into v_codigo,v_precio;
        exit when not found;

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

    end loop;
    close cursorr;
    return total;
end;
$$language plpgsql;

select recaudacion(88);


--8) Realice un cursor que reciba como parámetro un “dia” y guarde el dni, nombre del idioma, nombre del profesor y nombre
--del alumno, luego recorra el cursor y muestre el contenido del mismo

create or replace function recibir(diaa date)
returns void as $$
declare
    datos cursor for
    select a.dni, i.nom_idioma, p.nombre, a.nom_alumno from clase c
    inner join alumno a on c.dni = a.dni
    inner join profesor p on c.legajo = p.legajo
    inner join idioma i on c.cod_idioma = i.cod_idioma
    where c.dia = diaa;

    v_dni integer;
    v_nombre_idioma varchar;
    v_nombre varchar;
    v_nombre_alumno varchar;

begin
    open datos;
    loop
        fetch datos into v_dni,v_nombre_idioma,v_nombre,v_nombre_alumno;
        exit when not found;

        raise notice'dni: %,nombre idioma: %,nombre: %, nombre alumno: %', v_dni,v_nombre_idioma,v_nombre,v_nombre_alumno;
    end loop;
    close datos;
end;
$$language plpgsql;
select recibir('25-10-2024');


--9) Realice un trigger que antes de ingresar una nueva clase verifique que se haya asignado un aula, y si el campo aula está
--vacío no inserte ese registro y debe mostrar el mensaje “debe asignar un aula para la clase”
create or replace function verificacionn()
returns trigger as $$
begin
    if new.aula is null or trim (new.aula) = '' then
        raise exception 'debe asignar un aula para la clase';
    end if;
    return new;
end;
$$language plpgsql;

create trigger verificarr
before insert on clase
for each row
execute function verificacionn();

--10)Realice un trigger que luego de insertar un registro en la tabla “clase”, inserte un nuevo registro en la tabla
--“historial_clase”, ésta última tabla posee la misma estructura de la tabla “clase”, pero se le adiciona el campo
--“fecha_registro” de tipo fecha, en el cual debe registrar la fecha actual (con current_date puede registrar la fecha del
--sistema).

create table historial_clase(dni integer, cod_idioma varchar, legajo integer, dia date, aula integer, fecha_registro date);

create or replace function insertar()
returns trigger as $$
begin
    insert into historial_clase(
    dni,
    cod_idioma,
    legajo,
    dia,
    aula,
    fecha_registro)
    values(
    new.dni,
    new.cod_idioma,
    new.legajo,
    new.dia,
    current_date
    );

    return new;
end;
$$language plpgsql;

create trigger insercionn
after insert on clase
for each row
execute function insertar();