ITC CCMP 2025 - Partie SQL

Sujet CCMP 2025

Partie SQL⚓︎

Base de données - Exemple de fret maritime de conteneurs⚓︎

On donne les tables NAVIRES et CONTENEURS suivantes :

NAVIRES(idN,evp,portDepN,dateDep,portDestN)

idN : clé primaire, identifiant du navire (type entier, non nul)
evp : capacité en équivalent conteneurs de longueur 20 pieds (type entier)
portDepN : port de départ (type chaîne de caractères)
dateDep : date de départ (type date)
portDestN : port de destination (type chaîne de caractères)

CONTENEURS(idC,idN,taille,portDepC,dateDisp,portDestC,val)

idC : clé primaire, identifiant du conteneur (type entier)
idN : identifiant du navire attribué (type entier, valeur 0 si non encore attribué).
taille : longueur du conteneur, 20 ou 40 pieds (type entier). La hauteur et la profondeur sont identiques. • portDepC : port de départ (type chaîne de caractères) • dateDisp : date de mise à disposition (type date) • portDestC : port de destination (type chaîne de caractères) • val : tarification, valeur entière de 1 à 5 par ordre croissant de profit pour l’entreprise (type entier)

Corrigé⚓︎

Q1

Écrire une requête permettant de retourner la liste des identifiants de conteneurs et leur ratio tarification/longueur trié par ordre décroissant, partant de Marseille et devant aller à Barcelone, avec une mise à disposition avant le 01/01/2025.

SQL
SELECT idC, (val * 1.0 / taille) AS ratio
FROM CONTENEURS
WHERE portDepC = 'Marseille' 
  AND portDestC = 'Barcelone'
  AND dateDisp < '2025-01-01'
ORDER BY ratio DESC;

Remarques :

La multiplication par 1.0 sert à convertir la valeur entière de val pour ne pas obtenir le quotient de la division entière qui serait obtenu sans cette conversion.

Il est possible de vérifier que sans cette conversion le ratio est toujours 0 ici.

Les dates s'écrivent avec des guillemets comme les chaînes de caractères.

Q2

SQL
SELECT idN, COUNT(idC) AS nb_conteneurs
FROM NAVIRES
NATURAL JOIN CONTENEURS
GROUP BY idN;

Remarque :

Le NATURAL JOIN est ici possible car les deux relations NAVIRES et CONTENEURS seront jointes par le couple clé primaire / clé étrangère avec le même nom : idN.

Il est aussi possible d'écrire : JOIN CONTENEURS ON NAVIRES.idN = CONTENUERS.idN

Test de requête :⚓︎

Il est possible de tester des requêtes, arpès avoir exécuter (bouton Exécuter sous le code) le chargement du code SQL plus bas.

Votre code SQL

Les relations de l'exercice:⚓︎

tester SQL avec du code pré-saisi

-- Création de la table NAVIRES
CREATE TABLE NAVIRES (
    idN INT PRIMARY KEY,
    evp INT NOT NULL,
    portDepN VARCHAR(50),
    dateDep DATE,
    portDestN VARCHAR(50)
);

-- Création de la table CONTENEURS
CREATE TABLE CONTENEURS (
    idC INT PRIMARY KEY,
    idN INT,
    taille INT,
    portDepC VARCHAR(50),
    dateDisp DATE,
    portDestC VARCHAR(50),
    val INT CHECK(val BETWEEN 1 AND 5),
    FOREIGN KEY (idN) REFERENCES NAVIRES(idN)
);

-- Insertion des données dans NAVIRES
INSERT INTO NAVIRES VALUES (12, 1500, 'Marseille', '2025-06-23', 'Hambourg');
INSERT INTO NAVIRES VALUES (2, 16000, 'Valence', '2025-06-18', 'Algésiras');
INSERT INTO NAVIRES VALUES (5, 500, 'Le Havre', '2025-10-06', 'Rotterdam');
INSERT INTO NAVIRES VALUES (8, 23000, 'Hongkong', '2025-07-02', 'Shanghai');
INSERT INTO NAVIRES VALUES (15, 1800, 'Marseille', '2024-12-15', 'Barcelone');  -- pour Q1

-- Insertion des données dans CONTENEURS
INSERT INTO CONTENEURS VALUES (103, 12, 20, 'Marseille', '2025-08-08', 'Hambourg', 2);
INSERT INTO CONTENEURS VALUES (218, 12, 40, 'Marseille', '2025-07-08', 'Valence', 1);
INSERT INTO CONTENEURS VALUES (5, 0, 40, 'Le Havre', '2025-10-01', 'Shangai', 5);
INSERT INTO CONTENEURS VALUES (885, 8, 20, 'Hongkong', '2025-07-01', 'Barcelone', 1);
INSERT INTO CONTENEURS VALUES (999, 15, 20, 'Marseille', '2024-12-01', 'Barcelone', 5);  -- pour Q1
INSERT INTO CONTENEURS VALUES (1000, 15, 40, 'Marseille', '2024-11-25', 'Barcelone', 4);  -- pour Q1
INSERT INTO CONTENEURS VALUES (1001, 2, 40, 'Valence', '2025-06-10', 'Algésiras', 3);      -- pour Q2