2023/2024 TP-LANGAGE SQL
SIGL2
Enseignant: Dr SILUE Kolo
KOFFI SOWORE LANDRY
Travail à faire :
1. cérate database TourIvoire ;
2.
create table EQUIPE(
CodeEquipe char(3) not null primary key,
NomEquipe varchar(30) not null,
DirecteurSportif varchar(20) not null,
constraint U_Equipe unique(NomEquipe)
);
go
create table PAYS(
CodePays char(3) not null primary key,
NomPays varchar(30) not null,
constraint CK_Pays check(NomPays = upper(NomPays))
);
go
create table COUREUR(
NumeroCoureur char(3) not null primary key,
NomCoureur varchar(30) not null,
CodeEquipe char(3) not null,
CodePays char(3) not null,
constraint FK_Coureur foreign key (CodeEquipe) references EQUIPE(CodeEquipe),
constraint FK_Pays foreign key (CodePays) references PAYS(CodePays)
);
go
create table TYPE_ETAPE(
CodeType int primary key identity(1,1) not null,
LibelleType varchar(50) not null
);
go
create table ETAPE(
NumeroEtape int primary key not null,
DateEtape date not null,
VilleDep varchar(50) not null,
VilleArr varchar(50) not null,
NbKm int not null,
CodeType int not null,
constraint FK_Type foreign key (CodeType) references TYPE_ETAPE(CodeType)
);
1
go
CREATE TABLE PARTICIPER(
NumeroCoureur CHAR(3) not null,
NumeroEtape INT not null,
TempsRealise TIME not null,
PRIMARY KEY(NumeroCoureur,NumeroEtape),
CONSTRAINT FK_Participer_Coureur FOREIGN KEY(NumeroCoureur) REFERENCES
COUREUR(NumeroCoureur),
CONSTRAINT FK_Participer_Etape FOREIGN KEY(NumeroEtape) REFERENCES
ETAPE(NumeroEtape)
);
go
create table ATTRIBUER_BONIFICATION(
NumeroEtape int not null,
NumeroCoureur char(3) not null,
constraint PK_Attribuer_Bonification primary key(NumeroEtape,NumeroCoureur),
constraint FK_Etape_AB foreign key (NumeroEtape) references ETAPE(NumeroEtape),
constraint FK_Coureur_AB foreign key (NumeroCoureur) references COUREUR(NumeroCoureur),
km int not null,
NbSecondes int not null
);
3. Modifier la table « COUREUR >> pour y ajouter les attributs << téléphone >> et << courriel »
ALTER TABLE COUREUR
ADD telephone VARCHAR(30), courriel VARCHAR(50) not null;
4. Définir une contrainte qui permet de respecter le format d'adresse électronique
conventionnelle.
ALTER TABLE COUREUR
ADD CONSTRAINT CHK_courriel CHECK (courriel LIKE '%_@__%.__%');
5. Supposons que le nom d'un coureur ait été défini avec une taille de données de 30 caractères,
maintenant, on vous demande d'augmenter cette valeur à 80 caractères pour pouvoir prendre en
compte tous les cas de figure.
ALTER TABLE COUREUR
ALTER COLUMN NomCoureur VARCHAR(80);
6. Enregistrer nécessairement assez de tuples dans chacune des tables pour pouvoir traiter les requêtes
de la deuxième partie ci-après.
2
INSERT INTO EQUIPE (CodeEquipe, NomEquipe, DirecteurSportif)
VALUES
('E1', 'IvoirCyclisme', 'Koffi landry'),
('E2', 'Équipe', 'Marie jean'),
('E3', 'Équipe C', 'Paul Durand'),
('E4', 'Équipe D', 'Sophie Lass'),
('E5', 'Équipe E', 'David Luiz'),
('E6', 'Équipe F', 'David Okit'),
('E7', 'Équipe G', 'Émiliano Tayc');
INSERT INTO PAYS (CodePays, NomPays)
VALUES
('CIV', 'Côte D’Ivoire'),
('US', 'États-Unis'),
('ES', 'Espagne'),
('IT', 'Italie'),
('DE', 'Allemagne');
INSERT INTO COUREUR (NumeroCoureur, NomCoureur, Courriel, CodeEquipe, CodePays)
VALUES
(1, 'Alice Martin', '
[email protected]', 'E1', 'CIV'),
(2, 'Bob Dupont', '
[email protected]', 'E2', 'CIV'),
(3, 'Caroline Leroy', '
[email protected]', 'E3', 'CIV'),
(4, 'David Durand', '
[email protected]', 'E4', 'US'),
(5, 'Émilie Garcia', '
[email protected]', 'E5', 'DE'),
(6, 'David Okit', '
[email protected]', 'E6', 'US'),
(7, 'Émiliano Tayc', '
[email protected]', 'E7', 'DE');
INSERT INTO TYPE_ETAPE (LibelleType)
VALUES
('Circuit fermés'),
('Course en ligne'),
('Course en ligne'),
('Circuit fermés'),
('Course en ligne'),
('Course en ligne'),
('Course en ligne');
INSERT INTO ETAPE (NumeroEtape, DateEtape, VilleDep, VilleArr, NbKm, CodeType)
3
VALUES
(1, '2024-05-15', 'Bouaké', 'Bouaké', 89.1,1),
(2, '2024-05-16', 'Bouaké', 'Dabakala', 132.8,2),
(3, '2024-05-17', 'Bouaké', 'Daoukro', 160,3),
(4, '2024-05-18', 'Dimbokro', 'Dimbokro', 76,4),
(5, '2024-05-19', 'Loukouyakro', 'Toumodi', 16.3,5),
(6, '2024-05-18', 'Yamoussoukro', 'Daloa', 97.6,6),
(7, '2024-05-18', 'Daloa', 'Bouaflé', 96.9,7);
INSERT INTO ATTRIBUER_BONIFICATION (NumeroEtape, NumeroCoureur, km, Rang,
NbSecondes)
VALUES
(1, 1, 60, 1, 10),
(2, 2, 80, 3, 8),
(3, 3, 50, 5, 6),
(4, 4, 40, 2, 4),
(5, 5, 20, 5, 4),
(6, 6, 45, 9, 4),
(7, 7, 37, 6, 2);
INSERT INTO PARTICIPER (NumeroCoureur, NumeroEtape, TempsRealise)
VALUES
(1, 1, '02:35:00'),
(2, 1, '02:40:00'),
(3, 1, '02:45:00'),
(4, 1, '02:30:00'),
(5, 1, '02:38:00'),
(6, 1, '02:42:00'),
(7, 1, '02:50:00'),
4
II- Exprimez en SQL les requêtes
1. Quelle est la composition de l'équipe IvoirCyclisme (Numéro, nom et pays des
coureurs)?
SELECT COUREUR.NumeroCoureur, COUREUR.NomCoureur, PAYS.NomPays
FROM COUREUR, EQUIPE, PAYS
WHERE COUREUR.CodeEquipe = EQUIPE.CodeEquipe
AND COUREUR.CodePays = PAYS.CodePays
AND EQUIPE.NomEquipe = 'IvoirCyclisme';
2. Quel est le nombre de kilomètres total du Tour de la Réconciliation 2016?
SELECT SUM(NbKm) AS TotalKilometres
FROM ETAPE;
3. Quel est le nombre de kilomètres total des étapes de type "Circuits Fermés" ?
SELECT SUM(NbKm) AS TotalCircuitFermesKm
FROM ETAPE, TYPE_ETAPE
WHERE ETAPE.CodeType = TYPE_ETAPE.CodeType
AND TYPE_ETAPE.LibelleType = 'Circuit Fermés';
4. Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications?
SELECT DISTINCT COUREUR.NomCoureur
FROM COUREUR
WHERE COUREUR.NumeroCoureur NOT IN (SELECT NumeroCoureur FROM
ATTRIBUER_BONIFICATION);
5. Quels sont les noms des coureurs qui ont participé à toutes les étapes?
SELECT COUREUR.NomCoureur
FROM COUREUR
GROUP BY COUREUR.NomCoureur
HAVING COUNT(DISTINCT COUREUR.NumeroCoureur) = (SELECT COUNT(*) FROM
ETAPE);
5
6. Quel est le classement général des coureurs (nom, code équipe, code pays et temps des
coureurs) à l'issue des 7 étapes sachant que les bonifications ont été intégrées dans les
temps réalisés à chaque étape?
SELECT
C.NumeroCoureur,
C.NomCoureur,
C.CodeEquipe,
C.CodePays,
CONVERT(TIME, DATEADD(SECOND, SUM(DATEDIFF(SECOND, '00:00:00',
P.TempsRealise)), 0)) AS TempsTotal
FROM
COUREUR C
JOIN
PARTICIPER P ON C.NumeroCoureur = P.NumeroCoureur
GROUP BY
C.NomCoureur, C.CodeEquipe, C.CodePays, C.NumeroCoureur
ORDER BY
SUM(DATEDIFF(SECOND, '00:00:00', P.TempsRealise)) ASC;
7. Quel est le classement par équipe à l'issue des 7 étapes (nom et temps des équipes)?
SELECT EQUIPE.NomEquipe,
SUM(DATEDIFF(SECOND, CAST('00:00:00' AS TIME),
PARTICIPER.TempsRealise)) AS TempsTotal
FROM EQUIPE
JOIN COUREUR ON EQUIPE.CodeEquipe = COUREUR.CodeEquipe
JOIN PARTICIPER ON COUREUR.NumeroCoureur = PARTICIPER.NumeroCoureur
GROUP BY EQUIPE.NomEquipe
ORDER BY TempsTotal;
8. Quel est le temps moyen réalisé par les coureurs de chaque à l'étape «< Bouaké-
Dabakala » ?
SELECT EQUIPE.NomEquipe,
AVG(DATEDIFF(SECOND, CAST('00:00:00' AS TIME), PARTICIPER.TempsRealise))
AS TempsMoyen
FROM EQUIPE
JOIN COUREUR ON EQUIPE.CodeEquipe = COUREUR.CodeEquipe
JOIN PARTICIPER ON COUREUR.NumeroCoureur = PARTICIPER.NumeroCoureur
JOIN ETAPE ON PARTICIPER.NumeroEtape = ETAPE.NumeroEtape
WHERE ETAPE.VilleDep = 'Bouaké' AND ETAPE.VilleArr = 'Dabakala'
GROUP BY EQUIPE.NomEquipe;
6
9. Donner les coureurs qui ont réalisé un temps de courses inférieur à la moyenne de l'étape
de «Bouaké-Daoukro »> ?
SELECT COUREUR.NomCoureur, PARTICIPER.TempsRealise
FROM COUREUR
JOIN PARTICIPER ON COUREUR.NumeroCoureur = PARTICIPER.NumeroCoureur
JOIN ETAPE ON PARTICIPER.NumeroEtape = ETAPE.NumeroEtape
WHERE ETAPE.VilleDep = 'Bouaké' AND ETAPE.VilleArr = 'Daoukro'
AND DATEDIFF(SECOND, CAST('00:00:00' AS TIME), PARTICIPER.TempsRealise) < (
SELECT AVG(DATEDIFF(SECOND, CAST('00:00:00' AS TIME),
PARTICIPER.TempsRealise))
FROM PARTICIPER
JOIN ETAPE ON PARTICIPER.NumeroEtape = ETAPE.NumeroEtape
WHERE ETAPE.VilleDep = 'Bouaké' AND ETAPE.VilleArr = 'Daoukro'
)
ORDER BY PARTICIPER.TempsRealise;
10. Donner la liste des coureurs qui participé aux étapes où << Isiaka CISSE» de la COTE
D'IVOIRE a réalisé le meilleur temps.
SELECT COUREUR.NomCoureur
FROM COUREUR
JOIN PARTICIPER ON COUREUR.NumeroCoureur = PARTICIPER.NumeroCoureur
JOIN ETAPE ON PARTICIPER.NumeroEtape = ETAPE.NumeroEtape
WHERE ETAPE.NumeroEtape IN (
SELECT ETAPE.NumeroEtape
FROM PARTICIPER
JOIN COUREUR ON PARTICIPER.NumeroCoureur = COUREUR.NumeroCoureur
JOIN ETAPE ON PARTICIPER.NumeroEtape = ETAPE.NumeroEtape
WHERE COUREUR.NomCoureur = 'Isiaka CISSE'
);
7
III.Programation T-SQL
1. Afficher la moyenne du temps réaliser par des coureurs d’un pays quelconque
CREATE FUNCTION MoyenneTempsParPays (@NomPays VARCHAR(50))
RETURNS FLOAT
AS
BEGIN
DECLARE @MoyenneTemps FLOAT;
SELECT @MoyenneTemps = AVG(DATEDIFF(SECOND, CAST('00:00:00' AS TIME),
TempsRealise))
FROM PARTICIPER
JOIN COUREUR ON PARTICIPER.NumeroCoureur = COUREUR.NumeroCoureur
JOIN PAYS ON COUREUR.CodePays = PAYS.CodePays
WHERE PAYS.NomPays = @NomPays;
RETURN @MoyenneTemps;
END;
SELECT dbo.MoyenneTempsParPays('Côte D’Ivoire');
2. Afficher les noms des coureurs avec leurs équipes et le pays d'origine
CREATE PROCEDURE AfficherCoureursAvecEquipesPays
AS
BEGIN
SELECT NomCoureur, NomEquipe, NomPays
FROM COUREUR, EQUIPE,PAYS
WHERE COUREUR.CodeEquipe = EQUIPE.CodeEquipe
AND COUREUR.CodePays = PAYS.CodePays;
END;
EXEC AfficherCoureursAvecEquipesPays;
3. compter le nombre de coureur d'une équipe connaissant le directeur sportif
CREATE FUNCTION NbCoureursEquipe (@DirecteurSportif VARCHAR(80))
RETURNS INT
8
AS
BEGIN
DECLARE @NombreCoureurs INT;
SELECT @NombreCoureurs = COUNT(*)
FROM COUREUR
WHERE CodeEquipe IN (SELECT CodeEquipe FROM EQUIPE WHERE DirecteurSportif =
@DirecteurSportif);
RETURN @NombreCoureurs;
END;
SELECT dbo.NbCoureursEquipe('Koffi Landry');
4. Donner le rang d’un coureur , lorsqu’on connait son numéro et le numéro de l’étape parcourues.
CREATE FUNCTION RangCoureur (
@NumeroCoureur CHAR(3),
@NumeroEtape INT
)
RETURNS INT
AS
BEGIN
DECLARE @Rang INT;
SELECT @Rang = Rang
FROM ATTRIBUER_BONIFICATION
WHERE NumeroCoureur = @NumeroCoureur
AND NumeroEtape = @NumeroEtape;
RETURN @Rang;
END;
SELECT dbo.RangCoureur(1,1);
9
5. Donner le top 10 des meilleurs coureurs d’une étapes données.
CREATE PROCEDURE MeilleursCoureurs
@NumeroEtape INT
AS
BEGIN
SELECT TOP 10
NomCoureur,
TempsRealise
FROM PARTICIPER,COUREUR
WHERE PARTICIPER.NumeroCoureur = COUREUR.NumeroCoureur
AND PARTICIPER.NumeroEtape = @NumeroEtape
ORDER BY TempsRealise;
END;
EXEC MeilleursCoureurs @NumeroEtape = 1;
10
