Correction du TP4 suite
7. -insert into membre values(2321,'','ahmed','11/05/80',30)
- insert into membre values(2322,'','ali','05/05/96',20)
8. SELECT nmembre, pnomemb FROM membre WHERE nomemb IS NULL ;
9. SELECT ncompet, nmemb, place FROM resultat order by place ASC;
10. SELECT ncompet FROM competition WHERE ncompet NOT IN (SELECT ncompet FROM resultat);
Publicité
Ou par l’utilisation de “minus” :
SELECT ncompet FROM competition
MINUS
SELECT ncompet FROM resultat ;
11. SELECT count(\*) FROM resultat WHERE ncompet = 9701;
12. SELECT nmemb,nomemb,pnomemb,datememb FROM membre, resultat WHERE ( ncompet = 9701 AND membre.nmemb = resultat.nmemb) ;
Publicité
13. - insert into Resultat values (2312,9701,2);
- insert into Resultat values (2312,9702,2);
14. SELECT nmemb, count(ncompet) from resultat
Group by nmemb
Having (count(ncompet) =select count(distinst ncompet) from resultat);
15. SELECT nomemb, pnomemb, datememb FROM member WHERE ncateg > (SELECT ncateg FROM membre WHERE nmembre = 2310);
Publicité
16. SELECT ncompet, count(nmemb) FROM resultat GROUP BY (ncompet);
17. SELECT \* FROM membre WHERE nmemb NOT IN (SELECT nmemb FROM resultat WHERE nmemb = membre.nmemb );
18. même réponse que la question 10
19. SELECT AVG(SYSDATE -datememb ) as moyage FROM membre GROUP BY ncateg ;