// 1、查询emp表中的所有信息 select * from emp;

// 2、显示emp表的员工姓名和工资。 select ename, sal from emp;

// 3、查询emp表中部门编号为20的并且sal（工资）大于3000的所有员工信息 select * from emp where deptno=20 and sal > 3000;

// 4、查询emp表中部门编号为20的或者sal（工资）大于3000的所有员工信息 select * from emp where deptno=20 or sal > 3000;

// 5、使用between and 查询工资在2000和4000之间的员工（用and 重新实现） select * from emp where sal between 2000 and 4000; select * from emp where sal >= 2000 and sal = 3000;

// 19、显示1982年以后雇佣的员工姓名和雇佣时间。 select ename, to_char(hiredate, 'yyyy-mm-dd') from emp where extract(year from hiredate) > 1982;

// 20、显示部门编号为10的员工姓名和雇佣时间。 select ename, to_char(hiredate, 'yyyy-mm-dd') from emp where deptno = '10';

// 21、显示工资在1000～2000之间(不包括1000和2000)的员工信息。 select * from emp where sal > 1000 and sal < 2000;

// 22、显示部门10中工资大于1500的员工。 select * from emp where deptno=10 and sal > 1500;

// 23、显示职务为CLERK或MANAGER的员工信息。 select * from emp where job='CLERK' or job='MANAGER';

// 24、显示部门10以外的其他部门的员工。 select * from emp where deptno != 10;

25、显示部门10和部门20中工资小于1500的员工。 select * from emp where (deptno=10 or deptno=20) and sal < 1500;

PPT上已学查询：

1、查询姓名首字母为“A”或第二个字符为“A”的所有员工信息 select * from emp where ename like 'A%' or ename like '_A%';

2、查询部门20和30中的、岗位不是“CLERK”或“SALESMAN”的所有员工信息。 select * from emp where (deptno=20 or deptno=30) and job != 'CLERK' and job !='SALESMAN';

3、查询出工资在2500-3500之间，1981年入职的，没有奖金的所有员工信息。 select * from emp where sal between 2500 and 3500 and extract(year from hiredate)=1981 and comm is null;

4、查询比平均员工工资高的员工信息。 select * from emp where sal > (select avg(sal) from emp);   5、查询平均工资高于2000的部门信息。 select * from dept where deptno in (select deptno from emp group by deptno having avg(sal) > 2000);

6、查询出WARD的工作所在地。 select loc from dept where deptno = (select deptno from emp where ename='WARD');

7、查询出工资比ADAMS高的所有人姓名、部门、所在地。 select e.ename, d.dname, d.loc from emp e, dept d where e.sal >(select sal from emp where ename='ADAMS') and e.deptno=d.deptno; select e.ename, d.dname, d.loc from emp e JOIN dept d ON e.deptno=d.deptno where e.sal > (select sal from emp where ename='ADAMS');

8、查询工资排名第7的员工信息。 select * from emp where sal = (select min(sal) from (select sal from (select * from emp order by sal desc) where rownum (select sal from emp where ename='ADAMS') order by e.ename;

// 查询员工工资排名第一的员工信息 select * from emp where sal = (select max(sal) from emp); select * from (select * from emp order by sal desc) where rownum=1;

// 查询当前日期 select sysdate, add_months(sysdate, 1) from dual; 

// 查询与部门20岗位不同的员工姓名和工资 select e.ename, e.job, e.sal from emp e where job not in(select e.job from emp e where deptno='20');

// 查询与 SMITH 岗位部门完全相同的员工姓名，工作，工资 select ename, job, sal from emp  where job = (select job from emp where ename='SMITH') and deptno = (select deptno from emp where ename='SMITH') AND ename !='SMITH';

// 列出在部门sales工作的员工姓名 select ename from emp e, dept d where e.deptno=d.deptno and d.dname='SALES';

// 列出所有员工的姓名，部门和工资 select ename, e.deptno, sal from emp e, dept d where e.deptno =d.deptno;

// 列出所有部门详细信息和部门人数 select d.*, count(ename) from dept d, emp e where d.deptno=e.deptno(+) group by d.deptno, d.dname, d.loc;

// 列出各个部门为 MANAGER 的最低工资 select d.deptno, min(e.sal) from dept d, emp e where d.deptno=e.deptno(+) and e.job='MANAGER' group by d.deptno;

// 查询部门人数只少为1的部门信息 select d.*, count(e.ename) num from dept d, emp e where e.deptno(+)=d.deptno group by d.deptno, d.dname, d.loc having count(e.ename) >=1 order by num;

// 列出工资比 SMITH 多个员工 select e.ename, e.sal from emp e where e.sal > (select sal from emp where ename='SMITH');

// 列出所有员工对应领导的姓名 select e.ename 员工姓名, e1.ename BOSS, e1.sal from emp e, emp e1 where e.mgr = e1.empno(+);

// 列出某个员工的领导，并且领导的工资要大于等于3000 select e.ename, e1.ename BOSS, e1.sal from emp e, emp e1 where e.mgr=e1.empno(+) and e1.sal >= 3000;

// 列出部门名称和部门的员工信息 select d.dname, e.* from emp e, dept d where e.deptno(+)=d.deptno;

// 列出职位为 CLERK 的员工名称及部门名称，部门人数 select e.job, e.ename, d.deptno, d.dname, d.num from emp e, (select d.deptno, d.dname, count(e.ename) num from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d where d.deptno(+) = e.deptno and e.job='CLERK'; select d.deptno, d.dname, count(e.ename) from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname;

// 列出工资高于公司平均工资的所有员工，所在部门，上级领导，公司的工资等级。 select d.dname, d.员工, s.grade, d.领导 from salgrade s,    (select d.dname, d.ename 员工, e.ename 领导, d.sal from emp e,            (select d.dname, s.* from dept d, (select * from emp where sal > (select avg(sal) from emp)) s where d.deptno=s.deptno) d    where d.mgr=e.empno(+)) d where d.sal between s.losal and s.hisal;   select e.ename 员工, e1.ename BOSS, d.dname, s.grade from emp e, emp e1, dept d, salgrade s where e.sal > (select avg(sal) from emp)        and e.mgr = e1.empno(+)       and e.deptno = d.deptno(+)        and e.sal between s.losal and s.hisal;

// 列出与scott从事相同工作的所有员工及部门名称 select e.ename, e.job, d.dname from emp e, dept d where e.deptno=d.deptno  and e.job=(select job from emp where ename='SCOTT');

// 列出工资大于30号部门任意员工工资的所有员工的姓名和工资 select e.ename, e.sal  from emp e where e.sal > any(select sal from emp where deptno=30);

// 列出工资大于30号部门所有员工的所有员工信息和部门名称 select e.ename, e.sal, d.dname  from emp e, dept d  where e.sal > all(select sal from emp where deptno=30)  and e.deptno=d.deptno;

// 列出每个部门的员工数量和平均工资 select d.dname, NVL(avg(e.sal), 0) 平均工资, count(e.ename) 员工数量  from emp e, dept d  where e.deptno(+)=d.deptno  group by d.deptno, d.dname;

// 列出每个部门的员工数量，平均工资和平均服务期限 select d.dname, NVL(avg(e.sal), 0) 平均工资, count(e.ename) 员工数量, floor(avg(months_between(sysdate, e.hiredate))) 平均期限 from emp e, dept d  where e.deptno(+)=d.deptno  group by d.deptno, d.dname;

// 列出每个工作的最低工资和从事最低工资工作的雇员名称 select e.sal, e1.ename, e.job from emp e1, (select min(e.sal) sal, e.job from emp e group by e.job) e where e1.sal=e.sal and e1.job=e.job;

// 求出部门名称带'S'的部门名称，部门员工，工资合计和部门人数。 select e.ename, d.dname, d.sal, d.num  from emp e, (select d.deptno, d.dname, sum(e.sal) sal, count(e.ename) num     from emp e, dept d      where d.dname like '%S%'            and e.deptno(+)=d.deptno      group by d.deptno, d.dname) d where e.deptno(+) = d.deptno;

// 求出部门平均工资以及等级 select d.deptno, d.dname, d.sal, NVL(s.grade, 0)  from salgrade s, (select d.deptno, d.dname, NVL(avg(e.sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d  where d.sal between s.losal and s.hisal;

// 求平均工资等级最低的部门名称 select d.dname, d.grade from (select d.deptno, d.dname, d.sal, NVL(s.grade, 0) grade from salgrade s, (select d.deptno, d.dname, NVL(avg(e.sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d  where d.sal between s.losal and s.hisal) d,       (select min(grade) grade       from ((select d.deptno, d.dname, d.sal, NVL(s.grade, 0) grade             from salgrade s, (select d.deptno, d.dname, NVL(avg(e.sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d              where d.sal between s.losal and s.hisal))) d1 where d.grade=d1.grade;

// 部门经理中平均工资最低的部门名称 select d.dname from dept d,       (select d.deptno       from (select deptno, avg(sal) sal from emp where job='MANAGER' group by deptno) d,            (select min(sal) sal from (select deptno, avg(sal) sal from emp where job='MANAGER' group by deptno)) d1        where d.sal=d1.sal) d1 where d.deptno=d1.deptno;  

1,列出在部门sales工作的员工的姓名 select e.ename from emp e, dept d where e.deptno(+)=d.deptno and d.dname='SALES';

2,列出所有员工的姓名，部门名称和工资 select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno;

3,列出所有部门的详细信息和部门人数 select d.*, NVL(count(e.ename), 0) from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname, d.loc;

4,列出各个部门职位为manager的最低薪金 select e.deptno, min(e.sal) from emp e where e.job='MANAGER' group by e.deptno;

5,查询出部门人数至少是1的部门名字 select d.dname, d1.num from dept d, (select d.deptno, count(e.ename) num from dept d, emp e where d.deptno=e.deptno(+) group by d.deptno) d1 where d.deptno=d1.deptno and d1.num >= 1;

6,列出工资比smith多得员工 select e.ename from emp e where e.sal > (select sal from emp where ename='SMITH');

7,列出所有员工的对应领导的姓名 select e.ename, e1.ename from emp e, emp e1 where e.mgr=e1.empno(+);

8,求出某个员工的领导，并要求这些领导的薪水高于或等于3000 select e.ename, e.sal from emp e, (select distinct e1.empno as empno from emp e, emp e1 where e.mgr=e1.empno(+)) e1 where e.empno=e1.empno and e.sal >= 3000;

9.列出部门名称，和这些部门的员工信息 select d.dname, e.* from emp e, dept d where e.deptno(+)=d.deptno;

10.列出所有职位为clerk的员工姓名及其部门名称，部门的人数 select ename, deptno from emp where job='CLERK'; select d.dname, count(e.ename), e.ename from emp e, dept d where e.deptno(+)=d.deptno and e.job='CLERK' group by e.ename, d.dname;

11.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级 select avg(sal) sal from emp; select e.ename, e.sal, d.dname, e1.ename, s.grade from emp e, emp e1, dept d, salgrade s  where e.sal > (select avg(sal) sal from emp)        and e.deptno=d.deptno        and e.mgr=e1.empno        and e.sal between s.losal and s.hisal;

12.列出与scott从事相同工作的所有员工及部门名称 select job from emp where ename='SCOTT'; select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno(+) and e.job=(select job from emp where ename='SCOTT');

13.列出薪金大于部门30中的任意员工的薪金的所有员工的姓名和薪金 select ename, sal from emp where sal > ANY(select sal from emp where deptno=30);

14.列出薪金大于部门30中的全部员工的薪金的所有员工的姓名和薪金，部门名称 select ename, sal, dname from emp e, dept d where e.deptno=d.deptno(+) and sal > ALL(select sal from emp where deptno=30);

15.列出每个部门的员工数量，平均工资 select dname, NVL(count(e.ename), 0), NVL(avg(e.sal), 0) from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, dname;

---------------------------------------------

1.查询每个部门的员工数量和部门编号 select NVL(count(e.ename), 0), d.deptno from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno;

2.查询每个部门的员工数量、平均工资和平均入职时间 select NVL(count(e.ename), 0), NVL(avg(e.sal), 0), NVL(avg(extract(year from sysdate) - extract(year from hiredate)), 0)  from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno;

3.查询每个部门的员工数量以及该部门名称,将员工数大于2人的部门筛选出来 select d.dname, d.deptno, d.num from (select dname, d.deptno, NVL(count(ename), 0) num from emp e, dept d where e.deptno(+)=d.deptno group by dname, d.deptno) d where d.num > 2;

4.查询出没有员工的部门信息（部门编号、名称、所在地） select d.* from (select d.*, NVL(count(e.ename), 0) num from emp e, dept d where e.deptno(+)=d.deptno group by d.dname, d.deptno, d.loc) d where d.num=0;

5.查询出SMITH所在部门的部门编号，部门名称，部门人数 select d.deptno, d.dname, NVL(count(e.ename), 0) from emp e, dept d where e.deptno=d.deptno and e.deptno=(select deptno from emp where ename='SMITH') group by d.deptno, d.dname;

6.查询出平均工资最高的两个岗位 select e.job, e.sal from (select job, NVL(avg(sal), 0) sal from emp group by job  order by sal desc) e where rownum