Exercices SQL débranchés

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 :

	Patients
id	Int
nom	Text
prenom	Text
genre	Text
annee_naissance	Int

	Ordonnances
code	Int
id_patient	Int
matricule_medecin	Int
date_ord	Text
medicaments	Text

	Medecins
matricule	Int
nom_prenom	Text
specialite	Text
telephone	Text

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

On donne le diagramme relationnel de cette base :

Q0. Écrire le schéma relationnel de la table Ordonnances. On soulignera les clés primaires et marquera d'un # les clés étrangères.

Q1. (HP) Donner les commandes SQL permettant de créer ces tables.

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

Q3. Le patient numéro 100 a changé de prénom et s'appelle maintenant "Alice". Donner la commande SQLite modifiant en conséquence ses données.

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.

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

Exercice 2

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é :

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 3

Exercice 1 du sujet Amérique du Nord J1 2022

Exercice 4

Exercice 4 du sujet Centres Étrangers J1 2022

Exercice 5

Exercice 4 du sujet Métropole J2 2022

Exercice 6

Exercice 2 du sujet La Réunion J2 2022

Exercice 7

Exercice 3 du sujet Amérique du Nord J1 2024

Partie A

Si vous souhaitez jouer avec ces flashcards, téléchargez le fichier flashcards.csv et utilisez le code ci-dessous :

import csv
import time

def charger(nom_fichier):
    with open(nom_fichier, 'r') as fichier:
        donnees = list(csv.DictReader(fichier, delimiter=';'))
    return donnees

def choix_discipline(donnees):
    disciplines = []
    for i in range(len(donnees)):
        disc = donnees[i]['discipline']
        if not disc in disciplines:
            disciplines.append(disc)
    for i in range(len(disciplines)):
        print(i + 1, disciplines[i])
    num_disc = int(input('numéro de la discipline ? '))
    return disciplines[num_disc - 1]


def choix_chapitre(donnees, disc):
    chapitres = []
    for i in range(len(donnees)):
        if donnees[i]['discipline'] == disc:
            ch = donnees[i]['chapitre']
            if not ch in chapitres:
                chapitres.append(ch)
    for i in range(len(chapitres)):
        print(i + 1, chapitres[i])
    num_ch = int(input('numéro du chapitre ? '))
    return chapitres[num_ch - 1]


def entrainement(donnees, disc, ch):
    for i in range(len(donnees)):
        if donnees[i]['discipline'] == disc and donnees[i]['chapitre'] == ch:
            print('QUESTION : ', donnees[i]['question'])
            time.sleep(5)
            print(donnees[i]['réponse'])
            time.sleep(1)


flashcard = ...
d = ...
c = ...
entrainement(...)

Partie B

Exercice 8

Exercice 2 du sujet Amérique du Nord J2 2024

Exercice 9

Exercice 3 Partie B du sujet Centres Étrangers J2 2024