MySQL - SQL - Découverte et travaux pratiques

Après une introduction sur les concepts du Structure Query Langage, le SQL, vous allez pouvoir vous plonger dans le cours de base en SQL

Ce cours aborde tous les concepts important pour savoir construire des requêtes. Il contient 31 exercices sur la base de données bd-avion.

Pour ce cours nous avons besoin de faire quelques installations.

Voici le liens vers le script de création des tables et des enregistrements: lien vers le script sql

Script de création de la base Avions et des données

--
-- Structure de la table `avion`
--

DROP TABLE IF EXISTS `avion`;
CREATE TABLE IF NOT EXISTS `avion` (
  `AV_ID` int(11) NOT NULL,
  `AV_CONST` varchar(20) DEFAULT NULL,
  `AV_MODELE` varchar(10) DEFAULT NULL,
  `AV_CAPACITE` int(11) DEFAULT NULL,
  `AV_SITE` varchar(20) DEFAULT NULL,
  UNIQUE KEY `AV_ID` (`AV_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- données de la table `avion`
--

INSERT INTO `avion` (`AV_ID`, `AV_CONST`, `AV_MODELE`, `AV_CAPACITE`, `AV_SITE`) VALUES
(100, 'AIRBUS', 'A320', 300, 'Nice'),
(101, 'BOEING', 'B707', 250, 'Paris'),
(102, 'AIRBUS', 'A320', 300, 'Toulouse'),
(103, 'CARAVELLE', 'Caravelle', 200, 'Toulouse'),
(104, 'BOEING', 'B747', 400, 'Paris'),
(105, 'AIRBUS', 'A320', 300, 'Grenoble'),
(106, 'ATR', 'ATR42', 50, 'Paris'),
(107, 'BOEING', 'B727', 300, 'Lyon'),
(108, 'BOEING', 'B727', 300, 'Nantes'),
(109, 'AIRBUS', 'A340', 350, 'Bastia');

-- --------------------------------------------------------

--
-- Structure de la table `pilote`
--

DROP TABLE IF EXISTS `pilote`;
CREATE TABLE IF NOT EXISTS `pilote` (
  `PI_ID` int(11) NOT NULL,
  `PI_NOM` varchar(20) DEFAULT NULL,
  `PI_SITE` varchar(20) DEFAULT NULL,
  UNIQUE KEY `PI_ID` (`PI_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- données de la table `pilote`
--

INSERT INTO `pilote` (`PI_ID`, `PI_NOM`, `PI_SITE`) VALUES
(1, 'SERGE', 'NICE'),
(2, 'JEAN', 'PARIS'),
(3, 'CLAUDINE', 'GRENOBLE'),
(4, 'ROBERT', 'NANTES'),
(5, 'MICHEL', 'PARIS'),
(6, 'LUCIENNE', 'TOULOUSE'),
(7, 'BERTRAND', 'LYON'),
(8, 'HERVE', 'BASTIA'),
(9, 'LUC', 'PARIS');

--
-- Structure de la table `vol`
--

DROP TABLE IF EXISTS `vol`;
CREATE TABLE IF NOT EXISTS `vol` (
  `VO_ID` char(5) NOT NULL,
  `VO_AVION` int(11) NOT NULL,
  `VO_PILOTE` int(11) NOT NULL,
  `VO_SITE_DEPART` varchar(50) DEFAULT NULL,
  `VO_SITE_ARRIVEE` varchar(50) DEFAULT NULL,
  `VO_HEURE_DEPART` time DEFAULT NULL,
  `VO_HEURE_ARRIVEE` time DEFAULT NULL,
  UNIQUE KEY `VO_ID` (`VO_ID`),
  KEY `fk_vol_pilote_id` (`VO_PILOTE`),
  KEY `fk_vol_avion_id` (`VO_AVION`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- données de la table `vol`
--

INSERT INTO `vol` (`VO_ID`, `VO_AVION`, `VO_PILOTE`, `VO_SITE_DEPART`, `VO_SITE_ARRIVEE`, `VO_HEURE_DEPART`, `VO_HEURE_ARRIVEE`) VALUES
('IT100', 100, 1, 'NICE', 'PARIS', '07:00:00', '09:00:00'),
('IT101', 100, 2, 'PARIS', 'TOULOUSE', '11:00:00', '12:00:00'),
('IT102', 101, 1, 'PARIS', 'NICE', '12:00:00', '14:00:00'),
('IT103', 105, 3, 'GRENOBLE', 'TOULOUSE', '09:00:00', '11:00:00'),
('IT104', 105, 3, 'TOULOUSE', 'GRENOBLE', '17:00:00', '19:00:00'),
('IT105', 107, 7, 'LYON', 'PARIS', '06:00:00', '07:00:00'),
('IT106', 109, 8, 'BASTIA', 'PARIS', '10:00:00', '13:00:00'),
('IT107', 106, 9, 'PARIS', 'BRIVE', '07:00:00', '08:00:00'),
('IT108', 106, 9, 'BRIVE', 'PARIS', '19:00:00', '20:00:00'),
('IT109', 107, 7, 'PARIS', 'LYON', '18:00:00', '19:00:00'),
('IT110', 102, 2, 'TOULOUSE', 'PARIS', '15:00:00', '16:00:00'),
('IT111', 101, 4, 'NICE', 'NANTES', '17:00:00', '19:00:00');

--
-- Contraintes pour les tables
--

--
-- Contraintes pour la table `vol`
--
ALTER TABLE `vol`
  ADD CONSTRAINT `fk_vol_avion_id` FOREIGN KEY (`VO_AVION`) REFERENCES `avion` (`AV_ID`),
  ADD CONSTRAINT `fk_vol_pilote_id` FOREIGN KEY (`VO_PILOTE`) REFERENCES `pilote` (`PI_ID`);
COMMIT;

• Liste des 31 requêtes à écrire et tester

Pratique de base

Schéma de la base de données

Dans cette base de données, il y a 3 tables :

• Pilote
• Avion
• Vol

Un Vol a obligatoirement un Avion et un Pilote (enfin, on espère !)

Script SQL de la structure des tables

CREATE TABLE IF NOT EXISTS `avion` (  
  `AV_ID` int(11) NOT NULL,  
  `AV_CONST` varchar(20) DEFAULT NULL,  
  `AV_MODELE` varchar(10) DEFAULT NULL,  
  `AV_CAPACITE` int(11) DEFAULT NULL,  
  `AV_SITE` varchar(20) DEFAULT NULL,  
  UNIQUE KEY `AV_ID` (`AV_ID`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

CREATE TABLE IF NOT EXISTS `pilote` (  
  `PI_ID` int(11) NOT NULL,  
  `PI_NOM` varchar(20) DEFAULT NULL,  
  `PI_SITE` varchar(20) DEFAULT NULL,  
  UNIQUE KEY `PI_ID` (`PI_ID`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

CREATE TABLE IF NOT EXISTS `vol` (  
  `VO_AVION` int(11) NOT NULL,  
  `VO_PILOTE` int(11) NOT NULL,  
  `VO_SITE_DEPART` varchar(50) DEFAULT NULL,  
  `VO_SITE_ARRIVEE` varchar(50) DEFAULT NULL,  
  `VO_HEURE_DEPART` time DEFAULT NULL,  
  `VO_HEURE_ARRIVEE` time DEFAULT NULL,  
  UNIQUE KEY `VO_ID` (`VO_ID`),  
  KEY `fk_vol_pilote_id` (`VO_PILOTE`),  
  KEY `fk_vol_avion_id` (`VO_AVION`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

Liste des 31 requêtes à écrire et tester

1. liste de tous les numéros d’avions
2. Liste des noms des pilotes
3. Liste des marques d’avions (sans doublon)
4. Liste des vols pour Nice
5. Liste des avions qui ont 200 places et plus
6. Liste des avions AIRBUS localisés à Toulouse
7. Liste des avions AIRBUS allant à Paris
8. Liste des vols Paris-Nice et Toulouse-Paris
9. Liste des avions Airbus et Boeing
10. Liste des Airbus ou des avions de plus de 200 places
11. Liste des avions AIRBUS qui ne sont pas localisés à Toulouse
12. Liste des Airbus qui ne vont pas à Paris
13. Liste des avions pour Paris qui ne sont pas des Airbus
14. Liste de tous les vols avec le nom des avions
15. Type et capacité des avions en service (donc des avions qui volent !)
16. Liste des avions AIRBUS allant à Paris
17. Nom des pilotes en service
18. Nom des avions (BOEING) ayant une même capacité (auto-jointure)
19. Nombre d’avions de chaque marque

20. Nombre de pilotes différents pour chaque avion en service

21. Nombre de vols différents pour chaque pilote (regroupé par nom)
22. Pilotes (ordre croissant des numéros) assurant plus d’un vol (Afficher: Numéro et nom des pilotes, nombre de vols)
23. Nombre de vols assurés au départ de Nice ou de Paris par chaque pilote (Afficher: Numéros des pilotes, ville de départ et nombre de vols)
24. Nombre de vols assurés au départ ou à l’arrivée de Nice par chaque pilote (Afficher: Numéros des pilotes, nombre de vols)
25. Liste des vols dont la ville de départ correspond à la ville où est localisé l’avion
26. Liste des avions de capacité égale ou supérieure à la moyenne
27. Capacité mini et maxi des BOEING
28. Capacité moyenne des avions localisés à Paris avec 2 chiffres après la virgule
29. Capacité moyenne des avions par marque
30. Capacité totale des avions de la table avion
31. Affichage de l’heure système avec les secondes

Solution pour les 31 requêtes

Les solutions

Certaines requêtes peuvent être simplifiées. Je vous laisse chercher et tester…

Travaux pratiques avec SELECT et CRUD sur la BD-Concession

TP avec SQL

Ressources Web

Supports & sites pour s’entrainer

• sql.sh pour revoir les concepts SQL.
• w3schools pour s’entraîner
• sqlbolt.com
• SQL_Tutorial

Remplir vos tables

Il existe des sites pour générer du code afin de remplir vos tables MySQL sans devoir écrire des tonnes de requêtes d’insertion.

Utiliser le site Mockaroo

Auteur : Philippe Bouget