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

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_l’attribut type [ liste_de_contraintes_d’attribut ] nom_de_l’attribut type [ liste_de_contraintes_d’attribut ] . . . 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_l’attribut in domaine_de_définition ) PRIMARY KEY(liste_d’attributs), FOREIGN KEY(nom_de_l’attribut) . . . . . . REFERENCES nom_de_la_table(nom_de_l’attribut)

 

  



Mines de Nancy – Tony Bourdier & Fabienne Thomarat – 2008 − 2009

TD Bases de Données et Ingénierie des Systèmes d’Information 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 d’imbriquer 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,11*s1 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 d’Information 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 d’informations

La direction des études des Mines de Nancy a décidé d’informatiser 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 identifié de façon unique par un sigle (SI033, MD021, . . . ) et possède un intitulé (bases de données, mathématiques discrètes, . . . ) ainsi qu’un enseignant responsable. Les enseignants sont caractérisés par un identifiant alphanumérique, leur nom et leur prénom. Enfin, chaque séance est identifié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 d’intervention (CM, TD, TP), la date, l’heure de début et l’heure de fin auxquelles la séance a lieu ainsi que la salle et l’enseignant qui dispense la séance. Les étudiants s’inscrivent 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 d’Information 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 l’enseignant 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 d’enseignants 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 d’Algè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) Affichez 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 d’Information 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 à l’ensemble 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 (diffé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 d’Information 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