LE - Examen corrige

LE







LANGAGE








S.Q.L.









TABLE DES MATIERES


1. INTRODUCTION 3

1.1. Historique 3
1.2. Définition 3

2. ALGEBRE RELATIONNELLE 5

2.1. Opérateurs ensemblistes 5
2.1.1. Union 5
2.1.2. Intersection 6
2.1.3. Différence 7
2.2. Opérateurs unaires relationnels 8
2.2.1. Sélection 8
2.2.2. Projection 8
2.3. Opérateurs binaires relationnels 8
2.3.1. Le produit cartésien 8
2.3.2. La jointure 9
2.3.3. La division 9

3. LE LANGAGE SQL 10

3.1. Langage de Description de Données 10
3.1.1. CREATE 10
3.1.2. DROP 10
3.1.3. ALTER 11
3.2. Langage de Manipulation des Données 11
3.2.1. SELECT 11
3.2.2. INSERT 12
3.2.3. DELETE 12
3.2.4. UPDATE 12

4. LA SELECTION 13

4.1. Syntaxe complète du SELECT 13
4.2. Fonctions integrées 14
4.3. La Jointure 15
4.3.1. Méthode ensembliste 15
4.3.2. Méthode prédicative 16
4.3.3. Auto-jointure 16
4.4. Opérateur de partitionnement 17
4.4.1. Group by 17
4.4.2. Having 17
4.5. Opérateurs du WHERE 18

5. EXERCICES 19

5.1. Enoncés 19
5.2. Corrigés Erreur ! Signet non défini.
5.2.1. Exercice 1 Erreur ! Signet non défini.
5.2.2. Exercice 2 Erreur ! Signet non défini.
5.2.3. Exercice 3 Erreur ! Signet non défini.
5.2.4. Exercice 4 Erreur ! Signet non défini.
5.2.5. Exercice 5 Erreur ! Signet non défini.
5.2.6. Exercice 6 Erreur ! Signet non défini.
5.2.7. Exercice 7 Erreur ! Signet non défini.
5.2.8. Exercice 8 Erreur ! Signet non défini.
5.2.9. Exercice 9 Erreur ! Signet non défini.
5.2.10. Exercice 10 Erreur ! Signet non défini.
5.2.11. Exercice 11 Erreur ! Signet non défini.
5.2.12. Exercice 12 Erreur ! Signet non défini.
13. Exercice 13 Erreur ! Signet non défini.
13. . Exercice 14 Erreur ! Signet non défini.

introduction



1 Historique



S.Q.L. est un langage structuré permettant d'interroger et de modifier
les données contenues dans une base de données relationnelle.
S.Q.L. signifie Structured Query Language. Il est issu de SEQUEL :
Structured English Query Language.
C'est le premier langage pour les S.G.B.D Relationnels. Il a été
développé par IBM en 1970 pour système R, son 1er SGBDR.
S.Q.L. a été reconnu par l'ANSI (Association de Normalisation des
Systèmes d'Information) puis imposé comme norme. Il n'existe pas de
S.G.B.D.R sans S.Q.L..
Malheureusement, malgrè la norme S.Q.L., il existe un ensemble de
dialectes. Les différences entre ces différents dialectes sont souvent
minimes et tous respectent un minimum commun : ce que nous allons
étudier ici.



2 Définition



S.Q.L. est un langage relationnel qui permet d'effectuer les tâches
suivantes :
1. Définition et modification de la structure de la base de données
2. Interrogation et modification non procédurale (c'est à dire
interactive) de la base de données
3. Contrôle de sécurité et d'intégrité de la base.


S.Q.L. est un langage interactif, mais il peut aussi être intégré dans
un langage de programmation pour le développement d'applications.


S.Q.L. n'est pas le meilleur langage, en particulier pour la
manipulation des données, mais c'est un standard.


Dans tout ce qui suit les exemples seront donnés par rapport à la base
de données suivante :


AVION(AV#, AVMARQ, AVTYPE, CAP, LOC)
PILOTE(PIL#, PILNOM, ADR)
VOL(VOL#,PIL#,AV#,VD,VA,HD,HA)


AV# : numéro d'avion
AVMARQ : marque de l'avion
AVTYPE : type de l'avion
CAP : capacité en nb de passagers
LOC : ville où est basé l'avion
PIL# : numéro du pilote
PILNOM : nom du pilote
ADR : adresse du pilote
VOL# : numéro du vol
VD : ville départ
VA : ville d'arrivée
HD : heure de départ
HA : heure d'arrivée
TABLE AVION




|AV# |Marque |Type |Capaci|Localis|
| | | |té |ation |
|100 |AIRBUS |A320 |300 |Nice |
|101 |BOIENG |B707 |250 |Paris |
|102 |AIRBUS |A320 |300 |Toulous|
| | | | |e |
|103 |CARAVELLE |Caravel|200 |Toulous|
| | |le | |e |
|104 |BOEING |B747 |400 |Paris |
|105 |AIRBUS |A320 |300 |Grenobl|
| | | | |e |
|106 |ATR |ATR42 |50 |Paris |
|107 |BOEING |B727 |300 |Lyon |
|108 |BOEING |B727 |300 |Nantes |
|109 |AIRBUS |A340 |350 |Bastia |

TABLE PILOTE




|PIL# |Nom |Adresse|
|1 |SERGE |Nice |
|2 |JEAN |Paris |
|3 |CLAUDE |Grenobl|
| | |e |
|4 |ROBERT |Nantes |
|5 |MICHEL |Paris |
|6 |LUCIEN |Toulous|
| | |e |
|7 |BERTRAND |Lyon |
|8 |HERVE |Bastia |
|9 |LUC |Paris |

TABLE VOL



|VOL# |Avion |Pilote |Ville |Ville |Heure|Heure|
| | | |Départ |Arrivée |Dépar|Arriv|
| | | | | |t |ée |
|IT100 |100 |1 |NICE |PARIS |7 |9 |
|IT101 |100 |2 |PARIS |TOULOUSE |11 |12 |
|IT102 |101 |1 |PARIS |NICE |12 |14 |
|IT103 |105 |3 |GRENOBLE |TOULOUSE |9 |11 |
|IT104 |105 |3 |TOULOUSE |GRENOBLE |17 |19 |
|IT105 |107 |7 |LYON |PARIS |6 |7 |
|IT106 |109 |8 |BASTIA |PARIS |10 |13 |
|IT107 |106 |9 |PARIS |LYON |7 |8 |
|IT108 |106 |9 |LYON |PARIS |19 |20 |
|IT109 |107 |7 |PARIS |LYON |18 |19 |
|IT110 |102 |2 |TOULOUSE |PARIS |15 |16 |
|IT111 |101 |4 |NICE |NANTES |17 |19 |
Algèbre relationnelLE





1 Opérateurs ensemblistes






1 Union

















R1 U R2
Est utilisé pour relier 2 relations unicompatibles, c'est à dire ayant
le même nombre d'attributs et des attributs respectifs définis sur le
même domaine.
Pour l'union il faut une structure identique.






R1 : TABLE AVION PARIS NICE



|VOL |Avion |Pilote |Ville |Ville |Heure |Heure |
| | | |Départ |Arrivée|Départ |Arrivée|
|IT102 |101 |1 |Paris |Nice |12h00 |14h00 |
|IT118 |256 |1024 |Paris |Nice |9h36 |10h30 |
|IT112 |812 |2048 |Paris |Nice |12h25 |14h00 |
|IT123 |999 |1246 |Paris |Nice |19h00 |20h00 |


R2 : TABLE AVION LYON MARSEILLE



|VOL |Avion |Pilote |Ville |Ville |Heure |Heure |
| | | |Départ |Arrivée|Départ |Arrivée|
|IT99 |999 |2 |Lyon |Marseil|10h30 |13h00 |
| | | | |le | | |


R1 U R2



|Avion |
|101 |
|256 |
|812 |
|999 |


Exemples :
4. liste des vols Paris-Nice et Lyon-Marseille
5. liste des avions Airbus et Boeing
6. liste des vols sur Airbus ou sur un avion de plus de 200 places



2 Intersection















R1 ( R2
Est utilisé pour relier 2 relations unicompatibles, c'est à dire ayant
le même nombre d'attributs et des attributs respectifs définis sur le
même domaine.


Pour l'intersection il faut une structure identique.


R1 : TABLE AVION AIRBUS



|AV# |AVMARQ |Ville |Ville |
| | |Départ |Arrivée |
|100 |AIRBUS |NICE |PARIS |
|100 |AIRBUS |PARIS |TOULOUSE |
|105 |AIRBUS |GRENOBLE |TOULOUSE |
|105 |AIRBUS |toulouse |GRENOBLE |
|109 |AIRBUS |BASTIA |PARIS |
|102 |AIRBUS |TOULOUSE |PARIS |


R2 : TABLE AVION PARIS





|AV# |AVMARQ |Ville |Ville |
| | |Départ |Arrivée |
|100 |AIRBUS |NICE |PARIS |
|107 |BOEING |LYON |PARIS |
|109 |AIRBUS |BASTIA |PARIS |
|106 |ATR |Lyon |PARIS |
|102 |AIRBUS |TOULOUSE |PARIS |


R1 ( R2



|AV# |AVMARQ |Ville |Ville |
| | |Départ |Arrivée |
|100 |AIRBUS |NICE |PARIS |
|109 |AIRBUS |BASTIA |PARIS |
|102 |AIRBUS |TOULOUSE |PARIS |


Exemples :
7. Liste des avions AIRBUS allant à Paris
8. liste des pilotes de boeing habitant toulouse






3 Différence










R1-R2
Est utilisé pour relier 2 relations unicompatibles, c'est à dire ayant
le même nombre d'attributs et des attributs respectifs définis sur le
même domaine.


R1 : TABLE AVION AIRBUS



|AV# |AVMARQ |Ville |Ville |
| | |Départ |Arrivée |
|100 |AIRBUS |NICE |PARIS |
|100 |AIRBUS |PARIS |TOULOUSE |
|105 |AIRBUS |GRENOBLE |TOULOUSE |
|105 |AIRBUS |toulouse |GRENOBLE |
|109 |AIRBUS |BASTIA |PARIS |
|102 |AIRBUS |TOULOUSE |PARIS |


R2 : TABLE AVION PARIS





|AV# |AVMARQ |Ville |Ville |
| | |Départ |Arrivée |
|100 |AIRBUS |NICE |PARIS |
|107 |BOEING |LYON |PARIS |
|109 |AIRBUS |BASTIA |PARIS |
|106 |ATR |Lyon |PARIS |
|102 |AIRBUS |TOULOUSE |PARIS |


R2 - R1



|AV# |AVMARQ |Ville |Ville |
| | |Départ |Arrivée |
|107 |BOEING |LYON |PARIS |
|106 |ATR |Lyon |PARIS |


R1 - R2



|AV# |AVMARQ |Ville |Ville