SQL et phpMyadmin – TP avec solutions – Exercices et corrigés SQL | Examens, Exercices, Astuces tous ce que vous Voulez

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

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