TD1 - Correction

SQL

Exercice 1 Type de donn es

D finissez le type de donn e le mieux appropri pour sp cifier :

a) un nom de jour de la semaine

b) un nom de mois de lann e

c) un num ro de semaine

d) un trigramme

e) un code postal

Correction :

a) CHAR(8)

est suffisant car aucun nom de jour na plus de 8 caract res. On peut aussi utiliser

un VARCHAR(8).

b) CHAR(9)

est suffisant car aucun nom de mois na plus de 9 caract res. On peut aussi

utiliser un VARCHAR(9).

c) SMALLINT

para t suffisant car la num rotation des semaines va de 1 53.

d) CHAR(3)

est parfait car un trigramme est compos de trois lettres.

e) INT(5). Mais mieux vaut un CHAR(5) car sinon le tri sur le code postal remonterait

le code postal 09800 sera enregistr comme 9800, le z ro en t te est ignor lors

de lenregistrement en base car consid r non significatif.

Exercice 2 Syntaxe des types de donn es

Quelles sont les d finitions de type syntaxiquement incorrectes ?

a) CHAR(16, 2)

b) VARCHAR(16)

c) FLOAT

d) DECIMAL

e) DECIMAL(16,2)

f) DATE (10)

g) TIME (3)

Correction :

Sont incorrects :

a) Un seul param tre doit sp cifier la longueur.

a) Une date ne contient pas de param tre de sp cification de taille.

Exercice 3 Syntaxe des types de donn es

Quelles sont les sp cifications de valeurs syntaxiquement incorrectes ?

a) DATE '18/11/2004'

b) DATE '2002-11-18'

c) TIME '11:16'

d) DATE '2004-11-18 20:20:30 000'

e) 123,55

f) 1E-10

g) 3.145 E-1

h) '456,78'

Correction

Sont incorrects :

a) Une date doit tre sp cifi e au format ISO 'AAAA-MM-JJ'.

d) Une date ne peut pas contenir de sp cification horaire.

e) Le s parateur dun r el ou dun d cimal est le point.

g) Il ne doit pas y avoir despace dans la d finition dun r el.

Exercice 4 Cr ation et manipulation de tables

Ecrivez les requ tes suivantes :

1. Cr ez la table FILM

CREATE TABLE film ( idFilm INT(2),

idRealisateur INT(2),

titre VARCHAR(50),

genre VARCHAR(10),

annee INT(5)UNSIGNED);

2. S lectionnez dans la table FILM les films qui sont du genre Drame et Policier

SELECT * FROM film WHERE (genre=Drame OR

genre=Policier);

3. Supprimez les enregistrements de la table FILM datant davant 1995

DELETE FROM film WHERE (annee <1995);

4. Supprimez la table FILM

DROP TABLE film ;

5. Restaurez la table FILM gr ce un script SQL. Que contiendra ce script ?

SOURCE script.sql ;

6. Affichez le contenu de la table PERSONNE

SELECT * FROM personne ;

7. Ajouter ce nouvel enregistrement dans la table FILM :

12 22 Lage de glace 4 Animation 2011

a. En utilisant un INSERT

INSERT INTO film VALUES (12, 22, Lage de glace 4,

Animation, 2011) ;

b. En utilisant un fichier de donn es

LOAD DATA LOCAL INFILE 3film.txt3 INTO TABLE film;

Exercice 5 Acc s aux donn es

On consid re la base de donn es BD AIRBASE suivante :

PILOT (NumP, NameP, Address, Salary)
AIRPLANE (NumAP, NameAP, Capacity, Localisation)
FLIGHT (NumF, NumP, NumAP, Dep_T, Arr_T, Dep_H, Arr_H)

Convention :

Les cl s primaires sont soulign es et les cl s trang res sont en italique gras.

a. Expression des projections et s lections

Q1 : Donnez la liste des avions dont la capacit est sup rieure 350 passagers.

SELECT * FROM AirPlane WHERE Capacity > 350 ;

Q2 : Quels sont les num ros et noms des avions localis s Nice ?

SELECT NumAP, NameAP FROM AirPlane WHERE Localisation = Nice ;

Q3 : Quels sont les num ros des pilotes en service et les villes de d part de leurs

vols ?

SELECT NumP, Dep_T FROM Flight;

Q4 : Donnez toutes les informations sur les pilotes de la compagnie.

SELECT * FROM Pilot;

Q5 : Quel est le nom des pilotes domicili s Paris dont le salaire est sup rieur

15000 F ?

SELECT NameP FROM Pilot WHERE Address = Paris AND Salary > 200000;

b. Utilisation des op rateurs ensemblistes

Q6 : Quels sont les avions (num ro et nom) localis s Nice ou dont la capacit est

inf rieure 350 passagers ?

SELECT NumAP, NameAP FROM AirPlane WHERE Localisation = Nice OR

Capacity < 350;

Q7 : Liste des vols au d part de Nice allant Paris apr s 18 heures ?

SELECT * FROM Flight WHERE Dep_T = Nice AND Arr_T = Paris AND

Arr_H > 18:00:00;

Q8 : Quels sont les num ros des pilotes qui ne sont pas en service ?

SELECT NumP FROM Pilot WHERE NumP NOT IN (SELECT NumP FROM Flight);

Q9 : Quels sont les vols (num ro, ville de d part) effectu s par les pilotes de

num ro 100 et 204 ?

SELECT NumF, Dep_T FROM Flight WHERE NumP = 100 OR NumP = 105;