À l'école de musique

[30 min]

Une école de musique souhaite gérer les inscriptions aux différentes classes d'instrument et aux orchestres à l'aide d'une base de données.

Schéma conceptuel

Famille (#nom:chaîne)
Instrument (#lib:chaîne, lib_long:chaîne, famille=>Famille(nom))
Professeur (#num:entier, nom:chaîne, prénom:chaîne, tâche:chaîne)
Eleve (#num:entier, nom:chaîne, prénom:chaîne, date_naissance:date, inst=>Instrument(lib))
Orchestre (#lib:chaîne, min:entier, max:entier)
Enseigne (#num=>Professeur(num), #lib=>Instrument(lib))
Participe (#num=>Eleve(num), #lib=>Orchestre(lib))

Créer la base de données permettant de gérer les élèves et les instruments (sans les familles). Insérer des données exemple.

CREATE TABLE instrument (
lib VARCHAR PRIMARY KEY,
lib_long VARCHAR
);
CREATE TABLE eleve (
num INTEGER PRIMARY KEY,
nom VARCHAR NOT NULL,
prenom VARCHAR,
date_naissance DATE,
inst VARCHAR REFERENCES instrument(lib)
);
INSERT INTO instrument (lib) VALUES ('Violon');
INSERT INTO instrument (lib) VALUES ('Guitare');
INSERT INTO eleve (num, nom, inst) VALUES (1, 'Jimmy', 'Guitare');
INSERT INTO eleve (num, nom, inst) VALUES (2, 'Robby', 'Guitare');

Écrire le code SQL qui permet d'afficher la liste des élèves (nom et prénom) triés par instrument.

SELECT E.nom, E.prenom, E.inst 
FROM Eleve E 
ORDER BY E.inst ;

Écrire le code SQL qui permet d'afficher la liste des instruments avec le nombre d'élèves associés (on affichera même les instruments dont personne ne joue).

SELECT I.lib, COUNT(E.inst)
FROM Instrument I LEFT JOIN Eleve E ON E.inst=I.lib
GROUP BY lib ;

Écrire le code SQL qui permet d'afficher la liste des instruments avec le nombre de places restantes par instruments, sachant qu'il y a 20 places disponibles par instrument.

SELECT I.lib, 20 - COUNT(E.inst)
FROM Instrument I LEFT JOIN Eleve E ON E.inst=I.lib
GROUP BY lib ;

Compléter le code PHP ci-dessous pour afficher le nombre de places disponibles par instruments listés par leur libellé long.

On utilisera une requête paramétrée.

<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<title>École de musique</title>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	</head>
	
	<body>
	<h2>Liste des places disponibles par instrument</h2>
	<table border="1">
	<tr> <td><b>Instruments</b></td> <td><b>Places disponibles</b></td> </tr>
	
	<?php
	/* Nombre maximum d'élèves autorisé par instrument */
    $max_eleves = 20; 
    
    /* Nombre maximum d'élèves autorisé par instrument */
    $connexion = new PDO('pgsql:host=localhost;port=5432;dbname=musique', 'me', 'mypassword');	
    
    /** Préparation et exécution de la requête **/
    $sql = "SELECT I.lib AS lib, ? - COUNT(E.inst) AS dispo ...";
    $resultset = $connexion->prepare($sql);
    $resultset->bindParam(1, ...);
    $resultset->execute();            
        
    while ($row = $resultset->fetch(PDO::FETCH_ASSOC)) {
        echo "<tr>";
        echo "<td>" . $row[...] . "</td>";
        echo "<td>" . $row[...] . "</td>";
        echo "</tr>";
        }
        
    /** Déconnexion **/
    $connexion=null;    
    ?>
    
    </table>
    </body>
</html>

Requêtes préparées

<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<title>École de musique</title>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	</head>
	
	<body>
	<h2>Liste des places disponibles par instrument</h2>
	<table border="1">
	<tr> <td><b>Instruments</b></td> <td><b>Places disponibles</b></td> </tr>
	
	<?php
	/* Nombre maximum d'élèves autorisé par instrument */
    $max_eleves = 20; 
    
    /* Nombre maximum d'élèves autorisé par instrument */
    $connexion = new PDO('pgsql:host=localhost;port=5432;dbname=musique', 'me', 'mypassword');	
    
    /** Préparation et exécution de la requête **/
    $sql = "SELECT I.lib AS lib, ? - COUNT(E.inst) AS dispo 
            FROM Instrument I LEFT JOIN Eleve E ON E.inst=I.lib 
            GROUP BY lib";
    $resultset = $connexion->prepare($sql);
    $resultset->bindParam(1, $max_eleves);
    $resultset->execute();            
        
    while ($row = $resultset->fetch(PDO::FETCH_ASSOC)) {
        echo "<tr>";
        echo "<td>" . $row['lib'] . "</td>";
        echo "<td>" . $row['dispo'] . "</td>";
        echo "</tr>";
        }
        
    /** Déconnexion **/
    $connexion=null;    
    ?>
    
    </table>
    </body>
</html>

AccueilExercices > Exercice : À l'école de musique< PrécédentSuivant >