Correction requete sql

Disponible uniquement sur Etudier
  • Pages : 3 (563 mots )
  • Téléchargement(s) : 0
  • Publié le : 7 décembre 2010
Lire le document complet
Aperçu du document
C:\Users\PEA\Desktop\Cours Oracle\TP_Oracle\correctionSql.txt

vendredi 15 janvier 2010 11:57

page 31 SELECT nocli,rscli,nocat FROM client WHERE rscli NOT LIKE '%VELOCIP%' AND nocat 6 ORDER BYnocat, 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 BYnofam; 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 fauxpour 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 SELECTcli.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,categoriecat,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 WHEREb.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 HAVINGCOUNT(*) >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...
tracking img