Tester des requêtes SQL simples avec ACCESS

Tester des requêtes SQL simples avec ACCESS
Création de la base de données
1.
2.
Ouvrir Microsoft ACCESS (outil Microsoft Office)
Cliquer sur ‘Base de données vide’
3.
4.
5.
Choisir le dossier ‘Mes Documents’ pour la sauvegarde
Donner un nom à la base de données : Base_Planning_ Sport
Cliquer sur ‘Créer’
6.
Création de la première relation ( ‘table’) :
7.
Cliquer sur le symbole enregistrer.
Katia Barré - Lycée LESAGE Vannes
Page 1
8.
9.
Choisir un nom pour la relation : ETUDIANT
Cliquer sur ‘OK’
10. Entrez les noms des attributs (‘champs’) après double-clic sur la case correspondante :
CodeEtudiant, Nom, Prénom, Adresse, CodePostal, Ville
11. Entrez les t-uplets (enregistrements) suivants :
Katia Barré - Lycée LESAGE Vannes
Page 2
12. SAUVEGARDEZ
REGULIÈREMENT VOTRE
TRAVAIL
13. La clé primaire ne vous plait pas. Nous allons la modifier :
Cliquer sur le nom de la clé primaire ‘CodeEtudiant’.
Dans le menu déroulant ‘type de données’, choisissez :Texte
14. Remplacez les valeurs de CodeEtudiant par E1, E2, etc…
15. Créer de même deux autres relations (tables) SPORT et PRATIQUE. Sauvegardez régulièrement.
Cliquer sur Créer
Katia Barré - Lycée LESAGE Vannes
Page 3
puis sur Table
16. Créer la relation SPORT
17. Créer la relation PRATIQUE
Katia Barré - Lycée LESAGE Vannes
Page 4
18. Création des liens (‘relations’) entre ces relations (‘tables’) :
Dans ‘Outils bases de données’, cliquer sur ‘relations’
19. Ajouter successivement les relations (‘tables’) créées :
Cliquer sur ‘ETUDIANT’ puis sur ‘Ajouter’ ; sur ‘PRATIQUE’ puis sur ‘Ajouter’ ; sur ‘SPORT’ puis sur
‘Ajouter.’
20. Création des liens (‘relations’) ‘UN vers PLUSIEURS’
Un étudiant peut pratiquer plusieurs sports.
Chaque sport peut être pratiqué par plusieurs étudiants.
La table PRATIQUE synthétise qui pratique quel sport.
Katia Barré - Lycée LESAGE Vannes
Page 5
Cliquer gauche sur la clé primaire de la table ETUDIANT ‘CodeEtudiant’ et faire glisser sur l’attribut
(‘champ’) ‘CodeEleve’ de la relation (‘table’) PRATIQUE.
Valider ‘Appliquer l’intégrité référentielle’. Puis cliquer sur ‘Créer’.
Le premier lien (‘ relation’) est créé.
Katia Barré - Lycée LESAGE Vannes
Page 6
21. Second lien (‘relation’), seconde façon de procéder au choix :
Cliquer sur ‘Modifier Relations’.
Dans la petite fenêtre nouvellement apparue, cliquer sur ‘nouvelle relation’.
Dans la nouvelle fenêtre, faire les choix de relations (‘tables’) et de attributs (‘champs’) indiqués.
Valider (cliquer ‘OK’)
Puis ‘Appliquer l’intégrité référentielle’, et enfin ‘Créer’.
Katia Barré - Lycée LESAGE Vannes
Page 7
Tester des requêtes SQL simples sur les relations de notre base de données
Nous allons pouvoir faire des recherches dans la base de données en écrivant des requêtes SQL.
22. Premier exemple : recherche du lieu où se pratique la danse.
Il s’agit d’une recherche dans la table SPORT.
23. Dans ‘Créer’, choisir ‘Création de requête’, puis fermer la boîte de dialogue.
24. Clic droit sur l’onglet ‘requête1’,
Choisir ‘Mode SQL’
Taper la requête SQL,
Sauvegarder,
Choisir un nom pour la requête,
Valider ‘OK’.
Katia Barré - Lycée LESAGE Vannes
Page 8
25.
Clic droit sur l’onglet de la requête ‘lieu de la danse’,
puis choisir ‘Mode Feuille de données’.
Ou bien :
Cliquer sur ‘Exécuter’
26. On visualise le résultat sous forme de table :
27. À présent une requête plus complexe.
Il s’agit d’obtenir les Nom, Prénom des étudiants qui pratiquent la danse.
Créer → Création de requête →Définition des données → Taper la requête
SELECT ETUDIANT.Nom, ETUDIANT.Prénom
FROM (ETUDIANT INNER JOIN PRATIQUE ON ETUDIANT.CodeEtudiant = PRATIQUE.CodeEleve)
INNER JOIN SPORT ON PRATIQUE.CodeSport = SPORT.CodeSport
WHERE SPORT.NomSport = 'Danse';
→ Sauvegarde sous Nom_Prenom_Danse →Clic droit sur l’onglet ‘Nom_Prenom_Danse’ → Mode
Feuille de données → lecture de Table
Remarque : on ne sait pas de quelle ‘Katia Barré’ il s’agit… Il serait utile d’afficher le numéro d’étudiant
aussi.
À vous de jouer à présent !
Katia Barré - Lycée LESAGE Vannes
Page 9
Entraînement !
Créer une nouvelle relation (‘table’) FRAIS de clé primaire CodeFrais:
Créer les liens (‘relations’) suivants :
Obtenir le résultat de la jointure des relations ETUDIANT et FRAIS
SELECT *
FROM (ETUDIANT INNER JOIN FRAIS ON ETUDIANT.CodeEtudiant = FRAIS.CodeEtudiant ) ;
Katia Barré - Lycée LESAGE Vannes
Page 10
Obtenir le résultat de la jointure des relations ETUDIANT, PRATIQUE et SPORT
SELECT *
FROM ((ETUDIANT INNER JOIN PRATIQUE ON ETUDIANT.CodeEtudiant =PRATIQUE.CodeEleve )
INNER JOIN SPORT ON PRATIQUE.CodeSport = SPORT.CODESport);
Obtenir le nom et nombre de pratiquants par sport
SELECT SPORT.NomSport, COUNT(*) as NombrePratiquants
FROM SPORT INNER JOIN PRATIQUE ON SPORT.CodeSport =
PRATIQUE.CodeSport
GROUP BY SPORT.NomSport
ORDER BY COUNT(*);
Obtenir les montants de l’étudiant Katia Barré de Lyon :
SELECT FRAIS.Montant
FROM ETUDIANT INNER JOIN FRAIS
ON ETUDIANT.CodeEtudiant = FRAIS.CodeEtudiant
WHERE ETUDIANT.Nom = 'Barré' AND ETUDIANT.Prénom = 'Katia'
AND ETUDIANT.Ville = 'LYON';
Obtenir le montant total des frais de Katia Barré de Lyon :
SELECT SUM(FRAIS.Montant) as SOMME
FROM ETUDIANT INNER JOIN FRAIS
ON ETUDIANT.CodeEtudiant = FRAIS.CodeEtudiant
WHERE ETUDIANT.Nom = 'Barré' AND ETUDIANT.Prénom = 'Katia' AND
ETUDIANT.Ville = 'LYON';
Obtenir les Nom, Prénom, Montant total des frais (pour l’ensemble des étudiants)
SELECT ETUDIANT.Nom, ETUDIANT.Prénom, SUM(FRAIS.Montant) as SOMME
FROM ETUDIANT INNER JOIN FRAIS
ON ETUDIANT.CodeEtudiant = FRAIS.CodeEtudiant
GROUP BY ETUDIANT.Nom, ETUDIANT.Prénom, ETUDIANT.CodeEtudiant;
Ce n’est pas très lisible, car plusieurs étudiants portent le même nom. Prenons l’initiative d’afficher aussi le
numéro d’étudiant, et classons par ordre croissant de montant :
Katia Barré - Lycée LESAGE Vannes
Page 11
SELECT ETUDIANT.CodeEtudiant, ETUDIANT.Nom, ETUDIANT.Prénom, SUM(FRAIS.Montant) as SOMME
FROM ETUDIANT INNER JOIN FRAIS
ON ETUDIANT.CodeEtudiant = FRAIS.CodeEtudiant
GROUP BY ETUDIANT.Nom, ETUDIANT.Prénom, ETUDIANT.CodeEtudiant
ORDER BY SUM(FRAIS.Montant);
Obtenir les Nom, Prénom, Moyenne des frais pour l’ensemble des étudiants classés par
moyenne
SELECT ETUDIANT.CodeEtudiant, ETUDIANT.Nom, ETUDIANT.Prénom, AVG(FRAIS.Montant) as MOYENNE
FROM ETUDIANT INNER JOIN FRAIS
ON ETUDIANT.CodeEtudiant = FRAIS.CodeEtudiant
GROUP BY ETUDIANT.Nom, ETUDIANT.Prénom, ETUDIANT.CodeEtudiant
ORDER BY AVG(FRAIS.Montant);
Obtenir les noms de sports pratiqués par les étudiants avec le montant des frais , classés
par montant des frais.
SELECT SPORT.NomSport, SUM(FRAIS.Montant) as MONTANT
FROM (((SPORT INNER JOIN PRATIQUE ON SPORT.CodeSport =
PRATIQUE.CodeSport)
INNER JOIN ETUDIANT ON ETUDIANT.CodeEtudiant = PRATIQUE.CodeEleve)
INNER JOIN FRAIS ON FRAIS.CodeEtudiant = ETUDIANT.CodeEtudiant)
GROUP BY SPORT.NomSport
ORDER BY SUM(FRAIS.Montant) DESC;
Obtenir les noms des sports et montant des frais, dont les frais sont supérieurs à 1000
euros
SELECT SPORT.NomSport, SUM(FRAIS.Montant) as MONTANT
FROM (((SPORT INNER JOIN PRATIQUE ON SPORT.CodeSport = PRATIQUE.CodeSport)
INNER JOIN ETUDIANT ON ETUDIANT.CodeEtudiant = PRATIQUE.CodeEleve)
INNER JOIN FRAIS ON FRAIS.CodeEtudiant = ETUDIANT.CodeEtudiant)
GROUP BY SPORT.NomSport
HAVING SUM(FRAIS.Montant) > 1000
ORDER BY SUM(FRAIS.Montant);
Katia Barré - Lycée LESAGE Vannes
Page 12