logo Pierre AULAS
Php/MySql - Examen du 20 juin 2007
 Parcours  |   Réalisations  |   Cours  |   Chroniques  |   Divers
Élections parisiennes
Librairie
Élections
Correction Librairie
Correction Élections
Carnet de note
Météo France
Boursicotage
Diaporama

Examen php/mysql du 20 juin 2007

L'examen comprend deux parties :

La première partie doit être rendue sous forme de fichier ".txt" portant votre nom en majuscule.

Exemple :

La seconde partie sera rendue sous forme d'un unique fichier ".php" portant votre nom en majuscule.

Exemple :

À l'heure dite, vous m'enverrez un mail contenant en pièce-jointe ces deux fichiers sous forme d'un ".zip" portant votre nom en majuscule.

Exemple :

Ps : celui qui m'envoie un fichier nommé "TARTEMPION.zip" ne sera pas corrigé.

Vous avez le droit d'utiliser votre cours et internet. Les outils de communication interpersonnels sont interdits (téléphone, messagerie, bouts de papier, yeux qui louchent, etc.).

Requêtes SQL : une histoire de librairie (8 points)

Toutes les requêtes qui vous sont demandées portent sur une base de donnée dont le schéma est le suivant :

MCD de la base

Écrire les requêtes MySQL qui répondent aux questions suivantes :

1. Les titres des livres qui commencent par la lettre W. (1/2 point)

2. La liste des livres avec leur éditeur. (1/2 point)

3. Les titres des livres de science-fiction. (1/2 point)

4. Les titres des livres coûtant au moins 20 euros HT avec leur prix TTC (la TVA est de 5,5%). (1/2 point)

5. Les livres de science-fiction coutant moins de 20 euros HT avec leur prix HT. (1/2 point)

6. Les titres des livres policiers ou SF coutant moins de 20 euros HT. (1/2 point)

7. Les auteurs (nom et prenom) par ordre alphabétique de nom. (1/2 point)

8. Les auteurs et leurs éditeurs (sans doublon). (1/2 point)

9. Le nombre de livres référencés dans la base et le prix moyen. (1/2 point)

10. Le nombre de livre par genre. (1/2 point)

11. Afficher les genres qui contiennent au moins 20 livres: (1/2 point)

12. Afficher le titre des livres écrits par 2 auteurs et plus. (1/2 point)

13. Afficher les titres de tous les livres, avec éventuellement leur genre (certains n'ont pas de genre) (1 point)

14. Ecrire la question qui conduit à écrire cette requête: (1 point)

SELECT nom, prenom
FROM auteur a, ecrit e, livre l, genre g
WHERE a.id=e.id_auteur
AND e.id_livre = l.id
AND l.id_genre = g.id
AND genre IN ('SF', 'Histoire');

Correction Librairie

Haut de page

Cas pratique : les élections présidentielles à Paris (12 points)

L'exercice consiste à permettre à un utilisateur de visualiser le résultat des élections présidentielles à Paris pour les élections 2002 et 2007.

Vous pouvez voir le fonctionnement ici.

Vous pouvez charger la base de donnée sur laquelle vous allez travailler ici.

Ce fichier permet la création de la base et l'insertion de données pour les 4 premiers arrondissement de Paris pour les éléctions de 2002 et 2007. Charge à vous de la mettre en place sur votre poste de travail. La correction se fera avec cette base.

Le schéma de cette base (limité mais votre temps l'est aussi) est le suivant :

MCD de la base Election
Haut de page

Votre travail consiste à réaliser un formulaire qui ait l'allure suivante :

Le premier menu déroulant permet de sélectionner les scrutins (il n'y en a que deux disponibles dans la base):

Les cases à cocher permettent de préciser les tours à afficher : le premier, le second, voire les deux.

Le second menu déroulant permet de sélectionner les circonscriptions (seules les 4 premières contiennent des résultats) :

Haut de page

L'affichage des résultats devra présenter :

L'exemple ci-dessous montre ce que cela peut donner :

Le barême est le suivant :

Voir le fonctionnement

Correction Élections

Haut de page

Correction Librairie

MCD de la base

1. Les titres des livres qui commencent par la lettre W.

SELECT titre
FROM livre
WHERE titre LIKE 'W%';

2. La liste des livres avec leur éditeur.

SELECT titre, nom
FROM livre, editeur e
WHERE id_editeur = e.id;

3. Les titres des livres de science-fiction

SELECT titre
FROM livre, genre g
WHERE id_genre=g.id
AND genre='science-fiction';

4. Les titres des livres coutant au moins 20 euros HT avec leur prix TTC (la TVA est de 5,5%).

SELECT titre, prixHT*1.055 AS 'Prix TTC'
FROM livre
WHERE prixHT >= 20;

MCD de la base

5. Les livres de science-fiction coutant moins de 20 euros HT avec leur prix HT.

SELECT titre, prixHT
FROM livre, genre g
WHERE id_genre=g.id
AND genre='science-fiction'
AND prixHT < 20;

6. Les titres des livres policiers ou SF coutant moins de 20 euros HT.

SELECT titre
FROM livre, genre g
WHERE id_genre=g.id
AND genre IN ('science-fiction','polar')
AND prixHT < 20;

7. Les auteurs (nom et prenom) par ordre alphabétique de nom.

SELECT nom, prenom
FROM auteur
ORDER BY nom;

8. Les auteurs et leurs éditeurs (sans doublon).

SELECT DISTINCT a.nom, prenom, ed.nom
FROM auteur a, ecrit e, livre l, editeur ed
WHERE a.id=e.id_auteur
AND e.id_livre=l.id
AND l.id_editeur = ed.id;

MCD de la base

9. Le nombre de livres référencés dans la base et le prix moyen.

SELECT COUNT(*) AS 'Nombre de livres total', AVG(prixHT) AS 'Prix moyen'
FROM livre;

10. Le nombre de livre par genre.

SELECT id_genre, COUNT(*) AS 'Nombre de livres'
FROM livre
GROUP BY id_genre;

11. Afficher les genres qui contiennent au moins 20 livres:

SELECT id_genre, genre, COUNT(*) AS 'Nombre de livres'
FROM livre, genre g
WHERE id_genre=g.id
GROUP BY id_genre
HAVING COUNT(*) >= 20;

12. Afficher le titre des livres écrits par 2 auteurs et plus.

SELECT l.titre
FROM auteur a, livre l, ecrit e
WHERE a.id=e.id_auteur
AND e.id_livre=l.id
GROUP BY l.id
HAVING COUNT(*) > 1;

MCD de la base

13. Afficher les titres de tous les livres, avec éventuellement leur genre (certains n'ont pas de genre)

SELECT titre, genre
FROM livre AS l LEFT OUTER JOIN genre AS g
ON g.id = l.id_genre;

14. Afficher nom et prenom des auteurs ayant écrit un livre de genre SF ou science humaine.

SELECT nom, prenom
FROM auteur a, ecrit e, livre l, genre g
WHERE a.id=e.id_auteur
AND e.id_livre = l.id
AND l.id_genre = g.id
AND g.genre IN ('science-fiction', 'Sciences humaines');

Retour à l'énoncé

Haut de page

Correction Élections

Voir le fonctionnement

On commence par écrire une grosse fonction qui écrit les résultats des éléctions en fonction de l'année, du tour et de la circonscription indiqués en paramètre. Pour bien faire les choses, il faudrait utiliser un return plutôt que des print. Mais bon, sur le principe, il faut passer par une fonction.

NOTA-BENE : les fichiers identifiant.php contient vos login, mdp, nom de la base et du serveur, le fichier connexion.php quelques fonctions de connexion classiques comme seconnecter, faireRequete, passerObjetSuivant.

<?php

include("identifiant.php");
include("connexion.php");

$connexion = seconnecter(SERVEUR, BASE, LOGIN, MDP);

function afficherResultat($pAnnee, $pTour, $pCirc){

$connexion = seconnecter(SERVEUR, BASE, LOGIN, MDP);

print "<TABLE width=\"400px\">";

if ($pTour == 1)
$tour = 'premier';
else
$tour = 'second';

if ($pCirc == 1)
$circ = 'er';
else
$circ = 'ème';

print "<TR><TD colspan=\"3\"><B>Résultats du $tour tour des présidentielles $pAnnee dans le $pCirc $circ arrondissement de Paris</B>";
print "<TR><TD colspan=\"3\">&nbsp;";

$requete = "SELECT SUM(nb) as 'somme' "
."FROM scrutin s, candidat c, vote v, circonscription cc "
."WHERE s.id=v.id_scrutin "
."AND c.id=v.id_candidat "
."AND cc.id=v.id_circonscription "
."AND s.tour=$pTour "
."AND cc.id=$pCirc "
."AND YEAR(s.date)=$pAnnee";
$resultat = faireRequete($requete, $connexion);
$objet = passerObjetSuivant($resultat);
$somme = $objet->somme;

$requete = "SELECT c.nom, c.prenom, nb, s.tour, cc.nom as 'nomCirc' "
."FROM scrutin s, candidat c, vote v, circonscription cc "
."WHERE s.id=v.id_scrutin "
."AND c.id=v.id_candidat "
."AND cc.id=v.id_circonscription "
."AND s.tour=$pTour "
."AND cc.id=$pCirc "
."AND YEAR(s.date)=$pAnnee";
$resultat = faireRequete($requete, $connexion);
while($objet = passerObjetSuivant($resultat))
{
print "<TR><TD>".utf8_encode($objet->nom)." ".utf8_encode($objet->prenom);
print "<TD>".$objet->nb;
print "<TD>";
printf ("%.1f %%", ($objet->nb/$somme)*100);
}

print "<TR><TD>Inscrits: ";
print "<TD>$somme<TD>";

print "</TABLE>";
}
?>

Ici on s'occupe uniquement du formulaire et de récupérer les choix de l'utilisateur afin de les passer à la grosse fonction écrite précédement.

Élections pr&eacute;sidentielles &agrave; Paris:<BR><BR>

<TABLE>

<FORM method="get">

<TR><TD align="right">S&eacute;lectionnez un scrutin:&nbsp;&nbsp;&nbsp;
<TD><SELECT name="date">
<?php
$chaine = "";
$requete = "SELECT DISTINCT YEAR(date) AS 'date' FROM scrutin";
$select = (isset($_GET['date'])?$_GET['date']:0);
$resultat = faireRequete($requete, $connexion);
while($objet = passerObjetSuivant($resultat))
{
$chaine .= "<OPTION value=\"".$objet->date."\"";
if ($objet->date==$select)
$chaine .= "SELECTED";
$chaine .= ">Présidentielles ".$objet->date;
}
print $chaine;
?>
</SELECT>

<TR><TD align="right">Afficher le:&nbsp;&nbsp;&nbsp;
<TD><INPUT TYPE="checkbox" NAME="tour1" <? if (isset($_GET['tour1'])) print ($_GET['tour1'])?"CHECKED":""; ?>> 1er tour
<TR><TD>
<TD><INPUT TYPE="checkbox" NAME="tour2" <? if (isset($_GET['tour2'])) print ($_GET['tour2'])?"CHECKED":""; ?>> 2nd tour

<TR><TD align="right">S&eacute;lectionnez une circonscription:&nbsp;&nbsp;&nbsp;
<TD><SELECT name="circ">

<?php
$chaine = "";
$requete = "SELECT id, nom FROM circonscription";
$select = (isset($_GET['circ'])?$_GET['circ']:0);
$resultat = faireRequete($requete, $connexion);
while($objet = passerObjetSuivant($resultat))
{
$chaine .= "<OPTION value=\"".$objet->id."\"";
if ($objet->id==$select)
$chaine .= "SELECTED";
$chaine .= ">".$objet->nom;
}
print $chaine;
?>
</SELECT>

<TR><TD><TD>&nbsp;
<TR><TD><TD><INPUT type="submit" value="Afficher les r&eacute;sultats">
</FORM>

</TABLE>

Enfin on s'occupe d'afficher les résultats demandés par l'utilisateur. Comme la grosse fonction afficherResultat a été rédigée une fois pour toute au début de mon script, cette partie du code est nettement plus facile à écrire.

<TABLE>
<TR>
<TD>
&nbsp;
</TD>
</TR>
<TR>
<TD>
<?php
if (isset($_GET['tour1']))
print afficherResultat($_GET['date'], 1, $_GET['circ']);
?>
</TD>
<TD valign="top">
<?php
if (isset($_GET['tour2']))
print afficherResultat($_GET['date'], 2, $_GET['circ']);
?>
</TD>
</TR>
</TABLE>

Retour à l'énoncé

Haut de page
Plan du site  |  Mentions légales  |  Crédits  |  Aide