Proposition de Solution TP4.pdf
Télécharger PDFExercice 1
use gestion;
1) insert into dept values(10,'FINANCES','PARIS'),
(20,'RECHERCHES','GRENOBLE'),
(30,'VENTES','LYON'),
(40,'FABRICATION','ROUEN');
select * from dept;
desc emp;
alter table emp
modify dateEMb date;
insert into emp values (7369,'LECLERC','SECRETAIRE','1980-12-02',7902,6400,NULL,20);
select * from emp;
select * from dept;
insert into emp values(7499,'BIRAUD','commercial','1981-02-21',7698,12000,2400,30),
(7521,'BERGER','commercial','1981-10-21',7698,10000,4000,30),
(7566,'MERCIER','DIRECTEUR','1981-03-02',7839,23000,NULL,20),
(7654,'MARTIN','COMMERCIAL','1981-05-28',7698,10000,11200,30),
(7698,'NOIRET','Directeur','1981-05-01',7839,22000,NULL,30),
(7782,'LASAGE','Directeur','1981-06-09',7839,19600,NULL,10),
(7788,'DUBOIS','INGENIEUR','1985-04-14',7566,24000,NULL,20),
(7839,'LEROY','PRESIDENT','1981-11-07',NULL,40000,NULL,10),
(7844,'BENAIN','COMMERCIAL','1981-05-08',7698,12000,0,30),
(7876,'CLEMENT','SECRETAIRE','1985-05-10',7788,8000,NULL,20),
(7900,'FREMENT','SECRETAIRE','1981-12-03',7698,7600,NULL,30),
(7902,'CHATEL','INGENIEUR','1981-12-17',7566,24000,NULL,20),
(7934,'VILLARD','SECRETAIRE','1982-08-23',7782,10400,NULL,10);
2) select * from emp;
3) select matr, nomE
from emp;
4) select nomE
from emp
where numdept=20;
5) select distinct poste
from emp
where salaire >3000;
6) select nomE, salaire+ ifnull(commission,0)as 'somme totale perçue'
from emp
where poste ='INGENIEUR' OR poste='COMMERCIAL';
7) insert into dept values(50,'COMPTABILITE','TOULOUSE');
select * from dept;
8) insert into dept(numdept,nomdept) values(60,'DIRECTION');
9) insert into dept
select 25,nomdept,lieu
from dept
where numdept=20;
10) insert into dept
select numdept+1,nomdept,lieu
from dept
where numdept=20;
11) a) update dept
set lieu='BORDEAUX'
where numdept=60;
b) select * from emp;
c) update emp
set POSTE='PDG', numdept=60
where nomE ='MERCIER';
12) delete from emp
where nomE='BIRAUD';
13) a) update emp
set salaire = 1.1*salaire
where numdept=10;
b) select * from emp;
14) insert into emp values(7910,'SIMON','COMMERCIAL',NULL,NULL,10000,NULL,30);
15)
a) insert into projet values('P1','SPORT'),
('P2','QUALITE');
c) select * from participation;
d) insert into participation(matr,codeP) values(7566,'P1'),
(7902,'P1');
16)
insert into participation(matr,codeP)
select matr,codeP
from emp,projet
where nomE IN('LEROY','CHATEL') and nomP='QUALITE';
17)
a) update participation
set fonction='CHEF'
where codeP=(select codeP from projet where nomP='SPORT')
and matr=(select matr from emp where nomE='MERCIER');
Pour tester:
select * from participation;
b)
update participation
set fonction ='TRESORIER'
where matr= (select matr from emp where nomE='CHATEL');
Exercice 2
use gestion; 1) select nomE,salaire,ifnull(commission,0)+salaire from emp where poste='COMMERCIAL'; 2) select nomE from emp where poste='COMMERCIAL' order by (ifnull(commission,0))/salaire desc; 3) select count(*) as 'nombre des employes' from emp where numdept=30; 4) select count(commission) from emp; select * from emp; 5) select count(distinct poste) from emp; 6) select poste,AVG(salaire)as 'salaire moyen' from emp group by poste; 7) select nomdept, sum(salaire) as 'somme salaire' from emp,dept where emp.numdept=dept.numdept and nomdept= 'VENTES' GROUP BY nomdept; 8) select nomE,nomdept from emp, dept where emp.numdept=dept.numdept; 9) select nomE,poste, salaire from emp where salaire=(select max(salaire) from emp); 10) select nomE from emp where salaire>(select salaire from emp where nomE='LECLERC'); 11) select nomE from emp where nomE<>’LECLERC’ and poste=(Select poste from emp where nomE='LECLERC'); 12) select nomE from emp where nomE<>’MARTIN’ and ifnull(sup,0)=(select ifnull(sup,0) from emp where nomE='MARTIN'); 13) a- select nomE,poste from emp where nomE<>’BERGER’ and poste=(select poste from emp where nomE='BERGER') and sup=(select sup from emp where nomE='BERGER'); b- select nomE,poste from emp where nomE<>’BERGER’ and (poste,sup)= (select poste,sup from emp where nomE='BERGER'); 14) a- select nomE from emp where dateEmb < all(select dateEmb from emp where numdept=10); b- select nomE,dateEmb from emp where dateEmb< (select min(dateEmb) from emp where numdept=10); 15) select e.nomE,m.nomE from emp e, emp m where e.sup=m.matr; 16) select e.nomE from emp e, emp m where e.sup=m.matr and e.numdept<>m.numdept;
FAQ
Qu'est-ce que la base de données "gestion" ?
La base de données "gestion" est une base de données utilisée pour stocker des informations sur les employés et les départements d'une entreprise.
Comment insérer des données dans une table ?
Pour insérer des données dans une table, utilisez la commande SQL "INSERT INTO". Par exemple, "INSERT INTO dept VALUES(10,'FINANCES','PARIS')".
Comment mettre à jour des données dans une table ?
Pour mettre à jour des données dans une table, utilisez la commande SQL "UPDATE". Par exemple, "UPDATE dept SET lieu='BORDEAUX' WHERE numdept=60".