Ejercicios Prácticos de SQL: Consultas DDL, DML y Agrupaciones Complejas

Definición de Tablas (DDL)

Creación de la tabla PUNTAJES

CREATE TABLE PUNTAJES (
    idPlato INT NOT NULL,
    idJurado INT NOT NULL,
    fechaHora DATETIME NOT NULL,
    puntaje INT NOT NULL,
    CONSTRAINT PK_Puntajes PRIMARY KEY (idPlato, idJurado, fechaHora),
    CONSTRAINT FK_Puntajes_Plato FOREIGN KEY (idPlato) REFERENCES PLATOS(idPlato),
    CONSTRAINT FK_Puntajes_Jurado FOREIGN KEY (idJurado) REFERENCES JURADOS(idJurado),
    CONSTRAINT CK_Puntajes_Puntaje CHECK (puntaje BETWEEN 0 AND 10)
);

Creación de la tabla PLATOS

CREATE TABLE PLATOS (
    idPlato INT,
    nombrePlato VARCHAR(100) NOT NULL,
    tipo VARCHAR(20) NOT NULL,
    idEquipo INT NOT NULL,
    CONSTRAINT PK_Platos PRIMARY KEY (idPlato),
    CONSTRAINT FK_Platos_Equipo FOREIGN KEY (idEquipo) REFERENCES EQUIPOS(idEquipo),
    CONSTRAINT CK_Platos_Tipo CHECK (tipo IN ('entrada', 'principal', 'postre'))
);

Modificación de Esquema y Consultas de Datos

2. Modificar la columna ColorDistintivo

Permite editar la columna colorDistintivo para que admita texto de hasta 25 caracteres:

ALTER TABLE Equipos ALTER COLUMN colorDistintivo VARCHAR(25);

3. Información detallada de platos de tipo 'entrada'

Lista el nombre del equipo, el nombre del plato, el nombre del jurado y el puntaje para todas las entradas:

SELECT eq.nombreEquipo, p.nombrePlato, j.nombreJurado, pu.puntaje
FROM PUNTAJES pu
INNER JOIN PLATOS p ON pu.idPlato = p.idPlato
INNER JOIN EQUIPOS eq ON p.idEquipo = eq.idEquipo
INNER JOIN JURADOS j ON pu.idJurado = j.idJurado
WHERE p.tipo = 'entrada';

4. Puntaje promedio otorgado por jurado

Muestra la calificación media asignada por cada integrante del jurado durante el año anterior:

SELECT j.nombreJurado, AVG(pu.puntaje) AS promedio
FROM PUNTAJES pu
INNER JOIN JURADOS j ON pu.idJurado = j.idJurado
WHERE YEAR(pu.fechaHora) = YEAR(GETDATE()) - 1
GROUP BY j.nombreJurado;

5. Equipos con más de un postre presentado

Lista la información de los equipos que han registrado más de una preparación dulce:

SELECT e.nombreEquipo, e.colorDistintivo
FROM EQUIPOS e
INNER JOIN PLATOS pl ON pl.idEquipo = e.idEquipo
WHERE pl.tipo = 'postre'
GROUP BY e.nombreEquipo, e.colorDistintivo
HAVING COUNT(pl.idPlato) > 1;

6. Equipos con puntajes consistentes (sin calificaciones bajo 8)

Lista sin duplicados los equipos que nunca han obtenido una nota inferior a 8 puntos en sus platos:

SELECT DISTINCT e.idEquipo, e.nombreEquipo
FROM EQUIPOS e
INNER JOIN PLATOS pl ON pl.idEquipo = e.idEquipo
INNER JOIN PUNTAJES p ON p.idPlato = pl.idPlato
GROUP BY e.idEquipo, e.nombreEquipo
HAVING MIN(p.puntaje) >= 8;

7. Los 3 platos con mayor puntaje acumulado en el año en curso

Obtiene el podio con el nombre y equipo correspondiente a los platos mejor evaluados durante el presente ciclo:

SELECT TOP 3 pl.nombrePlato, e.nombreEquipo, SUM(p.puntaje) AS puntajeAcumulado
FROM PUNTAJES p
INNER JOIN PLATOS pl ON p.idPlato = pl.idPlato
INNER JOIN EQUIPOS e ON pl.idEquipo = e.idEquipo
WHERE YEAR(p.fechaHora) = YEAR(GETDATE())
GROUP BY pl.nombrePlato, e.nombreEquipo
ORDER BY puntajeAcumulado DESC;