TD1 - Correction SQL

Exercice 1 - Type de données

Définir le type de données adéquat est une étape cruciale pour optimiser la mémoire et garantir l'intégrité de la base.

Question a - Nom de jour de la semaine

Le type approprié est CHAR(8) ou VARCHAR(8). En français, le nom de jour le plus long est "dimanche" (ou "mercredi"), qui compte exactement 8 caractères. Un type CHAR(8) allouera toujours 8 caractères, tandis qu'un VARCHAR(8) optimisera l'espace si le mot est plus court (comme "lundi").

Question b - Nom de mois de l'année

Le type approprié est CHAR(9) ou VARCHAR(9). Le mois le plus long est "septembre", qui comporte 9 caractères.

Question c - Numéro de semaine

Le type SMALLINT est tout à fait suffisant. Une année compte au maximum 53 semaines. Sachant que le type SMALLINT (ou même TINYINT selon les SGBD) permet de stocker des entiers bien au-delà de cette valeur, c'est un choix optimal.

Question d - Trigramme

Le type idéal est CHAR(3). Un trigramme étant, par définition, une chaîne d'exactement trois caractères, une taille fixe est la plus performante.

Question e - Code postal

Bien que le type INT(5) puisse être envisagé puisqu'il s'agit de chiffres, le type recommandé est CHAR(5). Explication : En base de données, un nombre entier perd ses zéros non significatifs à gauche. Si vous stockez le code postal de Foix (09000) dans un entier, il sera enregistré et restitué comme 9000. Utiliser CHAR(5) garantit la préservation du zéro initial.

Exercice 2 - Syntaxe des types de données

Il s'agit d'identifier les déclarations de type qui provoqueront une erreur de syntaxe.

Les définitions incorrectes sont :

• a) CHAR(16, 2) : Le type CHAR attend un seul paramètre représentant sa longueur (le nombre de caractères). La virgule et le second paramètre n'ont aucun sens pour une chaîne de caractères (cette syntaxe est réservée aux types numériques comme DECIMAL).
• f) DATE (10) : Le type DATE n'accepte pas de paramètre de taille. Le format d'une date est standardisé par le moteur SQL, sa taille de stockage est donc implicite.

Note de correction : Le document source comporte une coquille dans sa correction en listant deux fois la lettre "a)". La deuxième explication ("Une date ne contient pas de paramètre...") fait bien référence à l'option f).

Exercice 3 - Syntaxe des valeurs

Il s'agit de repérer les erreurs dans l'écriture littérale des valeurs.

Les spécifications incorrectes sont :

• a) DATE '18/11/2004' : Le format standard ISO exigé par SQL pour les dates est 'AAAA-MM-JJ' (Année-Mois-Jour).
• d) DATE '2004-11-18 20:20:30 000' : Le type DATE ne stocke que la date. Pour inclure une spécification horaire, il faut utiliser le type DATETIME ou TIMESTAMP.
• e) 123,55 : En SQL, selon la norme anglo-saxonne, le séparateur décimal est toujours le point (.), et non la virgule (,).
• g) 3.145 E-1 : Il ne doit y avoir aucun espace dans l'écriture scientifique d'un nombre réel. Il faut écrire 3.145E-1.

Exercice 4 - Création et manipulation de tables

Attention : Lors de l'écriture de requêtes SQL, utilisez toujours des guillemets simples droits ('). Le document source utilise par endroits des guillemets typographiques (’ ou « ») qui provoqueront des erreurs de syntaxe lors de l'exécution.

1. Création de la table FILM

CREATE TABLE film (
    idFilm INT(2),
    idRealisateur INT(2),
    titre VARCHAR(50),
    genre VARCHAR(10),
    annee INT(5) UNSIGNED
);

2. Sélection des films dramatiques ou policiers

SELECT * 
FROM film 
WHERE genre = 'Drame' OR genre = 'Policier';

Note : On pourrait également utiliser l'opérateur IN : WHERE genre IN ('Drame', 'Policier');

3. Suppression des films d'avant 1995

DELETE FROM film 
WHERE annee < 1995;

4. Suppression de la table FILM

DROP TABLE film;

5. Restauration depuis un script SQL

La commande pour exécuter un script externe directement depuis l'invite de commande MySQL/MariaDB est :

SOURCE script.sql;

6. Affichage du contenu de la table PERSONNE

SELECT * 
FROM personne;

7. Ajout d'un nouvel enregistrement dans FILM

a. En utilisant la commande INSERT

INSERT INTO film VALUES (12, 22, 'L''age de glace 4', 'Animation', 2011);

Explication : Pour insérer une chaîne contenant une apostrophe (comme "L'age"), il faut la doubler ('') afin de "l'échapper", sinon SQL pensera que la chaîne se termine après le "L".

b. En utilisant un fichier de données

LOAD DATA LOCAL INFILE 'film.txt' INTO TABLE film;

Exercice 5 - Accès aux données

a. Expression des projections et sélections

Q1 : Liste des avions dont la capacité est supérieure à 350 passagers

SELECT * 
FROM AirPlane 
WHERE Capacity > 350;

Q2 : Numéros et noms des avions localisés à Nice

SELECT NumAP, NameAP 
FROM AirPlane 
WHERE Localisation = 'Nice';

Q3 : Numéros des pilotes en service et villes de départ

SELECT NumP, Dep_T 
FROM Flight;

Note : Si un pilote effectue plusieurs vols depuis la même ville, cette requête renverra des doublons. Dans un cas réel, on utiliserait SELECT DISTINCT NumP, Dep_T pour nettoyer le résultat, mais la version sans DISTINCT reste techniquement correcte par rapport au modèle.

Q4 : Toutes les informations sur les pilotes

SELECT * 
FROM Pilot;

Q5 : Nom des pilotes domiciliés à Paris dont le salaire est supérieur à 15000 F

SELECT NameP 
FROM Pilot 
WHERE Address = 'Paris' AND Salary > 15000;

Erreur dans la source : La correction officielle du PDF propose Salary > 200000, ce qui contredit l'énoncé de la question (15000 F). Le code ci-dessus est la réponse exacte à la question posée.

b. Utilisation des opérateurs ensemblistes

Q6 : Avions (numéro et nom) localisés à Nice ou de capacité inférieure à 350

SELECT NumAP, NameAP 
FROM AirPlane 
WHERE Localisation = 'Nice' OR Capacity < 350;

Q7 : Vols au départ de Nice allant à Paris après 18 heures

SELECT * 
FROM Flight 
WHERE Dep_T = 'Nice' AND Arr_T = 'Paris' AND Dep_H > '18:00:00';

Erreur dans la source : La correction officielle propose Arr_H > '18:00:00', ce qui vérifie l'heure d'arrivée. La question demandant un départ après 18h, c'est bien la colonne Dep_H (Departure Hour) qu'il faut tester, comme corrigé ci-dessus.

Q8 : Numéros des pilotes qui ne sont pas en service

SELECT NumP 
FROM Pilot 
WHERE NumP NOT IN (
    SELECT NumP 
    FROM Flight
);

Explication : La sous-requête liste tous les identifiants de pilotes actuellement affectés à un vol. Le NOT IN permet de filtrer la table Pilot pour ne garder que ceux qui n'apparaissent pas dans cette liste.

Q9 : Vols (numéro, ville de départ) des pilotes de numéro 100 et 204

SELECT NumF, Dep_T 
FROM Flight 
WHERE NumP = 100 OR NumP = 204;

Erreur dans la source : La correction officielle indique NumP = 105 au lieu de 204. La requête ci-dessus a été corrigée pour correspondre exactement à la question.

Méthode

Pour réussir ce type d'épreuve sur le langage SQL, voici la démarche à adopter :

1. Lisez attentivement le schéma relationnel (fourni au début de l'exercice 5). Repérez les noms exacts des tables et des colonnes. N'inventez pas de noms (ex: utilisez Dep_T et non Depart_Ville).
2. Identifiez le besoin exact : "Quels sont les avions (numéro et nom)" indique que vous devez faire une projection (SELECT NumAP, NameAP), tandis que "Toutes les informations" implique l'utilisation de l'étoile (SELECT *).
3. Surveillez les types de données lors des filtres : Si vous comparez une chaîne de caractères (comme 'Nice' ou 'Paris') ou une heure, vous devez utiliser des guillemets simples. S'il s'agit d'un nombre (Capacity > 350), les guillemets ne sont pas nécessaires.
4. Méfiez-vous de la syntaxe sur machine : Les espaces de traitement de texte transforment souvent les guillemets simples en apostrophes typographiques incurvées. Sur feuille, soyez explicites. Sur machine, tapez toujours vos guillemets au clavier sans mise en forme.
5. Ne doutez pas de votre logique face aux coquilles : Les sujets et corrections comportent parfois des erreurs de frappe (comme les valeurs de salaire ou de numéro de pilote qui diffèrent entre la question et le corrigé). Appliquez strictement la consigne demandée.