¶ Eurovision
¶ ER
¿hay algún país que se haya votado a si mismo?
SELECT p.nombre
FROM votos v JOIN paises p
ON p.id = v.fk_pais JOIN actuacion a
ON v.fk_actuacion = a.id
WHERE v.fk_pais = a.fk_pais
SELECT SUM(v.voto), p.nombre, year(c.fecha)
FROM votos v JOIN actuacion a
ON v.fk_actuacion = a.id JOIN paises p
ON a.fk_pais = p.id JOIN certamenes c
ON c.id = a.fk_certamen
GROUP BY a.fk_certamen, a.fk_pais
ORDER BY 1 desc
Un cantante sólo puede participar una vez
SELECT COUNT(a.id)
FROM actuacion a JOIN cantantes c
ON a.fk_cantante = c.id JOIN certamenes ce
ON ce.id = c.fk_certamen
WHERE anyo = 2025
SELECT c.anyo, p.nombre, SUM(v.voto) FROM votos v
JOIN certamenes c ON v.fk_certamen = c.id JOIN paises p
ON v.fk_votado = p.id
GROUP BY c.id, p.nombre ORDER BY 1, 3 desc
¿Cuántos integrantes acompaña a cada cantante?
SELECT c.nombre, count(i.id) as num_int
FROM integrantes i right JOIN cantantes c
ON i.fk_cantante = c.id
GROUP BY c.id ORDER BY num_int desc, c.nombre asc
¿Cuenta cuántas actuaciones han recibido 2 votos?
SELECT COUNT(*) FROM votos
WHERE votos.voto = 2
-- SELECT MAX(mp) from
SELECT SUM(v.voto) mp FROM votos v
GROUP BY v.fk_certamen, v.fk_votado
ORDER BY 1 DESC
LIMIT 1
¶ Versión 2 E/R
CREATE TABLE IF NOT EXISTS `eurovision`.`paises` (
`id` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(45) NULL,
`voto` INT NULL,
`televoto` INT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `eurovision`.`cantantes` (
`id` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(45) NULL,
`fecha_nac` DATE NULL,
`fk_pais` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_cantantes_paises_idx` (`fk_pais` ASC) VISIBLE,
UNIQUE INDEX `clave_alternatica` (`fk_pais` ASC) INVISIBLE,
CONSTRAINT `fk_cantantes_paises`
FOREIGN KEY (`fk_pais`)
REFERENCES `eurovision`.`paises` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `eurovision`.`integrantes` (
`id` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(45) NULL,
`fk_cantante` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_integrantes_cantantes1_idx` (`fk_cantante` ASC) VISIBLE,
CONSTRAINT `fk_integrantes_cantantes1`
FOREIGN KEY (`fk_cantante`)
REFERENCES `eurovision`.`cantantes` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `eurovision`.`actuacion` (
`id` INT NOT NULL AUTO_INCREMENT,
`orden` INT NULL,
`titulo` VARCHAR(45) NULL,
`url` VARCHAR(45) NULL,
`comentarios` TEXT NULL,
`fk_cantante` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_actuacion_cantantes1_idx` (`fk_cantante` ASC) VISIBLE,
UNIQUE INDEX `fk_cantante_UNIQUE` (`fk_cantante` ASC) VISIBLE,
CONSTRAINT `fk_actuacion_cantantes1`
FOREIGN KEY (`fk_cantante`)
REFERENCES `eurovision`.`cantantes` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `eurovision`.`certamenes` (
`id` INT NOT NULL,
`anyo` INT NULL,
`fk_pais` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_certamenes_paises1_idx` (`fk_pais` ASC) VISIBLE,
UNIQUE INDEX `anyo_UNIQUE` (`anyo` ASC) VISIBLE,
CONSTRAINT `fk_certamenes_paises1`
FOREIGN KEY (`fk_pais`)
REFERENCES `eurovision`.`paises` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `eurovision`.`participa` (
`fk_certamen` INT NOT NULL,
`fk_cantante` INT NOT NULL,
`fk_actuacion` INT NULL,
PRIMARY KEY (`fk_certamen`, `fk_cantante`),
INDEX `fk_certamenes_has_cantantes_cantantes1_idx` (`fk_cantante` ASC) VISIBLE,
INDEX `fk_certamenes_has_cantantes_certamenes1_idx` (`fk_certamen` ASC) VISIBLE,
INDEX `fk_participa_actuacion1_idx` (`fk_actuacion` ASC) VISIBLE,
CONSTRAINT `fk_certamenes_has_cantantes_certamenes1`
FOREIGN KEY (`fk_certamen`)
REFERENCES `eurovision`.`certamenes` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_certamenes_has_cantantes_cantantes1`
FOREIGN KEY (`fk_cantante`)
REFERENCES `eurovision`.`cantantes` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_participa_actuacion1`
FOREIGN KEY (`fk_actuacion`)
REFERENCES `eurovision`.`actuacion` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `eurovision`.`votos` (
`voto` INT NULL,
`televoto` INT NULL,
`fk_certamen` INT NOT NULL,
`fk_votante` INT NOT NULL,
`fk_actuacion` INT NOT NULL,
INDEX `fk_votos_certamenes1_idx` (`fk_certamen` ASC) VISIBLE,
INDEX `fk_votos_paises1_idx` (`fk_votante` ASC) VISIBLE,
UNIQUE INDEX `clave_ppal` (`fk_certamen` ASC, `fk_votante` ASC) VISIBLE,
PRIMARY KEY (`fk_votante`, `fk_certamen`, `fk_actuacion`),
INDEX `fk_votos_actuacion1_idx` (`fk_actuacion` ASC) VISIBLE,
CONSTRAINT `fk_votos_certamenes1`
FOREIGN KEY (`fk_certamen`)
REFERENCES `eurovision`.`certamenes` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_votos_paises1`
FOREIGN KEY (`fk_votante`)
REFERENCES `eurovision`.`paises` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_votos_actuacion1`
FOREIGN KEY (`fk_actuacion`)
REFERENCES `eurovision`.`actuacion` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB