<!-- If the browser does not support frames put the first topic content here --> <div class="WordSection1"> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; background:#DFDFDF'> <p class="MsoHeader" align="center" style= 'text-align:center;background:#DFDFDF; border:none;padding:0cm'> <b><span style='font-size:14.0pt;color:black'>CORRIGE REVISIONS-1 – CAS EMPLOYES</span></b></p> <p class="MsoHeader" align="center" style= 'text-align:center;background:#DFDFDF; border:none;padding:0cm'> <b><span style='font-size:14.0pt;color:black'>Manipulation des données et langage de définition de données</span></b></p> </div> <p class="MsoHeader"> </p> <p class="MsoHeader"><b><u><span style='font-size:14.0pt'>Création des tables :</span></u></b></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">create table dept</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">(deptno numeric(2) constraint pk_dept primary key,</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">dname varchar(30) not null,</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">loc varchar(20) not null);</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">create table emp</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">(empno numeric(4) constraint pk_emp primary key,</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">ename varchar(30) not null,</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">job varchar(30) not null,</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">mgr numeric(4),</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">hiredate date not null,</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">sal numeric (8,2) not null,</span></p> <p class="MsoNormal">comm numeric(8,2),</p> <p class="MsoNormal"><span lang="EN-GB" xml:lang="EN-GB">deptno numeric(2) not null constraint fk_emp references dept(deptno));</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoHeader"><b><u><span style='font-size:14.0pt'>Ecrire et exécuter les requêtes ci-dessous : </span></u></b></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>1)<span style= 'font:7.0pt "Times New Roman"'> </span> Obtenir les départements (numéro, nom) ayant des employés.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select d.deptno, dname</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From dept d, emp e</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where e.deptno = d.deptno ;</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" xml:lang="EN-GB">Ou</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select d.deptno, dname</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From dept d</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner join emp e On e.deptno = d.deptno;</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>2)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner la liste des employés (numéro, nom) non vendeurs.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select empno, ename</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where job != 'SALESMAN';</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>3)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner les employés embauchés entre le 01-01-1981 et le 30-06-81.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select empno, ename</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where hiredate between '1981-01-01' and '1981-06-30';</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>4)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner le salaire le plus élevé et le salaire le plus bas par département et job.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select dname, job, max(sal) as Maximum, min(sal) as Minimum</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp e</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner join dept d On e.deptno = d.deptno</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Group by dname, job;</span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>5)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner pour chaque département : son nom, le nombre d’employés et la moyenne des salaires.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select dname, <span style='color:red'>count(*)</span>, avg(sal)</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp e</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner join dept d On e.deptno = d.deptno</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where e.deptno = d.deptno</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Group by dname;</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <span style= 'font-size:12.0pt;font-family:"Times New Roman",serif'><br clear= "all" style='page-break-before:always' /></span> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>6)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner le montant total des commissions des vendeurs.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select sum(comm) <s>as Total_Commissions</s></span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where job = ' SALESMAN';</span></p> <p class="MsoNormal"><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>7)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner La liste des employés (son nom, sa fonction et le nom de son département) par ordre alphabétique.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select ename, job, dname</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp e</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner Join dept d On e.deptno = d.deptno</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Order by ename;</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>8)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner le nom et le job de la dernière personne embauchée dans le département ‘ACCOUNTING’.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select ename, job</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp e</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner Join dept d On e.deptno = d.deptno</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">And dname='ACCOUNTING'</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">And hiredate =</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang= "EN-GB"> (select max(hiredate)</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang= "EN-GB"> From emp e</span></p> <p class="MsoNormal" style='margin-left:18.0pt;text-indent:17.4pt'> <span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner Join dept d On e.deptno = d.deptno</span></p> <p class="MsoNormal" style='margin-left:18.0pt;text-indent:17.4pt'> <span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">And dname='ACCOUNTING');</span></p> <p class="MsoHeader"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>9)<span style= 'font:7.0pt "Times New Roman"'> </span> Lister les employés (no, nom) dont le salaire est supérieur à la moyenne des salaires.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select empno, ename</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where sal ></span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang= "EN-GB"> (select avg(sal)</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:10.0pt'>from emp);</span></p> <p class="MsoNormal"> </p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>10)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner le nombre d’employés n’ayant pas de responsable.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select count(*)</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where mgr is null;</span></p> <p class="MsoNormal" style='margin-left:14.15pt'><span lang="EN-GB" xml:lang="EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>11)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner la liste des employés (numéro, nom et job) avec le nom de leur responsable.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select e1.empo, e1.ename, e1.job, e2.ename</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp e1</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner Join emp e2 On e1.mgr = e2.empo</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>12)<span style= 'font:7.0pt "Times New Roman"'> </span> On peut constater que le chef ne figure pas dans la liste de la question précédente (c'est logique car il n'a pas de responsable), il est demandé de l'ajouter dans cette liste (penser à UNION).</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select e1.empo, e1.ename, e1.job, e2.ename</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp e1</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Inner Join emp e2 On e1.mgr = e2.empo</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Union</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select empno, ename, job, NULL</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where mgr is NULL;</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman",serif' xml:lang= "EN-GB"><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'><span lang="EN-GB" xml:lang="EN-GB">13)<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span lang= "EN-GB" xml:lang="EN-GB">Donner la liste des employés dont le nom commence par A.</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select empno, ename</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where ename like ' A%';</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>14)<span style= 'font:7.0pt "Times New Roman"'> </span> Donner la liste des employés (numéro, nom) avec leur salaire total ordonnée sur le nom de l'employé ; attention si on ajoute NULL à un nombre, le résultat vaut NULL.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select empno, ename, sal</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span style= 'font-size:10.0pt'>From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where job != ' SALESMAN'</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Union</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Select empno, ename, sal + comm</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">From emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where job = ' SALESMAN'</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Order by ename;</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>15)<span style= 'font:7.0pt "Times New Roman"'> </span> Augmenter les salaires des vendeurs de 1%.</p> <p class="MsoNormal" style='margin-left:18.0pt'><span style= 'font-size:10.0pt'>Update emp</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span style= 'font-size:10.0pt'>Set sal = sal *1.01</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Where job = 'SALESMAN';</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:36.0pt;text-indent:-18.0pt'>16)<span style= 'font:7.0pt "Times New Roman"'> </span> Insérer un département (la seule contrainte est que le numéro doit être égal au dernier + 10).</p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">Insert into dept values(</span></p> <p class="MsoNormal" style='margin-left:18.0pt'><span lang="EN-GB" style='font-size:10.0pt' xml:lang="EN-GB">(Select (max(d.deptno) + 10) from dept d), 'INFORMATIQUE', 'NEW-YORK');</span></p> </div>
Table des matières
Sujets
Recherche
Help content
Help content
Générée avec
chmProcessor
Générée avec
chmProcessor