Quizz SQL

Implémentez vos bases de données relationnelles avec SQL

Créez une base de données avec MySQL

1 - Afin de pouvoir utiliser MYSQL et de créer votre base de données, il vous faut créer l’utilisateur root.
Ouvrez-votre terminal.
Comment connectez-vous MySQL avec l'utilisateur root ?

mysql -u root -p puis on tape le mot de passe de l’utilisateur root.

mysqladmin -u root -p puis on tape le mot de passe de l’utilisateur root.

mysqladmin -u root password puis on tape le mot de passe de l’utilisateur root.

L’utilisateur root, est l’utilisateur vous permettant de manipuler votre BDD.
MySQL est la commande pour lancer… MySQL.
Puis -u sert à mentionner avec quel utilisateur on se connecte (ici, “root”).
Enfin, -p indique qu’on va ensuite taper le mot de passe de cet utilisateur.

2 - MySQL est désormais prêt à être utilisé.
Vous allez appeler votre base de données Moviz.
Quelle commande saisissez-vous pour créer la base de données de votre projet ?

CREATE BASE moviz;

CREATE DATABASE project;

CREATE DATABASE moviz;

Pour créer une BDD, on utilise la commande CREATE DATABASE suivie du nom de la base.

3 - La base de données Moviz comprend 4 tables différentes. Les 4 tables sont :

film ;

acteur ;

realisateur ;

note

La table film comprend les données suivantes :

nom : le nom du film ;

date_de_sortie : la date de sortie du film ;

synopsis : la description du scénario du film ;

langue : la langue principale du film ;

budget : le budget de production du film.

Créez la table film.
Quelle est la commande à réaliser pour créer la table ?

CREATE TABLE film (

nom VARCHAR(255) NOT NULL,

date_de_sortie DATE,

synopsis TEXT,

langue VARCHAR(255),

budget INT

)

CREATE TABLE film (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

nom VARCHAR(255) NOT NULL,

date_de_sortie DATE,

synopsis TEXT,

langue VARCHAR(255),

budget INT

)

CREATE TABLE film (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

nom DATE NOT NULL,

date_de_sortie VARCHAR(255),

synopsis TEXT,

langue VARCHAR(255),

budget TEXT

)

Créer une table se fait avec la commande CREATE TABLE, suivie du nom de la table, puis des colonnes (ou champs) de la future table.

4 - Vous avez créé votre table film, mais vous aimeriez vous en assurer.
Quelle commande permet de vérifier la création de la table film ?

SHOW tables;

SHOW databases:

SHOW me the money;

SHOW tables permet de voir toutes les tables d’une BDD, et ainsi, d'en vérifier la création.

5 - Votre table film est bien créée, mais vous aimeriez à présent vérifier sa structure.
Quelle commande faites-vous ?

SHOW FIELDS FROM film;
SHOW COLUMNS FROM film;

SHOW STRUCTURE FROM film;

La commande pour voir la structure d’une table est celle qui permet d’en voir les colonnes.
Soit SHOW COLUMNS FROM suivie du nom de la table.

Manipulez des données avec les opérations CRUD

de les lire depuis votre base ;

de les modifier ;

et enfin de les supprimer.

Pour rappel, voici le schéma de la table “film” :

Nom du champ	Type du champ	Description du champ
nom	VARCHAR(255)	le nom du film
date_de_sortie	DATE	la date de sortie du film
synopsis	TEXT	la description du scénario du film
langue	VARCHAR(255)	la langue principale du film
budget	INTEGER	le budget de production du film

Et les trois films que l’on va ajouter puis manipuler :

Nom	Date de sortie	Budget	Synopsis	Langue
Titanic	04 avril 1998	200 000 000 $	L'histoire du plus grand paquebot de son époque, et de sa funeste fin.	anglais
Skyfall	26 octobre 2012	200 000 000 $	Dans cet épisode le plus sombre de la saga, James Bond retourne à ses origines.	anglais
La La Land	25 janvier 2017	30 000 000 $	Une histoire d'amour à Los Angeles sous forme de comédie musicale.	anglais

1 - Quelle commande crée les trois films ?

INSERT INTO `film` (`nom`, `budget`, `synopsis`, `langue`, `date_de_sortie`)

VALUES

(`Titanic`, `1998-04-04`, `L'histoire du plus grand paquebot de son époque, et de sa funestre fin.`, `anglais`, 200000000),

(`Skyfall`, `2012-10-26`, `Dans cet épisode le plus sombre de la saga, James Bond retourne à ses origines.`, `anglais`, 200000000),

(`La La Land`, `2017-01-25`, `Une histoire d'amour à Los Angeles sous forme de comédie musicale.`, `anglais`, 30000000);

UPDATE INTO `film` (`nom`, `date_de_sortie`, `synopsis`, `langue`, `budget`)

VALUES

(`Titanic`, `1997-04-04`, `L'histoire du plus grand paquebot de son époque, et de sa funestre fin.`, `français`, 15000000),

(`Skyfall`, `2015-10-26`, `Dans cet épisode le plus sombre de la saga, James Bond retourne à ses origines.`, `allemand`, 85000000),

(`La La Land`, `2017-01-15`, `Une histoire d'amour à Los Angeles sous forme de comédie musicale.`, `anglais`, 30000000);

INSERT INTO `film` (`nom`, `date_de_sortie`, `synopsis`, `langue`, `budget`)

VALUES

(`Titanic`, `1998-04-04`, `L'histoire du plus grand paquebot de son époque, et de sa funestre fin.`, `anglais`, 200000000),

(`Skyfall`, `2012-10-26`, `Dans cet épisode le plus sombre de la saga, James Bond retourne à ses origines.`, `anglais`, 200000000),

(`La La Land`, `2017-01-25`, `Une histoire d'amour à Los Angeles sous forme de comédie musicale.`, `anglais`, 30000000);

La commande pour ajouter des objets est INSERT INTO,
suivi de la table dans laquelle insérer les objets (ici, “film”),
puis du nom des colonnes que l’on souhaite renseigner,
de VALUES,
avec les valeurs de chaque objet dans l’ordre des colonnes mentionné précédemment.

2 - Quelle commande permet de lire les titres et dates de sorties des films que l’on vient d’ajouter ?

SELECT * FROM film;

SELECT `nom`, `date_de_sortie` FROM film;

DELETE * FROM film;

SELECT, quand il est utilisé avec des colonnes spécifiques (au contraire de *, qui signifie “toutes les colonnes”),
n’affichera que les colonnes mentionnées.

3 - Quelle serait la fin d’une commande pour filtrer sur le film Titanic uniquement ?

WHERE `nom` = “Titanic”;

WHERE `Titanic`:

DELETE * FROM film;

Ici, WHERE, suivi de la condition sur le titre égal à “Titanic”, restreindra la commande à ce film uniquement.

4 -Si on utilisait UPDATE sans WHERE, que se passerait-il ?

On supprimerait tous les objets d’une table.

On mettrait à jour tous les objets d’une table.

Le commande ne fonctionnerait pas.

Par défaut, une commande UPDATE, mais aussi SELECT ou DELETE,
s’applique sur tous les objets d’une table en l’absence de WHERE.

5 - Quelle commande permet de mettre à jour le budget de Skyfall ?

UPDATE `film` SET `budget` = 200500000 WHERE `nom` = “Skyfall”;
INSERT INTO `film` SET `budget` = 200500000 WHERE `nom` = “Skyfall”;

UPDATE `film` SET `budget` = 200500000 WHERE `nom` = “Titanic”;

La mise à jour d’un objet implique une commande UPDATE.
SET sert à indiquer la colonne que l’on souhaite modifier, et avec quelle valeur.
Ensuite, on utilise WHERE pour filtrer sur le bon film.

6 - Quelle commande permet de supprimer La La Land ?

DELETE `film` WHERE `nom` = “La La Land”;
DELETE FROM `film` WHERE `nom` = “La La Land”;

DELETE `film`;

Supprimer implique la commande DELETE,
puis FROM pour signaler dans quelle table supprimer l’objet.
Enfin, on filtre le bon objet avec WHERE.

Effectuez des requêtes SQL avancées

Reprenons notre BDD Moviz du précédent quiz. Cette base doit contenir des films, or elle n’en contient actuellement aucun !
Si vous ne l’avez plus, voici le lien pour la télécharger : Moviz.sql
En plus de la table “film” qui a évolué pour inclure la note_id,
j’ai rajouté les tables “note”
et “pays_de_sortie”
ainsi que la table de liaison “film_pays_de_sortie”.
Il existe donc une relation un à plusieurs entre “film” et “note”,
et une relation plusieurs à plusieurs entre “film” et “pays_de_sortie”.

Voici le schéma mis à jour de la table “film” :

Nom du champ	Type du champ	Description du champ
nom	VARCHAR(255)	le nom du film
date_de_sortie	DATE	la date de sortie du film
synopsis	TEXT	la description du scénario du film
langue	VARCHAR(255)	la langue principale du film
budget	INTEGER	le budget de production du film
note_id	INTEGER	l'id de l'objet note associé au film

Le schéma de la table “note” est disponible ici :

Nom du champ	Type du champ	Description du champ
note	INTEGER	la note sur 5

Le schéma de la table “pays_de_sortie” est disponible ici :

Nom du champ	Type du champ	Description du champ
nom	VARCHAR(255)	le nom du pays

1 - Quelle est la commande qui permet de récupérer uniquement le film Skyfall ?

SELECT * FROM film WHERE nom LIKE “Skyfall”;

SELECT * FROM film WHERE id = 1;

SELECT * FROM film ORDER BY nom = “Skyfall”;

Ici, on souhaite récupérer le film dont le nom est “Skyfall”.
Filtrer se fait avec le mot clé WHERE auquel on ajoute la règle de comparaison.
Ici, on cherche à matcher exactement le mot Skyfall sur la colonne nom,
d’où l’utilisation de l’opérateur d’égalité.

2 - Quels sont les films dont le budget est supérieur ou égal à 100 000 000 $ ?

Titanic et Skyfall

Titanic, Skyfall et La La Land

Titanic uniquement

Ici, on souhaite récupérer plusieurs films selon un opérateur de comparaison sur la colonne budget.
Il s’agit de l’opérateur supérieur ou égal >= qui se place lui aussi après un WHERE.
La commande est donc :

SELECT * FROM film WHERE budget >= 100000000;

3 - Quels sont les films dont le synopsis contient le mot “histoire” ?

Skyfall uniquement

Skyfall et La La Land

Titanic et La La Land

Ici, ou souhaite récupérer plusieurs films dont la colonne synopsis contient le mot "histoire".
Pour ce faire, on utilise toujours un filtre après WHERE.
Il s’agit du filtre LIKE, auquel on ajoute le texte à comparer,
et on indique à MySQL que ce texte se situe au milieu d’une phrase grâce aux pourcentages avant et après le mot,
soit %histoire%.
La commande à taper est donc

SELECT * FROM film WHERE synopsis LIKE “%histoire%”;

4 -Combien y-a-t-il de films ?

Ici, on souhaite compter le nombre de films.
Cela est possible grâce au mot clé COUNT qui, adossé à une commande SELECT,
permet de retourner le nombre d’objets matchés.
La commande à taper est

SELECT COUNT(*) FROM film;

5 - Quels sont les mots clés permettant d’effectuer des opérations arithmétiques sur des requêtes SQL ?

SUM, AVG, MAX, MIN
SOMME, MOYENNE, MAXIMUM, MINIMUM

SUM, AVE, MAXI, MINI

Les mots clés disponibles sont SUM pour faire une somme,
AVG pour faire une moyenne,
MAX pour retrouver un maximum,
MIN pour retrouver un minimum.

6 - Combien de films ont une note inférieure à 4 ?

Ici, il faut relier les tables “film” et “note” grâce au mot clé JOIN,
en précisant à MySQL (grâce au mot clé ON) quelles sont les colonnes qui permettent de relier les deux tables
(ici, l’id de chaque note, et la “note_id” de chaque film).
La commande est donc

SELECT * FROM film JOIN note ON (film.note_id = note.id) WHERE note.note < 4;

7 - Qu’est-ce qu’une table de liaison ?

C’est une table qui permet de lier deux tables ayant une relation un à plusieurs entre elles.
C’est une table qui permet de relier deux tables ayant une relation plusieurs à plusieurs entre elles.

C’est une table qui regroupe toutes les vues présentes dans une BDD.

La table de liaison est une table qui permet de lier deux tables entre elles,
en stockant des objets avec l’id de chaque table,
afin d’en matérialiser les relations plusieurs à plusieurs
sous forme de relations un à plusieurs.

8 - Sélectionnez les bonnes associations de films avec leurs pays de sortie.

Skyfall en France, Angleterre et USA.
Titanic en France, USA et Allemagne.

La La Land aux USA uniquement.

Ici, nous sommes face à une relation plusieurs à plusieurs.
Il faut donc passer par la table de liaison “film_pays_de_sortie”,
qu’on JOIN à la fois à film et à pays_de_sortie.
La commande qui permet de vérifier la solution est donc :

SELECT * FROM film
JOIN film_pays_de_sortie ON (film.id = film_pays_de_sortie.film_id)
JOIN pays_de_sortie ON (pays_de_sortie.id = film_pays_de_sortie.pays_de_sortie_id);

Transformez la structure d’une base de données

Reprenons notre BDD Moviz du précédent quiz. Cette base doit contenir des films, or elle n’en contient actuellement aucun !
Si vous ne l’avez plus, voici le lien pour la télécharger : Moviz.sql
Souvenez-vous, j’ai modifié le schéma de la BDD entre la partie 2 et la partie 3 pour y ajouter la relation un à plusieurs entre “film” et “note”,
ainsi que la relation plusieurs à plusieurs entre “film” et “pays_de_sortie”.

Voici le schéma mis à jour de la table “film” :

Nom du champ	Type du champ	Description du champ
nom	VARCHAR(255)	le nom du film
date_de_sortie	DATE	la date de sortie du film
synopsis	TEXT	la description du scénario du film
langue	VARCHAR(255)	la langue principale du film
budget	INTEGER	le budget de production du film
note_id	INTEGER	l'id de l'objet note associé au film

Le schéma de la table “note” est disponible ici :

Nom du champ	Type du champ	Description du champ
note	INTEGER	la note sur 5

Le schéma de la table “pays_de_sortie” est disponible ici :

Nom du champ	Type du champ	Description du champ
nom	VARCHAR(255)	le nom du pays

1 - Quels sont les mots clés disponibles dans une commande de type ALTER TABLE ?

ADD, DROP, CHANGE, MODIFY

AJOUTER, SUPPRIMER, MODIFIER

ADD, DROP, CHANGE

ALTER TABLE sert à modifier la structure d’un objet, et de la table associée.
ADD permet d’ajouter un champ,
DROP de le supprimer,
CHANGE de le renommer,
MODIFY de modifier son type.

2 - Comment fait-on pour ajouter une relation un à plusieurs entre deux objets existants déjà en base ?

On rajoute l’id de l’objet “un” comme colonne dans l’objet “plusieurs” avec comme nom “objet_id” >

On utilise une commande SQL avec le mot clé JOIN.

On supprime et recrée les deux objets.

Pour effectuer une relation un à plusieurs, l’objet “un” de la relation doit être mentionné dans l’objet “plusieurs”.
La convention en SQL est de reprendre son id (sa clé primaire) comme clé étrangère dans l’objet plusieurs.

3 - Dans la BDD Moviz, quelle est la commande qui permet de rajouter le champ note_id à la table “film” ?

ALTER TABLE film ADD note_id;

ALTER TABLE film ADD note_id INTEGER NOT NULL;

ALTER TABLE film ADD note_id VARCHAR(100);

Pour ajouter un champ à un objet (et donc, à sa table), on utilise une commande de type ALTER TABLE avec le mot clé ADD.
Comme on souhaite ajouter un champ qui sera une clé étrangère, il est du même type qu’une clé primaire, soit un entier (INTEGER) non nul (NOT NULL).

4 -Quelle est la commande qui permet de supprimer le budget sur les films ?

ALTER TABLE film REMOVE budget;

ALTER TABLE film DROP budget;
ALTER TABLE film DELETE budget;

Pour supprimer un champ à un objet (et donc, à sa table), on utilise une commande de type ALTER TABLE avec le mot clé DROP suivi du nom du champ à supprimer.

5 - Quelle est la commande qui permet de modifier le type du champ “budget” d’entier vers décimal ?

ALTER TABLE film MODIFY budget FLOAT;
ALTER TABLE film MODIFY budget VARCHAR(100);

ALTER TABLE film CHANGE budget FLOAT;

Pour modifier le type d’un champ, on utilise une commande de type ALTER TABLE avec le mot clé MODIFY suivi du nom du champ et de son nouveau type (ici, décimal, soit FLOAT).

6 - Quelle est la commande qui permet de renommer le budget sur les films en “cout_de_production” ?

ALTER TABLE film CHANGE budget cout_de_production;
ALTER TABLE film CHANGE budget cout_de_production FLOAT;

ALTER TABLE film MODIFY budget cout_de_production;

Pour renommer un champ, on utilise une commande de type ALTER TABLE avec le mot clé CHANGE suivi du nom du champ et de son nouveau nom.
MySQL oblige, on doit aussi repréciser son type (ici FLOAT, car on l’a modifié à la question précédente).

7 - Comment crée-t-on une relation plusieurs à plusieurs entre deux objets existants déjà en base ?

On supprime et recrée les deux tables.
On crée une table de liaison qui va stocker les relations entre ces deux objets.

On joint les tables avec un JOIN.

Rappelez-vous, la table de liaison regroupe les relations plusieurs à plusieurs comme s’il s’agissait de relations un à plusieurs.
Pour ce faire, elle contient 2 colonnes, chacune étant une clé étrangère vers chaque objet de la relation plusieurs à plusieurs.

8 - Quelle est la commande qui permet de créer la table de liaison entre “film” et “pays_de_sortie” ?

CREATE TABLE film_pays_de_sortie (

film_id INT NOT NULL,

pays_de_sortie_id INT NOT NULL,

FOREIGN KEY (film_id) REFERENCES film (id) ON DELETE RESTRICT ON UPDATE CASCADE,

FOREIGN KEY (pays_de_sortie_id) REFERENCES pays_de_sortie (id) ON DELETE RESTRICT ON UPDATE CASCADE,

PRIMARY KEY (film_id, pays_de_sortie_id)

);
CREATE TABLE film_pays_de_sortie;

CREATE TABLE film_pays_de_sortie (

film_id INT NOT NULL,

pays_de_sortie_id INT NOT NULL

)

La table de liaison doit avoir pour nom “nom_objet_1_nom_objet_2”, soit ici “film_pays_de_sortie” et stocker deux champs,
qui sont les clés étrangères de chaque autre table, soit ici “film_id” (clé étrangère vers l’id de “film”) et “pays_de_sortie_id” (clé étrangère vers l’id de “pays_de_sortie”).