TP2 sous requête et jointure
Étape1 : Création des tables :
1- Créer la table Categories avec les attributs suivants :
Colonnes Types et contraintes
idCategorie Char(1) Primary Key
nomCategorie Varchar(30) not null
couleur Varchar(30)
2- Créer la table Questions avec les attributs suivants :
Colonnes Types et contraintes
idQuestion int , Primary Key.
La clé est générée par défaut. Commence à 100 et s’incrémente de 1
enonce varchar(100) not null,
flag char(1) . Ce flag prend uniquement les valeur n ou o. Par défaut le
flag est à n
difficulte char(1) not null. Cet attribut est égal à d, ou m ou f. rien d’autres
idCategorie char(1) not null.
IdCategorie et une clé étrangère faisant référence à idCategorie de
la table Categories
3- Créer la table Reponses avec les attributs suivants :
Colonnes Types et contraintes
idReponse int generated by default as identity (commence à 1 et
s’incrémente de 1). Primary key
laReponse, varchar(100) not null
estBonne char(1) not null. Cet attribut peut être égal à o ou n. Rien d’autre
idQuestion int not null. Clé étrangère faisant référence à idQuestion de la table
Questions
1
4- Créer la table JoueursTrivia avec les attributs suivants :
Colonnes Types et contraintes
idJoueur int generated by default as identity. (commence à 1 et
s’incrémente de 1). Primary key
aliass varchar(10) unique not null,
nom varchar(20),
prenom varchar(20),
5- Créer La table Scores avec les attributs suivants.
Colonnes Types et contraintes
idJoueur int,
idCategorie char(1),
nbBonneReponse int,
nbMauvaiseReponse int
Attention :
(idJoueur ,idCategorie) : est la clé primaire de la table Scores.
idJoueur : est une clé étrangère dans la table Scores. Cet attribut fait référence à idJoueur la
table JoueursTrivia
idCategorie : clé étrangère dans la table Scores. Cet attribut fait référence à idCategorie de la
table Catégories
2
Étape 2 : Insertion des enregistrements
Exécuter le script [Link] pour insérer les enregistrements dans chacune des tables.
Faire un SELECT * pour chacune des tables pour vérifier que tout est OK
Étape 3 : Questions.
Pour chacune des questions, lisez le message erreur retourné par le SGBD, puis donner la
réponse dans vos mots pour montrer que vous avez compris.
La réponse doit être en commentaires dans le fichier sql.
1. Que se passe-t-il si vous essayez d’insérer l’enregistrement suivant dans la table
Questions
insert into Questions(enonce,difficulte,idCategorie)
values('Quel est le nom de votre prof ?','f','z');
2. Que se passe-t-il si vous essayez de supprimer la catégorie dont le code est ‘s’
DELETE FROM categories WHERE idCategorie ='s';
3. Que se passe-t-il si vous essayez de modifier le code s par w dans la table Categories?
UPDATE categories SET idCategorie ='w' WHERE idCategorie ='s';
4. Que se passe-t-il si vous supprimer de la table Questions, la question numéro 100 ?
DELETE FROM questions WHERE idQuestion =100;
5. Que se passe-t-il lorsque vous insérez l’enregistrement suivant de la table Scores ?
INSERT INTO Scores VALUES (1, 's',2,2);
6. Que se passe-t-il lorsque vous insérez l’enregistrement suivant dans la table Scores ?
INSERT INTO Scores VALUES (1, 'k',2,2);
7. Que se passe-t-il lorsque vous supprimer le joueur numéro 1 de la table joueursTrivia ?
DELETE FROM joueursTrivia WHERE idJoueur =1;
8. Insérer 2 enregistrements de votre choix dans la table Scores. (donner et exécuter la
requête)
3
9. Ajouter deux questions de votre choix pour la catégorie 'j' dans la table Questions
10. Ajouter les réponses (dans la table Réponses) des questions que vous venez d’ajouter
11. Dans la table Scores, pourquoi est-ce que le idJoueur n’est pas IDENTITY ? Est-ce que
nous aurions pu mettre cet attribut IDENTITY ? Justifier votre réponse.
12. Afficher les questions qui contiennent dans l’énoncé le mot : élément.
13. Afficher toutes les questions de la catégorie s et qui sont difficiles (d)
14. Afficher les questions qui sont dans les catégories s ou h.
15. Mettre à jour la difficulté de la question « Quel élément chimique a comme
symbole la lettre K ? »par la difficulté de la question « Quel est le poids moyen
d''un cerveau humain? »
16. Utiliser une sous requête pour supprimer les catégories qui n’ont pas de questions
(Ne pas exécuter ou faire un ROLLBACK).
17. Afficher les deux meilleurs joueurs (alias, totalpoints). Chaque bonne réponse vaut
2 points, chaque mauvaise réponse vaut -1 points.
18. Créer la table Questionsdifficiles qui aura les colonnes suivantes : nomcategorie,
enonce,lareponse. Cette table va contenir toutes les questions difficiles avec
uniquement leurs bonnes réponses.
19. Afficher le nomCategorie, enonce et difficulte de toutes les catégories ayant une
question
20. Afficher idQuestion, enonce, difficulte, lareponse de toutes les questions. Afficher
uniquement les bonnes réponses.
21. Afficher toutes les questions (enonce, difficulte) de la categorie : histoire et
géographie;
22. Afficher nomCategorie, enonce , difficulte, lareponse de toutes les questions. On
affiche uniquement les bonnes réponses. Cette sortie est ordonnée selon le nom de
la catégorie.
23. Afficher nom et aliass des joueurs , nomCategorie, nbbonnereponse,
bmauvaisereponse.
24. Afficher nom, aliass des joueurs ayant plus que 2 bonnes réponses dans la catégorie
sciences.
25. Afficher le nomCategorie, enonce et difficulte . Cet affichage doit inclure toutes les
catégories y compris celles qui n’ont pas de questions. Pour plus de lisibilité,
ordonnez la sortie par énonce
26. Afficher les nomCategorie et la couleur de toutes les catégories qui n’ont pas de
questions.
27. Afficher la liste des joueurs (nom et alias) qui n’ont pas joué (nbbonnereponse,
nbmauvaisereponse sont tous les deux NULL)