Consultas SQL Prácticas para la Gestión de Estadísticas Deportivas

Ejercicios de Consultas SQL para Gestión de Equipos y Jugadores

1. Listado de Jugadores, Equipos y Puntuaciones

Descripción: Muestra el equipo, nombre y puntos totales de todos los jugadores, ordenados por equipo de forma ascendente y por puntos totales de forma descendente.

-- 1. Muestra equipo, nombre y puntos totales de todos los jugadores
-- ordenados por equipo ascendente y por puntos totales descendente.
SELECT e.nombre AS equipo, j.nombre, j.puntos
FROM jugador j
JOIN equipo e ON j.id_equipo = e.id_equipo
ORDER BY e.nombre ASC, j.puntos DESC;

2. Equipos con más de dos Partidos Ganados

Descripción: Muestra un listado con todas las columnas de la tabla equipo, filtrando solo aquellos que hayan ganado más de dos partidos. El resultado se ordena por id_equipo de manera ascendente.

-- 2. Muestra un listado con todas las columnas de la tabla equipo,
-- de sólo aquellos equipos que hayan ganado más de dos partidos.
-- Ordena el resultado por id_equipo de manera ascendente.
SELECT e.*
FROM equipo e
JOIN (
    SELECT equipo_local AS equipo, COUNT(*) AS ganados
    FROM partido
    WHERE puntos_local > puntos_visitante
    GROUP BY equipo_local
    UNION ALL
    SELECT equipo_visitante AS equipo, COUNT(*) AS ganados
    FROM partido
    WHERE puntos_visitante > puntos_local
    GROUP BY equipo_visitante
) t ON e.id_equipo = t.equipo
GROUP BY e.id_equipo
HAVING SUM(t.ganados) > 2
ORDER BY e.id_equipo ASC;

3. Filtrado de Jugadores por Inicial y Puntos

Descripción: Muestra un listado de los jugadores cuyo nombre comience por la letra 'A' y tenga un total de puntos superior a 90.

-- 3. Muestra un listado de los jugadores cuyo nombre comience por A
-- y tenga un total de puntos superior a 90.
SELECT *
FROM jugador
WHERE nombre LIKE 'A%' AND puntos > 90;

4. Partidos con Alta Puntuación

Descripción: Muestra un listado de todos los partidos en donde al menos uno de los dos equipos haya superado los 100 puntos.

-- 4. Muestra un listado de todos los partidos en donde al menos uno
-- de los dos equipos haya superado los 100 puntos.
SELECT *
FROM partido
WHERE puntos_local > 100 OR puntos_visitante > 100;

5. Clasificación General de Equipos

Descripción: Muestra una clasificación de los equipos junto con la diferencia entre puntos a favor y en contra, ordenados por partidos ganados y diferencia de puntos de forma descendente.

-- 5. Muestra un listado (clasificación) de los equipos junto con la
-- diferencia entre puntos a favor y en contra, ordenados por:
-- 1º partidos ganados DESC
-- 2º diferencia de puntos DESC
SELECT e.id_equipo, e.nombre,
       SUM(CASE 
            WHEN e.id_equipo = p.equipo_local THEN p.puntos_local - p.puntos_visitante
            ELSE p.puntos_visitante - p.puntos_local
       END) AS diferencia,
       SUM(CASE 
            WHEN (e.id_equipo = p.equipo_local AND p.puntos_local > p.puntos_visitante)
              OR (e.id_equipo = p.equipo_visitante AND p.puntos_visitante > p.puntos_local)
            THEN 1 ELSE 0
       END) AS ganados
FROM equipo e
JOIN partido p 
ON e.id_equipo IN (p.equipo_local, p.equipo_visitante)
GROUP BY e.id_equipo
ORDER BY ganados DESC, diferencia DESC;

6. Creación de Vista: Top 5 de la Clasificación

Descripción: Limita el número de filas de la consulta anterior a los 5 mejores equipos y convierte el resultado en una vista persistente.

-- 6. Limita el número de filas de la consulta anterior a 5 y conviértela en una vista.
CREATE OR REPLACE VIEW clasificacion_top5 AS
SELECT e.id_equipo, e.nombre,
       SUM(CASE 
            WHEN e.id_equipo = p.equipo_local THEN p.puntos_local - p.puntos_visitante
            ELSE p.puntos_visitante - p.puntos_local
       END) AS diferencia,
       SUM(CASE 
            WHEN (e.id_equipo = p.equipo_local AND p.puntos_local > p.puntos_visitante)
              OR (e.id_equipo = p.equipo_visitante AND p.puntos_visitante > p.puntos_local)
            THEN 1 ELSE 0
       END) AS ganados
FROM equipo e
JOIN partido p 
ON e.id_equipo IN (p.equipo_local, p.equipo_visitante)
GROUP BY e.id_equipo
ORDER BY ganados DESC, diferencia DESC
LIMIT 5;

7. Puntos Totales como Local

Descripción: Muestra el total de puntos que ha anotado cada equipo jugando exclusivamente en su propio campo.

-- 7. Muestra el total de puntos que ha anotado cada equipo como local.
SELECT e.nombre, SUM(p.puntos_local) AS puntos_locales
FROM equipo e
JOIN partido p ON e.id_equipo = p.equipo_local
GROUP BY e.id_equipo;

8. Identificación del Ganador por Partido

Descripción: Utilizando la función IF, muestra todas las columnas de la tabla partido junto con una nueva columna que indique el ID del equipo ganador.

-- 8. Utilizando IF, muestra todas las columnas de partido y una nueva
-- que indique el equipo ganador de cada partido.
SELECT p.*,
       IF(p.puntos_local > p.puntos_visitante, p.equipo_local, p.equipo_visitante) AS ganador
FROM partido p;

9. Máximos Anotadores por Equipo

Descripción: Muestra todos los datos del jugador que ostenta la máxima puntuación dentro de cada equipo.

-- 9. Muestra todos los datos del máximo anotador por equipo.
SELECT j.*
FROM jugador j
JOIN (
    SELECT id_equipo, MAX(puntos) AS max_puntos
    FROM jugador
    GROUP BY id_equipo
) t 
ON j.id_equipo = t.id_equipo AND j.puntos = t.max_puntos;

10. Relación Completa de Jugadores y Equipos

Descripción: Muestra todos los datos de los jugadores junto con la información de su equipo, ordenados alfabéticamente por nombre de equipo y jugador.

-- 10. Muestra todos los datos de jugadores junto con los de su equipo,
-- ordenados por nombre de equipo y jugador.
SELECT j.*, e.*
FROM jugador j
JOIN equipo e ON j.id_equipo = e.id_equipo
ORDER BY e.nombre, j.nombre;

11. Equipos y Jugadores (Incluyendo Equipos Vacíos)

Descripción: Muestra todos los datos de los equipos junto con sus jugadores, utilizando un LEFT JOIN para incluir aquellos equipos que no tienen jugadores registrados.

-- 11. Muestra todos los datos de equipo junto con los de sus jugadores,
-- teniendo en cuenta que puede haber equipos sin jugadores.
SELECT e.*, j.*
FROM equipo e
LEFT JOIN jugador j ON e.id_equipo = j.id_equipo;

12. Detalle de Partidos con Nombres de Equipos

Descripción: Muestra la información de cada partido sustituyendo o añadiendo los nombres reales de los equipos local y visitante.

-- 12. Muestra la información de partido añadiendo el nombre de cada equipo.
SELECT p.*, 
       el.nombre AS nombre_local,
       ev.nombre AS nombre_visitante
FROM partido p
JOIN equipo el ON p.equipo_local = el.id_equipo
JOIN equipo ev ON p.equipo_visitante = ev.id_equipo;