Aller au contenu

TNSI : Exercices sur le Langage SQL⚓︎

Exercice 1⚓︎

(d'après Prépabac NSI, Terminale, G.CONNAN, V.PETROV, G.ROZSAVOLGYI, L.SIGNAC, éditions HATIER.)

On veut créer une base de données baseHopital.db qui contiendra les trois tables suivantes :

erDiagram Patients { int id string nom string prenom string genre int annee_naissance } Ordonnances { int code int id_patient int matricule_medecin string date_ord string medicaments } Medecins { int matricule string nom_prenom string specialite string telephone }

On suppose que les dates sont données sous la forme jj-mm-aaaa.

Q1. Donner les commandes SQLite permettant de créer ces tables.

Correction 
CREATE TABLE Patients(
id INTEGER PRIMARY KEY AUTOINCREMENT,
nom TEXT,
prenom TEXT,
genre TEXT,
annee_naissance INTEGER
);

CREATE TABLE Ordonnances(
code INTEGER PRIMARY KEY,
id_patient INTEGER,
matricule_medecin INTEGER,
date_ord TEXT,
medicaments INTEGER
);

CREATE TABLE Medecins(
matricule INTEGER  PRIMARY KEY,
nom_prenom TEXT,
specialite TEXT,
telephone TEXT
);

Q2. Mme Anne Wizeunid, née en 2000 et demeurant 3 rue des Pignons Verts 12345 Avonelit doit être enregistrée comme patiente. Donner la commande SQLite correspondante.

Correction 
INSERT INTO Patients VALUES (NULL, "Wizeunit", "Anne", "F", 2000);

Commentaire : NULL sert ici à ne rien mettre là où le SGBD gère tout seul la clé primaire en autoincrement. (hors-programme)

Q3. Le patient numéro 100 a changé de genre et est maintenant une femme. Donner la commande SQLite modifiant en conséquence ses données.

Correction 
UPDATE Patients SET genre = 'F' WHERE id = 100 ;

Q4. Par souci d'économie, la direction décide de se passer des médecins spécialisés en épidémiologie. Donner la commande permettant de supprimer leurs fiches.

Correction 
DELETE FROM Medecins WHERE specialite = "épidémiologie";

Q5. Donner la liste des patient(e)s ayant été examiné(e)s par un(e) psychiatre en avril 2020.

Correction 
SELECT p.nom, p.prenom FROM Patients AS p
JOIN Ordonnances AS o ON p.id = o.id_patient
JOIN Medecins AS m ON o.matricule_medecin = m.matricule
WHERE m.specialite = "psychiatrie" AND o.date_ord LIKE "%04-2020%"

Exercice 2⚓︎

Questions interactives à réaliser sur le site sqlzoo.net

  1. Travail sur SELECT, (base de données Nobel) ici

Correction : voir https://github.com/jisaw/sqlzoo-solutions/blob/master/select-from-nobel.sql

  1. Travail sur SUM et COUNT, (base de données World) ici

Correction : voir https://github.com/jisaw/sqlzoo-solutions/blob/master/sum-and-count.sql

  1. Travail sur JOIN, (base de données Euro2012) ici

Correction : voir https://github.com/jisaw/sqlzoo-solutions/blob/master/join.sql

Exercice 3⚓︎

basé sur le travail de G.Viateau (Bayonne)

On considère ci-dessous le schéma de la base de données du stock d'un supermarché :

erDiagram Stocks o{--o{} Produits : "est disponible" Produits ||--|| Fournisseurs : "acheter" Stocks { int id int produit int quantite date date_peremption } Produits { int id varchar_20 nom_court varchar_100 nom int prix_achat int prix_vente int fournisseur } Fournisseurs { int id varchar_30 nom varchar_100 adresse char_5 cp varchar_30 ville char_10 telephone varchar_50 courriel varchar_50 responsable }

Q1. Quelle requête SQL donne le prix d'achat du produit dont le nom_court est «Liq_Vaiss_1L» ?

Correction 
SELECT prix_achat FROM Produits WHERE nom_court = 'Liq_Vaiss_1L'

Q2. Quelle requête donne l'adresse, le code postal et la ville du fournisseur dont le nom est «Avenir_confiseur» ?

Correction 
SELECT adresse, cp, ville FROM Fournisseurs WHERE nom = 'Avenir_confiseur';

Q3. Quelle requête donne les produits étant en rupture de stock ?

Correction 
SELECT Produits.nom FROM Produits
JOIN Stocks ON Produits.id = Stocks.produit
WHERE Stocks.quantite = 0;

Q4. Quelle requête donne la liste de toutes les ampoules vendues en magasin ? On pourra faire l'hypothèse que le nom du produit contient le mot «ampoule»

Correction 
SELECT nom FROM Produits WHERE nom LIKE "%ampoule%";

Q5. Quelle requête permet d'avoir le prix moyen de ces ampoules ?

Correction 
SELECT AVG(prix_vente) FROM Produits WHERE nom LIKE "%ampoule%";

Q6. Quelle requête permet d'identifier le produit le plus cher du magasin ?

Correction 

SELECT nom_court FROM Produits ORDER BY prix_vente DESC LIMIT 1;
ou

SELECT nom FROM Produits WHERE prix_vente = (SELECT MAX(prix_vente) FROM Produits);

Q7. Quelle requête renvoie les noms des produits dont la date de péremption est dépassée ? (on pourra utiliser la fonction SQL NOW() qui renvoie la date actuelle )

Correction 
SELECT p.nom FROM Produits AS p
JOIN Stocks AS s ON s.produits = p.id
WHERE s.date_peremption < NOW();

Exercice 4⚓︎

site réalisé par J. Le Coupanec (Académie de Rennes)

Sur la page http://colbert.bzh/sql/, suivez le parcours des deux TPs :

  • TP1 : Gestion d'un réseau d'agences de location de voitures. La base de données locations.db contient les tables Agences,Locations, Vehicules.



  • TP2 : Le championnat de France 2015-2016 La base de données soccer.db contient les tables Team,Match, Event, Player.