Ejercicio Colección de funkos

Mi hija quiere hacer una base de datos para guardar su colección de funkos. Los funkos están organizados en colecciones, que tienen un nombre de la colección y el nombre del diseñador. Un funko solo pertenece a una colección. De cada funko queremos guardar el Nombre, Precio, Número,Estilo, Fecha de lanzamiento y Tamaño.

La mayoría de los funkos son de productos audiovisuales (series, películas…). Nos interesa conocer el título de ese producto, la fecha de emisión, la nacionalidad y el número de temporadas.

CREATE TABLE `coleccion` (
  `idcoleccion` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(150) DEFAULT NULL,
  `disenyador` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`idcoleccion`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `audiovisual` (
  `idaudiovisual` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `titulo` varchar(150) DEFAULT NULL,
  `emision` date DEFAULT NULL,
  `nacionalidad` varchar(150) DEFAULT NULL,
  `temporadas` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`idaudiovisual`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `funko` (
  `idfunko` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idcoleccion` int(11) unsigned DEFAULT NULL,
  `idaudiovisual` int(11) unsigned DEFAULT NULL,
  `nombre` varchar(150) DEFAULT NULL,
  `precio` decimal(8,2) DEFAULT NULL,
  `numero` smallint(6) DEFAULT NULL,
  `estilo` varchar(150) DEFAULT NULL,
  `lanzamiento` date DEFAULT NULL,
  `tamanyo` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`idfunko`),
  KEY `fk_coleccion_idx` (`idcoleccion`),
  KEY `fk_audiovisual_idx` (`idaudiovisual`),
  CONSTRAINT `fk_audiovisual` FOREIGN KEY (`idaudiovisual`) REFERENCES `audiovisual` (`idaudiovisual`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_coleccion` FOREIGN KEY (`idcoleccion`) REFERENCES `coleccion` (`idcoleccion`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Datos de prueba:

INSERT INTO `coleccion` (`nombre`, `disenyador`) VALUES
('Star Wars: Original Trilogy', 'John Doe'),
('Marvel Avengers', 'Jane Smith'),
('Harry Potter', 'Robert Brown'),
('Game of Thrones', 'Emily White'),
('The Walking Dead', 'Michael Green'),
('Disney Classics', 'Sarah Black'),
('DC Comics Superheroes', 'Thomas Blue'),
('Stranger Things', 'Olivia Purple'),
('Rick and Morty', 'Daniel Red'),
('The Simpsons', 'Jessica Pink'),
('Dragon Ball Z', 'Matthew Yellow'),
('Naruto Shippuden', 'Sophia Orange'),
('One Piece', 'James Violet'),
('Pokemon', 'Emma Indigo'),
('Lord of the Rings', 'Christopher Teal'),
('The Office', 'Isabella Grey'),
('Friends', 'Benjamin Azure'),
('My Hero Academia', 'Mia Lime'),
('Overwatch', 'Elijah Olive'),
('Fortnite', 'Charlotte Cyan');

INSERT INTO `audiovisual` (`titulo`, `emision`, `nacionalidad`, `temporadas`) VALUES
('Breaking Bad', '2008-01-20', 'USA', 5),
('Stranger Things', '2016-07-15', 'USA', 4),
('Game of Thrones', '2011-04-17', 'USA', 8),
('Narcos', '2015-08-28', 'USA', 3),
('The Crown', '2016-11-04', 'UK', 4),
('La Casa de Papel', '2017-05-02', 'Spain', 5),
('Dark', '2017-12-01', 'Germany', 3),
('Money Heist', '2017-05-02', 'Spain', 5),
('The Mandalorian', '2019-11-12', 'USA', 2),
('The Witcher', '2019-12-20', 'USA', 2),
('Friends', '1994-09-22', 'USA', 10),
('The Office', '2005-03-24', 'USA', 9),
('Sherlock', '2010-07-25', 'UK', 4),
('Black Mirror', '2011-12-04', 'UK', 5),
('The Simpsons', '1989-12-17', 'USA', 32),
('Rick and Morty', '2013-12-02', 'USA', 5),
('Naruto Shippuden', '2007-02-15', 'Japan', 21),
('One Piece', '1999-10-20', 'Japan', 20),
('Dragon Ball Z', '1989-04-26', 'Japan', 9),
('Attack on Titan', '2013-04-07', 'Japan', 4);

INSERT INTO `funko` (`idcoleccion`, `idaudiovisual`, `nombre`, `precio`, `numero`, `estilo`, `lanzamiento`, `tamanyo`) VALUES
(1, 3, 'Darth Vader', 12.99, 1, 'Vinyl', '2018-05-04', 'Standard'),
(1, 3, 'Luke Skywalker', 12.99, 2, 'Vinyl', '2018-05-04', 'Standard'),
(2, 10, 'Iron Man', 14.99, 3, 'Vinyl', '2019-04-26', 'Standard'),
(2, 10, 'Captain America', 14.99, 4, 'Vinyl', '2019-04-26', 'Standard'),
(3, 7, 'Harry Potter', 11.99, 5, 'Vinyl', '2017-07-31', 'Standard'),
(3, 7, 'Hermione Granger', 11.99, 6, 'Vinyl', '2017-07-31', 'Standard'),
(4, 2, 'Jon Snow', 13.99, 7, 'Vinyl', '2019-04-14', 'Standard'),
(4, 2, 'Daenerys Targaryen', 13.99, 8, 'Vinyl', '2019-04-14', 'Standard'),
(5, 1, 'Rick Grimes', 10.99, 9, 'Vinyl', '2018-10-07', 'Standard'),
(5, 1, 'Daryl Dixon', 10.99, 10, 'Vinyl', '2018-10-07', 'Standard'),
(6, 19, 'Mickey Mouse', 9.99, 11, 'Vinyl', '2019-11-18', 'Standard'),
(6, 19, 'Donald Duck', 9.99, 12, 'Vinyl', '2019-11-18', 'Standard'),
(7, 11, 'Batman', 12.99, 13, 'Vinyl', '2019-03-01', 'Standard'),
(7, 11, 'Superman', 12.99, 14, 'Vinyl', '2019-03-01', 'Standard'),
(8, 2, 'Eleven', 14.99, 15, 'Vinyl', '2019-07-04', 'Standard'),
(8, 2, 'Jim Hopper', 14.99, 16, 'Vinyl', '2019-07-04', 'Standard'),
(9, 17, 'Rick', 13.49, 17, 'Vinyl', '2020-09-20', 'Standard'),
(9, 17, 'Morty', 13.49, 18, 'Vinyl', '2020-09-20', 'Standard'),
(10, 12, 'Homer Simpson', 11.49, 19, 'Vinyl', '2019-09-29', 'Standard'),
(10, 12, 'Bart Simpson', 11.49, 20, 'Vinyl', '2019-09-29', 'Standard'),
(11, 14, 'Goku', 12.49, 21, 'Vinyl', '2018-11-18', 'Standard'),
(11, 14, 'Vegeta', 12.49, 22, 'Vinyl', '2018-11-18', 'Standard'),
(12, 16, 'Naruto Uzumaki', 13.99, 23, 'Vinyl', '2019-02-15', 'Standard'),
(12, 16, 'Sasuke Uchiha', 13.99, 24, 'Vinyl', '2019-02-15', 'Standard'),
(13, 18, 'Monkey D. Luffy', 14.49, 25, 'Vinyl', '2020-05-20', 'Standard'),
(13, 18, 'Roronoa Zoro', 14.49, 26, 'Vinyl', '2020-05-20', 'Standard'),
(14, 15, 'Pikachu', 9.99, 27, 'Vinyl', '2019-07-06', 'Standard'),
(14, 15, 'Charmander', 9.99, 28, 'Vinyl', '2019-07-06', 'Standard'),
(15, 5, 'Frodo Baggins', 11.99, 29, 'Vinyl', '2018-12-19', 'Standard'),
(15, 5, 'Gandalf', 11.99, 30, 'Vinyl', '2018-12-19', 'Standard'),
(16, 8, 'Michael Scott', 10.99, 31, 'Vinyl', '2019-10-07', 'Standard'),
(16, 8, 'Dwight Schrute', 10.99, 32, 'Vinyl', '2019-10-07', 'Standard'),
(17, 9, 'Rachel Green', 11.99, 33, 'Vinyl', '2019-05-01', 'Standard'),
(17, 9, 'Ross Geller', 11.99, 34, 'Vinyl', '2019-05-01', 'Standard'),
(18, 4, 'Izuku Midoriya', 14.99, 35, 'Vinyl', '2020-03-15', 'Standard'),
(18, 4, 'Katsuki Bakugo', 14.99, 36, 'Vinyl', '2020-03-15', 'Standard'),
(19, 13, 'Tracer', 12.99, 37, 'Vinyl', '2019-10-10', 'Standard'),
(19, 13, 'Reaper', 12.99, 38, 'Vinyl', '2019-10-10', 'Standard'),
(20, 6, 'Jonesy', 11.49, 39, 'Vinyl', '2019-06-05', 'Standard'),
(20, 6, 'Raven', 11.49, 40, 'Vinyl', '2019-06-05', 'Standard'),
(1, 3, 'Boba Fett', 14.99, 41, 'Vinyl', '2019-12-05', 'Standard'),
(1, 3, 'Princess Leia', 14.99, 42, 'Vinyl', '2019-12-05', 'Standard'),
(2, 10, 'Thor', 15.99, 43, 'Vinyl', '2020-02-20', 'Standard'),
(2, 10, 'Hulk', 15.99, 44, 'Vinyl', '2020-02-20', 'Standard'),
(3, 7, 'Ron Weasley', 11.99, 45, 'Vinyl', '2018-08-31', 'Standard'),
(3, 7, 'Albus Dumbledore', 11.99, 46, 'Vinyl', '2018-08-31', 'Standard'),
(4, 2, 'Tyrion Lannister', 13.99, 47, 'Vinyl', '2018-03-25', 'Standard'),
(4, 2, 'Arya Stark', 13.99, 48, 'Vinyl', '2018-03-25', 'Standard'),
(5, 1, 'Michonne', 10.99, 49, 'Vinyl', '2017-04-06', 'Standard'),
(5, 1, 'Negan', 10.99, 50, 'Vinyl', '2017-04-06', 'Standard');

Ejemplo modificar datos:

UPDATE `funko` SET `estilo` = 'Metallic' WHERE `idfunko` = 1;
UPDATE `funko` SET `estilo` = 'Glow in the Dark' WHERE `idfunko` = 2;
UPDATE `funko` SET `estilo` = 'Flocked' WHERE `idfunko` = 3;
UPDATE `funko` SET `estilo` = 'Diamond' WHERE `idfunko` = 4;
UPDATE `funko` SET `estilo` = 'Chrome' WHERE `idfunko` = 5;
UPDATE `funko` SET `estilo` = 'Pearlescent' WHERE `idfunko` = 6;
UPDATE `funko` SET `estilo` = 'Translucent' WHERE `idfunko` = 7;
UPDATE `funko` SET `estilo` = 'Glitter' WHERE `idfunko` = 8;
UPDATE `funko` SET `estilo` = 'Wooden' WHERE `idfunko` = 9;
UPDATE `funko` SET `estilo` = '8-Bit' WHERE `idfunko` = 10;
UPDATE `funko` SET `estilo` = 'Retro' WHERE `idfunko` = 11;
UPDATE `funko` SET `estilo` = 'Vintage' WHERE `idfunko` = 12;
UPDATE `funko` SET `estilo` = 'Chibi' WHERE `idfunko` = 13;
UPDATE `funko` SET `estilo` = 'Pixel' WHERE `idfunko` = 14;
UPDATE `funko` SET `estilo` = 'Steampunk' WHERE `idfunko` = 15;
UPDATE `funko` SET `estilo` = 'Graffiti' WHERE `idfunko` = 16;
UPDATE `funko` SET `estilo` = 'Art Series' WHERE `idfunko` = 17;
UPDATE `funko` SET `estilo` = 'Metallic Gold' WHERE `idfunko` = 18;
UPDATE `funko` SET `estilo` = 'Bronze' WHERE `idfunko` = 19;
UPDATE `funko` SET `estilo` = 'Silver' WHERE `idfunko` = 20;

UPDATE `funko` SET `tamanyo` = 'Large' WHERE `idfunko` = 1;
UPDATE `funko` SET `tamanyo` = 'Small' WHERE `idfunko` = 2;
UPDATE `funko` SET `tamanyo` = 'Mini' WHERE `idfunko` = 3;
UPDATE `funko` SET `tamanyo` = 'Jumbo' WHERE `idfunko` = 4;
UPDATE `funko` SET `tamanyo` = 'Super Sized' WHERE `idfunko` = 5;
UPDATE `funko` SET `tamanyo` = 'Pocket' WHERE `idfunko` = 6;
UPDATE `funko` SET `tamanyo` = 'Mega' WHERE `idfunko` = 7;
UPDATE `funko` SET `tamanyo` = 'Giant' WHERE `idfunko` = 8;
UPDATE `funko` SET `tamanyo` = 'Titan' WHERE `idfunko` = 9;
UPDATE `funko` SET `tamanyo` = 'Colossal' WHERE `idfunko` = 10;
UPDATE `funko` SET `tamanyo` = 'Compact' WHERE `idfunko` = 11;
UPDATE `funko` SET `tamanyo` = 'Petite' WHERE `idfunko` = 12;
UPDATE `funko` SET `tamanyo` = 'Grand' WHERE `idfunko` = 13;
UPDATE `funko` SET `tamanyo` = 'Tiny' WHERE `idfunko` = 14;
UPDATE `funko` SET `tamanyo` = 'Massive' WHERE `idfunko` = 15;
UPDATE `funko` SET `tamanyo` = 'Huge' WHERE `idfunko` = 16;
UPDATE `funko` SET `tamanyo` = 'Enormous' WHERE `idfunko` = 17;
UPDATE `funko` SET `tamanyo` = 'Oversized' WHERE `idfunko` = 18;
UPDATE `funko` SET `tamanyo` = 'Immense' WHERE `idfunko` = 19;
UPDATE `funko` SET `tamanyo` = 'Gargantuan' WHERE `idfunko` = 20;

UPDATE `funko` SET `estilo` = 'Diamond' WHERE `idfunko` between 20 and 25;
UPDATE `funko` SET `estilo` = 'Glitter' WHERE `idfunko` between 25 and 30;
UPDATE `funko` SET `estilo` = 'Steampunk' WHERE `idfunko` between 30 and 40;
UPDATE `funko` SET `estilo` = 'Metallic' WHERE `idfunko` >40;

UPDATE `funko` SET `tamanyo` = 'Small' WHERE `idfunko` between 20 and 25;
UPDATE `funko` SET `tamanyo` = 'Compact' WHERE `idfunko` between 25 and 30;
UPDATE `funko` SET `tamanyo` = 'Large' WHERE `idfunko` between 30 and 40;
UPDATE `funko` SET `tamanyo` = 'Oversized' WHERE `idfunko`>40;

Consultas

-- Consultas con condiciones simples
-- audiovisuales con más de 5 temporadas
select * from audiovisual where temporadas>5;
-- funkos que tengan una 'h' en el nombre
select * from funko where nombre like '%h%';
-- colecciones de diseñadores cuyo nombre empiece por 'M'
select * from coleccion where disenyador like 'm%';

-- Consultas con joins
-- Todos los funkos de la colección 'friends'
select funko.* from coleccion join funko on coleccion.idcoleccion=funko.idcoleccion
where coleccion.nombre='friends';
-- Todos los funkos del audiovisual 'one piece'
select funko.* from audiovisual join funko on audiovisual.idaudiovisual=funko.idaudiovisual
where titulo='one piece';
-- Todos los funkos de la colección o el audiovisual 'friends'
select f.* from funko f join coleccion c on f.idcoleccion=c.idcoleccion
join audiovisual a on f.idaudiovisual=a.idaudiovisual
where c.nombre='friends' or a.titulo='friends';

-- Consultas agrupadas
-- Total de funkos por colección
select coleccion.*, count(idfunko) total
from coleccion left join funko using(idcoleccion)
group by idcoleccion;

-- Total de funkos por audiovisual
select audiovisual.*, count(idfunko) total
from audiovisual join funko using(idaudiovisual)
group by idaudiovisual;
-- Total de funkos por estilos ordenados de mayor a  menor
select estilo,count(idfunko) total
from funko
group by estilo
order by total desc, estilo desc;

-- Media de precio de todos los funkos
select avg(precio) from funko;

-- Total de precios (suma) por tamaño
select tamanyo,sum(precio) total
from funko
group by tamanyo;
-- Colecciones con más de 10 funkos (no sé si hay)
select coleccion.*, count(idfunko) total
from coleccion left join funko using(idcoleccion)
group by idcoleccion
having total>=4;

-- Subconsultas y división
-- Coleccion (o colecciones) con mayor número de funkos

select coleccion.*, count(idfunko) total
from coleccion left join funko using(idcoleccion)
group by idcoleccion
having total=(maximo);


select max(total) total from (
select count(idfunko) total
from coleccion left join funko using(idcoleccion)
group by idcoleccion) foo;

select count(idfunko) total
from coleccion left join funko using(idcoleccion)
group by idcoleccion
order by total desc
limit 1 ;

-- Junto la primera con alguna de las segundas y voila
select coleccion.*, count(idfunko) total
from coleccion left join funko using(idcoleccion)
group by idcoleccion
having total=(select count(idfunko) total
from coleccion left join funko using(idcoleccion)
group by idcoleccion
order by total desc
limit 1);
-- Lo mismo para audiovisual

select audiovisual.*, count(idfunko) total
from audiovisual left join funko using(idaudiovisual)
group by idaudiovisual
having total=(select count(idfunko) total
from audiovisual left join funko using(idaudiovisual)
group by idaudiovisual
order by total desc
limit 1);


-- Funkos cuyo precio esté por encima de la media

-- calculo la media

select avg(precio) from funko;

select * from funko
where precio>(select avg(precio) from funko);

-- Consultas con funciones
-- Todos los funkos lanzados en febrero
select * from funko
where month(lanzamiento)=2;
-- Total de funkos por año
select year(lanzamiento) anyo,count(idfunko) total
from funko
group by year(lanzamiento);
-- Funkos cuyo nombre tenga una longitud mayor de 12 letras
select * from funko
where length(nombre)>12;
-- Funkos lanzados en 2020 o cuyo audiovisual se emitió en 2020
select * from funko join 
audiovisual on funko.idaudiovisual=audiovisual.idaudiovisual
where year(lanzamiento)=2020 or year(emision)=2020;