SQL - Créer et insérer

Contenu

SQL - Créer et insérer#

Les bases de données relationnelles peuvent être créées et manipulées grâce au langage SQL (Structured Query Language). Il ne s'agit pas d'un langage de programmation, mais d'un langage de requête permettant d'influer directement sur la base de données en créant des tables, insérant des données, et en y recherchant des informations.

Pour exemplifier la création de tables et l'insertion de données, la base de données d'une bibliothèque basée sur le schéma relationnel ci-dessous sera créée.

Création de tables#

Le langage SQL permet de créer des tables en spécifiant leur nom et le nom des différentes colonnes.

Pour créer une table, il faut utiliser l'instruction CREATE TABLE suivi du nom de la table et d'une paire de parenthèses. Entre ces parenthèses, nous indiquons la liste des attributs, ainsi que leur type de données.

Les types de données peuvent être les suivants :

INTEGER pour un nombre entier
REAL pour un nombre réel
TEXT pour du texte
DATE pour une date au format AAAA-MM-JJ

Finalement, on précise la clef primaire avec PRIMARY KEY suivi de parenthèses entre lesquelles on précise l'attribut devant faire office de clef primaire.

CREATE TABLE Livre (
    titre TEXT,
    auteur TEXT,
    date_pub DATE,
    numero_isbn INTEGER,
    prix REAL,
    PRIMARY KEY(numero_isbn)
);

Identifiants artificiels numériques#

Lorsque la clef primaire d'une table est un identifiant artificiel créé uniquement pour ce rôle, on peut utiliser le mot-clef AUTOINCREMENT dans la définition de la PRIMARY KEY afin que SQL se charge lui-même d'attribuer ce numéro unique aux futures lignes de la table. La valeur de cet identifiant doit obligatoirement être INTEGER

CREATE TABLE Utilisateur (
    nom TEXT,
    prenom TEXT,
    role TEXT,
    id_utilisateur INTEGER,
    PRIMARY KEY(id_utilisateur AUTOINCREMENT)
);

Clefs étrangères#

Lors de la création d'une table contenant des clefs étrangères, on doit également les spécifier avec FOREIGN KEY ... REFERENCES .... Après le FOREIGN KEY, on spécifie entre parenthèses quelle colonne est la clef étrangère. Puis, après le REFERENCES, on donne le nom de la table et de sa colonne référencée. Dans l'exemple ci-dessous, utilisateur est une clef étrangère référençant la colonne id_utilisateur de la table Utilisateur. De plus, livre est une clef étrangère référençant la colonne numero_isbn de la table Livre.

CREATE TABLE Emprunt (
    livre INTEGER,
    utilisateur INTEGER,
    date_emprunt DATE,
    id_emprunt INTEGER,
    PRIMARY KEY(id_emprunt AUTOINCREMENT),
    FOREIGN KEY(utilisateur) REFERENCES Utilisateur(id_utilisateur),
    FOREIGN KEY(livre) REFERENCES Livre(numero_isbn)
);

Insertion de données#

Pour insérer une ligne dans une table, il faut utiliser l'instruction INSERT INTO ... VALUES .... Après INSERT INTO, il faut préciser le nom de la table dans laquelle nous souhaitons ajoutons une ligne, ainsi que les colonnes à remplir. Nous ajoutons ensuite le mot-clef VALUES et une paire de parenthèses entre lesquelles nous indiquons les valeurs à insérer dans chaque colonne. L'ordre des valeurs doit être le même que celui établi plus tôt dans la requête.

Les valeurs de type TEXT et DATE doivent être entre guillemets simples, et la séparation entre les unités et les décimales d'une valeur REAL se fait avec un point.

INSERT INTO Livre(titre, auteur, date_pub, numero_isbn, prix)
VALUES ('La Vérité sur l`Affaire Harry Québert', 'Joël Dicker', '2012-03-01', 9782877068161, 23.95);

Lorsqu'on insère des données dans une table contenant une clef primaire qui a été définie avec AUTOINCREMENT, on peut omettre sa valeur et SQL se charge de l'attribuer automatiquement.

INSERT INTO Utilisateur(nom, prenom, role) VALUES ('Jan', 'Maxime', 'enseignant');

INSERT INTO Utilisateur(nom, prenom, role) VALUES ('Queloz', 'Aurélien', 'élève');

Exercices#

Exercice 8#

Quel type de données faut-il utiliser pour chacune de ces colonnes ?

Le prix d'un article
Le nom d'une ville
Le nombre d'habitants d'une ville
La date de sortie d'un film
Le numéro de téléphone d'un client
La note obtenue à une évaluation
Le numéro de maillot d'un joueur
L'adresse e-mail d'un utilisateur

Solution

Le numéro de téléphone (question 5) est le piège classique : bien qu'il ne soit composé que de chiffres, ce n'est pas un nombre. On ne fait jamais de calcul avec un numéro de téléphone, et surtout un INTEGER supprimerait le 0 du début (0791234567 deviendrait 791234567). La même logique s'applique aux numéros AVS et aux numéros IBAN.

Retenez la question à se poser : est-ce que je pourrais avoir envie de faire un calcul avec cette valeur ? Si la réponse est non, c'est du TEXT.

Exercice 9#

Pour chacune de ces clefs primaires, déterminez si le mot-clef AUTOINCREMENT est nécessaire.

numero_isbn dans une table Livre
id_commentaire dans une table Commentaire
email dans une table Utilisateur
id_emprunt dans une table Emprunt
plaque dans une table Voiture
id_video dans une table Video

Solution

La règle est toujours la même : AUTOINCREMENT sert uniquement lorsque la clef primaire est un identifiant artificiel, c'est-à-dire un numéro qui n'existe nulle part ailleurs et que l'on a créé uniquement pour identifier les lignes. C'est le cas des questions 2, 4 et 6.

À l'inverse, un numéro ISBN, une adresse e-mail et une plaque d'immatriculation existent déjà dans le monde réel : c'est nous qui les fournissons au moment de l'insertion, SQL n'a rien à inventer. De plus, AUTOINCREMENT n'est possible que sur une colonne de type INTEGER, ce qui exclut d'office l'e-mail et la plaque.

Exercice 10#

Chacune des requêtes ci-dessous comporte une erreur. Parfois, l'erreur fait directement buguer la requête avec un message d'erreur rouge. D'autres fois, la requête s'exécute sans problème mais la table créée est mal conçue.

Corrigez chacune de ces requêtes. Lisez bien les messages d'erreur, ils peuvent vous aider.

CREATE TABLE Ville (
    nom TEXT,
    canton TEXT,
    population INTEGER,
);

CREATE TABLE Pays (
    nom TEXT,
    capitale TEXT,
    population INTEGER,
    PRIMARY KEY(nom AUTOINCREMENT)
);

CREATE TABLE Jeu (
    titre TEXT,
    studio TEXT,
    prix REAL,
    PRIMARY KEY(id_jeu)
);

CREATE TABLE Film (
    titre TEXT,
    annee,
    duree INTEGER,
    id_film INTEGER,
    PRIMARY KEY(id_film AUTOINCREMENT)
);

CREATE TABLE Eleve (
    nom TEXT,
    prenom TEXT,
    classe TEXT
);

Solution

Il y a une virgule en trop après population INTEGER. La dernière ligne avant la parenthèse fermante ne doit pas être suivie d'une virgule. Au passage, cette table n'a pas de clef primaire (voir la question 5).
```
CREATE TABLE Ville (
    nom TEXT,
    canton TEXT,
    population INTEGER,
    id_ville INTEGER,
    PRIMARY KEY(id_ville AUTOINCREMENT)
);
```
AUTOINCREMENT n'est possible que sur une clef primaire de type INTEGER, or nom est du TEXT. Comme le nom d'un pays est unique, il suffit de retirer AUTOINCREMENT.
```
CREATE TABLE Pays (
    nom TEXT,
    capitale TEXT,
    population INTEGER,
    PRIMARY KEY(nom)
);
```

La clef primaire id_jeu est référencée alors que cette colonne n'a jamais été créée. Il faut la déclarer dans la liste des attributs.

CREATE TABLE Jeu (
    titre TEXT,
    studio TEXT,
    prix REAL,
    id_jeu INTEGER,
    PRIMARY KEY(id_jeu AUTOINCREMENT)
);

Le type de données de annee a été oublié. Cette requête ne produit aucun message d'erreur, mais la colonne acceptera alors n'importe quoi.

CREATE TABLE Film (
    titre TEXT,
    annee INTEGER,
    duree INTEGER,
    id_film INTEGER,
    PRIMARY KEY(id_film AUTOINCREMENT)
);

Cette requête fonctionne, mais la table n'a aucune clef primaire. Aucune des trois colonnes n'étant unique, il faut en créer une.

CREATE TABLE Eleve (
    nom TEXT,
    prenom TEXT,
    classe TEXT,
    id_eleve INTEGER,
    PRIMARY KEY(id_eleve AUTOINCREMENT)
);

Exercice 11#

Le schéma relationnel ci-dessous ne contient qu'une seule table et représente une base de données d'évaluations.

Partie A#

Ecrivez la requête SQL CREATE TABLE permettant de créer la table Evaluation. Veillez à bien préciser les types de données, la clef primaire et l'éventuel AUTOINCREMENT.

Solution

CREATE TABLE Evaluation(
    titre TEXT,
    branche TEXT,
    note REAL,
    date DATE,
    id_evaluation INTEGER,
    PRIMARY KEY(id_evaluation AUTOINCREMENT)
)

Partie B#

Ajoutez 2 évaluations dans la table créée dans la partie A :

Une évaluation de math nommée "Géométrie" faite le 2025-12-11 à laquelle vous avez fait 4.75
Une évaluation d'informatique nommée "Base de données" faite le 2025-10-30 à laquelle vous avez fait 6

Solution

INSERT INTO Evaluation(branche, titre, note, date) VALUES('Math', 'Géométrie', 4.75, '2025-12-11');

INSERT INTO Evaluation(branche, titre, note, date) VALUES('Informatique', 'Bases de données', 6, '2025-10-30')

Exercice 12#

Le schéma relationnel ci-dessous est celui d'une plateforme de streaming musical.

Schéma relationnel de la plateforme de streaming musical

La table Artiste a déjà été créée et remplie pour vous. Exécutez le bloc ci-dessous pour voir son contenu.

CREATE TABLE Artiste (
    nom TEXT,
    pays TEXT,
    id_artiste INTEGER,
    PRIMARY KEY(id_artiste AUTOINCREMENT)
);

INSERT INTO Artiste(nom, pays) VALUES ('Stromae', 'Belgique');
INSERT INTO Artiste(nom, pays) VALUES ('Angèle', 'Belgique');
INSERT INTO Artiste(nom, pays) VALUES ('Orelsan', 'France');

Partie A#

La création de la table Album est presque terminée : il ne reste que les deux dernières lignes à écrire. Remplacez les ... par le code correct.

CREATE TABLE Album (
    id_album INTEGER,
    titre TEXT,
    annee INTEGER,
    nb_pistes INTEGER,
    artiste INTEGER,
    PRIMARY KEY(...),
    FOREIGN KEY(...) REFERENCES ...
);

Solution

CREATE TABLE Album (
    id_album INTEGER,
    titre TEXT,
    annee INTEGER,
    nb_pistes INTEGER,
    artiste INTEGER,
    PRIMARY KEY(id_album AUTOINCREMENT),
    FOREIGN KEY(artiste) REFERENCES Artiste(id_artiste)
);

La clef étrangère artiste ne référence pas la table Artiste toute entière, mais bien sa clef primaire : Artiste(id_artiste).

Partie B#

Si votre table est correctement créée, le bloc ci-dessous doit ajouter trois albums.

INSERT INTO Album(titre, annee, nb_pistes, artiste) VALUES ('Racine carrée', 2013, 15, 1);
INSERT INTO Album(titre, annee, nb_pistes, artiste) VALUES ('Nonante-Cinq', 2021, 13, 2);
INSERT INTO Album(titre, annee, nb_pistes, artiste) VALUES ('Civilisation', 2021, 15, 3);

Ajoutez maintenant vous-même l'album Multitude de Stromae, sorti en 2022 et contenant 12 pistes.

Solution

INSERT INTO Album(titre, annee, nb_pistes, artiste) VALUES ('Multitude', 2022, 12, 1);

Comme id_album a été déclaré avec AUTOINCREMENT, on ne l'écrit pas : SQL lui donne automatiquement la valeur 4. En revanche, la clef étrangère artiste doit bien être renseignée, et avec le numéro de Stromae (1), pas avec son nom.

Exercice 13#

On reprend la même plateforme de streaming, cette fois entièrement créée et remplie avec les trois artistes de l'exercice précédent. Pour chacune des requêtes ci-dessous, prédisez d'abord si elle va fonctionner, puis exécutez-la pour vérifier.

INSERT INTO Artiste(nom, pays) VALUES ('Damso', 'Belgique');

INSERT INTO Artiste(nom, pays) VALUES (Damso, Belgique);

INSERT INTO Album(titre, annee, nb_pistes, artiste)
VALUES ('Multitude', 2022, 12, 99);

INSERT INTO Album(titre, artiste) VALUES ('Racine carrée', 1);

INSERT INTO Artiste(nom, pays, id_artiste) VALUES ('Zaho de Sagazan', 'France');

INSERT INTO Album VALUES ('Nonante-Cinq', 2021, 13, 2);

Solution

Fonctionne. Les deux valeurs TEXT sont bien entre guillemets simples, et id_artiste est omis car il est en AUTOINCREMENT.
Ne fonctionne pas. Les guillemets simples manquent. SQL cherche alors une colonne appelée Damso et affiche no such column: Damso.
Ne fonctionne pas. La clef étrangère artiste vaut 99, or aucun artiste ne porte le numéro 99. C'est exactement le rôle de la clef étrangère que d'interdire cela : FOREIGN KEY constraint failed.
Fonctionne. Rien n'oblige à remplir toutes les colonnes : annee et nb_pistes resteront simplement vides.
Ne fonctionne pas. Trois colonnes sont annoncées mais seulement deux valeurs sont fournies : 2 values for 3 columns.
Ne fonctionne pas. Quand on n'écrit pas la liste des colonnes après le nom de la table, il faut donner une valeur pour toutes les colonnes, id_album compris. La table en a 5, on n'en donne que 4.

Exercice 14#

Le schéma relationnel ci-dessous décrit une base de données d'équipes de foot et leurs joueur.euse.s.

Partie A#

Commencez par écrire, ci-dessous, la requête permettant de créer la table Equipe.

Si votre code SQL est correct, le code ci-dessous devrait permettre de créer et enregistrer 3 nouvelles équipes.

INSERT INTO Equipe(nom, entraineur, budget)
VALUES('PSG', 'Luis Enrique', 850000000);

INSERT INTO Equipe(nom, entraineur, budget)
VALUES('FC Gottéron', 'Jean-Marc Genoud', 2500);

INSERT INTO Equipe(nom, entraineur, budget)
VALUES('Young Boys', 'Giorgio Contini', 77900000);

Solution

CREATE TABLE Equipe(
    nom TEXT,
    entraineur TEXT,
    budget REAL,
    PRIMARY KEY(nom)
)

Partie B#

Créez maintenant la table Joueur. N'oubliez pas de référencer la clef étrangère avec FOREIGN KEY ... REFERENCES .... (Ne mettez pas d'accent sur le e de l'attribut equipe)

Avant de l'exécuter, prédisez le résultat de la requête INSERT INTO ci-dessous.

Cette requête va-t-elle fonctionner ?

Si votre code est correct, cette requête ne doit PAS fonctionner. Pourquoi est-ce le cas ? Si cette requête ajoute bel et bien un 1er joueur à cette table, retravaillez le référencement de la clef étrangère dans la création de table.

INSERT INTO Joueur(prénom, nom, numéro_maillot, equipe)
VALUES('Kylian', 'Mbappé', 10, 'Real Madrid')

Solution

CREATE TABLE Joueur(
    prénom TEXT,
    nom TEXT, 
    numéro_maillot INTEGER, 
    equipe TEXT, 
    id_joueur INTEGER, 
    primary KEY (id_joueur AUTOINCREMENT),
    FOREIGN KEY(equipe) references Equipe(nom)
)

Le INSERT INTO ne fonctionne pas car la clef étrangère equipe qui devrait ici prendre la valeur Real Madrid ferait référence à une valeur qui n'existe pas dans la colonne nom de la table Equipe.

Partie C#

Ajoutez maintenant 3 nouveaux joueurs dans cette base de données.

Aurélien Queloz (n° 12) est dans l'équipe entrainée par Jean-Marc Genoud
Isaac Genoud (n° 7) fait partie de la même équipe
Maxime Dupasquier (n° 3) est quant à lui dans l'équipe de Giorgi Contini.

Grâce au AUTOINCREMENT, ces joueurs devraient avoir automatiquement les id_joueur 1, 2, 3.

Solution

INSERT INTO joueur(nom, prénom, numéro_maillot, equipe)
VALUES('Queloz', 'Aurélien', 12, 'FC Gottéron');

INSERT INTO joueur(nom, prénom, numéro_maillot, equipe)
VALUES('Genoud', 'Isaac', 7, 'FC Gottéron');

INSERT INTO joueur(nom, prénom, numéro_maillot, equipe)
VALUES('Dupasquier', 'Maxime', 3, 'Young Boys');