Correction TDM de Bases de Données

Bases de données - SQL
Université René Descartes - Michel Soto /5
1
Correction TDM de Bases de Données
UTILISATION DU LMD ET DU LDD SQL
Le TDM se déroule sous Linux à partir de la machine saphyr
Avec la base de données maitrisedb écrire en SQL les requêtes suivantes :
psql –h gwens maitrisedb
a) Produit cartésien de la table etudiant avec elle-même.
select * from etudiant x, etudiant y;
b) Liste, classée par prénom puis par noms, des couples de noms d'étudiants ayant le
même prénom.
select x.nom, y.nom, x.prenom from etudiant x, etudiant y
where x.prenom=y.prenom and
x.nom<>y.nom
order by x.prenom, x.nom, y.nom;
c) Liste, classée par prénom puis par noms, des couples de noms d'étudiants ayant le
même prénom telle que si A et B ont le même prénom le seul le couple (A, B)
apparaisse.
select x.nom, y.nom, x.prenom from etudiant x, etudiant y
where x.prenom=y.prenom and
x.nom<y.nom
order by x.prenom, x.nom, y.nom;
d) Liste des prénoms, noms des étudiants ayant le même prénom
(select x.prenom, x.nom from etudiant x, etudiant y
where x.prenom=y.prenom and x.nom<y.nom)
union
(select x.prenom, x.nom from etudiant x, etudiant y
where x.prenom=y.prenom and x.nom>y.nom);

Bases de données - SQL
Université René Descartes - Michel Soto /5
2
ou bien :
select distinct x.nom, x.prenom from etudiant x, etudiant y
where x.prenom=y.prenom and
x.nom<>y.nom;
ou bien :
select prenom, nom from etudiant
where prenom in
(select distinct x.prenom from etudiant x, etudiant y
where x.prenom=y.prenom and
x.nom<>y.nom);
e) Liste des noms, prénoms des étudiants du groupe TD n°1 de l’UE Informatique 3
select nom, prenom from etudiant e, etudiant_td t, ue u
where e.etudiant_id=t.etudiant_id and groupe=1 and
t.ue_id=u.ue_id and u.ue.nom='Informatique 3';
g) Nombre d’étudiants par groupe de TD
select groupe, count(*) from etudiant_td
group by groupe;
h) N° des groupes de TD ayant plus de 26 étudiants
select groupe, count(*) from etudiant_td
group by groupe
having count(*)>26;
Avec la base de données votre_login écrire en SQL les requêtes suivantes :
a) Recréer, si ce n’est déjà fait, maitrisedb dans votre BD votre_login en utlisant
pg_dump et/ou psql.
pg_dump -h gwens --no-owner -x –f dump_maitrisedb maitrisedb
psql –h gwens –f dump_maitrisedb votre_login
b) Visualiser la relation etudiant
psql –h gwens votre_login
select * from etudiant;

Bases de données - SQL
Université René Descartes - Michel Soto /5
3
c) Insérer un tuple dans la relation etudiant
insert into etudiant values (54, ‘MARTIN’, ‘Pierre’);
d) Changer le groupe TD 1 en 3 et le groupe TD 2 en 4
update etudiant_td set groupe = groupe + 2;
e) Supprimer les tuples dont le groupe TD vaut 3
delete from etudiant_td where groupe=3;
f) Détruire la relation etudiant
drop table etudiant ;
g) Recréer les tables (schéma + données) de la BD votre_login avec les clés primaires
de chaque table.
drop table etudiant_td, etudiant_ue, td, ue;
\q
pg_dump –h gwens --no-owner -x –f dump_maitrisedb maitrisedb
Editer le fichier dump_maitrisedb et modifier les CREATE TABLE, tels que ci-dessous,
pour ajouter les clés primaires. Sauvegarder ensuite vos modifications puis faire:
psql –h gwens –f dump_maitrisedb votre_login
CREATE TABLE etudiant (
etudiant_id integer NOT NULL PRIMARY KEY,
nom character(15) NOT NULL,
prenom character(20) NOT NULL
);
CREATE TABLE etudiant_ue (
etudiant_id integer NOT NULL,
ue_id integer NOT NULL
PRIMARY_KEY (etudiant_id, ue_id)
);
CREATE TABLE etudiant_td (
etudiant_id integer NOT NULL,
ue_id integer NOT NULL,
groupe integer NOT NULL,
PRIMARY_KEY (etudiant_id, ue_id, groupe)
);

Bases de données - SQL
Université René Descartes - Michel Soto /5
4
CREATE TABLE ue (
ue_id integer NOT NULL PRIMARY KEY,
ue_nom character(20) NOT NULL
);
CREATE TABLE td (
ue_id integer NOT NULL,
groupe integer NOT NULL,
jour character(8),
horaire character(11),
salle character(10),
PRIMARY_KEY (ue_id, groupe)
);
\q
h) Sauvegarder la BD ainsi créée dans un fichier dump_votre_login_pk.
pg_dump -h gwens --no-owner -x –f dump_votre_login_pk votre_login
i) Supprimer toutes les relations de la BD votre_login.
psql –h gwens votre_login
drop table etudiant, etudiant_td, etudiant_ue, td, ue;
\q
j) Dans la BD votre_login, créer le schéma d’une BD conforme au schéma de la BD
utilisée en TD (tennis)
psql votre_login
CREATE TABLE joueur (
Nom character(30) NOT NULL PRIMARY KEY,
Prenom character(30),
Age integer,
Nationalite character(30)
);

Bases de données - SQL
Université René Descartes - Michel Soto /5
5
CREATE TABLE rencontre (
nomgagnant character(30) NOT NULL,
nomperdant character(30) NOT NULL,
lieutournoi character(30) NOT NULL,
date date NOT NULL,
score character(10),
PRIMARY KEY (NomGagnant, NomPerdant, LieuTournoi, Date)
);
CREATE TABLE gain (
nomjoueur character(30) NOT NULL,
lieutournoi character(30) NOT NULL,
date date NOT NULL,
prime integer,
nomsponsor character(20),
PRIMARY KEY (nomjoueur, lieutournoi, date)
);
CREATE TABLE sponsor (
Nom character(20) NOT NULL PRIMARY KEY,
Adresse varchar(60) NOT NULL,
ChiffreAffaire integer
);