2020.sql

DROP TABLE equipo;
DROP TABLE partido;
CREATE TABLE equipo(
    idequipo int, 
    nombre varchar(20),
    presupuesto int
);

CREATE TABLE partido(
    equipo_local int,
    equipo_visitante int,
    goles_local int,
    goles_visitantes int,
    jornada timestamp
);


--* datos de ejemplo
INSERT INTO equipo (idequipo, nombre, presupuesto) VALUES
(1, 'R.C. Celta', 5000000),
(2, 'R.C. Deportivo', 4000000),
(3, 'Real Madrid', 100000000),
(4, 'Mazaricos F.C.', 999999999),
(5, 'Barcelona', 60000000),
(6, 'Ponteceso SD', 0);

INSERT INTO partido (equipo_local, equipo_visitante, goles_local, goles_visitantes, jornada) VALUES
(2, 4, 3, 1984, '1975-11-20 00:52:00'),
(4, 3, 9999, 0, '2001-04-02 00:43:00'),
(4, 6, 9999, 0, '2001-04-02 00:43:00'),
(1, 2, 2, 1, '2025-01-01 18:00:00'),
(3, 4, 1, 1, '2025-01-02 20:00:00'),
(5, 1, 0, 3, '2025-01-03 18:00:00'),
(2, 3, 1, 2, '2025-01-04 16:00:00'),
(4, 5, 3, 0, '2025-01-05 19:00:00'),
(1, 4, 1, 4, '2025-01-06 18:00:00'),
(2, 5, 2, 2, '2025-01-07 20:00:00'),
(3, 1, 4, 2, '2025-01-08 18:00:00'),
(4, 2, 2, 2, '2025-01-09 19:00:00'),
(5, 3, 1, 3, '2025-01-10 18:00:00'),
(1, 5, 3, 1, '2025-01-11 17:00:00'),
(2, 4, 0, 1, '2025-01-12 20:00:00');

--? Lista de equipos con un presupuesto mayor de 12000000
SELECT *
FROM equipo
WHERE presupuesto>12000000;

--? Lista de partidos en los que el equipo ‘R.C. Celta’ jugó como local. Muestra para cada
--? partido, la jornada, el código del adversario y la diferencia de goles marcados menos
--? encajados.
SELECT p.jornada, p.equipo_visitante, (goles_local-goles_visitantes) as diferencia
FROM equipo e, partido p
WHERE e.idequipo = p.equipo_local
AND e.nombre = 'R.C. Celta';


--? Muestra los partidos en los que el equipo ‘R.C.Celta’ jugó contra el equipo
--? ‘R.C.Deportivo’. Para cada partido muestra la jornada, los códigos de los equipos local y
--? visitante y el resultado con el formato ‘goleslocal - golesvisitante’. Ordena el resultado de
--? forma ascendente por jornada.

SELECT p.jornada, p.equipo_local, p.equipo_visitante,
cast(p.goles_local AS TEXT) || '-' || cast(p.goles_visitantes AS TEXT) AS resultado
FROM equipo l, equipo v, partido p
WHERE l.idequipo = p.equipo_local AND v.idequipo = p.equipo_visitante
AND l.nombre in ('R.C. Celta', 'R.C. Deportivo')
AND v.nombre in ('R.C. Celta', 'R.C. Deportivo')
ORDER BY p.jornada;


--? Obtén el número de goles anotados en cada una de las jornadas. Ordena el resultado
--? de forma inversa por dicho número de goles.
SELECT jornada, SUM(goles_local+goles_visitantes) as goles_jornada
FROM partido
GROUP BY jornada
ORDER BY goles_jornada DESC;


--? Obtén los equipos que, jugando como local, han marcado al menos el mismo número
--? de goles que han encajado. Muestra para cada equipo, su nombre y los goles marcados y
--? encajados. Ordena el resultado de forma descendiente por la diferencia de goles marcados
--? menos encajados.
SELECT e.nombre, sum(goles_local) as goles_marcados, sum(goles_visitantes) as goles_encajados
FROM equipo e, partido p
WHERE e.idequipo = p.equipo_local
GROUP BY e.nombre
ORDER BY (sum(goles_local) - sum(goles_visitantes)) DESC;

--? Obtén el nombre del equipo que en las primeras 3 jornadas ha marcado más goles
--? como visitante.
SELECT e.nombre, sum(p.goles_visitantes)
FROM equipo e, partido p
WHERE e.idequipo = p.equipo_visitante 
  AND p.jornada IN (
      SELECT DISTINCT jornada 
      FROM partido 
      ORDER BY jornada ASC 
      LIMIT 3
  )
GROUP BY e.nombre
HAVING SUM(p.goles_visitantes) = (
    SELECT MAX(goles_visitantes)
    FROM (
        SELECT e1.idequipo, SUM(p1.goles_visitantes) AS goles_visitantes
        FROM equipo e1, partido p1
        WHERE e1.idequipo = p1.equipo_visitante
          AND p1.jornada IN (
              SELECT DISTINCT jornada 
              FROM partido 
              ORDER BY jornada ASC 
              LIMIT 3
          )
        GROUP BY e1.idequipo
    )
);

--? Obtén el nombre de cada equipo y el número de partidos que ha ganado. Ordena el
--? resultado de forma descendente por el número de partidos ganados.

SELECT idequipo, nombre, COUNT(partidos)
FROM(
    SELECT e.idequipo, e.nombre
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_local AND p.goles_local>p.goles_visitantes
    UNION ALL
    SELECT e.idequipo, e.nombre
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_visitante AND p.goles_local<p.goles_visitantes
) AS partidos
GROUP BY idequipo, nombre
ORDER BY COUNT(partidos) DESC;

--? Obtén un informe con la clasificación de la liga, en la que muestres el número de
--? partidos ganados, empatados y perdidos, goles a favor y goles en contra y puntos (3 por
--? victoria y 1 por empate). Ordena los equipos por el número de puntos de forma
--? descendente.
SELECT equipos.nombre,
COALESCE(ganados_local.partidos,0)+
COALESCE(ganados_visitante.partidos,0) AS ganados,
COALESCE(empatados_local.partidos,0)+
COALESCE(empatados_visitante.partidos,0) AS empatados,
COALESCE(perdidos_local.partidos,0)+
COALESCE(perdidos_visitante.partidos,0) AS perdidos,

COALESCE(ganados_local.gf,0)+COALESCE(empatados_local.gf,0)+COALESCE(perdidos_local.gf,0)+
COALESCE(ganados_visitante.gf,0)+COALESCE(empatados_visitante.gf,0)+COALESCE(perdidos_visitante.gf,0) AS goles_a_favor,

COALESCE(ganados_local.gc,0)+COALESCE(empatados_local.gc,0)+COALESCE(perdidos_local.gc,0)+
COALESCE(ganados_visitante.gc,0)+COALESCE(empatados_visitante.gc,0)+COALESCE(perdidos_visitante.gc,0) AS goles_en_contra,

((COALESCE(ganados_local.partidos,0)+ COALESCE(ganados_visitante.partidos,0) )*3 + 
(COALESCE(empatados_local.partidos,0)+ COALESCE(empatados_visitante.partidos,0))) 
as puntos

FROM equipo AS equipos
LEFT JOIN 
(
    SELECT e.idequipo, e.nombre, SUM(goles_local) as gf, SUM (goles_visitantes) as gc, count(*) as partidos
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_local and p.goles_local > p.goles_visitantes
    GROUP BY e.idequipo, e.nombre
) as ganados_local on ganados_local.idequipo=equipos.idequipo
LEFT JOIN 
(
    SELECT e.idequipo, e.nombre, SUM(goles_local) as gf, SUM (goles_visitantes) as gc, count(*) as partidos
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_local and p.goles_local = p.goles_visitantes
    GROUP BY e.idequipo, e.nombre
) as empatados_local on empatados_local.idequipo=equipos.idequipo
-- este putísimo ejercicio vale 1.3 puntos.
LEFT JOIN 
(
    SELECT e.idequipo, e.nombre, SUM(goles_local) as gf, SUM (goles_visitantes) as gc, count(*) as partidos
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_local and p.goles_local < p.goles_visitantes
    GROUP BY e.idequipo, e.nombre
) as perdidos_local on perdidos_local.idequipo=equipos.idequipo
LEFT JOIN 
(
    SELECT e.idequipo, e.nombre, SUM(goles_local) as gc, SUM (goles_visitantes) as gf, count(*) as partidos
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_visitante and p.goles_local > p.goles_visitantes
    GROUP BY e.idequipo, e.nombre
) as perdidos_visitante on perdidos_visitante.idequipo=equipos.idequipo
LEFT JOIN 
(
    SELECT e.idequipo, e.nombre, SUM(goles_local) as gc, SUM (goles_visitantes) as gf, count(*) as partidos
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_visitante and p.goles_local = p.goles_visitantes
    GROUP BY e.idequipo, e.nombre
) as empatados_visitante  on empatados_visitante.idequipo=equipos.idequipo
LEFT JOIN 
(
    SELECT e.idequipo, e.nombre, SUM(goles_local) as gc, SUM (goles_visitantes) as gf, count(*) as partidos
    FROM equipo e, partido p
    WHERE e.idequipo = p.equipo_visitante and p.goles_local < p.goles_visitantes
    GROUP BY e.idequipo, e.nombre
) as ganados_visitante on ganados_visitante.idequipo=equipos.idequipo

ORDER BY (
(COALESCE(ganados_local.partidos,0)+ COALESCE(ganados_visitante.partidos,0) )*3 + 
(COALESCE(empatados_local.partidos,0)+ COALESCE(empatados_visitante.partidos,0))
) DESC;