Cv bts sio

• Requête 1 : Combien d’automobiles sont référencées dans la base ?

SELECT COUNT(automobile.numImma) AS "nbre d'automobiles référencées"

FROM automobile

• Requête 2 : Donnez la liste des automobiles accompagnées de leurs marques respectives (immatriculations, modèles et noms de marques).

SELECT automobile.numImma, automobile.modele, marque.nom 
FROM automobile, marque

• Requête 3 : De combien de diesel l’entreprise dispose-t-elle ?

SELECT automobile.idEnergie, energie.idEnergie, energie.libelleEnergie

FROM automobile  A

LEFT JOIN energie  E  ON E.idEnergie = A.idEnergie

WHERE E.libelleEnergie = 'diesel'

• Requête 4 : Donnez la liste de tous les VRP avec le kilométrage total que chacun a effectué, en affichant les plus kilométrés en premier.

SELECT V.idVRP

            ,V.nom

           ,V.prenom

           ,SUM(S.km) as NB_KM

FROM vrp V

LEFT JOIN suiviimpact S ON V.idVRP = S.idVRP

GROUP BY V.idVRP

ORDER BY NB_KM DESC,V.nom ASC

• Requête 5 : Donnez la liste des automobiles (immatriculation, modèle et consommation moyenne au 100 km), en affichant les plus économiques en carburant en premier.

SELECT automobile.numImma, automobile.modele, AVG (suiviimpact.nbLitres)

FROM automobile JOIN suiviimpact USING (numImma)

GROUP BY numImma, nbLitres

ORDER BY nbLitres ASC

• Requête 6 : Quelle est l’automobile la plus récente (immatriculation, modèle et date d’achat) ?

SELECT *

FROM automobile A

ORDER BY A.dateAchat DESC

LIMIT 1

• Requête 7 : Combien de kilomètres ont parcouru les deux plus anciens de nos VRP (ils sont les tous premiers à avoir été embauchés et l’ont été le même jour), en affichant le plus gros rouleur en premier ?

SELECT T1.idVRP, T1.nom, t1.prenom, SUM(T2.km)

FROM VRP as T1

INNER JOIN suiviimpact as T2

   ON T2.idVRP = T1.idVRP

WHERE NOT exists

     (SELECT 1

      FROM vrp as T3

      where T3.dateEmbauche < T1.dateEmbauche)

GROUP BY T1.idVRP, T1.nom, t1.prenom

ORDER BY SUM(T2.km)DESC

• Requête 8 : Quelle est l’empreinte carbone en tonne de notre flotte de véhicule pour l’année 2015 ?

SELECT suiviimpact.date, automobile.gCO2km AS "empreinte carbone en tonne" 
FROM automobile, suiviimpact 
WHERE gCO2km = SUM(gCO2km) 
AND suiviimpact.date = '2015' 

• Requête 10 : La correspondance des différentes pastilles se trouve dans la table temporaire «pastillesEco2015» dont voici un extrait. Prenez en compte ces informations pour mettre à jour la liste des pastilles.

UPDATE pastille 
SET idPastille = idPastille + 1 
WHERE
INSERT INTO pastille 
FROM pastillesEco2015 
VALUES (Couleur, Explications, Code) 

• Requête 11 : Attribuez la pastille «Verte-Anis» à tous nos véhicules concernés.

SELECT 
FROM energie NATURAL JOIN automobile USING (idEnergie) 
WHERE libelleEnergie = 'Essence' 
AND automobile.dateAchat BETWEEN '01/01/2006' AND '31/12/2010' 
AND idEnergie IN (SELECT automobile.dateAchat, automobile.idEnergie, energie.libelleEnergie 
FROM energie NATURAL JOIN automobile USING (idEnergie) 
WHERE libelleEnergie = 'diesel' 
AND automobile.dateAchat >= '01/01/2011')

...