Base Aviation |BDD 2ISA

Télécharger AVIATION_init_bd-fr.sql, script de la version française de la base Aviation.

Définitions

Pilote en service : pilote participant à un vol.

Exercices

1. Afficher les numéros de vols au départ de Paris.

VOLNO|
-----+
003  |
004  |
005  |
006  |
100  |
165  |
400  |
409  |
421  |

2. Afficher les numéros de pilotes en service sur des vols et la ville d'arrivée de leur vol.

PILNO|VOLARR        |
-----+--------------+
109  |NEW-YORK      |
110  |RIO           |
109  |NICE          |
120  |LILLE         |
130  |MOSCOU        |
140  |BREST         |
024  |ALGER         |
063  |TOULOUSE      |
163  |PRAGUE        |
062  |PARIS         |
072  |PARIS         |
102  |ABIDJAN       |
130  |ALGER         |
028  |DELHI         |
011  |LONDRES       |
056  |ROME          |
130  |GENEVE        |
099  |DUBLIN        |
063  |SAINT AFFRIQUE|
063  |LONDRES       |
067  |DUBLIN        |
063  |BOHN          |
063  |ROME          |
063  |MADRID        |
063  |STOCKOLM      |
063  |BRUXELLE      |
063  |LISBONNE      |
063  |COPENHAGUE    |
063  |LUXEMBOURG    |
063  |OSLO          |
063  |LE HAVRE      |
063  |MARSEILLE     |
063  |LE HAVRE      |
011  |DJAKARTA      |
011  |TOKYO         |
011  |HANOI         |
011  |BAGDAD        |
109  |LISBONNE      |
109  |HAWAI         |

3. Afficher les caractéristiques (numéro, nom, capacité, localisation) de tous les AIRBUS.

AVNO|AVNOM      |AVCAP|AVLOC   |
----+-----------+-----+--------+
067 |AIRBUS A300|  240|TOULOUSE|
100 |AIRBUS     |  350|TOULOUSE|
124 |AIRBUS A300|  233|TOULOUSE|
125 |AIRBUS A300|  251|LYON    |
143 |AIRBUS A310|  230|PARIS   |

4. Afficher les noms des pilotes qui habitent PARIS (autre que JEAN).

PILNO|PILNOM|PILADR|PILSAL|
-----+------+------+------+
100  |PIERRE|PARIS | 15000|
101  |PIERRE|PARIS | 11000|
102  |PAUL  |PARIS | 12000|
110  |PAUL  |PARIS | 25000|

5. Afficher les avions de capacité supérieure à 250 localisés à PARIS.
```
AVNO|
----+
148 |
194 |
```
6. Afficher les avions de capacité supérieure à 250 ou localisés à PARIS.
```
AVNO|
----+
100 |
104 |
105 |
125 |
143 |
148 |
180 |
194 |
```
7. Afficher les numéros de pilotes qui pilotent l'avion 100 et aussi l'avion 101.
```
PILNO|
-----+
109  |
063  |
```

8. Pour chaque pilote en service, afficher

le numéro du pilote
le nom du pilote
l'adresse du pilote
le numéro de l'avion piloté

Trier par nom de pilote.

PILNO|PILNOM  |PILADR   |AVNO|
-----+--------+---------+----+
011  |DELAUNAY|MILLAU   |067 |
011  |DELAUNAY|MILLAU   |143 |
011  |DELAUNAY|MILLAU   |124 |
011  |DELAUNAY|MILLAU   |100 |
011  |DELAUNAY|MILLAU   |125 |
062  |FOURNIER|ORLEANS  |125 |
067  |GASTON  |MILLAU   |100 |
163  |GUYOMARD|RODEZ    |145 |
120  |HECTOR  |LYON     |104 |
109  |JEAN    |PARIS    |180 |
109  |JEAN    |PARIS    |100 |
109  |JEAN    |PARIS    |194 |
109  |JEAN    |PARIS    |101 |
028  |JORY    |LA CRESSE|100 |
140  |MICHEL  |TOULOUSE |105 |
024  |MONTOIS |LYON     |067 |
110  |PAUL    |PARIS    |100 |
102  |PAUL    |PARIS    |100 |
056  |ROUSSEAU|NICE     |177 |
099  |ROY     |MENDE    |125 |
063  |SCHMITT |BORDEAUX |177 |
063  |SCHMITT |BORDEAUX |145 |
063  |SCHMITT |BORDEAUX |124 |
063  |SCHMITT |BORDEAUX |148 |
063  |SCHMITT |BORDEAUX |105 |
063  |SCHMITT |BORDEAUX |104 |
063  |SCHMITT |BORDEAUX |180 |
063  |SCHMITT |BORDEAUX |067 |
063  |SCHMITT |BORDEAUX |101 |
063  |SCHMITT |BORDEAUX |194 |
063  |SCHMITT |BORDEAUX |100 |
063  |SCHMITT |BORDEAUX |143 |
063  |SCHMITT |BORDEAUX |125 |
130  |SERGE   |TOULOUSE |194 |
130  |SERGE   |TOULOUSE |105 |
130  |SERGE   |TOULOUSE |101 |
072  |VINCENT |ARCACHON |105 |

9. Afficher les numéros et noms des pilotes qui pilotent un avion au départ de PARIS.

PILNO|PILNOM  |
-----+--------+
024  |MONTOIS |
109  |JEAN    |
063  |SCHMITT |
120  |HECTOR  |
130  |SERGE   |
140  |MICHEL  |
056  |ROUSSEAU|

10. Afficher les numéros et noms des pilotes qui pilotent un AIRBUS.

PILNO|PILNOM  |
-----+--------+
024  |MONTOIS |
109  |JEAN    |
011  |DELAUNAY|
062  |FOURNIER|
102  |PAUL    |
063  |SCHMITT |
028  |JORY    |
099  |ROY     |
110  |PAUL    |
067  |GASTON  |

11. Calculer le nombre de pilotes différents qui pilotent des AIRBUS, en nommant le résultat "Nb".
```
NB|
--+
10|
```
12. Afficher le nombre de pilotes, la moyenne des salaires, le plus petit salaire, le plus grand salaire parmi les pilotes qui gagnent plus que la moyenne.
Nommer les résultats "Nb", "Moyenne", "Min", "Max".
Arrondissez la moyenne à 2 chiffres après la virgule
```
NB|MOYENNE |MIN  |MAX  |
--+--------+-----+-----+
11|27663.64|20000|35000|
```

13. Calculer la capacité totale pour chaque destination, trier les résultats par capacité totale décroissante.
Nommez les colonnes "Destination" et "Total"

DESTINATION   |TOTAL|
--------------+-----+
LUXEMBOURG    |  150|
LILLE         |  150|
NICE          |  200|
MARSEILLE     |  200|
SAINT AFFRIQUE|  230|
TOKYO         |  230|
PRAGUE        |  233|
STOCKOLM      |  233|
HANOI         |  240|
MOSCOU        |  250|
MADRID        |  250|
BREST         |  250|
DJAKARTA      |  251|
BRUXELLE      |  251|
ROME          |  300|
COPENHAGUE    |  344|
RIO           |  350|
BAGDAD        |  350|
OSLO          |  350|
GENEVE        |  350|
DELHI         |  350|
NEW-YORK      |  350|
BOHN          |  350|
HAWAI         |  350|
ABIDJAN       |  350|
TOULOUSE      |  350|
LE HAVRE      |  400|
ALGER         |  440|
LONDRES       |  473|
PARIS         |  501|
LISBONNE      |  583|
DUBLIN        |  601|

14. Afficher la liste des vols triés par nom de pilote croissant et par nom d'avion croissant.

VOLNO|PILNOM  |AVNOM      |
-----+--------+-----------+
506  |DELAUNAY|AIRBUS     |
505  |DELAUNAY|AIRBUS A300|
161  |DELAUNAY|AIRBUS A300|
500  |DELAUNAY|AIRBUS A300|
501  |DELAUNAY|AIRBUS A310|
125  |FOURNIER|AIRBUS A300|
401  |GASTON  |AIRBUS     |
120  |GUYOMARD|BOEING 707 |
004  |HECTOR  |BOEING     |
001  |JEAN    |AIRBUS     |
600  |JEAN    |BOEING 747 |
601  |JEAN    |DC10       |
003  |JEAN    |DOUGLAS    |
150  |JORY    |AIRBUS     |
006  |MICHEL  |CARAVELLE  |
100  |MONTOIS |AIRBUS A300|
002  |PAUL    |AIRBUS     |
135  |PAUL    |AIRBUS     |
165  |ROUSSEAU|DC8        |
201  |ROY     |AIRBUS A300|
402  |SCHMITT |AIRBUS     |
406  |SCHMITT |AIRBUS A300|
400  |SCHMITT |AIRBUS A300|
407  |SCHMITT |AIRBUS A300|
341  |SCHMITT |AIRBUS A310|
404  |SCHMITT |BOEING     |
409  |SCHMITT |BOEING 707 |
410  |SCHMITT |BOEING 727 |
112  |SCHMITT |BOEING 747 |
405  |SCHMITT |CARAVELLE  |
415  |SCHMITT |DC10       |
411  |SCHMITT |DC8        |
420  |SCHMITT |DOUGLAS    |
421  |SCHMITT |DOUGLAS    |
430  |SCHMITT |DOUGLAS    |
180  |SERGE   |BOEING 747 |
005  |SERGE   |CARAVELLE  |
140  |SERGE   |DOUGLAS    |
130  |VINCENT |CARAVELLE  |

15. Calculer le nombre de vols au départ de PARIS.
```
COUNT(*)|
--------+
       9|
```

16. Afficher les villes qui ont plus de 2 vols au départ triés par nombre de vols décroissant.

VOLDEP   |COUNT(*)|
---------+--------+
PARIS    |       9|
MARSEILLE|       5|
LILLE    |       3|

Bonus

17. Afficher les numéros de pilote qui ne conduisent aucun avion.

PILNO|
-----+
014  |
015  |
034  |
042  |
061  |
068  |
082  |
100  |
101  |

18. Afficher le nom des pilotes qui n'effectuent aucun vol au départ de PARIS.

PILNOM  |
--------+
DELAUNAY|
BRUDY   |
MARTIN  |
JORY    |
LOPEZ   |
BECKER  |
LEFEVRE |
FOURNIER|
GASTON  |
HENRY   |
VINCENT |
COMMAULT|
ROY     |
PIERRE  |
PIERRE  |
PAUL    |
PAUL    |
GUYOMARD|

18 bis. Afficher le nom des pilotes en service qui n'effectuent aucun vol au départ de PARIS.

PILNOM  |
--------+
DELAUNAY|
JORY    |
FOURNIER|
GASTON  |
VINCENT |
ROY     |
PAUL    |
PAUL    |
GUYOMARD|

19. Afficher les numéros des pilotes qui conduisent un avion conduit aussi par le pilote 109.
```
PILNO|
-----+
130  |
102  |
109  |
063  |
110  |
028  |
011  |
067  |
```

20. Afficher les numéros d’avion qui ont des capacités identiques.

AVNO|AVNO|
----+----+
180 |100 |
194 |100 |
177 |104 |
145 |124 |
124 |145 |
104 |177 |
100 |180 |
194 |180 |
100 |194 |
180 |194 |

21. Afficher les numéros de pilote qui conduisent au moins un avion.

PILNOM  |
--------+
DELAUNAY|
MONTOIS |
JORY    |
ROUSSEAU|
FOURNIER|
SCHMITT |
GASTON  |
VINCENT |
ROY     |
PAUL    |
JEAN    |
PAUL    |
HECTOR  |
SERGE   |
MICHEL  |
GUYOMARD|

22. Afficher les pilotes qui pilotent tous les avions de la compagnie.
```
PILNO|
-----+
063  |
```
23. Afficher les pilotes qui pilotent tous les airbus en service.
```
PILNO|
-----+
011  |
063  |
```