Bases de données - SQL 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); Université René Descartes - Michel Soto 1 /5 Bases de données - SQL 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; Université René Descartes - Michel Soto 2 /5 Bases de données - SQL 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) ); Université René Descartes - Michel Soto 3 /5 Bases de données - SQL 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) ); Université René Descartes - Michel Soto 4 /5 Bases de données - SQL 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 ); Université René Descartes - Michel Soto 5 /5