II- Cas pratique (50 min)

Pour la suite de cette partie, nous allons créer une base de données et effectuer des requêtes sur
cette dernière. Pour cela nous allons utiliser le logiciel "DB Browser for SQLite".

SQLite est un SGBD relationnel très répandu, il est open source et n’utilise pas de serveur. Il
stocke la base dans un unique fichier et est très rapide pour des bases "modestes" (< 1 million
d’enregistrements). Il existe d'autres SGBD relationnelles libres et répendus comme MySQL ou
PostgreSQL. Dans tous les cas, le langage de requête utilisé est le SQL donc ce qui est valable
avec SQLite restera valable (à quelques détails près) avec d'autres SGBDR.

SQLite est implémenté en Python (import sqlite3), sous windows, macOs et Linux avec DB
Browser et même en ligne (et même sur les OS des téléphones IOS et Android).

II-1. Construction de la base de données.

Lancer le logiciel "DB Browse for SQlite" et cliquer sur Nouvelle base de données et donner un
nom de votre choix (par exemple "cinéma") :
Sauvegarder, puis cliquer sur Annuler : la base de données est alors créée.

Pour construire la base de données, on peut alors soit créer les tables en utilisant l'interface du
logiciel (il ne fallait pas cliquer sur Annuler dans ce cas) soit "à la main" en utilisant directement
SQL. Le but étant de découvrir le langage SQL, nous allons créer les relations "à la main"
directement avec SQL.

Les mots-clés de SQL sont usuellement écrits en majuscule mais ce n’est pas obligatoire et pour
les différencier des noms des tables et des attributs, il est conseillé d'écrire ces derniers en
minuscule. Les requêtes se terminent par un point-virgule.

Cliquer sur "Exécuter le SQL".

En SQL, on utilise le mot table plutôt que le mot relation .

Pour créer une table, on utilise la syntaxe : CREATE TABLE nom_table (nom_col1 TYPE1,
nom_col2 TYPE2, ...)

Copier-coller la requête suivante dans la fenêtre et exécuter là (bouton en forme de triangle) :

 CREATE TABLE Artistes
(id INT NOT NULL,
nom TEXT NOT NULL,
prenom TEXT NOT NULL,
annee_naissance INT,
PRIMARY KEY(id));

[Link]

La requête s'est bien passée (il suffit de le lire, c'est écrit), on vient donc de créer notre première
table, la table "Artistes".

La syntaxe est simple à comprendre, on a créé la table Artistes avec les attributs id, nom,
prenom, annee_naissance.

On a précisé que la clé primaire est l'attribut id donc on ne pourra pas attribuer 2 fois la même
valeur à cet attribut, si on essaye de le faire, le logiciel nous avertira.

Il n'était pas obligatoire de préciser "NOT NULL", le fait de le préciser oblige à avoir une valeur
qui correspond à cet attribut.

Remarque : On peut, bien-sûr, aussi supprimer une table qui a été créée, pour cela, on utilise la
syntaxe : DROP TABLE nom_table

Maintenant que la table a été créée, il faut ajouter des données à la table Artistes.

II-2. Ajout des données.

On utilise la syntaxe :

INSERT INTO NomTable (attribut1, attribut2,...) VALUES (valeurChamp1, valeurChamp2, ...

valeurChampn);

On peut ajouter plusieurs lignes à une table en une seule requête en séparant chaque p-uplet
par une virgule.

Exemple : Effacer la requête précédente (sinon elle sera de nouveau exécutée et renvera une
erreur puisque la table a déjà été créée) puis copier-coller la requête suivante dans la fenêtre
"Exécuter le SQL" :

INSERT INTO Artistes (id, nom, prenom, annee_naissance)

VALUES (1,'Hitchcock','Alfred', 1899),
(2,'Scott','Ridley', 1937),
(3,'Weaver','Sigourney', 1949),
(4,'Cameron','James', 1954),
(5,'Woo','John', 1946),
(6,'Travolta','John', 1954),
(7,'Cage','Nicolas', 1964),
(8,'Burton','Tim', 1958),
(9,'Depp','Johnny', 1964),
(10,'Stewart','James', 1908),
(11,'Spacey','Kevin', 1959),
(12,'Eastwood','Clint', 1930),
(13,'Freeman','Morgan', 1937),
(14,'Crowe','Russell', 1964),
(15,'Ford','Harrison', 1942),
(16,'Willis','Bruce', 1955),
(17,'Harlin','Renny', 1959),
(18,'Pialat','Maurice', 1925),
(19,'Fincher','David', 1962),
(20,'Pitt','Brad', 1963),
(21,'Connery','Sean', 1930),
(22,'Tarantino','Quentin', 1963),
(23,'Jackson','Samuel L.', 1948),
(24,'Arquette','Rosanna', 1959),
(25,'Thurman','Uma', 1970),
(26,'Farrelly','Bobby', 1958),
(27,'Diaz','Cameron', 1972),
(28,'Schwartzenegger','Arnold', 1947),
(29,'Spielberg','Steven', 1946),
(30,'Hopkins','Anthony', 1937),
(31,'Foster','Jodie', 1962),
(32,'Kilmer','Val', 1959),
(33,'Pfeiffer','Michelle', 1957),
(34,'Bullock','Sandra', 1964),
(35,'Goldblum','Jeff', 1952),
(36,'Reno','Jean', 1948),
(37,'Wachowski','Andy', 1967),
(38,'Reeves','Keanu', 1964),
(39,'De Palma','Brian', 1940),
(40,'Cruise','Tom', 1962),
(41,'Bart','Emmanuelle', 1965),
(42,'De Funes','Louis', 1914),
(43,'Galabru','Michel', 1922),
(44,'Balasko','Josiane', 1950),
(45,'Lavanant','Dominique', 1944),
(46,'Lanvin','Gerard', 1950),
(47,'Villeret','Jacques', 1951),
(48,'Hoffman','Dustin', 1937),
(49,'Leconte','Patrice', 1947),
(50,'Blanc','Michel', 1952),
(51,'Clavier','Christian', 1952),
(52,'Lhermite','Thierry', 1952),
(53,'Perkins','Anthony', 1932),
(54,'Fisher','Carrie', 1956),
(55,'Ricci','Christina', 1980),
(56,'Walken','Christopher', 1943),
(57,'Kubrick','Stanley', 1928),
(58,'Kidman','Nicole', 1967),
(59,'Nicholson','Jack', 1937),
(60,'Kelly','Grace', 1929),
(61,'DiCaprio','Leonardo', 1974),
(62,'Besson','Luc', 1959),
(63,'Jovovich','Milla', 1975),
(64,'Dunaway','Fane', 1941),
(65,'Malkovitch','John', 1953),
(66,'Holm','Ian', 1931),
(67,'Portman','Natalie', 1981),
(68,'Parillaud','Anne', 1960),
(69,'Barr','Jean-Marc', 1960),
(70,'Ferrara','Abel', 1951),
(71,'Caruso','David', 1956),
(72,'von Trier','Lars', 1956),
(73,'Deneuve','Catherine', 1943),
(74,'Kassowitz','Matthieu', 1967),
(75,'Cassel','Vincent', 1966),
(76,'Theron','Charlize', 1975),
(77,'Chabrol','Claude', 1930),
(78,'Huppert','Isabelle', 1953),
(79,'Costner','Kevin', 1955),
(80,'Hanks','Tom', 1956),
(81,'Damon','Matt', 1970),
(82,'Douglas','Kirk', 1976),
(83,'Bergman','Ingrid', 1915),
(84,'De Niro','Robert', 1943),
(85,'Fonda','Bridget', 1964),
(86,'Keaton','Michael', 1951),
(87,'Pacino','Al', 1940),
(88,'Crowe','Russel', 1964),
(89,'Coppola','Francis Ford', 1939),
(90,'Brando','Marlon', 1924),
(91,'Keaton','Diane', 1946),
(92,'Garcia','Andy', 1956),
(93,'Moss','Carrie-Anne', 1967),
(94,'Jackson','Samuel', 1948),
(95,'Liu','Lucy', 1968),
(96,'Simpson','Homer', 1987),
(97,'Carradine','David', 1936);

Remarque : il n'est pas nécessaire de préciser les attributs si on ajoute des lignes en indiquant
les informations pour chaque colonne existante en respectant l’ordre.

Comme avec la requête précédente, tout se passe bien et les données sont écrites dans la table
"Artistes".

On vérifie que les données sont bien présentes avec l'onglet "Parcourir les données" :

Si on ne souhaite pas préciser de valeur pour un champ, il faut passer le mot-clé dédié NULL.
Remarque : Attention une éventuelle violation d'une contrainte telle que l'unicité d'une clé est
signalée (avec un bandeau rouge !) et l'insertion est refusée.

On peut modifier et supprimer une ligne donnée d'une table :

• Pour supprimer, la syntaxe est : DELETE FROM NomTable WHERE condition

La commande WHERE dans une requête SQL permet d’extraire les lignes d’une base de
données qui respectent une condition. Cela permet d’obtenir uniquement les informations
désirées.

Par exemple pour supprimer la ligne ayant l'id 96 :

DELETE FROM Artistes

WHERE ID = 96;

• Pour modifier, la syntaxe est : UPDATE nomTable SET champx = valx, champy = valy, ...
WHERE condition Par exemple pour modifier la date de naissance de Kirk Douglas qui
est fausse :
UPDATE Artistes
SET annee_naissance = 1916
WHERE id = 82;

On peut vérifier que la date de naissance de Kirk Douglas a bien été corrigée...

II-3. Lire les données.

L’utilisation la plus courante de SQL consiste à lire des données issues de la base de données.
Cela s’effectue grâce à la commande SELECT, qui retourne des enregistrements dans un tableau
de résultat. Cette commande peut sélectionner une ou plusieurs colonnes d’une table.

La syntaxe "basique" est : SELECT nom_du_champ FROM nom_du_tableau

La syntaxe est (encore) facile à comprendre, la requête sélectionne (SELECT) le champ

“nom_du_champ” provenant (FROM) du tableau appelé “nom_du_tableau”.

Exemple : Copier-Coller la requête suivante :

SELECT id, nom, prenom

FROM Artistes;
On constate que la requête SQL a permis d'afficher tous les artistes et leur identifiant (mais pas
leur date de naissance). Il est évidemment possible d'afficher tous les attributs ou au contraire
un seul attribut.

Pour obtenir tous les attributs, au lieu de tous les saisir dans la requête, on peut saisir :

SELECT *
FROM Artistes;

Copier-coller la requête suivante :

SELECT prenom
FROM Artistes;

La requête SQL a permis d'afficher tous les prénoms des artistes et on retrouve plusieurs les
mêmes prénoms (John, James...). On peut utiliser la clause DISTINCT si on veut éviter les
doublons :
 SELECT DISTINCT prenom
FROM Artistes;

La commande ORDER BY permet de trier les lignes dans un résultat d’une requête SQL. Il est
possible de trier les données sur une ou plusieurs colonnes, par ordre croissant ou décroissant.

Copier-coller la requête suivante :

SELECT prenom
FROM Artistes
ORDER BY prenom;

On remarque que par défaut les résultats sont classés par ordre croissant mais il est possible
d’inverser l’ordre en utilisant le suffixe DESC après le nom de la colonne :

SELECT prenom
FROM Artistes
ORDER BY prenom DESC;

Tout comme ce qu'on a déjà utilisé avec les commandes UPDATE et DELETE, il est possible
d'utiliser WHERE afin d'imposer des conditions permettant de sélectionner uniquement
certaines lignes.

Exemple : On affiche le nom et prénom des artistes nés en 1970 :

SELECT nom, prenom

FROM Artistes
WHERE annee_naissance = 1970;

La fonction COUNT() permet de compter le nombre d’enregistrements correspondant à un

critère spécifié dans une table. Connaître ce nombre est très pratique dans de nombreux cas, par
exemple pour savoir combien d’utilisateurs sont présents dans une table ou pour connaître le
nombre de commentaires sur un article.

Copier-coller la requête suivante :

SELECT COUNT(*)
FROM Artistes;

Remarques : Il est aussi possible de connaitre le nombre d’enregistrements sur une colonne en
particulier, les enregistrements possédant la valeur nul ne sont pas comptabilisés.

SELECT COUNT(nom_colonne) FROM table;

À vous de faire :
1. Écrire une requête SQL qui permet d'obtenir le nom des artistes dont le prénom est
'David'.
2. Écrire une requête SQL qui permet d'ajouter le nouvel artiste : 'Christopher Nolan - 1972'
dans la table Artiste.
 3. Écrire une requête SQL qui permet d'ajouter une nouvelle artiste : 'Camille Honette -
1987' dans la table Artiste.
4. Après vérification, l'année de naissance de Christopher Nolan est 1970. Écrire une
requête SQL qui permet de corriger cette erreur.
5. Écrire une requête SQL qui permet d'obtenir le nom des artistes dont le prénom est
'John' et dont l'id est strictement supérieur à 30.
6. Écrire une requête SQL qui permet d'obtenir le nom et prénom des artistes nés à partir
de 1970 ou dont le prénom est 'James'.
7. Écrire une requête SQL qui permet d'obtenir le nombre d'artistes nés avant 1920.
8. Écrire une requête qui supprime l'artiste Camille Honette.

II-4. Les jointures.

Nous allons maintenant utiliser une table "Films" avec les attributs : id (INT), titre (TEXT), annee
(INT) et id_Artistes (INT).

Pour cela, vous devez saisir la requête : ... (à vous de trouver)

Une fois la table créée, ajouter les données en copiant-collant la requête ci-dessous :

INSERT INTO Films

VALUES
(1, 'Vertigo', 1958, 1),
(2, 'Alien', 1979, 2),
(3, 'Titanic', 1997, 4),
(4, 'Sleepy Hollow', 1999,8),
(5, 'Impitoyable', 1992, 12),
(6, 'Gladiator', 2000, 2),
(7, 'Blade Runner', 1982, 2),
(8, 'Pulp fiction', 1994, 22),
(9, 'Terminator', 1984, 2),
(10, 'Les dents de la mer', 1975, 29),
(11, 'Le monde perdu', 1997, 29),
(12, 'La mort aux trousses', 1959, 1),
(13, 'Nikita', 1990, 62),
(14, 'Le grand bleu', 1988,62),
(15, 'Le parrain', 1972, 39),
(16, 'Le parrain II', 1974, 39),
(17, 'Kill Bill', 2003,22),
(18, 'Million Dollar Baby', 2005,12);

Nous avons maitenant 2 tables dans la base de données et nous allons pouvoir associer ces 2
tables dans une même requête grâce aux jointures.

Les jointures permettent d’associer plusieurs tables dans une même requête. Cela permet
d’exploiter la puissance des bases de données relationnelles pour obtenir des résultats qui
combinent les données de plusieurs tables de manière efficace.

En général, les jointures consistent à associer des lignes de 2 tables en associant l’égalité des
valeurs d’une colonne d’une première table par rapport à la valeur d’une colonne d’une seconde
table. Elles permettent d'établir un lien entre 2 tables. Qui dit lien entre 2 tables dit souvent clef
étrangère et clef primaire.

Par exemple, si un site web possède une table pour les articles (titre, contenu, date de
publication...) et une autre pour les rédacteurs (nom, date d’inscription, date de naissance...),
avec une jointure il est possible d’effectuer une seule recherche pour afficher un article et le nom
du rédacteur. Cela évite d’avoir à stocker le nom du rédacteur dans la table “article” et donc la
redondance de données.

Il y a plusieurs méthodes pour associer 2 tables ensemble. Nous allons utiliser : JOIN. C’est l’une
des jointures les plus communes. C'est une jointure interne pour retourner les enregistrements
quand la condition est vraie dans les 2 tables.

Copier-Coller la requête ci-dessous

SELECT *
FROM Films
JOIN Artistes
ON Films.id_Artistes = [Link];

"FROM Films JOIN Artistes" permet de créer une jointure entre les tables Films et Artistes
(comme si les 2 tables étaient "rassemblées").

"ON Films.id_Artistes = [Link]" signifie qu'une ligne A de la table Films sera fusionnée avec
la ligne B de la table Artistes à condition que l'attribut id de la ligne A soit égal à l'attribut
id_Artistes de la ligne B.

Par exemple, la ligne 1 (id=1) de la table Films (ligne A) sera fusionnée avec la ligne 1 (id=1) de la
table Artistes (ligne B) car l'attribut id_Artistes de la ligne A est égal à 1 et l'attribut id de la ligne
B est aussi égal à 1.

ATTENTION : Si un même nom d'attribut est présent dans les 2 tables (par exemple ici l'attribut
id), il est nécessaire d'ajouter le nom de la table devant afin de pouvoir les distinguer ([Link]
et [Link]).

À vous de faire :
• Écrire une requête SQL qui permet d'ajouter un nouveau film : '19, Psychose, 1960, 1'.
• Dans le cas d'une jointure, il est tout à fait possible de sélectionner certains attributs et
pas d'autres. Écrire une requête SQL qui permet d'obtenir pour tous les films, leur titre,
leur année de sortie et les noms et prénoms de leur réalisateur.
• Il est de nouveau possible d'utiliser la clause WHERE dans le cas d'une jointure. Écrire
une requête SQL qui permet d'obtenir pour tous les films sortis depuis 1980, leur titre,
leur année de sortie et les noms et prénoms de leur réalisateur.

Requetes d'analyse
1. Range des films par annee de sortie
SELECT titre, annee, RANK() OVER (ORDER BY annee) as rang
FROM Films;
 1. classement des films par annee de sortie et realisateur
SELECT titre, annee, nom, prenom, RANK() OVER (PARTITION BY annee
ORDER BY id_Artistes) as rang
FROM Films JOIN Artistes ON [Link] = [Link];

1. clacul de la moyenne des annee de sortie des films par reaisateur

SELECT nom, prenom, AVG(annee) OVER (PARTITION BY od_Artiste) as
moyenne_annee
FROM Films JOIN Artistes ON Films.id_Artiste = [Link];

1. total cumule des annee de sortie des films par odre decroissant
SELECT titre, annee, SUM(annee) OVER (ORDER BY annee desc ) as
total_cumulatif
FROM Films;

1. Nombre cumulatif de films par realisateur par annee

SELECT nom, prenom, annee, Count(*) OVER (PARTITION BY id_Artiste
ORDER BY annee) as nb_cumulatif
FROM Films JOIN Artistes ON Films.id_Artiste = [Link];