Exercice : À l'école de musique [Requêtes SQL avec PHP et PostgreSQL]

À 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.

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))

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))

Question

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.

Solution

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');

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');

Question

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

Solution

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

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

Question

É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).

Solution

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

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

Question

É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.

Solution

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

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

Question

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>

<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>

Indice

Requêtes préparées

Solution

<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>

<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>