create table client(
codecli varchar2(15) primary key,
nomc varchar2(20),
catc number(15),
vilc varchar2(20));
create table article(
codeart varchar2(20) primary key,
noma varchar2(20),
couleur varchar2(20),
prixachat number(20),
prixvente number(20),
qtestk number(20));
insert into article( codeArt,nomA,couleur,prixAchat,prixVente,QteStk),
values ('A100','Jupe','Rouge','170','289','10');
desc nomTable;
create table commande(
numcom number(20) primary key,
codecli varchar2(20),
datecom date,
constraint fk_codecli_commande foreign key (codecli) references client(codecli));
create table detailcom(
numcom number(20),
codeart varchar2(20),
qtecomd number(20),
constraint fk_numcom_detailcom foreign key (numcom) references
commande(numcom),
constraint fk_codeart_article foreign key (codeart) references
article(codeart));
insert into client values ('C001','Martin','2','Londres');
insert into client values ('C002','Dupont','1','Paris');
insert into client values ('C003','Lebrave','3','Londres');
insert into client values ('C004','Csimple','2','Londres');
insert into client values ('C005','Lebon','1','Geneve');
insert into article values ('A100','Jupe','Rouge','170','289','10');
insert into article values ('A200','Robe','Rouge','180','329','15');
insert into article values ('A300','Robe','Blanche','185','339','20');
insert into article values ('A400','Chemise','Blanche','100','199','10');
insert into article values ('A500','Chemise','Rouge','100','199','5');
insert into commande values ('970817','C003','04/08/97');
insert into commande values ('970818','C003','05/08/97');
insert into detailcom values ('970817','A100','1');
insert into detailcom values ('970817','A200','5');
___________________________________________________________________________________
____________________________________
1 -
select nomc from client;
2 -
select codecli,catc from client;
3 -
select * from client where catc=3;
4 -
select codecli from client
where vilc='Paris';
5 - select codecli from client where vilc='Paris' and catc>3;
6 - select numcom from detailcom where codeart='A400' and qtecomd>4;
7 - select codecli,nomc from client where catc>2;
8 - select * from article where prixvente>=(2*prixachat);
9 - select * from article where (couleur='rouge' and prixvente > (2*prixachat);
10- select * from article where couleur <> 'rouge' and prixvente > (2*prixachat);
11- select * from article where (couleur='rouge' and prixvente >=250) or
(couleur='bleu');
12- select * from article where prixachat Between 150 and 200;
13- select * from article where (couleur='bleu' or couleur='blanche' or
couleur='rouge'); !!!OU!!! where couleur in('bleu','rouge',...);
14- select * from article where (couleur <> 'rouge' and couleur <> 'bleu' and
couleur <> 'blanche');
15- select nomc from client where nomc like'BO%';
16- select * from client order by nomc ASC;
17- select * from article where prixachat <= 200 order by qtestk desc;
18- select codeart, (prixvente -prixachat)from article;
19- select (prixvente - prixachat) as marge from article where (prixachat<100);
20- select (prixvente - prixachat) as marge from article where (prixachat<100)
order by marge;
21- select avg (QTESTK) from article;
22- select count (distinct couleur) from article;
23- select min (prixachat) from article;
24- select max (prixvente) from article;
25- select sum (QTESTK) from article;
26- select avg (QTESTK),max(prixvente-prixachat)as marge,(max(prixvente)-
max(prixachat)) from article
where( couleur='rouge');
27- select couleur,avg (prixvente) from article group by (couleur) ;
28- select couleur,avg (prixvente) from article group by (couleur) order by
(couleur);
29- select couleur,avg (prixvente) from article where (prixachat > 150) group by
(couleur) order by (couleur) ;
30- select catc, count (*) from client group by (catc);
31- select codeart,noma from article group by codeart,noma,qtestk having qtestk>
avg(qtestk);
36- select codecli from client
MINUS
select codecli from commande
where to_char(DATECOM ('MM/YYYY') = '09/97');
37- select codecli from commande
MINUS
select codecli from commande
where to_char(DATECOM ('MM/YYYY') <> '09/97');
38- select codeart from article
MINUS
select codeart from article
inner join(detailcom on([Link]=[Link]))
inner join(commande on ([Link]=[Link]))
where to_char(detailcom ('DD/MM/YYYY') = '20/11/1997 );
39- select codecli from commande
where( to_char (DATECOM, 'MM')='08')
INTERSECT
select codecli from commande
where(to_char(DATECOM,'MM')='09');
40- select codecli from client
where (vill in 'PARIS')
UNION
select codecli from commande
where (to_char(datecom,'MM')='09');
41- select nomcli,codecli from client,commande
where [Link]=[Link];
42- select codeart,numart from article,detailcom
where ([Link]=[Link])
and (numcom=971120);
43- select nomart,codeart from article
inner join detailcom on ([Link]=[Link])
inner join commande on([Link]=[Link])
where (to_char (datecom ('MM/YYYY')=08/1997));
44- select codecli,nomc from client
inner joint commande on ([Link]=[Link])
where (to_char (datecom,('MM/YYYY')='08/19997')
INTERSECT
select codecli,nomc from client
inner joint commande on ([Link]=[Link])
where (to_char (datecom,('MM/YYYY')='09/19997');
45-
46- select * from article where prixvente > (select prixvente from article where
codeart = 'A100');
47- select * from article where prixachat > (select prixachat from article where
codeart = 'A600') order by p cxrixachat;
48- select * from article where couleur = (select couleur from article where
codeart = 'A100') and prixvente >= (select avg(prixvente) from article);
___________________________________________________________________________________
_______________________________________________________________
LES JOINTURES ;
afficher toute les commandes avec numcomm le nom de client et la date de commande
select numcom,nomc,datecom from client,commande where
([Link]=[Link]);
,
codeart varchar2(20),
qtecom number(20),