El repaso de la verga segundo parcial

select mnombre, dia
from marineros  inner join reservas on marineros.mid=reservas.mid
where dia>'1999-01-01';

----------------------------------
select count ( mnombre)
from marineros  inner join reservas on marineros.mid=reservas.mid
where mnombre>'popeye';

obtener los numeros de botes alquilados
por el marino n 71

select *
from reservas
where mid = '71'

select bid
from reservas
where mid = '71';

obtener los nombres de los marinos que alquilaron
el bote titanic


select mnombre
from barcos b , marineros m, reservas r
where b.bid=r.bid and m.mid=r.mid and b.bnombre='Titanic';

los barcos que alquilo popeye
select bnombre
from barcos b , marineros m, reservas r
where b.bid=r.bid and m.mid=r.mid and m.mnombre='Popeye';


agregas un marinero en la tabla
insert into marineros values (15,'jack sparrow'. 0 , 36);
eliminas un marinero de la tabla
delete from marineros where mnombre='jack sparrow';
cambiar valores de una fila
update marineros set mnombre='Marco Poli' where mnombre= 'Marco Polo';

create table alumnos (
id int not null,
nombre varchar(20) not null,
apellido varchar(20) not null,
edad int not null);

create table materias(
id int not null,
nombre varchar(20) not null
);


create table cursa (
aid int not null,
mid int not null
);

alter table cursa add column finsc date;

alter table alumnos add primary key (id);

alter table materias add primary key (id);

alter table cursa add foreign key (aid) references alumnos(id);

alter table cursa add foreign key (mid) references materias(id);

insert into alumnos values (1, 'jorge','carvalho', 23);

insert into alumnos values (2,'lucas', 'vargas',27);

insert into alumnos values (3, 'gonzalo','cardoso',30);

insert into alumnos values (4 , 'gonzalo','pesado',22);

insert into alumnos values (5, 'maxi','correa',23);

insert into alumnos values (6, 'maxi', 'contetas',22);

insert into materias values (1,'base de datos');

insert into materias values (2,'programacion ');
update materias set nombre 'programacion' where id=2;


insert into materias values (3,'organizacion');

insert into materias values (4 ,'matematica');

insert into materias values (5 ,'psec');

insert into cursa values (1,1,'2018-03-04');

insert into cursa values (2,5,'2017-03-04');

insert into cursa values (3,4,'2017-06-01');

insert into cursa values (1,5,'2016-03-05');

insert into cursa values (1,2,'2018-03-10');

insert into cursa values (5,3,'2018-03-04');



alumnos que hacen programacion

select a.nombre , m.nombre
from alumnos a , materias m , cursa c
where c.aid=a.id and c.mid=m.id and m.nombre='programacion';

cantidad de alumnos que cursan psec

select count(a.nombre)
from alumnos a , materias m , cursa c
where c.aid=a.id and c.mid=m.id and m.nombre='psec';


alumnos que se inscribieron en 2018

select dinstinct(nombre) ,apellido, finsc
from alumnos a inner join  cursa c on a.id=c.aid
where finsc >= '2018-01-01';

insert into alumnos values (7,'fernando','torres',50);
insert into alumnos values (8,'sergio','esquivel',13);

alumnos que no cursan materias

select nombre ,  apellido
from alumnos a
where a.id  not in(select distinct(id)
from alumnos a ,cursa c
where a.id=c.aid  );

obtener alumno mas viejo edad

select max (alumnos.edad)
from alumnos;


obtener alumno mas viejo nombre
select a.nombre
from alumnos a
where a.edad = (select max(edad) from alumnos);


obtener al segundo mas viejo

select max (alumnos.edad)
from alumnos
where edad not in  (select max(edad)from alumnos);

select nombre
from alumnos
where edad=(select max (alumnos.edad)
from alumnos
where edad not in  (select max(edad)from alumnos)
);







-----------------------------------------------------
store procedures

* logica en la base de datos
* funciones que s ejecutan en el server
* PL/pgSQL


    Estructura


hello wolrd
create function hello () returns void as$$
begin
    raise notice 'hello world!'
end;
$$ language plpgsql;
-------------------------------
suma (a,b)
create or replace function suma(a int, b int) returns int as $$
declare
    c int
begin
    c:= a+b;
    return c;
end;
$$ langiage plpgsql;
----------------------------------------------
1. Escribir una función que reciba un número de legajo y que
devuelva el nombre del alumno.

 create or replace function damenombre(legajo int) returns text as $$
 declare
    resultado text;
 begin
  select a.nombre into resultado from alumnos a where a.id = legajo;
    return resultado;
 end;
 $$language plpgsql;

------------------------------------------------------------
hacer un stored procedured que imprima un mensaje en
"se ha agregado un nuevo registro".


create or replace function mensaje() returns void as $$

begin
 raise notice 'se ha agregado un nuevo registro'
end;
$$langiage plpgsql;


////////////////////////////////////////////////////////

TRIGGERS

trabaja con restricciones

*Eventos
    insert on nombre de la tabla
    delete on nombre de la tabla
    update on nombre de la tabla
*Activacion
    before | after | instead of Evento

 *Row Level
    for each Row

 *statement-level
    for each statement

pasos  a seguir
    1/tener una funcion que implemente el comportamiento.
    2/definir un trigger que use la funcion anterior.

el trigger se asocia a una tabla para borrar poner
 drop trigger on nombre de la tabla;
las funciones que devuelen trigger no pueden recibir parametros.

hacer un trigger que implemente la funcion mensaje cuando
se agregue una nueva materia en la base de datos escuela.




    create trigger msjaddmateria
    after insert on materias
    for each Row
    execute procedure addMateria();

    create function addMateria () returns trigger as $$
    begin
    raise notice 'Se ha agregado una nueva materia';
    return new;
    end;

    $$language plpgsql;

    create trigger msjdelmateria
    after delete on materias
    for each Row
    execute procedure delMateria();

    create function delMateria () returns trigger as $$
    begin
    raise notice 'Se ha eliminado una materia';
    return new;
    end;

    $$language plpgsql;
# trigger que no te notifique cuando se borre una batera
#
psql -U postgres -h localhost