Exercice Base de donnée

SELECT A.numImma, A.modele, M.nom 
FROM automobile A
LEFT JOIN  marque M ON  M.qqchose = A.qqchose

The ON clause is the most general kind of join condition: it takes a Boolean value expression of the same kind as is used in a WHERE clause. A pair of rows from T1 and T2 match if the ON expression evaluates to true.

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2 with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2.b.

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL behaves like CROSS JOIN.

SELECT vrp.idVRP, suiviimpact.idVRP, suiviimpact.km
FROM vrp LEFT JOIN suiviimpact ON vrp.idVRP = suiviimpact.idVRP
WHERE vrp.idVRP = suiviimpact.idVRP
GROUP BY VRP, km DESC

SELECT automobile.numImma, automobile.modele, AVG (suiviimpact.nbLitres)
FROM automobile JOIN suiviimpact USING (numImma)
GROUP BY numImma, nbLitres 
ORDER BY nbLitres ASC

SELECT automobile.numImma, automobile.dateAchat, automobile.modele
FROM automobile
HAVING dateAchat = MAX (dateAchat)

• • • • • • • • • • Erreur **********

SELECT A.numImma, A.dateAchat, A.modele, MAX(A.dateAchat) as MAX_DATE_ACHAT
FROM automobile A
LIMIT 1

SELECT SUM (suiviimpact.km)
FROM suiviimpact
ORDER BY suiviimpact.km DESC
AND idVRP IN (SELECT vrp.idVRP, vrp.dateEmbauche, nom, prenom
		 FROM vrp, suiviimpact
		 WHERE dateEmbauche = 1)

• • • • • • • • • • Erreur **********

SELECT SUM (suiviimpact.km)
    FROM suiviimpact
    WHERE suiviimpact.km
    ORDER BY suiviimpact.km DESC
    AND idVRP IN (SELECT vrp.idVRP, vrp.dateEmbauche, nom, prenom
          FROM vrp, suiviimpact
         WHERE dateEmbauche = .......)