TD2 Creation-Requêtes SQL.pdf
Télécharger PDFTD 2 : Création de tables
Soit la base de données décrite par le schéma et les instances suivantes :
Etudiant (NumEtudiant, Nom, Prenom, Telephone, Formation)
Livre (NumLivre, Titre, CodeAuteur)
Exemplaire (NumLivre, NumExemplaire, Etat)
Auteur (CodeAuteur, Nom, Prenom, DateNais, DateDeces, Pays)
Emprunt (NumEtudiant, NumLivre, DateEmprunt, DateRet)
Etudiant
| NumEtudiant | Nom | Prenom | Telephone | Formation |
|---|---|---|---|---|
| 1 | Christensen | Olivier | 0321231001 | MIAS1 |
| 2 | Cabrera | Christophe | MIAS1 | |
| 3 | Leveugle | Olivier | 0321120900 | GMI2 |
| 4 | Bulte | Nadege | 0321150867 | GMI2 |
| 5 | Carioca | Mikhail | SM1 | |
| 6 | Subiger | Jerome | 0321563412 | GMI2 |
Livre
| NumLivre | Titre | PrixUnit | CodeAuteur |
|---|---|---|---|
| HUG001 | Les miserables | 11,85 | 1 |
| BAL001 | Le pere Goriot | 6,50 | 2 |
| REN001 | Poils de carottes | 4,00 | 3 |
| VAL001 | Le bachelier | 5,10 | 4 |
| BAL002 | Eugenie Grandet | 5,00 | 2 |
| BAL003 | Illusions perdues | 6,60 | 2 |
| BAL004 | Le colonel Chambert | 2,80 | 2 |
| DIC001 | L'ami commun | 19.5 | 6 |
| CAR001 | La chasse au snark | 6.00 | 5 |
Exemplaire
| NumLivre | NumExemplaire | Etat |
|---|---|---|
| HUG001 | 1 | Mauvais |
| BAL001 | 1 | Moyen |
| HUG001 | 2 | Bon |
| BAL001 | 2 | Bon |
| REN001 | 1 | Bon |
| REN001 | 3 | Moyen |
| REN001 | 2 | Tres Bon |
| VAL001 | 1 | Moyen |
| BAL002 | 1 | Bon |
| BAL002 | 2 | Moyen |
| BAL003 | 1 | Moyen |
| BAL004 | 1 | Bon |
Auteur
| CodeAuteur | Nom | Prenom | DateNais | DateDeces | Pays |
|---|---|---|---|---|---|
| 1 | Hugo | Victor | 26/02/1802 | 22/05/1885 | France |
| 2 | Balzac | Honore | 20/05/1799 | 18/08/1850 | France |
| 3 | Renard | Jules | 22/02/1864 | 22/05/1910 | France |
| 4 | Vales | Jules | 11/06/1832 | 14/02/1885 | France |
| 5 | Carroll | Lewis | 27/01/1832 | 14/01/1898 | Royaume-Uni |
| 6 | Dickens | Charles | 07/02/1812 | 09/06/1870 | Royaume-Uni |
Emprunt
| NumEtudiant | NumLivre | NumExemplaire | DateEmp | DateRet |
|---|---|---|---|---|
| 1 | BAL001 | 1 | 12/10/2018 | 20/10/2018 |
| 3 | REN001 | 2 | 10/04/2018 | 20/05/2018 |
| 4 | BAL001 | 2 | 11/04/2018 | |
| 5 | VAL001 | 1 | 13/03/2018 | 20/03/2018 |
| 1 | BAL002 | 1 | 18/03/2018 | |
| 1 | VAL001 | 1 | 16/04/2018 | 30/04/2018 |
| 2 | REN001 | 1 | 16/06/2018 | 20/06/2018 |
| 1 | HUG001 | 1 | 12/05/2018 | |
| 1 | BAL003 | 1 | 20/09/2018 | |
| 1 | BAL004 | 1 | 20/09/2018 | |
| 1 | REN001 | 1 | 15/09/2018 | 19/09/2018 |
| 1 | DIC001 | 1 | 23/10/2018 | |
| 1 | CAR001 | 1 | 12/11/2018 |
Contraintes à assurer :
- La formation doit être : MIAS1, MIAS2, SMI1, SMI2, GMI1, GMI2
- La date de naissance doit être inférieure à la date de décès
- La date de retour peut ne pas être renseignée
- La date de retour doit être supérieure à la date d’emprunt
- Le titre n'a pas d'homonyme
- L'état d'un livre prend comme valeur : 'Bon', 'Mauvais', 'Tres bon', 'Moyen'
- La valeur par défaut de l'état d'un livre est Bon
Requêtes SQL
a) Afficher les étudiants de formation Mias1 ou de formation Sm1 de 2 façons.
b) Afficher les noms et prénom des étudiants dont la 1ère lettre du nom est un C.
c) Afficher les étudiants, sauf le numéro de téléphone, qui ont un téléphone.
d) Afficher les titres des livres ayant la lettre o dans le titre
e) Afficher tous les étudiants dont la 2ème lettre et la dernière lettre du nom est un a.
f) Afficher les étudiants (Nom, Prénom) qui ont fait des emprunts de 2 façons. Pour une des façons, il faudra utiliser le mot clé exists
g) Afficher les étudiants (Nom, Prénom) qui ont un emprunt en cours.
h) Afficher les étudiants (Nom et Prénom) qui n'ont pas emprunté de livres de 3 façons (avec in, avec left join et avec not exists)
i) Afficher les auteurs (Nom, Prénom) qui sont né en 1832 de 2 façons.
j) Afficher le nombre de livres dans la bibliothèque.
k) Afficher la dépense des livres (tenir compte de tous les exemplaires) de cette bibliothèque.
l) Faire le produit cartésien entre la table livre et la table etudiant.
m)Afficher le nombre d'étudiants par formation.
n) Afficher le nombre d'emprunts par étudiants
o) Afficher le livre (Titre) le plus cher.
p) Afficher le prix moyen de tous les livres (tenir compte de tous les exemplaires).
q) Afficher le nombre de livres différents.
r) Afficher les auteurs (Nom et Prénom) de livres qui ont été empruntés.
s) Afficher les livres (Titre) qui n'ont pas été empruntés.
t) Afficher le nombre d'emprunts par livre.
u) Afficher les livres (Titre et Numéro d'exemplaires) empruntés par l'étudiant Christensen.
v) Afficher le nombre d'exemplaires par livres.
w) Quels sont les étudiants (Nom et Prénom) qui ont emprunté le livre de titre 'Les miserables' de 2 façons.
x) Afficher les étudiants (Nom et Prénom) qui ont lu tous les livres de la bibliothèque. On supposera que les étudiants ne lisent qu'un seul exemplaire de chaque livre.
y) Afficher le titre du livre qui a été le plus emprunté.
z) Afficher le nombre d'étudiants qui ont lu le livre Poils de Carottes ?
aa)Une erreur de saisie a été faite lors de certaines saisies des prix unitaires des livres. Il faut augmenter de 10% le prix unitaire de tous les livres de Jules Renard
bb)Effacer les emprunts des étudiants ayant pour numéro étudiant 2 ou 3.
FAQ
1. Comment créer une base de données en SQL ?
Pour créer une base de données en SQL, vous devez utiliser la commande CREATE DATABASE suivie du nom de la base de données.
2. Comment insérer des données dans une table SQL ?
Pour insérer des données dans une table SQL, vous devez utiliser la commande INSERT INTO suivie du nom de la table et des valeurs à insérer.
3. Comment afficher les données d'une table SQL ?
Pour afficher les données d'une table SQL, vous devez utiliser la commande SELECT suivie du nom de la table.