Apprendre SQL : Cas Pratique : Netfloux
Apprenez à maîtriser les requêtes élémentaires à travers un cas pratique : Netfloux, un service de VOD.
7 chapitres
Pour illustrer les concepts abordés dans ce cours, nous travaillerons sur l’élaboration et l’exploitation d’une base de données de séries et films comme la plateforme Netflix. La nôtre s’appellera donc Netfloux. 😎
Voici le MCD illustrant les données manipulées au sein de la plateforme.
Création de la base de données
Créer la base de données nommée netfloux. Cette base de données doit être encodée en utf8_general_ci.
DROP DATABASE IF EXISTS netfloux;
CREATE DATABASE IF NOT EXISTS netfloux CHARACTER SET UTF8 COLLATE utf8_general_ci;
Création des tables
Tables classiques
Table users
Créer une table nommée users. Un utilisateur est défini par :
id: identifiant (int)firstname: prénom (varchar)lastname: nom (varchar)email: adresse email (varchar)password: mot de passe (varchar)created_at: date d’inscription (datetime)
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(100),
lastname VARCHAR(100),
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at DATE,
PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table movies
Créer une table nommée movies. Un film est défini par :
id: identifiant (int)title: titre (varchar)duration: durée (time)released_at: date de sortie (date)
CREATE TABLE movies (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255),
duration TIME,
released_at DATE,
PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table series
Créer une table nommée series. Une série est définie par :
id: identifiant (int)title: titre (varchar)finisheed: si la série est terminée ou pas (time)
CREATE TABLE series (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255),
finished INT(1),
PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Étant donné qu’une série est constituée de plusieurs épisodes, eux-mêmes regroupés en saisons, on aimerait faire apparaître cette structure dans la base de données en créant des tables dédiées seasons et episodes.
Cela permettra par exemple d’associer une date de sortie à un épisode plutôt qu’à la série toute entière.
Table seasons
Créer une table nommée seasons. Une saison est définie par :
id: identifiant (int)number: numéro (int)serie_id: clé étrangère (int)
CREATE TABLE seasons (
id INT NOT NULL AUTO_INCREMENT,
number INT(2),
serie_id INT NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_serie FOREIGN KEY(serie_id) REFERENCES series(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table episodes
Créer une table nommée episodes. Un épisode est défini par :
id: identifiant (int)title: titre (varchar)duration: durée (time)number: numéro (int)released_at: date de sortie (date)season_id: clé étrangère (int)
CREATE TABLE episodes (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255),
duration TIME,
number INT(2),
released_at DATE,
season_id INT NOT NULL,
PRIMARY KEY(id),
CONSTRAINT fk_season FOREIGN KEY(season_id) REFERENCES seasons(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table categories
Créer une table nommée categories. Une catégorie est définie par :
id: identifiant (int)name: nom (varchar)
CREATE TABLE categories (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Tables d’associations
Au lieu de stocker le nombre de vues, ainsi que l’évaluation d’un film ou d’une série sous la forme d’une valeur numérique directement dans les tables movies ou series, on aimerait plutôt respectivement les calculer à partir des visionnages de chaque utilisateur et des évaluations éventuellement laissées.
On va donc créer les tables d’association users_viewed_movies, users_viewed_episodes, users_rated_movies et users_rated_episodes mettant en relation des utilisateurs et des films ou séries.
Aussi, un film ou une série pouvant être liés à plusieurs catégories, on créera les tables d’association suivantes : movies_get_categories et series_get_categories.
Table users_viewed_movies
Créer une table nommée users_viewed_movies. Un visionnage est défini par :
user_id: identifiant / clé étrangère (int)movie_id: identifiant / clé étrangère (int)viewed_at: date de visionnage (datetime)
CREATE TABLE users_viewed_movies (
user_id INT NOT NULL,
movie_id INT NOT NULL,
viewed_at DATETIME NOT NULL,
PRIMARY KEY(user_id, movie_id, viewed_at),
CONSTRAINT fk_uvm_user FOREIGN KEY(user_id) REFERENCES users(id),
CONSTRAINT fk_uvm_movie FOREIGN KEY(movie_id) REFERENCES movies(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table users_viewed_episodes
Créer une table nommée users_viewed_episodes. Un visionnage est défini par :
user_id: identifiant / clé étrangère (int)episode_id: identifiant / clé étrangère (int)viewed_at: date de visionnage (datetime)
CREATE TABLE users_viewed_episodes (
user_id INT NOT NULL,
episode_id INT NOT NULL,
viewed_at DATETIME NOT NULL,
PRIMARY KEY(user_id, episode_id, viewed_at),
CONSTRAINT fk_uve_user FOREIGN KEY(user_id) REFERENCES users(id),
CONSTRAINT fk_uve_episode FOREIGN KEY(episode_id) REFERENCES episodes(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table users_rated_movies
Créer une table nommée users_rated_movies. Une évaluation est définie par :
user_id: identifiant / clé étrangère (int)movie_id: identifiant / clé étrangère (int)rating: évaluation (nombre)
CREATE TABLE users_rated_movies (
user_id INT NOT NULL,
movie_id INT NOT NULL,
rating DECIMAL(2,1) NOT NULL,
PRIMARY KEY(user_id, movie_id),
CONSTRAINT fk_urm_user FOREIGN KEY(user_id) REFERENCES users(id),
CONSTRAINT fk_urm_movie FOREIGN KEY(movie_id) REFERENCES movies(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table users_rated_episodes
Créer une table nommée users_rated_episodes. Une évaluation est définie par :
user_id: identifiant / clé étrangère (int)episode_id: identifiant / clé étrangère (int)rating: évaluation (nombre)
CREATE TABLE users_rated_episodes (
user_id INT NOT NULL,
episode_id INT NOT NULL,
rating DECIMAL(2,1) NOT NULL,
PRIMARY KEY(user_id, episode_id),
CONSTRAINT fk_ure_user FOREIGN KEY(user_id) REFERENCES users(id),
CONSTRAINT fk_ure_episode FOREIGN KEY(episode_id) REFERENCES episodes(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table movies_get_categories
Créer une table nommée movies_get_categories. Cette association est définie par :
movie_id: identifiant / clé étrangère (int)category_id: identifiant / clé étrangère (int)
CREATE TABLE movies_get_categories (
movie_id INT NOT NULL,
category_id INT NOT NULL,
PRIMARY KEY(movie_id, category_id),
CONSTRAINT fk_mgc_movie FOREIGN KEY(movie_id) REFERENCES movies(id),
CONSTRAINT fk_mgc_category FOREIGN KEY(category_id) REFERENCES categories(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Table series_get_categories
Créer une table nommée series_get_categories. Cette association est définie par :
serie_id: identifiant / clé étrangère (int)category_id: identifiant / clé étrangère (int)
CREATE TABLE series_get_categories (
serie_id INT NOT NULL,
category_id INT NOT NULL,
PRIMARY KEY(serie_id, category_id),
CONSTRAINT fk_sgc_serie FOREIGN KEY(serie_id) REFERENCES series(id),
CONSTRAINT fk_sgc_category FOREIGN KEY(category_id) REFERENCES categories(id)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Déchargement de la base de données
Insérons désormais un jeu de données dans notre base de données Netfloux. On appelle cela le déchargement de données.
Voir le script .sql
-- CATEGORIES
INSERT INTO categories VALUES
(1, "Comédie"),
(2, "Drame"),
(3, "Policier"),
(4, "Espionnage"),
(5, "Thriller"),
(6, "Aventure"),
(7, "Romance"),
(8, "Guerre"),
(9, "Action"),
(10, "Biopic"),
(11, "Historique");
-- UTILISATEURS
INSERT INTO users VALUES
(1, "Gérard", "Lanéguo", "[email protected]", "89e495e7941cf9e40e6980d14a16bf023ccd4c91", "2021-02-10"),
(2, "Claire", "Sorrad", "[email protected]", "89e495e7941cf9e40e6980d14a16bf023ccd4c91", "2010-09-12"),
(3, "Cirielle", "Bougy", "[email protected]", "89e495e7941cf9e40e6980d14a16bf023ccd4c91", "2018-09-12"),
(4, "Anaïs", "Verdant", "[email protected]", "89e495e7941cf9e40e6980d14a16bf023ccd4c91", "2015-01-12"),
(5, "Léna", "Logie", "[email protected]", "89e495e7941cf9e40e6980d14a16bf023ccd4c91", "2020-09-12"),
(6, "Camille", "Mafiye", "[email protected]", "89e495e7941cf9e40e6980d14a16bf023ccd4c91", "2018-09-12"),
(7, "Guillaume", "Filegou", "[email protected]", "89e495e7941cf9e40e6980d14a16bf023ccd4c91", "2010-09-17");
-- FILMS
INSERT INTO movies VALUES
(1, "Nos jours heureux", "01:43:00", "2006-06-28"),
(2, "Don't Look Up : Déni cosmique", "02:25:00", "2021-12-05"),
(3, "RRRrrrr!!!", "01:38:00", "2004-01-28"),
(4, "Le Grand Bleu", "02:12:00", "1988-05-11"),
(5, "BAC Nord", "01:47:00", "2020-10-16"),
(6, "Intouchables", "01:52:00", "2011-11-02"),
(7, "Le sens de la fête", "01:56:00", "2017-07-05"),
(8, "Gran Torino", "01:56:00", "2009-02-25"),
(9, "Le Chant du Loup", "01:55:00", "2019-02-20");
-- SERIES
INSERT INTO series VALUES
(1, "Silicon Valley", 1),
(2, "The Billion Dollar Code", 1),
(3, "Mr. Robot", 1),
(4, "Ozark", 0),
(5, "Breaking Bad", 1),
(6, "Peaky Blinders", 0);
-- SAISONS
INSERT INTO seasons VALUES
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 4, 1),
(5, 5, 1),
(6, 6, 1),
(7, 1, 2),
(8, 1, 3),
(9, 2, 3),
(10, 3, 3),
(11, 4, 3);
-- EPISODES
INSERT INTO episodes VALUES
(1, "Minimum Viable Product", "00:42:00", 1, "2014-04-06", 1),
(2, "The Cap Table", "00:40:00", 2, "2014-04-13", 1),
(3, "Articles of Incorporation", "00:42:00", 3, "2014-04-20", 1),
(4, "Fiduciary Duties", "00:40:00", 4, "2014-04-27", 1),
(5, "Signaling Risk", "00:40:00", 5, "2014-05-04", 1),
(6, "Third Party Insourcing", "00:38:00", 6, "2014-05-11", 1),
(7, "Proof of Concept", "00:40:00", 7, "2014-04-18", 1),
(8, "Optimal Tip-to-Tip Efficiency", "00:42:00", 8, "2014-06-01", 1),
(9, "Sand Hill Shuffle", "00:39:00", 1, "2015-04-12", 2),
(10, "Runaway Devaluation", "00:36:00", 2, "2015-04-19", 2),
(11, "Bad Money", "00:40:00", 3, "2015-04-26", 2),
(12, "The Lady", "00:40:00", 4, "2015-05-03", 2),
(13, "Server Space", "00:39:00", 5, "2015-05-10", 2),
(14, "Homicide", "00:43:00", 6, "2015-05-17", 2),
(15, "Adult Content", "00:41:00", 7, "2015-05-24", 2),
(16, "White Hat/Black Hat", "00:42:00", 8, "2015-05-31", 2),
(17, "Binding Arbitration", "00:37:00", 9, "2015-06-07", 2),
(18, "Two Days of the Condor", "00:40:00", 10, "2015-05-14", 2),
(19, "Founder Friendly", "00:38:00", 1, "2016-04-24", 3),
(20, "Two in the Box", "00:39:00", 2, "2016-05-01", 3),
(21, "Meinertzhagen's Haversack", "00:43:00", 3, "2016-05-08", 3),
(22, "Maleant Data Systems Solutions", "00:35:00", 4, "2016-05-15", 3),
(23, "The Empty Chair", "00:38:00", 5, "2016-05-22", 3),
(24, "Bachmanity Insanity", "00:40:00", 6, "2016-04-29", 3),
(25, "To Build A Better Beta", "00:42:00", 7, "2016-06-05", 3),
(26, "Bachman's Earnings Over-Ride", "00:40:00", 8, "2016-06-12", 3),
(27, "Daily Active Users", "00:41:00", 9, "2016-06-19", 3),
(28, "The Uptick", "00:37:00", 10, "2016-06-26", 3),
(29, "Success Failure", "00:39:00", 1, "2017-04-23", 4),
(30, "Terms of Service", "00:44:00", 2, "2017-04-30", 4),
(31, "Intellectual Property", "00:38:00", 3, "2017-05-07", 4),
(32, "Teambuilding Exercise", "00:41:00", 4, "2017-05-14", 4),
(33, "The Blood Boy", "00:38:00", 5, "2017-05-21", 4),
(34, "Customer Service", "00:40:00", 6, "2017-05-28", 4),
(35, "The Patent Troll", "00:43:00", 7, "2017-06-04", 4),
(36, "The Keenan Vortex", "00:37:00", 8, "2017-06-11", 4),
(37, "Hooli-Con", "00:41:00", 9, "2017-06-18", 4),
(38, "Server Error", "00:45:00", 10, "2017-06-25", 4),
(39, "Grow Fast or Die Slow", "00:41:00", 1, "2018-03-25", 5),
(40, "Reorientation", "00:35:00", 2, "2018-04-01", 5),
(41, "Chief Operating Officer", "00:39:00", 3, "2018-04-08", 5),
(42, "Tech Evangelist", "00:43:00", 4, "2018-04-15", 5),
(43, "Facial Recognition", "00:40:00", 5, "2018-04-22", 5),
(44, "Artificial Emotional Intelligence", "00:38:00", 6, "2018-04-29", 5),
(45, "Initial Coin Offering", "00:41:00", 7, "2018-05-06", 5),
(46, "Fifty-One Percent", "00:44:00", 8, "2018-05-13", 5),
(47, "Artificial Lack of Intelligence", "00:41:00", 1, "2019-10-29", 6),
(48, "Blood Money", "00:40:00", 2, "2019-11-03", 6),
(49, "Hooli Smokes!", "00:44:00", 3, "2019-11-10", 6),
(50, "Maximizing Alphaness", "00:35:00", 4, "2019-11-17", 6),
(51, "Tethics", "00:36:00", 5, "2019-11-24", 6),
(52, "RussFest", "00:40:00", 6, "2019-12-01", 6),
(53, "Exit Event", "00:47:00", 7, "2019-12-08", 6),
(54, NULL, "01:08:00", 1, "2021-10-07", 7),
(55, NULL, "01:03:00", 2, "2021-10-07", 7),
(56, NULL, "00:59:00", 3, "2021-10-07", 7),
(57, NULL, "01:17:00", 4, "2021-10-07", 7),
(58, "0_hellofriend.mov", "00:54:00", 1, "2015-06-24", 8),
(59, "1_ones-and-zer0es.mpeg", "00:51:00", 2, "2015-07-01", 8),
(60, "2_d3bug.mkv", "00:53:00", 3, "2015-07-08", 8),
(61, "3_da3m0ns.mp4", "00:58:00", 4, "2015-07-15", 8),
(62, "4_3xpl0its.wmv", "00:54:00", 5, "2015-07-22", 8),
(63, "5_br4ve-trave1er.asf", "00:59:00", 6, "2015-07-29", 8),
(64, "6_v1ew-s0urce.flv", "00:57:00", 7, "2015-08-05", 8),
(65, "7_wh1ter0se.m4v", "00:50:00", 8, "2015-08-12", 8),
(66, "8_m1rr0r1ng.qt", "00:55:00", 9, "2015-08-19", 8),
(67, "9_zer0-day.avi", "01:01:00", 10, "2015-09-02", 8),
(68, "0_unm4sk-pt1.tc", "00:49:00", 1, "2016-07-13", 9),
(69, "0_unm4sk-pt2.tc", "00:50:00", 2, "2016-07-13", 9),
(70, "1_k3rnel-pan1c.ksd", "00:51:00", 3, "2016-07-20", 9),
(71, "2_init1.asec", "00:52:00", 4, "2016-07-27", 9),
(72, "3_logic-b0mb.hc", "00:51:00", 5, "2016-08-03", 9),
(73, "4_m4ster_s1ave.aes", "00:55:00", 6, "2016-08-10", 9),
(74, "5_h4ndshake.sme", "00:51:00", 7, "2016-08-17", 9),
(75, "6_succ3ss0r.p12", "00:53:00", 8, "2016-08-24", 9),
(76, "7_init_5.fve", "00:51:00", 9, "2016-08-31", 9),
(77, "8_h1dden-pr0cess.axx", "00:51:00", 10, "2016-09-07", 9),
(78, "9_pyth0n-pt1.p7z", "00:58:00", 11, "2016-09-14", 9),
(79, "9_pyth0n-pt2.p7z", "00:58:00", 12, "2016-09-21", 9),
(80, "0_power-saver-mode.h", "00:51:00", 1, "2017-10-11", 10),
(81, "1_undo.gz", "00:58:00", 2, "2017-10-18", 10),
(82, "2_legacy.so", "00:52:00", 3, "2017-10-25", 10),
(83, "3_m3tadata.par2", "00:56:00", 4, "2017-11-01", 10),
(84, "4_runtime-err0r.r00", "00:55:00", 5, "2017-11-08", 10),
(85, "5_kill-pr0cess.inc", "00:47:00", 6, "2017-11-15", 10),
(86, "6_fredrick+tanya.chk", "00:51:00", 7, "2017-11-22", 10),
(87, "7_dont-delete-me.ko", "00:53:00", 8, "2017-11-29", 10),
(88, "8_stage3.torrent", "00:52:00", 9, "2017-12-06", 10),
(89, "shutdown -r", "01:08:00", 10, "2017-12-13", 10),
(90, "401 Unauthorized", "00:59:00", 1, "2019-10-06", 11),
(91, "402 Payment Required", "00:51:00", 2, "2019-10-13", 11),
(92, "403 Forbidden Error", "00:50:00", 3, "2019-10-20", 11),
(93, "404 Not Found", "00:52:00", 4, "2019-10-27", 11),
(94, "405 Method Not Allowed", "00:55:00", 5, "2019-11-03", 11),
(95, "406 Not Acceptable", "00:56:00", 6, "2019-11-10", 11),
(96, "407 Proxy Authentication Required", "00:53:00", 7, "2019-11-17", 11),
(97, "408 Request Timeout", "00:52:00", 8, "2019-11-24", 11),
(98, "409 Conflict", "00:48:00", 9, "2019-12-01", 11),
(99, "410 Gone", "00:49:00", 10, "2019-12-08", 11),
(100, "411 eXit", "00:54:00", 11, "2019-12-15", 11),
(101, "412 Precondition Failed", "01:00:00", 12, "2019-12-22", 11),
(102, "413 Payload Too Large", "01:13:00", 13, "2019-12-22", 11);
-- VISIONNAGES DE FILMS
INSERT INTO users_viewed_movies VALUES
(1, 3, "2021-01-10 21:11:20"),
(1, 4, "2019-02-14 21:59:59"),
(1, 5, "2021-08-11 07:54:34"),
(1, 9, "2019-11-11 17:45:01"),
(2, 1, "2018-07-21 13:12:58"),
(2, 1, "2007-01-13 19:00:46"),
(2, 9, "2022-01-15 06:30:12"),
(3, 2, "2012-12-24 20:05:30"),
(3, 3, "2021-12-24 23:11:09"),
(3, 9, "2022-01-30 23:12:34"),
(4, 1, "2020-01-22 18:12:08"),
(4, 2, "2019-02-17 20:12:05"),
(4, 3, "2021-09-04 21:12:32"),
(4, 4, "2017-01-07 20:02:45"),
(4, 5, "2019-11-01 21:29:34"),
(4, 6, "2019-12-24 21:55:18"),
(4, 7, "2016-05-20 18:39:34"),
(4, 8, "2015-06-15 02:20:27"),
(4, 9, "2011-06-06 13:15:43"),
(5, 2, "2021-02-19 01:12:37"),
(5, 8, "2020-03-28 09:28:34"),
(5, 9, "2021-10-20 03:48:17"),
(7, 7, "2021-02-19 01:12:37");
-- VISIONNAGES D'EPISODES
INSERT INTO users_viewed_episodes VALUES
(1, 1, "2021-01-10 21:11:20"),
(1, 2, "2021-01-11 21:59:59"),
(1, 3, "2021-01-11 23:54:34"),
(1, 4, "2021-01-12 13:12:58"),
(1, 5, "2021-01-17 19:00:46"),
(1, 6, "2021-01-24 20:05:30"),
(1, 7, "2021-01-25 21:12:34"),
(1, 8, "2021-02-04 23:12:34"),
(1, 9, "2021-02-05 18:12:08"),
(1, 10, "2021-02-10 20:12:05"),
(1, 54, "2021-06-20 20:12:05"),
(1, 55, "2021-06-21 20:12:05"),
(1, 56, "2021-06-28 20:12:05"),
(1, 57, "2021-07-10 20:12:05"),
(2, 54, "2020-09-01 22:10:05"),
(2, 55, "2020-09-04 23:37:05"),
(2, 56, "2020-09-12 04:12:45"),
(2, 57, "2020-09-15 00:00:00"),
(3, 1, "2018-09-23 15:37:11"),
(3, 2, "2018-12-05 22:00:00"),
(3, 3, "2019-04-01 13:22:09"),
(3, 4, "2019-04-01 13:22:09"),
(7, 58, "2017-03-08 10:11:00");
-- EVALUATIONS DE FILMS
INSERT INTO users_rated_movies VALUES
(1, 3, 5),
(1, 4, 2.5),
(2, 1, 5),
(3, 2, 3),
(4, 1, 4),
(4, 2, 3),
(4, 3, 3.5),
(4, 5, 2),
(4, 7, 5),
(4, 9, 5),
(5, 2, 1),
(7, 7, 3.5);
-- EVALUATIONS D'EPISODES
INSERT INTO users_rated_episodes VALUES
(1, 1, 5),
(1, 2, 4.5),
(1, 3, 4),
(1, 4, 3),
(1, 5, 3.5),
(1, 6, 2),
(1, 7, 4.5),
(1, 8, 5),
(1, 9, 4),
(1, 10, 4),
(1, 54, 3),
(1, 55, 3),
(1, 56, 4),
(1, 57, 5),
(2, 54, 4.5),
(2, 55, 5),
(2, 56, 5),
(2, 57, 4),
(3, 1, 4.5),
(3, 2, 3),
(3, 3, 3.5);
-- CLASSEMENT FILMS
INSERT INTO movies_get_categories VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(4, 6),
(4, 7),
(5, 3),
(5, 5),
(6, 2),
(7, 1),
(8, 2),
(8, 5),
(9, 5),
(9, 8),
(9, 9);
-- CLASSEMENT SERIES
INSERT INTO series_get_categories VALUES
(1, 1),
(2, 2),
(2, 10),
(3, 2),
(3, 5),
(4, 2),
(4, 5),
(5, 2),
(6, 2),
(6, 3),
(6, 11);
Requêter la base de données
Il est temps d’interroger notre base de données et d’obtenir des réponses à nos questions !
1. Combien d'utilisateurs sont inscrits sur Netfloux ?
SELECT COUNT(id) FROM users;
2. Quels sont les films disponibles sur Netfloux ?
SELECT title FROM movies;
3. Afficher la liste des utilisateurs par ordre alphabétique (nom puis prénom)
SELECT lastname, firstname FROM users ORDER BY lastname, firstname;
4. Quelles séries ne sont pas encore terminées ?
SELECT title FROM series WHERE finished = 0;
5. Quel est le titre du 3ᵉ épisode de la saison 5 de Silicon Valley ?
SELECT episodes.title FROM episodes
INNER JOIN seasons ON episodes.season_id = seasons.id
INNER JOIN series ON seasons.serie_id = series.id
WHERE seasons.number = 5 AND episodes.number = 3 AND series.title = "Silicon Valley";
6. Combien d'utilisateurs ont une messagerie Gmail ?
SELECT COUNT(id) FROM users WHERE email LIKE '%@gmail.com';
7. Quel a été le premier utilisateur à s'inscrire sur Netfloux ?
SELECT firstname, lastname FROM users ORDER BY created_at LIMIT 1;
8. Quel est le dernier film à être sorti ?
SELECT title FROM movies ORDER BY released_at DESC LIMIT 1;
9. Quelle est la durée du plus long épisode de The Billion Dollar Code ?
SELECT episodes.duration FROM episodes
INNER JOIN seasons ON episodes.season_id = seasons.id
INNER JOIN series ON seasons.serie_id = series.id
WHERE series.title = 'The Billion Dollar Code'
ORDER BY episodes.duration DESC LIMIT 1;
-- OU
SELECT MAX(episodes.duration) FROM episodes
INNER JOIN seasons ON episodes.season_id = seasons.id
INNER JOIN series ON seasons.serie_id = series.id
WHERE series.title = 'The Billion Dollar Code';
10. Quel film est le plus court ?
SELECT title FROM movies ORDER BY duration LIMIT 1;
11. Pour chaque série, afficher le nombre d'épisodes qu'elle contient.
SELECT series.title, COUNT(episodes.id) AS nb_episodes FROM episodes
INNER JOIN seasons ON episodes.season_id = seasons.id
INNER JOIN series ON seasons.serie_id = series.id
GROUP BY series.title;
12. Quelle série possède le plus d'épisodes ?
SELECT series.title, COUNT(episodes.id) AS nb_episodes FROM series
INNER JOIN seasons ON seasons.serie_id = series.id
INNER JOIN episodes ON seasons.id = episodes.season_id
GROUP BY series.title ORDER BY nb_episodes DESC LIMIT 1;
13. Quelle série possède le plus de saisons ?
SELECT series.title, COUNT(seasons.id) AS nb_saisons FROM series
INNER JOIN seasons ON seasons.serie_id = series.id
GROUP BY series.title ORDER BY nb_saisons DESC LIMIT 1;
14. Quels sont les 3 films ayant été visionnés le plus de fois (en cas d'égalité, trier par titre alphabétique) ?
SELECT movies.title, COUNT(users_viewed_movies.movie_id) AS nb_vues FROM movies
INNER JOIN users_viewed_movies ON movies.id = users_viewed_movies.movie_id
GROUP BY movies.title ORDER BY nb_vues DESC, movies.title LIMIT 3;
15. Combien d'utilisateurs différents ont déjà passé un réveillon (24 décembre entre 19h et minuit) devant un film ?
SELECT COUNT(DISTINCT users.id) FROM users
INNER JOIN users_viewed_movies ON users.id = users_viewed_movies.user_id
WHERE MONTH(users_viewed_movies.viewed_at) = 12
AND DAY(users_viewed_movies.viewed_at) = 24
AND HOUR(users_viewed_movies.viewed_at) BETWEEN 19 AND 24;
16. Quelle est la durée du second film le plus long ?
SELECT title, duration FROM movies ORDER BY duration DESC LIMIT 1 OFFSET 1;
17. Quelle série connaît le plus grand succès (meilleure note) ?
SELECT series.title, AVG(users_rated_episodes.rating) AS note_moyenne FROM users_rated_episodes
INNER JOIN episodes ON episodes.id = users_rated_episodes.episode_id
INNER JOIN seasons ON seasons.id = episodes.season_id
INNER JOIN series ON series.id = seasons.serie_id
GROUP BY series.title ORDER BY note_moyenne DESC LIMIT 1;
18. Quelle série ne nomme pas ses épisodes ?
SELECT DISTINCT series.title FROM series
INNER JOIN seasons ON series.id = seasons.serie_id
INNER JOIN episodes ON seasons.id = episodes.season_id
WHERE episodes.title IS NULL;
19. Quand est sorti le dernier épisode de Silicon Valley ?
SELECT episodes.title, episodes.released_at FROM episodes
INNER JOIN seasons ON seasons.id = episodes.season_id
INNER JOIN series ON series.id = seasons.serie_id
WHERE series.title = "Silicon Valley"
ORDER BY episodes.released_at DESC LIMIT 1;
20. En quelle année est sorti le film « RRRrrrr!!! » ?
SELECT YEAR(released_at) FROM movies WHERE title = "RRRrrrr!!!";
21. Afficher dans l'ordre décroissant combien d'avis ont été laissés par chaque utilisateur sur les films.
SELECT users.firstname, users.lastname, COUNT(users_rated_movies.user_id) AS nb_avis FROM users_rated_movies
INNER JOIN users ON users_rated_movies.user_id = users.id
GROUP BY users.id ORDER BY nb_avis DESC;
22. Qui a laissé le plus d'avis sur des séries ?
SELECT users.firstname, users.lastname, COUNT(users_rated_episodes.user_id) AS nb_avis FROM users
INNER JOIN users_rated_episodes ON users_rated_episodes.user_id = users.id
GROUP BY users.id
ORDER BY nb_avis DESC LIMIT 1;
23. Quelle est la note moyenne de la saison 1 de Silicon Valley ?
SELECT AVG(users_rated_episodes.rating) AS note_moyenne FROM users_rated_episodes
INNER JOIN episodes ON episodes.id = users_rated_episodes.episode_id
INNER JOIN seasons ON seasons.id = episodes.season_id
INNER JOIN series ON series.id = seasons.serie_id
WHERE seasons.number = 1 AND series.title = 'Silicon Valley';
24. Lister l'ensemble des catégories de la plateforme dans l'ordre alphabétique (Action, Aventure, Biopic…).
SELECT name FROM categories ORDER BY name;
25. Quelle(s) catégorie(s) ne propose(nt) ni films ni séries ?
SELECT name FROM categories
WHERE name NOT IN (
SELECT DISTINCT name FROM categories
INNER JOIN movies_get_categories ON categories.id = movies_get_categories.category_id
)
AND name NOT IN (
SELECT DISTINCT name FROM categories
INNER JOIN series_get_categories ON categories.id = series_get_categories.category_id
);