344 views
--- title: Espèces au zoo tags : NSI, Term, bac, SQL --- # Espèces au zoo > D'après 2023, Centres Étrangers, J2, Ex. 2 L’énoncé de cet exercice utilise les mots clefs du langage SQL suivants : ```sql SELECT, FROM, WHERE, JOIN ON, UPDATE SET, COUNT, INSERT INTO VALUES, ORDER BY ``` La clause `ORDER BY` suivie d'un attribut permet de trier les résultats par ordre croissant des valeurs de l'attribut. `SELECT COUNT (*)` renvoie le nombre de lignes d'une requête. Un zoo souhaite pouvoir suivre ses animaux et ses enclos. Tous les représentants d'une espece sont réunis dans un même enclos. Plusieurs espèces, si elles peuvent cohabiter ensemble, pourront partager le même enclos. Il crée une base de données utilisant le langage SQL avec une relation (ou table) `animal` qui recense chaque animal du zoo. Vous trouverez un extrait de cette relation ci-dessous (les unités des attributs `age`, `taille` et `poids` sont respectivement ans, m et kg) : * Relation **animal** | `id_animal` | `nom` | `age` | `taille` | `poids` | `nom_espece` | |:-----------:|:-----:|:-----:|:--------:|:-------:|:----------------:| | 145 | Romy | 18 | 2.3 | 130 | tigre du Bengale | | 52 | Boris | 30 | 1.10 | 48 | bonobo | | ... | ... | ... | ... | ... | ... | | 225 | Hervé | 10 | 2.4 | 130 | lama | | 404 | Moris | 6 | 1.70 | 100 | panda | | 678 | Léon | 4 | 0.30 | 1 | varan | Il crée la relation `enclos` dont vous trouverez un extrait ci-dessous, l'unité de surface étant m$^2$. * Relation **enclos** | `num_enclos` | `ecosysteme` | `surface` | `struct` | `date_entretien` | |:------------:|:---------------:|:---------:|:--------:|:----------------:| | 40 | banquise | 50 | bassin | 04/12/2024 | | 18 | forêt tropicale | 200 | vitré | 05/12/2024 | | ... | ... | | | | | 24 | savane | 300 | clôture | 04/12/2024 | | 68 | désert | 2 | vivarium | 05/12/2024 | Il crée également la relation `espece` dont vous trouverez un extrait ci-dessous : * Relation **espece** | `nom_espece` | `classe` | `alimentation` | `num_enclos` | |:----------------:|:----------:|:--------------:|:------------:| | impala | mammifères | herbivore | 15 | | ara de Buffon | oiseaux | granivore | 77 | | ... | ... | ... | ... | | tigre du Bengale | mammifères | carnivore | 18 | | caïman | reptiles | carnivore | 45 | | manchot empereur | oiseaux | carnivore | 40 | | lama | mammifères | herbivore | 13 | **1.** Cette question porte sur la lecture et l'écriture de requêtes SQL simples. **1.a** Écrire le résultat de la requête ci-dessous. ```sql SELECT age, taille, poids FROM animal WHERE nom = 'Moris'; ``` :::success :::spoiler Cliquer pour voir la réponse | `age` | `taille` | `poids` | |:-----:|:--------:|:-------:| | 6 | 1.70 | 100 | ::: **1.b** Écrire une requête qui permet d'obtenir le nom et l'âge de tous les animaux de l'espèce bonobo, triés du plus jeune au plus vieux. :::success :::spoiler Cliquer pour voir la réponse ```sql SELECT nom, age FROM animal WHERE nom_espece = 'bonobo' ORDER BY age ASC; ``` ::: **2.** Cette question porte sur le schéma relationnel. **2.a** Citer, en justifiant, la clé primaire et la clé étrangère de la relation `espece`. :::success :::spoiler Cliquer pour voir la réponse La clé primaire est `nom_espece`. Elle est spécifique à chaque enregistrement. La clé étrangère est `num_enclos`, qui fait référence à la clé primaire `num_enclos` de la relation `enclos`. ::: **2.b**. Donner le modèle relationnel de la base de données du zoo. On soulignera les clés primaires et on fera précéder les clés étrangères d'un #. :::success :::spoiler Cliquer pour voir la réponse **animal** (<u>id_animal</u> (INT), nom (TEXT), age (INT), taille (FLOAT), poids (INT), #nom_espece (TEXT)) **espece** (<u>nom_espece</u> (INT), classe (TEXT), alimentation (TEXT), #num_enclos (INT)) **enclos** (<u>num_enclos</u> (INT), ecosysteme (TEXT), surface (INT), struct (TEXT), date_entretien (DATE)) ::: **3.** Cette question porte sur les modifications d'une table. L'espèce "ornithorynque" a été entrée dans la base comme étant de la classe des oiseaux alors qu'il s'agit d'un mammifère. **3.a** Écrire une requête qui corrige cette erreur dans la table `espece`. :::success :::spoiler Cliquer pour voir la réponse ```sql UPDATE espece SET classe = "mammifère" WHERE nom_espece = "ornithorynque" ``` ::: Le couple de lamas du zoo vient de donner naissance au petit lama nommé "Serge" qui mesure 80 cm et pèse 30 kg. **3.b** Écrire une requête qui permet d'enregistrer ce nouveau venu au zoo dans la base de données, sachant que les clés primaires de 1 à 178 sont déjà utilisées. :::success :::spoiler Cliquer pour voir la réponse ```sql INSERT INTO animal VALUES (179, "Serge", 0, 80, 30, "lama") ``` ::: **4.** Cette question porte sur la jointure entre deux tables. **4.a** Recopier sur votre feuille la requête SQL et compléter les ... afin de recenser le nom et l'espèce de tous les animaux carnivores vivant en vivarium dans le zoo. ```sql SELECT ... FROM animal JOIN espece ON ... JOIN enclos ON ... WHERE enclos.struct = 'vivarium' and ... ; ``` :::success :::spoiler Cliquer pour voir la réponse ```sql SELECT nom, nom_espece FROM animal JOIN espece ON animal.nom_espece = espece.nom_espece JOIN enclos ON espece.num_enclos = enclos.num_enclos WHERE enclos.struct = 'vivarium' and espece.alimentation = 'carnivore' ; ``` ::: On souhaite connaître le nombre d'animaux dans le zoo qui font partie de la classe des oiseaux. **4.b** Écrire la requête qui permet de compter le nombre d'oiseaux dans tout le zoo. :::success :::spoiler Cliquer pour voir la réponse ```sql SELECT COUNT (*) FROM ( SELECT animal.id_animal FROM animal JOIN espece ON animal.nom_espece = espece.nom_espece WHERE espece.classe='oiseaux' ); ``` Autre proposition : ```sql SELECT COUNT(animal.id_animal) FROM animal JOIN espece ON animal.nom_espece = espece.nom_espece WHERE espece.classe = 'oiseaux'; ``` :::