Pages - Menu

Pages

SQL et phpMyadmin – TP avec solutions – Exercices et corrigés SQL


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

1 commentaire: