SQL

Nom, prénom:

Note:

Question	1	2	3	4	5	6	Total
Points	3	3	2	2	2	16	28
Obtenus

Détails des calculs obligatoires. Attention au soin. Calculatrice autorisée.

---

Question 1 (3 pts)

Citez les trois manières d'enregistrer des données et expliquez en quoi cela consiste.

Solution

Le stockage est l'enregistrement des fichiers sous forme de données binaires sur un support physique (disque dur, clé USB, carte SSD, etc.).

La sauvegarde (backup en anglais) est une copie des données pertinentes à garder sous la main pour une restauration (restore en anglais) rapide en cas de besoin.

L'archivage est l'enregistrement de fichiers pour des rétentions longues à conserver, en général, pour des raisons légales.

Question 2 (3 pts)

En utilisant la table film en annexe, indiquez ce qu'affichera les requêtes suivantes:

1. select titre from film where producteur = 'Kevin Feige';
   

2. select * from film where sortie = 2017;
   

3. select producteur from film where genre = 'Animation';
   

Solution

1. titre
   Avengers: Endgame
   Spider-Man: No Way Home

2. titre	producteur	genre	sortie	nb_entrees
   Pirates des Caraïbes 5	Jerry Bruckheimer	Action	2017	450 000

3. producteur
   Chris Meledandri
   Chris Meledandri
   Mark Nielsen

Question 3 (2 pts)

Nous souhaitons créer une base de données relationnelle qui gère l'organisation d'un meeting d'athlétisme. Les athlètes participent à des épreuves.

Dans le schéma relationnel suivant:

1. Indiquez les clés primaires et étrangères.

2. Dessinez les relations entre les tables par des flèches.

Solution

Question 4 (2 pts)

Écrivez la requête SQL qui permet de créer la table athlete avec tous les attributs nécessaires (type, clé primaire etc.). Toutes les informations sont obligatoires à l'exception de la société.

Solution

create table athlete (
    num_dossard int not null primary key,
    nom text not null,
    prenom text not null,
    date_naissance date not null,
    categorie text not null,
    societe text,
    email text not null
);

Question 5 (2 pts)

Ajoutez dans la table athlete deux lignes avec les informations suivantes:

1. Bob Dupont a le dossard numéro 1, sa date de naissance est le 1 avril 2009. Il appartient donc à la catégorie U18-M. Il fait partie du Club Athlétique de Belfaux. Son email est bob.dupont@gmail.com.

2. Alice Martin à le dossard numéro 7, sa date de naissance est le 28 février 2010. Elle appartient donc à la catégorie U18-F. Son email est alice.martin@gmail.com.

Solution

1. insert into athlete values (1, 'Dupont', 'Bob', '2009-04-01', 'U18-M',
       'Club Athlétique de Belfaux', 'bob.dupont@gmail.com');
   

2. insert into athlete values (7, 'Martin', 'Alice', '2010-02-28', 'U18-F',
       null, 'alice.martin@gmail.com');
   

   insert into athlete
       (num_dossard, nom, prenom, date_naissance, categorie, email)
       values (7, 'Martin', 'Alice', '2010-02-28', 'U18-F', 'alice.martin@gmail.com');
   

Question 6 (16 pts)

1. Écrivez une requête SQL qui retourne la liste de tous les athlètes avec leurs informations.

2. Écrivez une requête SQL qui retourne le nom et le prénom des athlètes qui font partie du Club athlétique Fribourg.

3. Écrivez une requête SQL qui retourne le nom de tous les clubs sans doublon.

4. Écrivez une requête SQL qui affiche l'horaire et le lieu de l'épreuve du 100m de la catégorie U20-F.

5. Écrivez une requête SQL qui retourne la liste des épreuves des catégories U18 (femme et homme) triée par ordre alphabétique des noms des épreuves.

6. Écrivez une requête SQL qui retourne tous les résultats de l'athlète Bob Dupont.

7. Écrivez une requête SQL qui retourne le nom et le prénom des athlètes qui participent à l'épreuve de Saut en longueur.

8. Écrivez une requête qui retourne la liste de tous les résultats avec le nom de l'épreuve, ainsi que le nom et le prénom de l'athlète.

Solution

1. select * from athlete;
   

2. select nom, prenom from athlete
   where societe = 'Club athlétique Fribourg';
   

3. select distinct societe from athlete;
   

4. select horaire, lieu from epreuve
   where nom = '100m' and categorie = 'U20-F';
   

5. select * from epreuve
   where categorie like 'U18%'
   order by nom;
   

6. select participe_a.resultat from participe_a
   join athlete
   on participe_a.athlete = athlete.num_dossard
   where athlete.nom = 'Dupont' and athlete.prenom = 'Bob';
   

7. select athlete.nom, athlete.prenom from athlete
   join participe_a on participe_a.athlete = athlete.num_dossard
   join epreuve on participe_a.epreuve = epreuve.id
   where epreuve.nom = 'Saut en longueur';
   

8. select participe_a.resultat, epreuve.nom, athlete.nom, athlete.prenom from athlete
   join participe_a on participe_a.athlete = athlete.num_dossard
   join epreuve on participe_a.epreuve = epreuve.id;