Compte Rendu

SQL – TP3

 

1. select distinct deptno from dept ;

1 bis. select deptno from dept where deptno not in(select distinct emp.deptno   from emp, dept where emp.deptno=dept.deptno);

 

1. select empno, ename from emp where deptno in (select deptno from emp where job =’CLERK’);

 

1. select empno, ename from emp where deptno=10 and job in (select job from emp, dept where emp.deptno=dept.deptno and dname=’SALES’);

 

1. select empno, ename from emp where job in (select job from emp where ename=’JONES’) or sal > (select sal from emp where ename=’FORD’);

 

1. select empno, ename from emp where job in (select job from emp where ename=’JONES’) and sal in (select sal from emp where ename=’JONES’) and ename!=’JONES’;

 

1. select sal as salaire_mensuel from emp order by sal desc;

 

1. select empno , ename, NVL(comm,0)/sal as ratio from emp where job=’SALESMAN’ order by NVL(comm,0)/sal desc;

 

1. select AVG(sal) as moyenne_sal, deptno, job from emp group by deptno, job;

 

1.  select AVG(sal) as moyenne_sal, job,  COUNT(*) as nb_emp from emp group by job having COUNT(*)>1 ;

 

1.  select MAX(COUNT(*)) as max_emp from emp group by deptno ;

 

 

remarque : création de tables
create table natasha as select * from emp;

1.  update natasha set sal=sal*1.1 ;

 

1. insert  into natasha (empno,ename,job) values ( 37,‘LUMAKY’,’BLADE’);

 

      insert  into natasha (empno,ename,sal) values (665,’NEOXSYSM’,10000);

      select empno, ename, sal, job from Natasha where empno=37 or empno=665;

      delete from natasha where empno=37 or empno=665;

 

1.   create table stpierre as select empno, ename, sal from emp where job=’CLERK’;

select ename from stpierre where empno=7369;

select ename, AVG(job) as moyenne_sal, MAX(sal) as maximum from stpierre;

drop table stpierre;

 

1. create view _dep_10 as select * from emp where deptno=10 ;

 

1. update vue_dep_10 set sal=sal*1.1 ;

select * from vue_dep_10;

drop view vue_dep_10 ;

 

 

 

 

 

Ajouter aux Favoris Ajouter aux votre page

 

---

Acceuil | Forum| Contactez-Nous | Annace |Chat| Music |Radios|Sockets |Protocoles|Windows NT
Langage C |Langage C++ | Assembleur | Java | Perl |Pascal/delphi | Visual basic|MERISE | SQL| JDBC| ODBC
ASP|CGI |HTML |DHTML |XML|Javascript |JSP |PHP|Servlets |VBScript |WAP |UNIX |Linux |MacOS|Windows 9x
Assemblage-PC |Modèle relationnel|Histoire-PC|Fonctionnement-PC|photo pour rire|MS-Dos|Architecture-PC
Flash MX |Word-2003|Power Point|Excel|Langage C|Langage Java|Visual Basic|La base XP | programming
Physique Arabic partie I|Programation en arabe|Physique Français|Internet| Sécurité Informatique
HTML |Agebre en Français|Math Français partie I|Physique Arabic partie II| الطرق العددية
Electricité| Math Français partie II|Math en Arabic|Agebre en Arabic| الأعداد العقدية
Tous les cours informatiques en vidéos |Droit Informatique |logiciels
Tous les cours physiques en vidéos |Microsoft office
Cours math en vidéos | Msn en ligne
TV Live

---

Autoformations les cours informatiques en vidéos gratuitement
:: Copyright © 2008 http://www.autoformations.co.cc All rights reserved ::