parte2TP4

--Consultas Resumen
--12. Muestre todos los campos de zona y equipo en una sola consulta relacionada. Realice una
--segunda consulta donde no se presente repetición de campos.
-- en esta primera consulta me muestra todo incluso campos repetidos.
select * from zona inner join equipo
on zona.codigo_zona=equipo.codigo_zona;
-- en la segunda como me esta pidiendo que no se repiten queda asi:
select zona.codigo_zona as cod_zona,
    zona.nombre_zona,
    equipo.codigo_equipo,
    equipo.nombre_equipo,
    equipo.id_deporte,
    equipo.categoria
from zona inner join equipo
on zona.codigo_zona = equipo.codigo_zona;
-- es decir que necesitamos un alias para renombrar el campo de codigo zona y que cuando lo relacione no chocque con la tabla2,
-- entonces lo renombro a cod_zona pero solo utilizo en una sola tabla el codigo zona. Luego menciono los demas campos
-- y ya quedaria.
++++++
--13. Muestre todos los campos de zona, equipo y campeonato en una sola consulta
--relacionada. En el resultado de la consulta no deben existir campos repetidos.
select zona.codigo_zona as cod_zona,
    zona.nombre_zona,
    equipo.codigo_equipo as cod_equipo,
    equipo.nombre_equipo,
    equipo.id_deporte,
    equipo.categoria,
    campeonato.codigo_ciudad,
    campeonato.fecha,
    campeonato.entradas_vendidas,
    campeonato.precio_entrada
from zona
inner join equipo on zona.codigo_zona = equipo.codigo_zona
inner join campeonato on equipo.codigo_equipo = campeonato.codigo_equipo;
--cuando tengo 3 tablas, debo poner alias a las 2 primeras tablas para no confundir al relacionar el campo del codigo, y
--utilizo 2 inner join para relacionar.
+++++++
--14. Muestre el “codigo_zona” y el “codigo_ ciudad” con fecha 07/03/2023.
select zona.codigo_zona, campeonato.codigo_ciudad, campeonato.fecha
from zona
inner join equipo on zona.codigo_zona = equipo.codigo_zona
inner join campeonato on  equipo.codigo_equipo = campeonato.codigo_equipo
where campeonato.fecha  = '07-03-2023';
-- selecciono los campos a mostrar, y parto desde zona, y relaciono las tablas. Con la condicion de where nombre de la tabla
--punto el campo y el = que es la condicion.

--15. Utilizando subconsulta muestre el ”nombre_deporte” de los equipos con categoría “B”.
select * from equipo;
select deporte.nombre_deporte, equipo.categoria, equipo.nombre_equipo
from deporte
inner join equipo on deporte.id_deporte = equipo.id_deporte
where equipo.categoria = 'b';

--16. Utilizando subconsulta muestre los registros de la tabla campeonato correspondiente a la
--ciudad de “Ledesma”.
select * from campeonato;
select campeonato.codigo_equipo,
        campeonato.codigo_ciudad,
        campeonato.fecha,
        campeonato.entradas_vendidas,
        campeonato.precio_entrada,
        ciudad.nombre_ciudad
from campeonato
inner join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad
where ciudad.nombre_ciudad = 'ledesma';

--17. Utilizando subconsulta muestre los registros de la tabla campeonato correspondiente a la “ZonaB”
select campeonato.codigo_equipo,
        campeonato.codigo_ciudad,
        campeonato.fecha,
        campeonato.entradas_vendidas,
        campeonato.precio_entrada,
        zona.nombre_zona
from zona
inner join equipo on zona.codigo_zona = equipo.codigo_zona
inner join campeonato on equipo.codigo_equipo = campeonato.codigo_equipo
where zona.nombre_zona = 'zonaB';

--18. Utilizando subconsulta muestre el “codigo_equipo” y “nombre_equipo” para el deporte
--“Voley” correspondiente a la “ZonaA”
select equipo.codigo_equipo,
        equipo.nombre_equipo,
        deporte.nombre_deporte,
        zona.nombre_zona
from zona
inner join equipo on zona.codigo_zona = equipo.codigo_zona
inner join deporte on equipo.id_deporte = deporte.id_deporte
where zona.nombre_zona = 'zonaA' and deporte.nombre_deporte = 'voley';

--19. Realice una vista denominada “v_campeonato” que muestre los datos relacionados de
--equipo y ciudad.

create view v_campeonato as
select equipo.codigo_equipo,
        equipo.nombre_equipo,
        equipo.codigo_zona,
        equipo.id_deporte,
        equipo.categoria,
        ciudad.codigo_ciudad,
        ciudad.nombre_ciudad,
        ciudad.id_region,
        ciudad.cp
from campeonato
join equipo on campeonato.codigo_equipo = equipo.codigo_equipo
join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad;

select * from v_campeonato;
--ahora si no quiero repetir utilizo distinc

create view v_campeonatos as
select distinct  equipo.codigo_equipo,
        equipo.nombre_equipo,
        equipo.codigo_zona,
        equipo.id_deporte,
        equipo.categoria,
        ciudad.codigo_ciudad,
        ciudad.nombre_ciudad,
        ciudad.id_region,
        ciudad.cp
from campeonato
join equipo on campeonato.codigo_equipo = equipo.codigo_equipo
join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad;
select * from v_campeonatos;

--20. Cree una vista denominada “v_equipo” que muestre los datos de zona y deporte.
create view v_equipo as
select zona.codigo_zona,
        zona.nombre_zona,
        deporte.nombre_deporte,
        deporte.id_deporte
from equipo
join zona on equipo.codigo_zona= zona.codigo_zona
join deporte on equipo.id_deporte = deporte.id_deporte;

select * from v_equipo;
-- 21. Muestre el número de registros que contiene la tabla campeonato.
select count(*) as numero_registros
from campeonato;

--22. Muestre la cantidad máxima de entradas vendidas
select max(entradas_vendidas) as cantidadMaxima_entradasVendidas
from campeonato;

--23. Muestre el promedio de las entradas vendidas.
select round(avg(entradas_vendidas),2) as promedio_entradasVendidas
from campeonato;


--24. Muestre el importe total (Entradas Vendidas * Precio Entrada) de entradas vendidas en
--todo el campeonato
select sum(entradas_vendidas*precio_entrada) as importe_total
from campeonato;

--25. Muestre el importe total (Entradas Vendidas * Precio Entrada) de entradas vendidas para
--la ciudad de Yavi y considere aplicar un descuento del 10%
select sum(entradas_vendidas * precio_entrada * 0.9) as importe_Total_conDescuento
from campeonato
where codigo_ciudad = (select codigo_ciudad from ciudad where nombre_ciudad='yavi');

--alternativa2
select sum(entradas_vendidas * precio_entrada * 0.9) as import_desc
from campeonato
inner join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad
where campeonato.codigo_ciudad = ciudad.codigo_ciudad and ciudad.nombre_ciudad = 'yavi';
+++
--26. Muestre el importe total (Entradas Vendidas * Precio Entrada) para cada “codigo_ciudad”
--ordenado de forma ascendente.
select codigo_ciudad, sum (entradas_vendidas * precio_entrada) as Importe_Total
from campeonato
group by codigo_ciudad
order by codigo_ciudad asc;
--Como me esta pidiendo para cada codigo que son 4, al usar group by agrupo el total para cada codigo en especifico
-- y al principio cuando coloco codigo_ciudad es para  q se muestre cual es el codigo.
++++


--27. Muestre el nombre de los deportes que se practican en la “ZonaB”.
select deporte.nombre_deporte,
        zona.nombre_zona
from zona
inner join equipo on zona.codigo_zona = equipo.codigo_zona
inner join deporte on equipo.id_deporte = deporte.id_deporte
where zona.nombre_zona = 'zonaB';

--28. Muestre la fecha de los campeonatos que se realizan en la región “Valle” y que corresponden a la “ZonaA”.
select campeonato.fecha,
        region.nombre_region,
        zona.nombre_zona
from zona
inner join equipo on zona.codigo_zona = equipo.codigo_zona
inner join campeonato on equipo.codigo_equipo = campeonato.codigo_equipo
inner join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad
inner join region on ciudad.id_region = region.id_region
where region.nombre_region = 'valle' and zona.nombre_zona = 'zonaA';

select*from region;

--alternativa 2 si esq comienzo desde region pero es lo mismo me da lo mismo.
select campeonato.fecha,
        region.nombre_region,
        zona.nombre_zona
from region
inner join ciudad on region.id_region = ciudad.id_region
inner join campeonato on ciudad.codigo_ciudad = campeonato.codigo_ciudad
inner join equipo on campeonato.codigo_equipo = equipo.codigo_equipo
inner join zona on equipo.codigo_zona = zona.codigo_zona
where region.nombre_region = 'valle' and zona.nombre_zona = 'zonaA';
++++++
--29. Utilizando subconsultas muestre el “nombre_region” en donde se practica “Voley”.
select region.nombre_region,
        deporte.nombre_deporte
from deporte
inner join equipo on deporte.id_deporte = equipo.id_deporte
inner join campeonato on equipo.codigo_equipo = campeonato.codigo_equipo
inner join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad
inner join region on ciudad.id_region = region.id_region
where deporte.nombre_deporte = 'voley';


select r.nombre_region
from region r
where r.id_region in (
    select c.id_region
    from ciudad c
    join campeonato ca on c.codigo_ciudad = ca.codigo_ciudad
    join equipo e on ca.codigo_equipo = e.codigo_equipo
    where e.id_deporte = (
        select d.id_deporte
        from deporte d
        where d.nombre_deporte = 'voley'

    )
);

--30. Utilice subconsultas y muestre el nombre de los equipos que practican el deporte “Futbol”
--en la ciudad de “S.S.Jujuy”.
select equipo.nombre_equipo,
        deporte.nombre_deporte,
        ciudad.nombre_ciudad
from deporte
inner join equipo on deporte.id_deporte = equipo.id_deporte
inner join campeonato on equipo.codigo_equipo = campeonato.codigo_equipo
inner join ciudad on campeonato.codigo_ciudad  = ciudad.codigo_ciudad
where deporte.nombre_deporte ='futbol' and ciudad.nombre_ciudad  = 's.s.jujuy';

--31. Muestre la cantidad de entradas vendidas en la ciudad de “Yavi”.
select  sum (entradas_vendidas) as Entradas_vendidasYavi
from campeonato
inner join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad
where ciudad.nombre_ciudad = 'yavi';

--32. Muestre el total recaudado por el "Equipo1” durante todo el campeonato.
select sum(precio_entrada*entradas_vendidas) as Recaudado_Equipo1
from campeonato
inner join equipo on campeonato.codigo_equipo=equipo.codigo_equipo
where equipo.nombre_equipo='equipo1';

--33. Muestre el nombre de la zona junto con la cantidad de deportes que se realizan en la misma.
select zona.nombre_zona, count(distinct equipo.id_deporte) as cantidad_deportes
from zona
join equipo on zona.codigo_zona= equipo.codigo_zona
group by zona.codigo_zona,zona.nombre_zona

--34. Muestre el nombre de la ciudad junto con el importe total recaudado en el campeonato

select ciudad.nombre_ciudad, sum(precio_entrada*entradas_vendidas) as Importe_total
from campeonato
join ciudad on campeonato.codigo_ciudad = ciudad.codigo_ciudad
group by ciudad.nombre_ciudad, ciudad.codigo_ciudad
order by ciudad.nombre_ciudad asc

--35. Muestre el nombre de la ciudad junto con la cantidad de equipos que compitieron en la misma
select ciudad.nombre_ciudad, count(distinct equipo.codigo_equipo) as Cantidad_equiposcompitieron
from ciudad
inner join campeonato on ciudad.codigo_ciudad = campeonato.codigo_ciudad
inner join equipo on campeonato.codigo_equipo = equipo.codigo_equipo
group by ciudad.nombre_ciudad,ciudad.codigo_ciudad
order by ciudad.nombre_ciudad asc
---FINISH!