Correction requete sql
563 mots
3 pages
C:\Users\PEA\Desktop\Cours Oracle\TP_Oracle\correctionSql.txtvendredi 15 janvier 2010 11:57
page 31 SELECT nocli,rscli,nocat FROM client WHERE rscli NOT LIKE '%VELOCIP%' AND nocat 6 ORDER BY nocat, rscli; page 34 SELECT ROUND(AVG(caart),1),MAX(caart),MIN(caart) FROM article; SELECT SUM(caart) FROM article WHERE nofour = 45; page 36 SELECT nofam, ROUND(AVG(caart),2),MAX(caart),MIN(caart) FROM article GROUP BY nofam; page 38 SELECT nofour, SUM(caart) FROM article GROUP BY nofour; page 40 SELECT nofam, ROUND(SUM(caart),2),ROUND(AVG(caart),2) FROM article GROUP BY nofam HAVING AVG(caart)= 2) ORDER BY nocli; page 65 SELECT nofam, AVG(caart) "CA Moyen", MAX(caart) "CA MAXi", MIN(caart) "CA MINi" FROM article WHERE noart IN (SELECT DISTINCT noart FROM lignecde) GROUP BY nofam ORDER BY nofam; mais surtout pas SELECT nofam, AVG(caart) "CA Moyen", MAX(caart) "CA MAXi", MIN(caart) "CA MINi" FROM article a, lignecde l WHERE a.noart = l.noart GROUP BY nofam; Cela fournit un résultat faux pour les familles ayant des articles commandés plus d une fois
-2-
C:\Users\PEA\Desktop\Cours Oracle\TP_Oracle\correctionSql.txt
vendredi 15 janvier 2010 11:57
page 70 SELECT cli.nocli,rscli,libcat,datcde FROM client cli INNER JOIN categorie cat ON cli.nocat=cat.nocat LEFT JOIN commande ON commande.nocli=cli.nocli; SELECT cli.nocli,rscli,libcat,datcde FROM client cli,categorie cat,commande WHERE cli.nocat=cat.nocat AND commande.nocli(+)=cli.nocli; page 72 SELECT noart,desart,libfam FROM article a, famille f WHERE f.nofam=a.nofam AND a.caart=(SELECT MAX(b.caart) FROM article b WHERE b.nofam=a.nofam) ORDER BY a.noart; page 74 SELECT DISTINCT a.noart,desart,caart FROM article a, lignecde lc WHERE a.noart=lc.noart AND a.noart IN (SELECT noart FROM lignecde GROUP BY noart HAVING COUNT(*) >3) UNION SELECT DISTINCT a.noart,desart,caart FROM article a WHERE caart > (SELECT AVG(caart)FROM article ar WHERE ar.nofam=a.nofam); page 76 SELECT DISTINCT a.noart,desart,caart FROM article