PHP 5 Cours et exercices

Chapitre 14: Le langage SQL et phpMyAdmin

Sauf indication contraire, toutes les opérations de sélection des exercices sont à réaliser sur la base magasin.

Exercice 1

Créer une base nommée voitures. Créer ensuite les tables de la base voitures selon le modèle logique défini dans les exercices du chapitre 13. Omettre volontairement certaines colonnes et faire volontairement quelques erreurs de type de colonne. Une fois les tables créées, ajouter les colonnes manquantes et corriger les erreurs. Vérifier la structure de chaque table. Nous utilisons bien sûr phpMyAdmin pour créer la base puis les tables. 1. Création de la table personne (en omettant volontairement le champ codepostal)

CREATE TABLE `proprietaire` (
`id_pers` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nom` VARCHAR( 30 ) NOT NULL ,
`prenom` VARCHAR( 30 ) NOT NULL ,
`adresse` VARCHAR( 50 ) NOT NULL ,
`ville` VARCHAR( 40 ) NOT NULL ,
PRIMARY KEY ( `id_pers` )
) TYPE = MYISAM ;

Nous ajoutons le champ codepostal « oublié » lors de la création de la table.

ALTER TABLE `proprietaire` ADD `codepostal` MEDIUMINT( 5 ) UNSIGNED
NOT NULL ;

Nous modifions le type du champ id_pers pour avoir un éventail de valeurs plus grand.

ALTER TABLE `proprietaire` CHANGE `id_pers` `id_pers` MEDIUMINT
UNSIGNED NOT NULL AUTO_INCREMENT

2. Création de la table cartegrise

CREATE TABLE `cartegrise` (
`id_pers` MEDIUMINT UNSIGNED NOT NULL ,
`immat` VARCHAR( 6 ) NOT NULL ,
`datecarte` DATE NOT NULL ,
PRIMARY KEY ( `id_pers` , `immat` )
);

3. Nous créons la table voiture.

CREATE TABLE `voitures` (
`immat` VARCHAR( 6 ) NOT NULL ,
`id_modele` VARCHAR( 10 ) NOT NULL ,
`couleur` ENUM( 'claire', 'moyenne', 'foncée' ) NOT NULL ,
`datevoiture` DATE NOT NULL ,
PRIMARY KEY ( `immat` )
);

4. Nous créons la table modele

CREATE TABLE `modele` (
`id_modele` VARCHAR( 10 ) NOT NULL ,
`modele` VARCHAR( 30 ) NOT NULL ,
`carburant` ENUM( 'essence', 'diesel', 'gpl', 'électrique' ) NOT
NULL ,
PRIMARY KEY ( `id_modele` )
);

Exercice 2

Exporter les tables de la base voitures dans des fichiers SQL. Nous obtenons les fichiers suivants :
1. Le fichier proprietaire.sql :

-- Base de données: `voitures`
-- Structure de la table `proprietaire`
CREATE TABLE `proprietaire` (
`id_pers` mediumint(8) unsigned NOT NULL auto_increment,
`nom` varchar(30) NOT NULL default '',
`prenom` varchar(30) NOT NULL default '',
`adresse` varchar(50) NOT NULL default '',
`ville` varchar(40) NOT NULL default '',
`codepostal` mediumint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id_pers`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

2. Le fichier cartegrise.sql :

-- Base de données: `voitures`
-- Structure de la table `cartegrise`
--
CREATE TABLE `cartegrise` (
`id_pers` mediumint(8) unsigned NOT NULL default '0',
`immat` varchar(6) NOT NULL default '',
`datecarte` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id_pers`,`immat`)
) TYPE=MyISAM;

3. Le fichier voiture.sql

-- Base de données: `voitures`
-- Structure de la table `voitures`
--
CREATE TABLE `voiture` (
`immat` varchar(6) NOT NULL default '',
`id_modele` varchar(10) NOT NULL default '',
`couleur` enum('claire','moyenne','foncée') NOT NULL default
'claire',
`datevoiture` date NOT NULL default '0000-00-00',
PRIMARY KEY (`immat`)
) TYPE=MyISAM;

4. Le fichier modele.sql

-- Base de données: `voitures`
-- Structure de la table `modele`
--
CREATE TABLE `modele` (
`id_modele` varchar(10) NOT NULL default '',
`modele` varchar(30) NOT NULL default '',
`carburant` enum('essence','diesel','gpl','électrique') NOT NULL
default 'essence',
PRIMARY KEY (`id_modele`)
) TYPE=MyISAM;

Exercice 3

Supprimer toutes les tables de la base voitures.
Le code SQL est le suivant :

DROP TABLE `proprietaire`
DROP TABLE `cartegrise`
DROP TABLE `voiture`
DROP TABLE `modele`

Exercice 4

Recréer les tables de la base voitures en utilisant les fichiers SQL précédents.
Pour recréer avec phpMyAdmin, les tables détruites, choisir successivement la base, puis l’onglet « SQL », « Emplacement du fichier texte », « Parcourir » pour désigner l’emplacement du fichier .sql, et enfin « Exécuter ». Les tables sont alors recréées l’une après l’autre.
Dans l’exercice 2, nous avions également la possibilité d’exporter l’ensemble de la base voitures en un seul fichier .sql. Nous aurions obtenu alors le fichier voitures.sql suivant :

	-- Base de données: `voitures`
-- Structure de la table `cartegrise`
--
CREATE TABLE `cartegrise` (
`id_pers` mediumint(8) unsigned NOT NULL default '0',
`immat` varchar(6) NOT NULL default '',
`datecarte` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id_pers`,`immat`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `modele`
--
CREATE TABLE `modele` (
`id_modele` varchar(10) NOT NULL default '',
`modele` varchar(30) NOT NULL default '',
`carburant` enum('essence','diesel','gpl','électrique') NOT NULL
default 'essence',
PRIMARY KEY (`id_modele`)
) TYPE=MyISAM;
-- --------------------------------------------------------
--
-- Structure de la table `proprietaire`
--
CREATE TABLE `proprietaire` (
`id_pers` mediumint(8) unsigned NOT NULL auto_increment,
`nom` varchar(30) NOT NULL default '',
`prenom` varchar(30) NOT NULL default '',
`adresse` varchar(50) NOT NULL default '',
`ville` varchar(40) NOT NULL default '',
`codepostal` mediumint(5) unsigned NOT NULL default '0',
PRIMARY KEY (`id_pers`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Structure de la table `voiture`
--
CREATE TABLE `voiture` (
`immat` varchar(6) NOT NULL default '',
`id_modele` varchar(10) NOT NULL default '',
`couleur` enum('claire','moyenne','foncée') NOT NULL default
'claire',
`datevoiture` date NOT NULL default '0000-00-00',
PRIMARY KEY (`immat`)
) TYPE=MyISAM;

Exercice 5

Insérer des données dans la table proprietaire de la base voitures puis en vérifier la bonne insertion.
Exemple de code d’insertion :

INSERT INTO `proprietaire` ( `id_pers` , `nom` , `prenom` ,`adresse` , `ville` , `codepostal`)
VALUES ('', 'Zouk', 'Julia', '56 Boulevard Nez', 'Paris', '75011');

Exercice 6

Créer un fichier texte contenant une liste de modèles de voitures avec autant de données par ligne que de colonnes dans la table modele de la base voitures. Insérer ces données dans la base.
Exemple de fichier texte contenant des modèles : le fichier modele.txt

"17C92853AZ";"Citroën C5";"diesel"
"178524ER45";"Citroën Picasso";"essence"
"7499RF5679";"Renault Mégane Scénic";"diesel"
"33356677PO";"Peugeot 206";"électrique"
"563339GH56";"Citroën C3";"essence"
"83321TY455";"Renault Espace";"diesel"

Pour revoir la méthode d’insertion à partir d’un fichier texte avec phpMyAdmin, voir la page 403 et suivantes.

Exercice 7

Créer un fichier Excel ou OpenOffice contenant une liste de modèles de voitures avec autant de données par ligne que de colonnes dans la table modele. L’enregistrer au format CSV et insérer les données dans la base.
La feuille du tableur à l’aspect type suivant :

EXCEL

L’insertion des données se fait selon la même procédure que celle utilisée pour un fichier texte. Après l’insertion la table modele a le contenu suivant :

TABLE

Exercice 8

Insérer des données dans les autres tables de la base voitures. Effectuer des mises à jour en modifiant certaines valeurs.

Trivial avec phpMyAdmin.

Exercice 9

Dans la base magasin, sélectionner les articles dont le prix est inférieur à 1 500 €.
Requête SQL :

SELECT id_article, designation, prix
FROM article
WHERE prix <1500

Exercice 10

Dans la base magasin, sélectionner les articles dont le prix est compris entre 100 et 500 €.
Requête SQL :

SELECT id_article, designation, prix
FROM article
WHERE prix
BETWEEN 100
AND 500

Exercice 11

Dans la base magasin, sélectionner tous les articles de marque Nikon (dont la désignation contient ce mot).
Requête SQL :

SELECT id_article, designation, prix
FROM article
WHERE designation LIKE '%Nikon%'

Exercice 12

Dans la base magasin, sélectionner tous les caméscopes, leur prix et leur référence.
Requête SQL :

SELECT id_article, designation, prix
FROM article
WHERE designation LIKE '%caméscope%'

On peut également écrire :

SELECT id_article, designation, prix
FROM article
WHERE categorie = 'vidéo'

Exercice 13

Dans la base magasin, sélectionner tous les produits de la catégorie informatique et afficher leur code, leur désignation et leur prix par ordre décroissant de prix.
Requête SQL :

SELECT id_article, designation, prix
FROM article
WHERE categorie = 'informatique '
ORDER BY prix DESC

Exercice 14

Dans la base magasin, sélectionner tous les clients de moins de 40 ans et ordonner les résultats par ville en ordre alphabétique.
Requête SQL :

SELECT nom, prenom, age, ville
FROM CLIENT WHERE age <40
ORDER BY ville ASC

Exercice 15

Dans la base magasin, calculer le prix moyen de tous les articles.
Requête SQL :

SELECT avg( prix )
FROM article

Exercice 16

Dans la base magasin, calculer le nombre d’e-mails non NULL et distincts l’un de l’autre.
Requête SQL :

SELECT count( DISTINCT mail) FROM client

Exercice 17

Dans la base magasin, afficher les coordonnées des clients ayant la même adresse (même adresse et même ville).
Requête SQL :

SELECT nom,prenom,adresse,ville,mail FROM client WHERE adresse='75 Bd Hochimin' AND ville='Lille'

Avec PHP, si l’adresse et la ville étaient contenues respectivement dans les variables $adresse et $ville on aurait le code suivant :

SELECT nom,prenom,adresse,ville,mail FROM client WHERE adresse='$adresse' AND ville='$ville'

Exercice 18

Dans la base magasin, sélectionner tous les articles commandés par chaque client.
Requête SQL :

SELECT nom,prenom,article.id_article,designation
FROM `client` ,commande,article,ligne
WHERE client.id_client=commande.id_client
AND ligne.id_comm=commande.id_comm
AND ligne.id_article=article.id_article

Exercice 19

Dans la base magasin, sélectionner tous les clients dont le montant d’une commande dépasse 1 500 €.
Requête SQL :

SELECT nom,prenom, ligne.id_comm, sum(prixunit*quantite) AS 'total'
FROM client,ligne,commande
WHERE ligne.id_comm=commande.id_comm
AND commande.id_client=client.id_client
GROUP BY ligne.id_comm
HAVING sum(prixunit*quantite)>1500

Exercice 20

Dans la base magasin, sélectionner tous les clients dont le montant total de toutes les commandes dépasse 5 000 €.
Requête SQL :

SELECT client.id_client, ligne.id_comm, sum(prixunit*quantite)
FROM client,ligne,commande
WHERE ligne.id_comm=commande.id_comm
AND commande.id_client=client.id_client
GROUP BY client.id_client
HAVING sum(prixunit*quantite)>5000

Exercice 21

Dans la base voitures, sélectionner tous les véhicules d’une personne donnée.
Requête SQL : Nous cherchons par exemple tous les véhicules de M. Algout.

SELECT cartegrise.immat,modele,proprietaire.id_pers
FROM voiture,modele,proprietaire,cartegrise
WHERE proprietaire.nom='Algout'
AND proprietaire.id_pers=cartegrise.id_pers
AND cartegrise.immat=voiture.immat
AND voiture.id_modele=modele.id_modele

Exercice 22

Dans la base voitures, sélectionner toutes les personnes ayant le même modèle de voiture.
Requête SQL : Nous cherchons par exemple tous les propriétaires de véhicules de type « Picasso ».

SELECT proprietaire.nom,proprietaire.prenom,modele.modele,modele.carburant
FROM voiture,modele,proprietaire,cartegrise
WHERE modele LIKE '%Picasso'
AND voiture.id_modele=modele.id_modele
AND cartegrise.immat=voiture.immat
AND proprietaire.id_pers=cartegrise.id_pers

Exercice 23

Dans la base voitures, sélectionner tous les véhicules ayant plusieurs copropriétaires.
Requête SQL :

SELECT cartegrise.immat FROM cartegrise
GROUP BY immat
HAVING count(*) >1

PHP 5 Cours et exercices 2e Edition

Jean ENGELS Editions Eyrolles