Devoir n1 CNED BTS SIO option SLAM Exploitation des données

Devoir n1

Requête 1 :

SELECT nom, prenom

FROM pilote

ORDER BY dateDeNaissance ASC

Requête 2 :

SELECT COUNT(positionArr)

FROM classement NATURAL JOIN pilote

WHERE nom = ‘ALESI’, prenom = ‘Jean’, postionArr  = 1

Requête 3 :

SELECT MAX(nbCourseGagne)

FROM (SELECT COUNT(positionArr) as ”nbCourseGagne”

        FROM pilote NATURAL JOIN classement

                          JOIN pays USING(idPays)

        WHERE pays.nom = ‘Suisse’, positionArr = 1) AS req

Requête 4 :

SELECT COUNT DISTINCT (pays.nom)

FROM classement JOIN pilote USING(idPilote) JOIN pays USING(idPays)

WHERE positionArr = '1'

Requête 5 :

SELECT nomEcurie, nom, prenom

FROM engager NATURAL JOIN ecurie

                NATURAL JOIN pilote

WHERE annee = 2010

Requête 6 :

SELECT nom, prenom, positionArr

FROM pilote RIGHT JOIN classement USING(idPilote)

    RIGHT JOIN ( SELECT MAX(c.dateCourse)

                                FROM course) AS req USING (dateCourse)

ORDER BY cl.positionArr ASC

Requête 7 :

SELECT nom, prenom, COUNT(classement.idPilote) AS nbCoursesNull

FROM classement JOIN pilote on classement.idPilote = pilote.idPilote

WHERE positionArr IS NULL

                         AND dateCourse = ‘2010’

GROUP BY pilote.nom, pilote.prenom

HAVING nbCoursesNull > 3

Requête 8 :

SELECT nomEcurie, SUM(req.nb_victoires) AS total_victoires

FROM ecurie INNER JOIN engager USING(idEcurie)

                  INNER JOIN (SELECT idPilote, COUNT(*) AS nb_victoires

                            FROM classement

                            WHERE positionArr = 1

                            GROUP BY idpilote ) AS req USING(idPilote)

GROUP BY nomEcurie

HAVING SUM(req.nb_victoires) = MAX(SUM(req.nb_victoires))

Requête 9 :

INSERT INTO ecurie

SELECT MAX(idEcurie)+1, ‘Dream CNED Team’, ‘01/02/2010’

FROM ecurie

Requête 10 :

INSERT INTO course

SELECT ‘09/05/2010’, idPays

FROM pays

WHERE nom = ‘Espagne’

Requête 11 :

INSERT INTO classement(idPilote, positionDep)

SELECT * FROM grilleDepGPEspagne2010

Requête 12 :

UPDATE classement

SET positionArr IS NULL

WHERE positionArr = 17

SET positionArr = positionArr – 1

WHERE positionArr > 17

Requête 13 :

DELETE FROM course

WHERE dateCourse = ‘21/07/2010’ AND (SELECT idPays

                                           FROM pays

                                           WHERE nom = ‘Bollivie’)

...