TD Bases de Données et Ingénierie des Systèmes d’Information TD 3 − Requêtes SQL

Exemple :

SELECT SUM(p.gain)

FROM

WHERE

Participe p, Jockey j

p.Numero_jockey = j.Numero_jockey

AND j.nom like Jean-Claude Dusse;

Cr ation de tables

CREATE TABLE nom_de_la_table (

nom_de_lattribut type [ liste_de_contraintes_dattribut ]

nom_de_lattribut type [ liste_de_contraintes_dattribut ]

. . .

liste_de_contraintes_de_table

);

o :

" type

(cid:26) VARCHAR(n) o n N, INT,

DATE, TIME

(cid:27)

" contrainte_d(cid:48)attribut

" contrainte_de_table

NULL, NOT NULL,

DEFAULT valeur,

CHECK(nom_de_lattribut in domaine_de_d nition )

PRIMARY KEY(liste_dattributs),

FOREIGN KEY(nom_de_lattribut) . . .

. . . REFERENCES nom_de_la_table(nom_de_lattribut)

Mines de Nancy Tony Bourdier & Fabienne Thomarat 2008 2009

TD Bases de Donn es et Ing nierie des Syst mes dInformation TD 3 Requ tes SQL

Exemple :

CREATE TABLE cours (

sigle

intitule

responsable

nombreSeances INT

PRIMARY KEY (sigle),

FOREIGN KEY (responsable) REFERENCES enseignant(id)

NOT NULL,

VARCHAR(20)

VARCHAR(128) NOT NULL,

NOT NULL,

VARCHAR(50)

NOT NULL DEFAULT 0,

);

Suppression de table

DROP TABLE nom_de_la_table ;

Insertion

INSERT INTO nom_de_la_table ( attribut_1, attribut_2, . . . )

VALUES( valeur_1, valeur_2, . . . ) ;

Requ tes imbriqu es / sous-requ tes

Une sous-requ te est une commande SELECT dans une autre commande. Par exemple :

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

On dit que la sous-requ te est imbriqu e dans la requ te externe. Il est possible dimbriquer des

requ tes dans des sous-requ tes. Une sous-requ te doit toujours tre entre parenth ses.

Voici un exemple de commande qui montre les principaux avantages des sous-requ tes et de leur

syntaxe :

SELECT r1

t1

FROM

s11 = (

WHERE

SELECT COUNT(*) FROM t2

WHERE NOT EXISTS (

SELECT * FROM t3

WHERE r3 =

(SELECT 50,11s1 FROM t4 WHERE r5 in (SELECT FROM t5) AS t5)

)

);

EXISTS teste simplement si la requ te interne retourne une ligne. NOT EXISTS teste si la requ te

interne ne retourne aucun r sultat.

Mines de Nancy Tony Bourdier & Fabienne Thomarat 2008 2009

TD Bases de Donn es et Ing nierie des Syst mes dInformation TD 3 Requ tes SQL

TD sur les requ tes SQL

d cembre 2008

Pr requis : Mod le conceptuel de donn es (entit -association), mod le relationnel, bases du lan-

gage SQL.

Dur e : 1 h 50

TD 3 Requ tes SQL

Correction

Description du syst me dinformations

La direction des tudes des Mines de Nancy a d cid dinformatiser la gestion des emplois du

temps. Chaque tudiant est caract ris par son num ro d tudiant, son nom, son pr nom et son

ge. Chaque cours est identi de fa on unique par un sigle (SI033, MD021, . . . ) et poss de un

intitul (bases de donn es, math matiques discr tes, . . . ) ainsi quun enseignant responsable. Les

enseignants sont caract ris s par un identiant alphanum rique, leur nom et leur pr nom. Enn,

chaque s ance est identi e par le cours ainsi que le num ro de la s ance (s ance 3 du cours SI033,

s ance 1 du cours de MD021, . . . ), le type dintervention (CM, TD, TP), la date, lheure de d but

et lheure de n auxquelles la s ance a lieu ainsi que la salle et lenseignant qui dispense la s ance.

Les tudiants sinscrivent aux cours auxquels ils souhaitent assister.

Sch ma relationnel retenu

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

Etudiant ( num ro , nom , prenom , ge )

Enseignant ( id , nom , prenom )

Cours ( sigle , intitul , responsable, NombreS ances )

S ance ( cours , num ro , type , date , salle , heureD but , heureFin , enseignant )

Inscription ( etudiant , cours )

Requ tes simples

i) crire les requ tes de cr ation des tables Etudiant et S ance .

R ponse :

CREATE TABLE Etudiant (

numero

nom

prenom

age

);

PRIMARY KEY,

VARCHAR(20)

VARCHAR(50) NOT NULL,

NOT NULL,

VARCHAR(50)

NOT NULL CHECK(age > 0)

INT

CREATE TABLE S ance (

cours

numero

type

date

salle

VARCHAR(20)

VARCHAR(50)

VARCHAR(2)

DATE

VARCHAR(10)

NOT NULL,

NOT NULL,

NOT NULL CHECK(type in "CM", "TD", "TP"),

NOT NULL,

NOT NULL,

Mines de Nancy Tony Bourdier & Fabienne Thomarat 2008 2009

TD Bases de Donn es et Ing nierie des Syst mes dInformation TD 3 Requ tes SQL

heureDebut TIME

heureFin

TIME

enseignant VARCHAR(20)

FOREIGN KEY (cours)

FOREIGN KEY (enseignant)

PRIMARY KEY (cours,numero)

NOT NULL,

NOT NULL CHECK(heureFin > heureDebut),

NOT NULL,

REFERENCES Cours(sigle),

REFERENCES Enseignant(id),

);

ii)

Inscrivez l tudiant (l0372,L ponge,Bob,20) au cours (LOG015,Logique,jh1908).

R ponse :

INSERT INTO Inscription VALUES ("l0372","LOG015");

iii) Cherchez le nom et le pr nom de tous les tudiants de moins de 20 ans.

R ponse :

SELECT nom, prenom FROM Etudiant WHERE age < 20;

iv) Cherchez le nom et le pr nom de lenseignant responsable du cours de Statistiques.

R ponse :

SELECT nom, prenom

FROM

WHERE

Enseignant, Cours

responsable = id

AND intitule LIKE "Statistiques";

v) Cherchez le nom et le pr nom de tous les tudiants inscrits au cours de Probabilit s.

R ponse :

SELECT e.nom, e.prenom

FROM

WHERE

etudiant e, inscription i, cours c

e.numero = i.etudiant

AND i.cours = c.sigle

AND c.intitule like "Probabilites";

vi) D terminez le nombre denseignants intervenant dans le cours de Mod lisation Stocha-

tique.

R ponse :

SELECT

FROM

WHERE

count(DISTINCT enseignant)

Seance, Cours

sigle = cours

AND intitule LIKE "%Modelisation%";

vii) O et quand a lieu le premier cours dAlg bre lin aire ?

R ponse :

SELECT date, salle, heureDebut, heureFin

FROM

WHERE

Seance, Cours

sigle = cours

AND numero = 1

AND intitule LIKE "Algebre lineaire";

viii) Achez un emploi du temps du cours de Logique.

Mines de Nancy Tony Bourdier & Fabienne Thomarat 2008 2009

TD Bases de Donn es et Ing nierie des Syst mes dInformation TD 3 Requ tes SQL

R ponse :

SELECT numero, date, salle, heureDebut, heureFin, e.nom, e.prenom

FROM

WHERE

Seance, Cours, Enseignant e

sigle = cours

AND enseignant = id

AND intitule LIKE "Logique"

ORDER BY date,heureDebut;

ix) Pour chaque enseignant, indiquez le nombre de cours dans lesquels il intervient (restreignez

les r ponses lensemble des enseignants qui interviennent dans au moins deux cours).

R ponse :

SELECT e.nom, e.prenom, count(distinct cours)

FROM

WHERE

Seance, Cours, Enseignant e

sigle = cours

AND enseignant = e.id

GROUP BY e.id

HAVING count(distinct cours)>1

Requ tes imbriqu es

i) Ajoutez un cours magistral de Logique le 14 d cembre avec Jacques Herbrand en salle S250

de 14h 18h.

R ponse :

INSERT INTO Seance VALUES (

(SELECT sigle FROM Cours

WHERE intitule LIKE Logique),

(SELECT nombreSeances+1 FROM Cours

WHERE intitule LIKE "Logique"),

CM, 2008-12-14, S250, 14:00, 18:00,

(SELECT id FROM Enseignant

WHERE nom like "Herbrand"

AND prenom = "Jacques")

);

UPDATE cours SET nombreSeances = nombreSeances+1 WHERE intitule LIKE Logique;

ii) Listez les tudiants inscrits aucun cours.

R ponse :

SELECT e.nom, e.prenom

FROM

WHERE

Etudiant e

NOT EXISTS

(SELECT * FROM Inscription i

WHERE i.etudiant = e.numero

);

iii) Combien d tudiants (di rents) ont assist s au moins une s ance anim e par Leonhard

Euler ?

R ponse :

SELECT COUNT(DISTINCT e.numero)

FROM

WHERE

Etudiant e, Inscription i

i.etudiant = e.numero

Mines de Nancy Tony Bourdier & Fabienne Thomarat 2008 2009

TD Bases de Donn es et Ing nierie des Syst mes dInformation TD 3 Requ tes SQL

AND EXISTS(

SELECT s.cours

FROM

WHERE

Enseignant e, Seance s

e.id = s.enseignant

AND s.cours = i.cours

AND e.nom LIKE "Euler"

AND e.prenom LIKE "Leonhard"

);

Mines de Nancy Tony Bourdier & Fabienne Thomarat 2008 2009