Discussion :

[Julien333]

Bonjour et bonne année à tous !

je débute sur SQL et je dois réaliser cet exercice.

Pourriez vous m'aider svp? Ci dessous l’énoncé et mes premières réponses. je bloque surtout sur la 4 et la 5

merci pour votre aide!

Enoncé :

On considère une base de données portant sur une course de rallye automobile et comportant les tables suivantes :

SPECIALE (NumeroSpeciale, Depart, Arrivee, Longueur, CodeCategorie)
CATEGORIE (CodeCategorie, NomCategorie)
PILOTE (Dossard, Nom, Age, Nationalite)
PARTICIPE (Dossard, NumeroSpeciale, Classement,Temps)

Une course de rallye automobile comporte plusieurs spéciales. Les pilotes sont inscrits comme participants à toutes les spéciales, et leurs performances (temps et classement) sont enregistrées à la fin de chaque spéciale, s’ils sont arrivés au bout ! Le temps est exprimé en minutes et le classement est le numéro d’ordre. Une spéciale est caractérisée par : numerospeciale qui sert de clé, Depart et Arrivee sont les lieux de départ et arrivée, Longueur est la longueur en kilomètres de la course, CodeCategorie fait référence à la catégorie de la spéciale. Une catégorie de spéciale est caractérisée par : CodeCategorie qui sert de clé, NomCategorie qui est le type de la spéciale (« asphalte », «terre »,…). Un pilote est caractérisé par : Dossard qui sert de clé, Nom est le nom du pilote.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE speciale (numerospeciale char(2) not null unique, depart varchar2(20), arrivee
varchar2(20), longueur number(3), codecategorie char(2)) ;
 
INSERT INTO speciale VALUES (S1, ‘le Mans’,’Alencon’, 200, C1);
INSERT INTO speciale VALUES (S4, ‘Alencon’,  Paris , 230,  C1 );
INSERT INTO speciale VALUES ( S2 , Cluny , Cluny , 49,  C2 );
INSERT INTO speciale VALUES ( S3 , Paris , Roubaix ,190, C1 );
INSERT INTO speciale VALUES ( S6 ,  Roubaix ,  Meteren , 100, C2 );
INSERT INTO speciale VALUES ( S5 , Meteren , Cluny , 400,  C3 );

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE TABLE categorie (codecategorie char(2) not null unique, nomcategorie varchar2(15));
INSERT INTO categorie VALUES ( C1 ,  asphalte );
INSERT INTO categorie VALUES ( C2 , terre );
INSERT INTO categorie VALUES ( C3 ,  neige );

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE pilote (dossard char(2) not null unique, nom varchar2(30), age number(2), nationalite
varchar2(30));
INSERT INTO pilote VALUES ( D1 , Jean-Pierre Beltoise , 54,  Francaise ) ;
INSERT INTO pilote VALUES ( D2 , Ayrton Senna , 48,  Brésilienne ) ;
INSERT INTO pilote VALUES ( D3 , Ari Vatanen , 63, Finlandaise ) ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE participe (dossard char(2), numerospeciale char(2), classement number(2), temps
number(4), unique(dossard,numerospeciale)) ;
 
INSERT INTO participe VALUES ( D1 , S1 , 1, 45);
INSERT INTO participe VALUES ( D2 , S1 , 2, 48) ;
INSERT INTO participe VALUES ( D2 , S2 , 1, 20) ;
INSERT INTO participe VALUES ( D1 , S2 , 2, 21) ;
INSERT INTO participe VALUES ( D1 , S3 , null, null);
INSERT INTO participe VALUES ( D2 , S3 , 1, 300);
INSERT INTO participe VALUES ( D3 , S1 , 3, 50);
INSERT INTO participe VALUES ( D3 ,  S2 , 3, 22);
INSERT INTO participe VALUES ( D3 ,  S3 , 2, 340);
INSERT INTO participe VALUES ( D3 , S4 , 1, 300);
INSERT INTO participe VALUES ( D2 , S4 , 2, 340);
INSERT INTO participe VALUES ( D3 ,  S5 , 1, 400);
INSERT INTO participe VALUES ( D2 , S5 , 2, 430);
INSERT INTO participe VALUES ( D3 , S6 , 2, 70);
INSERT INTO participe VALUES ( D2 ,  S6 , 1, 65);

Ecrire en SQL les requêtes suivantes :

1. Donner les lieux de départ et d’arrivée des spéciales qui se courent sur terre.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select depart, arrivee from speciale where codecategorie = (select codecategorie from categorie where nomcategorie = ‘terre’) ;

2. Donner le nom des pilotes qui ont gagné les spéciales de plus de 50 kilomètres.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select distinct (pilote.nom) from pilote, participe speciale where.classement = 1 and speciale.longueur>50 ;

3. Donner le numéro des spéciales qui se courent sur terre et des spéciales en boucle où la ville d’arrivée est la ville de départ.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select speciale.numerospeciale, speciale.depart, special.arrivee, categorie.nomcategorie from speciale, categorie where categorie.nomcategorie = ‘terre’ OR S2

4. Donner, pour chaque pilote recensé, son nom, le nombre de spéciales auxquelles il a participé et son temps cumulé.

5. Donner le nom des pilotes qui n’ont gagné aucune spéciale


6. Donner la liste des pilotes par ordre alphabétique

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select nom from pilote order by nom ;

7. Y a-t-il des pilotes dont le nom ne contient pas la lettre a ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select nom from pilote where nom not in (select nom from pilote where nom like ‘%a%’) ;

8. Donner la liste des pilotes par âge décroissant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select nom, age from piliteo order by desc ;

[SQLpro]

1. Donner les lieux de départ et d’arrivée des spéciales qui se courent sur terre.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select depart, arrivee from speciale where codecategorie = (select codecategorie from categorie where nomcategorie = ‘terre’) ;

Cela ne marchera pas s'il y a plusieurs "codecategorie" correspondant au "nomcategorie" 'terre'.
Pourquoi faire compliquer quand on peut faire une simple jointure ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT depart, arrivee 
FROM   speciale AS s
       JOIN categorie  AS c
          ON s.... = c....
WHERE  codecategorie = 'terre'

Je vous laisse compléter les manques...

2. Donner le nom des pilotes qui ont gagné les spéciales de plus de 50 kilomètres.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select distinct (pilote.nom) from pilote, participe speciale where.classement = 1 and speciale.longueur>50 ;

Une jointure se fait à l'aide de l'opérateur de jointure JOIN. Lisez le cours suivant :
https://sqlpro.developpez.com/cours/sqlaz/jointures/

3. Donner le numéro des spéciales qui se courent sur terre et des spéciales en boucle où la ville d’arrivée est la ville de départ.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select speciale.numerospeciale, speciale.depart, special.arrivee, categorie.nomcategorie from speciale, categorie where categorie.nomcategorie = ‘terre’ OR S2

Une jointure se fait à l'aide de l'opérateur de jointure JOIN. Lisez le cours suivant :
https://sqlpro.developpez.com/cours/sqlaz/jointures/

6. Donner la liste des pilotes par ordre alphabétique

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select nom from pilote order by nom ;

Oui

7. Y a-t-il des pilotes dont le nom ne contient pas la lettre a ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select nom from pilote where nom not in (select nom from pilote where nom like ‘%a%’) ;

Pourquoi faire simple quand on peut faire compliqué ?
Virez le NOT IN et utilisez un NOT LIKE….

8. Donner la liste des pilotes par âge décroissant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Select nom, age from piliteo order by desc ;

Oui, mais manque sur quelle colonne faire le tri (bien vu tata yoyo, comme dirais Annie Cordy !

A +

Lisez le cours sur le SQL que j'ai écrit !

[tatayo]

Pour la 8 je dis non, il manque quelque chose dans la clause ORDER BY.

Tatayo.

[soazig]

Bonjour, pour le 8 vous pouvez aussi regarder le nom de votre table piliteo ? est-ce bien sûr ?
Il n'y a pas que là que les noms de tables sont erronées (par exemple special.arrivee sans e à speciale).
Il semblerait que les requetes aient été écrite avec un traitement de texte, faite attention les chaines de caractères sont entre quotes simple ' et pas des quotes obliques.

Bonne soirée
Soazig

[soazig]

Rebonjour,
Pour le 4, il faut utiliser une jointure et les mots clés SUM, Count et group by
Pour le 5, on peut utiliser un not exist.
PS : ce serait bien si tu testais tes requetes, pour un exercice de sql, ça peut aider.
PS2: Je ne te donnerai pas la réponse puisqu'il s'agit d'un exercice, juste des pistes.
Cor

[escartefigue]

Bonjour et bonne année à tous !

Bonjour et bonne année également

Au sujet des requêtes SQL, les bonnes pistes ont déjà été données par mes collègues, je n'y reviendrai donc pas

Au sujet du modèle de données par contre, vous pourrez formuler quelques remarques à votre formateur, avec diplomatie bien sur

PILOTE (Dossard, Nom, Age, Nationalite)

Si le dossard est un attribut du pilote, alors cela signifie qu'un pilote porte le même dossard indépendamment de la course et de la saison , c'est aberrant !
Pire : stocker l'âge est une erreur de modélisation de débutant, qu'un prof propose de stocker cet attribut mérite un carton rouge . L'âge est une donnée à calculer en fonction de la date de naissance.

Un pilote est caractérisé par : Dossard qui sert de clé, Nom est le nom du pilote.

Super : le numéro de dossard étant la clef, à chaque fois qu'un pilote change de dossard, il faut le recréer en re-saisissant son nom, son prénom, sa date de naissance etc. avec tous les riques d'erreur que cela comporte bien sur

[SQLpro]

...
Pour le 5, on peut utiliser un not exist.
...

EXISTS avec un S à la fin. c'est le verbe à la 3e personne du singulier….

A +

[soazig]

Bonjour,

EXISTS avec un S à la fin.

Je conviens de mon erreur, mais une recherche sur le mot clé erroné Exist en sql permet de trouver la syntaxe du exists et du not exists, donc ce n'était pas si grave.
Cordialement
Soazig

[Julien333]

Merci à tous pour votre aide.

je vais faire l’exercice en m'aidant de vos conseils et je vous montrerai ce que j'ai fait.

@ escartefigue : je n'y manquerai pas. Ca peut être marrant !!

Merci .

Bonne journée