Projet Animal : SQL

Présentation

Un vétérinaire souhaite enregistrer des informations concernant les animaux et leur propriétaires

Au début, il utilisait un classeur Excel, mais la souplesse d'une base de données est plus puissante. Il a donc constitué une base de données Acess contenant 3 tables (voir schémas relationnels).

Cependant, il ne sait pas faire de requêtes pour obtenir différents renseignements ou extraits statistiques.

Vous êtes donc chargé de répondre aux question qu'il se pose afin d'obtenir exactement le résultat recherché.

Vous pouvez télécharger la base de données au format Access (veto.mdb)ou mysql (veto1.sql à importer).

N'oubliez pas qu'internet est votre ami ...

A vous de jouer.

Utiliser Access

Access est un SGBD de Microsoft. Quelle que soit la version (je travaille encore en v2003) la page d'accueil est presque la même, les fonctions n'ont que peu évolué.

Dans la partie gauche de la fenêtre, on a une liste qui permet d'afficher les différents objets (tables, requêtes, ...) de la base.

Pour créer une requête, on utilise l'assistant de création dans lequel on n'importe pas de table, mais on passe en mode SQL, puisque c'est ce langage que nous allons travailler.

Les requêtes peuvent être lancées par l'icône en forme de !.

Le schéma relationnel, version Access, est accessible avec l'icône :

Schéma relationnel de la base

La base de données est architecturée de la façon suivante :

proprietaire(id, nom, ville)
- Clé primaire : id
espece(id, nom)
- Clé primaire : id
animal(id, nom, date_naiss, sexe, idEspece, idProprio)
- Clé primaire : id
- Clé étrangère : idEspece référence id dans espece
- Clé étrangère : idProprio référence id dans proprietaire

Top of page

Les questions (enfin!)

1ère série : simples

Liste (12 requêtes)

1-01 Quelle est la liste des enregistrements de la table animal (r101_liste des animaux) ?
1-02 Quelle est la liste des animaux mâles (Access: male=-1 ; mysql : sexe="M")
1-03 Quelle est la liste des noms des animaux ?
1-04 Quelle est la liste des animaux mâles nés après 2010 ?
1-05 Quelle est la liste des animaux mâles de taille entre 30 et 50cm ?
1-11 Quelle est la liste des propriétaires ?
1-12 Quelle est la liste des propriétaires de Strasbourg ?
1-13 Quelle est la liste des propriétaires de Strasbourg ou de Molsheim ?
1-14 Quelle est la liste des propriétaires de Strasbourg, de Molsheim et Saverne ?
1-21 Quelle est la liste des espèces ?
1-22 Quelle est la liste des propriétaires, triée par ville et nom ?
1-23 Quelle est la liste des noms et tailles des animaux, classé par taille décroissante ?

Mots clés utilisés : SELECT, FROM, WHERE, ORDER BY, DESC, OR, AND, BETWEEN, IN et les opérateurs de comparaison

2ème série : moyennes

Si possible, respecter l'ordre des requêtes, il permet d'être progressif dans la création des requêtes.

Liste et regroupements (12 requêtes)

2-01 Combien y a-t-il d'animaux ?
2-02 Combien y a-t-il d'animaux dont la taille ne dépasse pas 30cm ?
2-03 Quels sont les noms des propriétaires de chaque animal (indiquer l'id, le nom de l'animal et celui du propriétaire) ?
2-04 Donner la liste des noms d'animaux, suivi du nom de l'espèce puis du nom du propriétaire.
2-05 Quelle est la liste espèces par propriétaire (nom proprio et nom espèce) ?
2-11 Combien y a-t-il de propriétaires ?
2-12 Combien y a-t-il de propriétaires par ville ?
2-21 Donner le nom de l'espece et le nom des animaux.
2-22 Combien y a-t-il d'animaux par espèce ?
2-31 Quelle est la taille moyenne des animaux ?
2-32 Quelle est la taille moyenne des animaux par id d'espèce ?
2-33 Combien d'animaux mâles sont nés après 2010 ?

Mots clés utilisés : ceux de la série 1 et : DISTINCT, COUNT(*), AVG, GROUP BY

3ème série : difficiles

La difficulté réside dans la requête ... ou dans la question!

Imbrication : utilisation du résultat d'une sous-requête pour obtenir une valeur ou une liste servant à une comparaison dans la requête cadre.

3-01 Combien y a-t-il d'animaux de strasbourg (simple) ?
3-02 Les propriétaires strasbourgeois n'ont ils qu'un seul animal (subtilité de la question) ?
3-03 Quels sont les animaux (id et nom) dont la taille est supérieure à la moyenne des tailles des animaux (imbrication simple)?
3-04 Quels sont les propriétaires ... qui n'ont pas d'animal (imbrication astucieuse) ?
3-05 Combien y a-t-il de propriétaires réels par ville (compatge avec regroupement et imbrication dans le where) ?
3-06 Quels sont les id et nom des propriétaires strasbourgeois qui ont plus d'un animal (HAVING, plusieurs syntaxes possibles)?

Top of page

Corrigés ???

Pas si facile à voir ...

Série 1 : simples

Série 2 : moyennes

2-01 Combien y a-t-il d'animaux ?
SELECT COUNT(*) FROM animal
2-02 Combien y a-t-il d'animaux dont la taille ne dépasse pas 30cm ?
SELECT COUNT(*) FROM animal WHERE taille<=30
2-03 Quels sont les noms des propriétaires de chaque animal (indiquer l'id, le nom de l'animal et celui du propriétaire) ?
SELECT animal.id, animal.nom, proprietaire.nom FROM animal, proprietaire WHERE animal.idProprio=proprietaire.id

/!\ Jointure et bien indiquer le nom de la table devant les champs homonymes dans les tables
2-04 Donner la liste des noms d'animaux, suivi du nom de l'espèce puis du nom du propriétaire.
SELECT animal.nom, espece.nom, proprietaire.nom FROM animal, espece, proprietaire
WHERE animal.idEspece=espece.id
AND animal.idProprio=proprietaire.id

Deux jointures ! Nombre de jointure = nombre de tables - 1 (= nombre de virgules dans FROM). Ici : 3 tables => 2 virgules = 2 jointures !
2-05 Quelle est la liste espèces par propriétaire (nom proprio et nom espèce) ?
SELECT DISTINCT proprietaire.nom, espece.nom FROM proprietaire, animal, espece
WHERE animal.idEspece=espece.id
AND animal.idProprio=proprietaire.id
ORDER BY proprietaire.nom

C'est la même qu'avant, il y a juste le tri en plus pour préparer la suivante ...
2-11 Combien y a-t-il de propriétaires ?
SELECT COUNT(*) FROM proprietaire
2-12 Combien y a-t-il de propriétaires par ville ?
SELECT ville, COUNT(*) FROM proprietaire GROUP BY ville
2-21 Donner le nom de l'espece et le nom des animaux.
SELECT DISTINCT espece.nom, animal.nom FROM espece, animal
WHERE espece.id=animal.idEspece
ORDER BY espece.nom

Le tri n'est pas obligatoire
2-22 Combien y a-t-il d'animaux par espèce ?
SELECT DISTINCT espece.nom, count(*) FROM espece, animal
WHERE espece.id=animal.idEspece
GROUP BY espece.nom
2-31 Quelle est la taille moyenne des animaux ?
SELECT AVG(taille) FROM animal
2-32 Quelle est la taille moyenne des animaux par id d'espèce ?
SELECT idEspece, AVG(taille) FROM animal GROUP BY idEspece
2-33 Combien d'animaux mâles sont nés après 2010 ?
SELECT COUNT(*) FROM animal WHERE sexe="M" AND date_naiss>'2010-12-31'
ou
SELECT COUNT(*) FROM animal WHERE sexe="M" AND YEAR(date_naiss)>2010

Astuce : on utilise la fonction YEAR() pour extraire l'année de la date de naissance. Il est aussi possible d'extraire le jour ou le mois (DAY() ou MONTH())

Série 3 : difficiles (très)

Top of page