Algo
TD : Algo. Recherche parmi 298 000+ dissertationsPar azertyze • 1 Mars 2017 • TD • 471 Mots (2 Pages) • 722 Vues
1.show tables;
select frs_nom
from fournisseurs;
2.select clt_loc
from clients;
3. select clt_nom,clt_pnom
from clients
where clt_loc='PARIS';
4. select art_num,art_nom
from articles
where art_poids>500;
5. select art_num,art_nom
from articles
where art_pv>=2*art_pa;
6. select art_num,art_nom
from articles
where art_poids>500 and art_coul='ROUGE';
7. select art_num,art_nom
from articles
where art_poids>500 or art_coul='ROUGE';
8. select art_num,art_nom
from articles
where art_poids<=500 and art_coul!='ROUGE';
9. select art_num,art_nom
from articles
where art_coul='VERT' or art_coul='ROUGE';
10. select clt_nom,clt_pnom
from clients
where clt_nom like 'JA%';
11. select clt_nom,clt_pnom
from clients
where clt_nom like 'DEF__Z';
12. select art_num,art_nom
from articles
where art_poids>500
union
select art_num,art_nom
from articles
where art_coul='ROUGE';
13. select art_num,art_nom,art_poids,frs_nom
from articles,fournisseurs
where art_coul='ROUGE' and art_frs=frs_num;
14. select art_num,art_nom
from articles
where art_pa>(select art_pa from articles where art_num=8);
15. select art_num,(art_pv-art_pa) as marge
from articles
where art_pa>100 ;
16. Select avg(art_poids)
from articles;
17. Select count(art_coul)
from articles
where art_coul!='NULL';
18. Select avg(art_poids),max(art_pv-art_pa),max(art_pv)-max(art_pa)
from articles
where art_coul!='NULL';
19. Select art_coul,avg(art_pv)
from articles
where art_coul!='NULL'
group by art_coul;
20. Select art_coul,avg(art_pv)
from articles
where art_coul!='NULL' and art_pa>=5
group by art_coul;
21. Select (-vnt_prix/vnt_qte+art_pv)
from articles,ventes
where vnt_art=art_num and vnt_date between '1989-01-06' and '1989-01-13'
22. Select art_coul
from articles
where art_coul IS NOT NULL
group by art_coul
HAVING avg(art_pv)>100;
23. Select art_num
from articles
where art_poids<(select art_poids from articles where art_num=2);
24. Select art_num
from articles
where art_poids<=(select avg(art_poids) from articles)and art_coul=(select art_coul from articles where art_num=10);
25. Select art_frs
from articles
where art_coul='ROUGE'
group by art_frs;
26. Select mag_ger
from magasins
where mag_num in (select vnt_mag from ventes where vnt_art=2)
27. select *
from clients
where clt_num in (select vnt_clt from ventes group by vnt_clt having sum(vnt_prix)> (select avg(vnt_prix) from ventes));
28create view semaineaa as
select vnt_art,count(*), sum(vnt_qte)
from ventes
where vnt_date between '1989-01-06' and '1989-01-13'
group by vnt_art;
select *
from semainea;
29. create view ventes_parisss as
select *
from ventes
where vnt_date between '1989-01-06'and '1989-01-13'
and vnt_mag in (select mag_num from magasins where mag_loc like 'PARIS%')
30. create view Hmm as
Select (vnt_prix/vnt_qte) as montant,mag_loc as lieu
from ventes_parisss,magasins
where vnt_mag=mag_num;
select *
from Hmm;
1.show tables;
select frs_nom
from fournisseurs;
2.select clt_loc
from clients;
3. select clt_nom,clt_pnom
from clients
where clt_loc='PARIS';
4. select art_num,art_nom
from articles
where art_poids>500;
5. select art_num,art_nom
from articles
where art_pv>=2*art_pa;
6. select art_num,art_nom
from articles
where art_poids>500 and art_coul='ROUGE';
7. select art_num,art_nom
from articles
...