2.
Sql> Select ENAME,SAL from EMP  where sal>1000 ;

3.
Sql> Select e.ename,d.dname from emp e,dept d where d.deptno=e.deptno and d.dname=’SALES’ ;

4.
Sql>select ename,(sal+nvl(comm,0)) « Salaire Totale » from emp ;


5.
Sql> Select ename,nvl(to_char(comm),’Sans Commission’) « Commission » from emp ;

6-a.
Sql>select ename,hiredate,next_day(add_months(hiredate,6),’Lundi’) from emp ;

6-b.
Sql> select ‘<’ || ename || ‘> gangne  <’ ||sal|| ‘> euros par mois,mais souhaite gangner <’ ||sal*3|| ’>dhs’ from emp ;

7.
Sql> Select ename,hiredate,to_char(hiredate,’day’) from emp order by to_char(hiredate,’d’) ;

9.
Sql> Select e.ename « Employé »,e.empno « No Emp »,m.ename « Chef »,m.empno « No Chef » from emp e,emp m where m.empno=e.mgr ;

10.
Sql> Select e.ename « Employé »,e.empno « No Employé »,m.ename « Chef »,m.empno « No Chef » from emp e,emp m where m.empno (+)=e.mgr ;

11.
Sql> Select e.ename,e.job,d.dname,e.sal,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal ;
12.
Sql>select job,(select sum(sal) from emp group by deptno) « Somme par departement »,sum(sal) from emp group by job ;

OR
Sql>select job,sum(DECODE(deptno,10,sal)) « DEPT10 », sum(DECODE(deptno,20,sal)) « DEPT20 », sum(DECODE(deptno,30,sal)) « DEPT30 »,sum(sal) « Somme Totale » from emp group by job ;

13.
Sql> Select deptno from dept minus select deptno from emp ;

14.
Sql> select ename,hiredate from emp where hiredate <= all(select hiredate from emp ) ;


15.
Sql> select e.ename,e.sal from emp e where (select count(*) from emp where sal>e.sal) < 3 order by 2 ;


16.
Sql> select deptno,dname,loc from dept where deptno=any(select deptno from dept minus select deptno from emp where job=’SALESMAN’) ;

17.
Sql>select empno,ename from emp where sal>(select avg(sal)from emp) and deptno=any(select d.deptno from emp e,dept d where d.deptno=e.deptno and e.ename like ‘%T%’) ;















Trigger

exercice 1 :
create or replace trigger alert after insert or update or delete on etudiant
 begin
 dbms_output.put_line('Operation termine');
 end;
 /

exercice 2 :
create or replace trigger verifeage
	before insert or update on etudiant
	for each row
	declare
	      exc exception;
	begin
	if     :new.ageet<18 or :new.ageet>100
	    then
           raise exc;
	end if;
	exception
       when exc then
       Raise_Application_error(-20001, 'l age d un etudiant doit etre compris entre 18 et 100');
	end;
	/


exercice 3 :
Sql > select trigger_name frum user_triggers ;

exercice 4 :
create or replace trigger update_age before update on etudiant
for each row
    declare
	      exc exception;
	begin
	if     :new.ageet<:old.ageet
	    then

           raise exc;
	end if;
	exception
       when exc then
       Raise_Application_error(-20001, 'le ouveau age doit etre superieur a l anciene');
end;
 /








exercice 5 :
create or replace trigger inc before insert on etudiant
for each row
    declare
	    id number;
	begin
	    select count(*) into id from etudiant;
	if id = 0
	then
	    :new.numet := 1 ;
	else
	    select max(numet) into id from etudiant;
    	:new.numet := id + 1;
	end if;
end;
 /


exercice 6 :
CREATE or replace trigger updateCOMPRO
after insert or update or delete on ligne_commande
	for each row
		declare
			MT float;
			qt Integer;
			stock1 Integer;
			NC Integer;
			NP Integer;
		begin
			IF Inserting then
				MT:=:new.qte * :new.prix_unité;
				qt:=:new.qte;
				NC:=:new.no_cmd;
				NP:=:new.no_prod;
			END IF;

			IF deleting then
				MT:=-:old.qte * :old.prix_unité;
				qt:=-:old.qte;
				NC:=:old.no_cmd;
				NP:=:old.no_prod;
			END IF;

			IF updating then
				MT:=:new.qte * :new.prix_unité - :old.qte*:old.prix_unité;
				qt:=:new.qte - :old.qte;
				NC:=:new.no_cmd;
				NP:=:new.no_prod;
			END IF;
        select stock into stock1 from produit where no-prod=NP;
	update commande set montant=MT where no_cmd=NC;
	update produit set stock=stock1 where no_prod=NP;
end;
/






Curseurs

Question 1 :

DECLARE
max_operation integer;
new_solde integer;
Begin
select max(num_operation)+1 into max_operation from budget;
select solde-500 into new_solde from budget where num_operation=(select max(num_operation) from budget);
INSERT INTO BUDGET VALUES(max_operation,'Courses','Ddebit','14/01/2002',500,new_solde);

End;
/













Question 2 :

Declare
Cursor budget_cursor is (select * from budget where NOM_OPERATION='Courses') ;
ligne budget_cursor % Rowtype;

Begin
open budget_cursor;

loop
Fetch budget_cursor into ligne;
exit when budget_cursor%NotFound ;
INSERT INTO Courses VALUES (ligne.num_operation,ligne.nom_operation,ligne.categorie,ligne.date_operation,ligne.montant) ;
end loop;
close budget_cursor;

END;
/










Question 3 :

truncate table BUDGET_EURO;

Declare
Cursor budget_ecursor is (select * from budget where DATE_OPERATION >'01/01/2002') ;
ligne budget_ecursor % Rowtype;

Begin
open budget_ecursor;

loop
Fetch budget_ecursor into ligne;
exit when budget_ecursor%NotFound ;
INSERT INTO BUDGET_EURO VALUES (ligne.num_operation,ligne.nom_operation,ligne.categorie,ligne.date_operation,ligne.montant/11) ;
end loop;
close budget_ecursor;

END;
/









Question 4 :

1.	Curseur parametré

truncate table budget_seuil;

Accept x number Prompt " Entrez le seuil : "
Declare
Cursor budget_eSeuil(s number) is (select Num_operation,Date_operation,montant from budget where montant > s) ;
ligne budget_eSeuil % Rowtype;
seuil number;
Begin
seuil:=&x;
open budget_eSeuil(seuil);
loop
Fetch budget_eSeuil into ligne;
exit when budget_eSeuil%NotFound ;
INSERT INTO BUDGET_SEUIL VALUES (ligne.num_operation,ligne.date_operation,ligne.montant) ;
end loop;
close budget_eSeuil;

END;
/







Curseur implicite

truncate table budget_seuil;
Accept x number Prompt " Entrez le seuil : "
Declare
Begin
for ligne in (select Num_operation,Date_operation,montant from budget where montant > &x)
loop
INSERT INTO BUDGET_SEUIL VALUES (ligne.num_operation,ligne.date_operation,ligne.montant) ;
end loop;
END;
/