tp5parte1

--Creacion de tablas:

create table deporte (id_deporte integer, nombre_deporte varchar (15), primary key (id_deporte));
create table equipo (codigo_equipo integer, nombre_equipo varchar(20), codigo_zona varchar(3), id_deporte integer, categoria varchar(1),
                        primary key (codigo_equipo));

create table zona(codigo_zona varchar(2),nombre_zona varchar(15),primary key(codigo_zona));
 create table region (id_region integer, nombre_region varchar (10), primary key (id_region));
 create table ciudad (codigo_ciudad integer, nombre_ciudad varchar(12),id_region integer, primary key(codigo_ciudad), cp varchar(4),
                        foreign key(id_region) references region(id_region));
 create table campeonato(codigo_equipo integer, codigo_ciudad integer, fecha date, entradas_vendidas integer, precio_entrada numeric (12,2),
                primary key(codigo_equipo,codigo_ciudad));

--rellenar tablas:
insert into zona
values
        ('aa','zonaA'),
        ('bb','zonaB'),
        ('cc','zonaC');

insert into deporte
values
        (1010, 'futbol'),
        (2020,'voley'),
        (3030,'basquet');

insert into ciudad
values
        (100,'s.s.jujuy',10,4600),
        (200,'palpala',10,4612),
        (300,'ledesma',11,4512),
        (400,'yavi',12,4651);

insert into region
values
        (10,'valle'),
        (11,'yungas'),
        (12,'puna');

insert into equipo
values
        (1,'equipo1', 'aa', 1010,'a'),
        (2,'equipo2','bb',2020,'a'),
        (3,'equipo3','cc',1010,'b'),
        (4,'equipo4','aa',2020,'b'),
        (5,'equipo5','bb',3030,'a');


insert into campeonato
values
        (1,100,'03-03-2023',500,30000.00),
        (1,200,'07-03-2023',800,20000.00),
        (2,200,'04-03-2023',200,10000.00),
        (3,100,'03-03-2023',600,15000.00),
        (4,300,'05-03-2023',400,20000.00),
        (2,400,'10-03-2023',250,5000.00),
        (5,300,'08-03-2023',350,30000.00),
        (3,400,'07-03-2023',700,15000.00);

--1. Realice una función que reciba como parámetro el nombre de la zona y devuelva la
--cantidad de campeonatos realizados en esa zona.

create or replace function cant_campeonatos(nombrede_zona TEXT)
returns INTEGER AS $$
declare
    numerodecam_realizados INTEGER;
begin
    select count(*)
    into numerodecam_realizados
    from campeonato
    join equipo on campeonato.codigo_equipo=equipo.codigo_equipo
    join zona on equipo.codigo_zona=zona.codigo_zona
    where zona.nombre_zona=nombrede_zona;

    return numerodecam_realizados;
End;
$$language plpgsql;

select cant_campeonatos('zonaC');

--2. Realice una función que reciba como parámetro el nombre de una región y devuelva el
--importe total recaudado en esa región.
create or replace function recaduo_total(nombre_r TEXT)
returns integer as $$
declare
    importe_total integer;
begin
    select sum (precio_entrada*entradas_vendidas)
    into importe_total
    from campeonato
    join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad
    join region on ciudad.id_region = region.id_region

    where region.nombre_region = nombre_r;

    return importe_total;
end;
$$language plpgsql;

select recaduo_total('yungas');
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--3. Realice una función que muestre por pantalla la cantidad total de registros de todas las tablas.

--4. Realice una función que reciba como parámetro un id de deporte y devuelva el nombre de
--los equipos que practican ese deporte.

create or replace function equipos_practican(ide_deporte integer)
returns table(nombre_equipo text) as $$
begin

    return query
    select equipo.nombre_equipo::text
    from equipo
    join deporte on equipo.id_deporte = deporte.id_deporte
    where equipo.id_deporte = ide_deporte;

end;
$$language plpgsql;

select * from equipos_practican(1010);

---otra alternativa, prque ademas del nombre del equipo quiero mostrar el nombre del deporte y el id deporte
-- especifico q son text asi en select no me genera error. a id deporte no hace falta ponerle integer de new
create or replace function practicaan(ide_deporte integer)
returns table(nombre_equipo text, nombress_deportes text, id_deporte integer ) as $$
begin
    return query
    select equipo.nombre_equipo::text, deporte.nombre_deporte::text, deporte.id_deporte
    from equipo
    join deporte on equipo.id_deporte = deporte.id_deporte
    where equipo.id_deporte = ide_deporte;
end;
$$language plpgsql;
--llamada a la funcion:
select * from practicaan(2020);
+++++++++++++++++++

--5. Realice una función que reciba como parámetro el Código Equipo, cargue un cursor con
--todos los datos de los campeonatos en los que participó el equipo, luego debe abrir el
--cursor, y calcular el importe recaudado en cada campeonato (entradas vendidas * precio
--entrada), sume el total y cargue la variable que será devuelta por la función.