Pg 1 : Exercice
Pg2 : Correction
Soit la base de données relationnelle suivante concernant la gestion des commandes de livres pour la
bibliothèque des facultés
1) Déterminer le modèle relationnel
2) Créer la table faculté sans spécifier sa clé primaire
3) Créer la table commande clé primaire
4) Créer la table livre en définissant : la contrainte pk_isbn de clé primaire et ne pas définir la
colonne stock.
5) Créer la table Ligne_Commande en spécifiant la contrainte de clé primaire et l’une des
contraintes de clé étrangère
6) Modifier de la taille du colonne titre à VARCHAR(20).
7) Supprimer la colonne nbeleve de la table faculte.
8) Ajouter de la contrainte PRIMARY KEY sur l’attribut ncmd de la table Commande.
9) Ajout des contraintes de clés étrangères sur la table Commande:
10) Ajout des contraintes de clés étrangères sur la table Ligne_commande:
11) Ajout de la contrainte CHECK sur l’attribut Qte (Qte > 0) de la table Ligne_Commande:
12) Donner la liste des facultés
13) Donner la liste des différentes livres (isbn ) qui ont été commandés:
14) Donner les codes et les noms des facultés de la ville de Sousse
15) Donner la liste les titres des livres qui ont un stock qui dépasse 100.
16) Donner la liste des livres dont le prix est compris entre 3 et 5 dinars.
17) Donner la liste des livres dont les titres commencent par B
18) Donner les noms des facultés qui n’ont pas téléphone:
19) les titres des livres qui ont un stocke supérieur à celui du livre BASE DE DONNEES.
20) Donner la valeur de chaque livre en stock
21) donner la moyenne des prix unitaires des livres:
22) donner la Liste des commandes du mois de janvier 2018
23) Donner les titres des livres de la commande numéro C123.
24) Donner pour chaque numéro livre la quantité totale commandée
25) Donner le nombre des livres par commande
26) Donner le total des montants par commande
27) Donner la liste des livres en pénurie et la quantité à acheter.
28) Afficher les facultés triés sur ville décroissant et nom croissant :
29) Donner le nombre de livres et la quantité totale par commande suivant l’ordre décroissant du
nombre de livres et l’ordre croissant de la quantité totale
1
1) Faculté (code_l, Nom, ville, tel, nbeleve)
Commande (ncmd, Date, #code_l)
Livre (isbn, titre, PU, stock)
Ligne_commande (#ncmd, #isbn, qte)
2) CREATE TABLE faculte (code_l VARCHAR(4) NOT NULL, nom VARCHAR(50)NOT NULL, ville
VARCHAR(15)NOT NULL, Tel VARCHAR(20), Nbeleve int (4)) type=Innodb;
//N.B. InnoDB : ne crée pas automatiquement les index nécessaires pour les clés étrangères. Vous devez les
créer vous-même.
Les index sont nécessaires pour accélérer les vérifications de contrainte.
3) CREATE TABLE Commande(ncmd VARCHAR(4) Primary Key, DateCmd DATE NOT NULL,
Code_l VARCHAR(4)NOT NULL) type=Innodb;
4) CREATE TABLE livre (isbn VARCHAR(20) CONSTRAINT pk_ISBN PRIMARY KEY, Titre
VARCHAR Not NULL(50), PU decimal(6,3) Not NULL) type = InnoDB;
5) CREATE TABLE ligne_commande (ncmd VARCHAR(4), isbn VARCHAR(20), Qte Int(5),
CONSTRAINT pk_LCMD PRIMARY KEY (ncmd, isbn), CONSTRAINT fk_isbn FOREIGN KEY (isbn)
REFERENCES livre(isbn)) type=Innodb;
6) ALTER TABLE livre MODIFY titre VARCHAR(20);
7) ALTER TABLE faculte drop nbeleve;
8) ALTER TABLE Commande ADD CONSTRAINT pk_NCMD PRIMARY KEY (NCmd);
9) ALTER TABLE Commande ADD CONSTRAINT fk_code_l FOREIGN KEY (code_l)
REFERENCES facult(code_l);
10) ALTER TABLE Ligne_commande ADD CONSTRAINT fk_NCMD FOREIGN KEY (ncmd)
REFERENCES Commande(ncmd);
11) ALTER TABLE Ligne_commande ADD CONSTRAINT ck_QTE CHECK (Qte > 0);
12) SELECT * Into outfile ‘c:\rq1.xls’ FROM faculte
13) Select DISTINCT isbn From ligne _commande;
14) Select code_l, nom From faculte Where vil8)le=‘soussse’;
15) Select titre From livre Where stock >100
16) SELECT * FROM livre WHERE (PU >= 3) AND (PU <= 5);
Ou bien SELECT * FROM livre WHERE PU BETWEEN 3 AND 5;
17) SELECT titre FROM LIVRE WHERE titre LIKE "B%";
18) SELECT nom FROM faculte WHERE tel Is Null;
19) SELECT V1.titre FROM livre AS V1, livre AS V2
WHERE (V1.stock > V2.stock) AND(V2.titre ="BASES DE DONNEES")
20) SELECT isbn, (pu*stock) AS valeur FROM livre;
21) SELECT AvG(pu) FROM livre;
22) SELECT ncmd FROM commande
WHERE ((Month(date_cmd)=1) AND (Year(date_cmd)=2018));
23) SELECT titre FROM livre,ligne_commandes
WHERE livre.isbn = ligne_commandes.isbn And ncmd ="C123";
24) SELECT isbn, SUM (qte) as Quantite_total FROM Ligne_commandes
GROUP BY isbn;
25) SELECT ncmd, COUNT (isbn) Nblivre FROM Ligne_commandes
GROUP BY ncmd;
26) SELECT ligne_commandes.ncmd, Sum(livre.pu*ligne_commandes.qte) AS total_cmd
FROM livre ,ligne_commandes
WHERE livre.isbn = ligne_commandes.isbn
GROUP BY ligne_commandes.ncmd;
27) SELECT L.isbn, stock, ( sum( qte ) - stock) AS acheter FROM Lignes_commandes L, livre V
WHERE L.isbn = V.isbn GROUP BY L.isbn
HAVING ( sum( qte ) > stock);
28) SELECT * FROM faculte
ORDER BY ville ASC , Nom DESC
29) SELECT ncmd, Count(ligne_commandes.isbn) as NBlivre, Sum(ligne_commandes.qte) as SomQte
FROM ligne_commandes GROUP BY ncmd
ORDER BY Count(ligne_commandes.isbn) DESC , Sum(ligne_commandes.qte) ASC;
2
3
