💻 TP – Gestion d’un parc informatique⚓︎

🎯 Objectif du TP

Manipuler les principales instructions du langage SQL (LMD).
Interroger et modifier les données d’une base
Comprendre et utiliser les jointures entre plusieurs tables.

🧠 Contexte du TP⚓︎

Le service informatique du lycée TechPlus gère un inventaire du matériel informatique installé dans les différentes salles du campus.

Chaque poste appartient à une salle, héberge un système d’exploitation, et est attribué à un utilisateur (enseignant, personnel administratif, étudiant, etc.).
Le service suit également les pannes signalées sur les équipements afin d’assurer la maintenance.

1. Schéma de la base de données 🧩⚓︎

schéma de la base

SALLE (idsalle, nomSalle, capacite)
clé primaire : idsalle
idSalle : INT : Identifiant de la salle
nomSalle : VARCHAR(30) : Nom de la salle (ex : B201, A104…)
capacite : INT : Nombre de postes possibles

POSTE(idPoste, nomPoste, systeme, dateAchat, #idsalle)
clé primaire : idPoste
idPoste : INT : Identifiant du poste
nomPoste : VARCHAR(30) : Nom du poste (ex : PC-B201-01)
systeme : VARCHAR(20) : Système d’exploitation (Windows 10, Linux Debian…)
dateAchat : DATE : Date d’achat du poste
idSalle : INT : Référence à la salle où se trouve le poste
clé étrangère : #idSalle : en référence à idSalle de Salle

UTILISATEUR(idUtilisateur, nom, prenom, fonction)
clé primaire : idUtilisateur
idUtilisateur : INT : Identifiant de l’utilisateur
nom : VARCHAR(30) : Nom de l’utilisateur
prenom : VARCHAR(30) : Prénom
fonction : VARCHAR(30) : Rôle dans l’établissement (Enseignant, Étudiant, Technicien, etc.)

AFFECTATION(#idPoste,#idUtilisateur, dateAffectation)
clé primaire : #idPoste, #idUtilisateur
#idPoste : INT : Référence au poste
#idUtilisateur : INT : Référence à l’utilisateur
dateAffectation : DATE : Date d’affectation du poste à l’utilisateur
dateFin : DATE : Date de fin d'affectation à l'utilisateur
clé étrangère : #idPoste : en référence à idPoste de POSTE
clé étrangère : #idUtilisateur : en référence à idUtilisateur de UTILISATEUR

INCIDENT(idIncident, idPoste, dateIncident, description, statut)
clé primaire : idIncident
idIncident : INT : Identifiant de l’incident (PK)
idPoste : INT : Poste concerné (FK en référence à idPoste de Poste)
dateIncident : DATE : Date de la panne
description : VARCHAR(100) | Description du problème
statut : VARCHAR(20) : Statut du ticket (ouvert, en cours, résolu)
clé étrangère : #idPoste : en référence à idPoste de POSTE

Télécharger fichier Création de la base

💾 Jeu de Données

🐬 SQL

INSERT INTO Salle VALUES (1, 'B201', 15);
INSERT INTO Salle VALUES (2, 'A104', 20);

INSERT INTO Poste VALUES (1, 'PC-B201-01', 'Windows 10', '2023-01-10', 1);
INSERT INTO Poste VALUES (2, 'PC-B201-02', 'Linux Debian', '2022-05-12', 1);
INSERT INTO Poste VALUES (3, 'PC-A104-01', 'Windows 11', '2024-03-15', 2);

INSERT INTO Utilisateur VALUES (1, 'Martin', 'Sophie', 'Enseignant');
INSERT INTO Utilisateur VALUES (2, 'Durand', 'Lucas', 'Technicien');
INSERT INTO Utilisateur VALUES (3, 'Lemoine', 'Camille', 'Étudiant');

INSERT INTO Affectation VALUES (1, 1, '2024-09-01', '2025-09-01');
INSERT INTO Affectation VALUES (2, 3, '2025-01-10');

INSERT INTO Incident VALUES (1, 1, '2025-02-12', 'Écran noir au démarrage', 'Résolu');
INSERT INTO Incident VALUES (2, 2, '2025-04-05', 'Problème de réseau', 'En cours');
INSERT INTO Incident VALUES (3,2, '2025-06-20', 'Blue screen', 'En cours')

2. Requêtes sur une seule table 🧩⚓︎

Q1

EnoncéCorrection

Lister toutes les salles avec leur capacité.

🐬 SQL

SELECT nomSalle, capacite 
FROM salle;

Q2

EnoncéCorrection

Afficher les postes sous Windows.

🐬 SQL

SELECT *
FROM poste
WHERE systeme LIKE "Windows%";

Q3

EnoncéCorrection

Afficher les postes achetés avant 2023.

🐬 SQL

SELECT *
FROM poste
WHERE dateAchat < '2023-01-01' ;
/* ou */
SELECT *
FROM poste
WHERE YEAR(dateAchat) < 2023 ;

Q4

EnoncéCorrection

Afficher les utilisateurs dont la fonction est “Technicien”.

🐬 SQL

SELECT *
FROM utilisateur
WHERE fonction="Technicien"

Q5_1

EnoncéCorrection

Insérer un nouvel incident toujours en Cours ayant eu lieu le 20 juin 2025 sur le poste 5 avec comme symptôme un blue screen.

🐬 SQL

INSERT INTO Incident VALUES (6,2, '2025-06-20', 'Blue screen', 'En cours')

Q5_2

EnoncéCorrection

Afficher la date et la description des incidents ayant le statut “En cours” classé par ordre decroissant (du plus récent au plus ancien).

🐬 SQL

SELECT dateIncident, description
FROM incident
WHERE statut="En cours"
ORDER by dateIncident DESC ;

Q6

EnoncéCorrection

Afficher le nombre total de postes.

🐬 SQL

SELECT COUNT(*) AS nbTotalPoste
FROM poste

Q7

EnoncéCorrection

Afficher le nombre d’incidents par statut.

🐬 SQL

SELECT statut, COUNT(*) AS nbIncident
FROM incident
GROUP BY statut ;

Q8

EnconcéCorrection

Le poste 'PC-B201-02' a été passé sous Linux Mint. Mettez à jour la donnée dans la base.

🐬 SQL

UPDATE poste
SET systeme = "Linux Mint"
WHERE nomPoste = "PC-B201-02" ;

3. Requêtes avec jointures 🔗⚓︎

Q9

EnoncéCorrection

Lister les postes avec le nom de leur salle.

🐬 SQL

SELECT POSTE.nomPoste, SALLE.nomSalle
FROM POSTE INNER JOIN SALLE
ON POSTE.idSalle = SALLE.idSalle

Q10

EnoncéCorrection

Lister les postes et le nom de l’utilisateur affecté à l'instant de la requête.

🐬 SQL

SELECT p.nomPoste, u.nom, max(a.idPoste)
FROM POSTE p INNER JOIN AFFECTATION a
ON p.idPoste = a.idPoste
INNER JOIN UTILISATEUR u
ON a.idUtilisateur=u.idUtilisateur
WHERE dateFin IS NULL ;

Q11

EnoncéCorrection

Afficher pour chaque incident, le nom du poste et le nom de la salle concernée.

🐬 SQL

SELECT i.idIncident, p.nomPoste, s.idSalle
FROM POSTE p INNER JOIN INCIDENT i 
ON p.idPoste = i.idPoste
INNER JOIN SALLE s
on p.idSalle = s.idSalle ;

Q12

EnoncéCorrection

Afficher les utilisateurs qui ont actuellement un poste affecté sous Linux.

🐬 SQL

SELECT nom, prenom
FROM UTILISATEUR u INNER JOIN AFFECTATION a
ON u.idUtilisateur = a.idUtilisateur
INNER JOIN POSTE p
ON a.idPoste = p.idPoste
WHERE P.systeme LIKE "Linux%"
AND a.dateFin IS NULL ;

Q13

EnoncéCorrection

Afficher les postes sans incident.

difficile non ???
La seule chose que l'on sait est que si un poste à un incident, alors il est présent dans la table INCIDENT

🐬 SQL

SELECT nomPoste 
FROM POSTE p INNER JOIN INCIDENT i 
ON p.idPoste = i.idPoste ;

et on sait gérer avec NOT IN quand qqch n'appartient par à une liste.

🐬 SQL

SELECT nomPoste 
FROM POSTE 
WHERE nomPoste NOT IN ('PC-B201-01', 'PC-B201-02', 'PC-A104-01', 'PC-A104-02','PC-C312-01')

Note : on obtient deux postes : PC-D010-01 et PC-D010-02

et si on assemblait les deux requêtes ...

🐬 SQL

SELECT nomPoste 
FROM POSTE 
WHERE nomPoste NOT IN (SELECT nomPoste  
FROM POSTE p INNER JOIN INCIDENT i 
ON p.idPoste = i.idPoste )

Bravo, vous venez de faire votre première requête imbriquée !

Q14

EnoncéCorrection

Afficher pour chaque salle le nombre de postes.

🐬 SQL

SELECT nomSalle, COUNT( DISTINCT p.idPoste)
FROM POSTE p INNER JOIN SALLE s
ON p.idSalle = s.idSalle
GROUP BY nomSalle

Q15

EnoncéCorrection

Afficher pour chaque utilisateur, la date de début d'affectation, la date de fin d'affectation et le nom du poste.

🐬 SQL

SELECT u.nom, u.prenom, p.nomPoste, a.dateAffectation, a.dateFin
FROM AFFECTATION a INNER JOIN UTILISATEUR u 
    ON u.idUtilisateur = a.idUtilisateur
INNER JOIN POSTE p       
    ON p.idPoste = a.idPoste
ORDER BY u.nom, u.prenom, a.dateAffectation;

Q16

EnoncéCorrection

Supprimer les incidents “résolus” antérieurs à 2024.

🐬 SQL

DELETE FROM Incident
WHERE statut = 'Résolu'
AND dateIncident < '2024-01-01';