Corrigé
Correction Exercice conception base + SQL
MCD :
MLD :
LIEU(LNUM, LNOM)
COURSE(CNUM, CNOM , CDATE, #LNUM)
EQUIPE(ENUM, ENOM)
ATHLETE(ANUM, ANOM, ADATENAIS, AADRESSE, ACP, AVILLE, ATEL,#ENUM)
PARTICIPER(#CNUM , #ANUM,prang)
Partie SQL :
PARTIE 1
1)
create database COURSE ;
2)
CREATE TABLE course.lieu(Lnum int not null PRIMARY KEY, Lnom varchar(50));
CREATE TABLE course.course(Cnum int not null PRIMARY KEY, cnom varchar(50) not null, Cdte date not null, Lnum int,CONSTRAINT fk_course_lieu FOREIGN KEY (Lnum) REFERENCES lieu(Lnum));
CREATE TABLE course.equipe(Enum int not null PRIMARY KEY, Enom varchar(50) not null);
CREATE TABLE course.athlete(Anum int not null PRIMARY KEY, Anom varchar(50) not null, ADATENAIS Date, Aadresse varchar(50), CP char(5), Ville varchar(50), Atel char(15), Enum int, CONSTRAINT fk_Athlete_equipe FOREIGN KEY (Enum) REFERENCES equipe(Enum));
CREATE TABLE course.C_PARTICIPE(Cnum int, Anum int, Prang int, primary key(Cnum, Anum), CONSTRAINT fk_participe_course FOREIGN KEY (Cnum) REFERENCES course(Cnum), CONSTRAINT fk_participe_Athlete FOREIGN KEY (Anum) REFERENCES athlete(Anum));
3)
CREATE user 'manager'@'%' IDENTIFIED BY 'manager';
GRANT ALL PRIVILEGES ON cours .* TO 'manager'@'%' WITH GRANT OPTION ;
4)
CREATE user 'christophe'@'%' IDENTIFIED BY '123456';
GRANT SELECT ON course.course TO 'christophe'@'%';
5)
ayant créé mon utilisateur avec le % mon utilisateurs possédes déjà le droit de se connecter partout sur le réseau
6)
INSERT INTO course.equipe VALUES (15, ‘titanium’);
7)
C:\wamp\bin\mysql\mysql5.0.51a\bin\mysql.exe -u manager -h localhost -p
8)
describe equipe;
UPDATE cours.Athlete SET enum =15 WHERE anom = "Yvan";
partie 2 /
1)
SELECT athlete.Anom
FROM athlete, equipe
WHERE athlete.Enum = equipe.Enum
AND equipe.enom = 'ONDINS'
ORDER BY athlete.Anom ;
2)
SELECT athlete.Anom
FROM athlete, equipe
WHERE athlete.Enum = equipe.Enum
AND equipe.enom = 'HEROS'
ORDER BY athlete.Anom ;
3)
SELECT course.Cnom
FROM course, Lieu
WHERE course.Lnum = Lieu.Lnum
AND Lieu.Lnom = "Finlande"
ORDER BY course.Cnom ;
4)
SELECT course.Cnom
FROM course, Lieu
WHERE course.Lnum = Lieu.Lnum
AND Lieu.Lnom = "Paris"
ORDER BY course.Cnom ;
5)
SELECT Athlete.Anom
FROM Athlete, C_PARTICIPE, course
WHERE athlete.anum = C_PARTICIPE.anum
AND C_PARTICIPE.cnum = course.cnum
AND course.cnom = "ETE2009"
ORDER BY Athlete.Anom;
6)
SELECT Athlete.Anom
FROM Athlete, C_PARTICIPE, course
WHERE athlete.anum = C_PARTICIPE.anum
AND C_PARTICIPE.cnum = course.cnum
AND course.cnom = "JUNIOR"
ORDER BY Athlete.Anom;
3° partie
1)
SELECT Athlete.anom, Athlete.aage, C_PARTICIPE.Prang
FROM Athlete, C_PARTICIPEWHERE Athlete.Anum = C_PARTICIPE.Anum
ORDER BY C_PARTICIPE.Cnum, C_PARTICIPE.prang ;
2)
SELECT Athlete.Anom, Equipe.Enom
FROM Athlete, Equipe
WHERE Athlete.Enum = Equipe.Enum
GROUP BY equipe.Enom ;
3)
SELECT athlete.Anom
FROM C_PARTICIPE, Athlete
WHERE C_PARTICIPE.anum = Athlete.anum
AND C_PARTICIPE.cnum
IN (SELECT course.cnum
FROM course, C_PARTICIPE, Athlete
WHERE course.cnum = C_PARTICIPE.cnum
AND C_PARTICIPE.anum = Athlete.anum
AND Athlete.Anom = "MORCELLI")GROUP BY athlete.Anom ;
4)
CREATE VIEW C_AOUITA AS SELECT c_participe.prang, C_participe.Cnum
FROM c_participe, Athlete
WHERE c_paticipe.Anum = Athlete.Anum
Athlete.anom = "AOUITA" ;
SELECT athlete.anom, C_participe.Cnum, c_participe.PRANG, c_AOUITA.PRANG
FROM athlete, c_participe, C_AOUITA, equipe
WHERE athlete.Anum = c_participe.Anum
AND equipe.enum = athlete.enum
AND c_participe.PRANG > c_AOUITA.PRANG
AND c_AOUITA.cnum = c_participe.cnum
AND equipe.enom = "ducerfal
ORDER BY Athlete.anom, C_participe.Cnum ;
5 )
SELECT athlete.Anom, equipe.enom
FROM athlete, equipe
WHERE athlete.Enum = equipe.Enum
AND athlete.anum NOT IN (SELECT c_participe.anum FROM c_participe);
6)
SELECT course.cnom, lieu.lnom, count( c_participe.Anum )
FROM course, lieu, c_participe
WHERE course.lnum = lieu.lnum
AND c_participe.cnum = course.cnum
GROUP BY course.cnum
ORDER BY count( c_participe.Anum ) DESC ;
7)
SELECT athlete.anom
FROM athlete, c_participe, course
WHERE athlete.anum = c_participe.anum
AND c_participe.cnum = course.cnum
AND prang = ( SELECT max( c_participe.prang )
FROM c_participe, course
WHERE c_participe.cnum = course.cnum
AND course.cnom = "junior" )
AND course.cnom = "junior" ;
8)
SELECT athlete.Anom, c_participe.prang, equipe.enom
FROM athlete, c_participe, course, equipe
WHERE athlete.anum = c_participe.anum
AND athlete.enum = equipe.enum
AND c_participe.cnum = course.cnum
AND course.cnom = "souvenir"
ORDER BY c_participe.prang ;
9)
SELECT equipe.enom
FROM equipe, athlete
WHERE equipe.enum = athlete.enum
GROUP BY equipe.enom
HAVING count( athlete.anum ) >10 ;
10)
SELECT equipe.enom
FROM equipe, athlete
WHERE equipe.enum = athlete.enum
GROUP BY equipe.enom
ORDER BY count( athlete.anum )desc
limit 1 ;
11)
SELECT equipe.enom
FROM equipe, athlete, C_participe
WHERE equipe.enum = athlete.enum
AND athlete.anum = c_participe.anum
GROUP BY equipe.enom
ORDER BY count( C_participe.anum ) DESC
LIMIT 1 ;
12)
SELECT course.cnom
FROM course, c_participe
WHERE course.cnum = c_participe.cnum
GROUP BY course.cnom
ORDER BY count( c_participe.anum )
LIMIT 1 ;
13)
SELECT 250 + ( 50 * ( count( anum ) ) ) AS ptotal
FROM c_participe, course
WHERE course.cnum = c_participe.cnum
AND course.cnom = "ETE2009"
GROUP BY course.cnom ;
14)
SELECT athlete.anom
FROM athlete, equipe
WHERE athlete.enum = equipe.enum
AND equipe.enum = ( SELECT equipe.enum
FROM equipe, athlete
WHERE equipe.enum = athlete.enum
GROUP BY enom
ORDER BY count( anum ) LIMIT 1 )
ORDER BY athlete.anom ;