SQL et phpMyadmin – TP avec solutions – Exercices et corrigés SQL
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.
Solution
Nous
utilisons bien sûr phpMyAdmin pour créer la base puis les tables.
• 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
• Création de la table carte grise
CREATE
TABLE `cartegrise` (
`id_pers`
MEDIUMINT UNSIGNED NOT NULL ,
`immat`
VARCHAR( 6 ) NOT NULL ,
`datecarte`
DATE NOT NULL ,
PRIMARY
KEY ( `id_pers` , `immat` )
);
• 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` )
);
• 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.
Solution
Nous obtenons les fichiers suivants :
• Le
fichier proprietaire.sql :
-- version
2.6.0-rc3
--
http://www.phpmyadmin.net
--
-- Serveur:
localhost
-- Généré le :
Mercredi 15 Décembre 2004 à 18:21
-- Version du
serveur: 4.0.21
-- Version de
PHP: 5.0.2
--
-- 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 ;
• Le
fichier cartegrise.sql :
-- phpMyAdmin
SQL Dump
-- version
2.6.0-rc3
--
http://www.phpmyadmin.net
--
-- Serveur:
localhost
-- Généré le :
Mercredi 15 Décembre 2004 à 18:26
-- Version du
serveur: 4.0.21
-- Version de
PHP: 5.0.2
--
-- 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;
• Le
fichier voiture.sql
-- phpMyAdmin
SQL Dump
-- version
2.6.0-rc3
--
http://www.phpmyadmin.net
--
-- Serveur:
localhost
-- Généré le : Mercredi 15 Décembre
2004 à 18:27
-- Version du
serveur: 4.0.21
-- Version de
PHP: 5.0.2
--
-- 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;
• Le
fichier modele.sql
-- phpMyAdmin
SQL Dump
-- version
2.6.0-rc3
--
http://www.phpmyadmin.net
--
-- Serveur:
localhost
-- Généré le :
Mercredi 15 Décembre 2004 à 18:29
-- Version du
serveur: 4.0.21
-- Version de
PHP: 5.0.2
--
-- 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.
Solution
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 :
Solution
-- phpMyAdmin SQL Dump
-- version 2.6.0-rc3
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mercredi 15 Décembre 2004 à 18:41
-- Version du serveur: 4.0.21
-- Version de PHP: 5.0.2
--
-- 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.
Solution
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 modèle de la base voitures.
Insérer ces données dans la base.
Solution
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 382 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.
Solution
La
feuille du tableur à l’aspect type suivant :
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 modèle a le contenu suivant :
Exercice 8:
Insérer des données dans les autres tables de la base voitures.
Effectuer des mises à jour en modifiant certaines valeurs.
Solution
Trivial avec phpMyAdmin.
Exercice 9:
Dans la base magasin, sélectionner les articles dont le prix est inférieur
à 1 500 €.
Solution
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 €.
Solution
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).
Solution
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.
Solution
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.
Solution
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.
Solution
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.
Solution
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.
Solution
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).
Solution
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.
Solution
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
ORDER BY nom
Exercice 19:
Dans la base magasin, sélectionner tous les clients dont le montant
d’une commande dépasse 1 500 €.
Solution
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 €.
Solution
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.
Solution
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.
Solution
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.
Solution
Requête SQL :
SELECT cartegrise.immat FROM cartegrise
GROUP BY immat
HAVING count(*) >1
merci
RépondreSupprimer