slect语句查询

1、orcal 分部分练习查询语句-第一部分：-1 、使用基本查询语句 .-(1)查询DEPT表显示所有部门名称.select dname from dept;-(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+ 补助)，处理NULL行，并指定列别名为年收入。(NVL(comm,0) comm取空值时用 0替代) select * from emp;select e.ename，(e.sal+nvl(null，0)*12 年收入 from emp e ;-(3)查询显示不存在雇员的所有部门号。select d.deptno from emp e，dept d where d.deptno

2、=e.deptno(+) group by d.deptno having count(e.deptno)=0;select d.deptno from emp e right join dept d on d.deptno=e.deptno group by d.deptno having count(e.deptno)=0;-2、限制查询数据-(1)查询EMP表显示工资超过 2850的雇员姓名和工资。select e.ename，e.sal from emp e where e.sal2850 ;-(2)查询EMP表显示工资不在 15002850之间的所有雇员及工资。select e.*，

3、e.ename ，e.sal from emp e where e.sal2850;-(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。select e.ename，e.deptno from emp e where e.empno=7566;select e.ename，d.deptno from emp e，dept d where e.deptno=d.deptno(+) and e.empno=7566;select e.ename，d.deptno from emp e join dept d on d.deptno=e.deptno and e.empno=7566;-

4、(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。select e.ename，e.sal from emp e where (e.deptno=10 or e.deptno=30) and e.sal1500;select e.ename，e.sal from emp e where e.deptno in(10，30) and e.sal1500;-(5)查询EMP表显示第2个字符为A的所有雇员名其工资。select e.ename,e.sal from emp e where e.ename like _A%;-(6)查询EMP表显示补助非空的所有雇员名及其补助。se

5、lect e.ename,m from emp e where m is not null;-3、排序数据-(1)查询 EMP 表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序。select e.ename,e.sal,e.hiredate from emp e order by e.ename;-查询EMP表显示在1981年2月1日至U 雇佣日期，并以雇佣日期进行排序。select * from emp;1981年 5 月 1 日之间雇佣的雇员名、岗位及select e.ename,e.job,e.hiredate from emp e.hiredate=1 -2 月-81 and

6、select e.ename，e.job，e.hiredate fromto_date(19810201，yyyy -mm-dd) and to_date(19810501，yyyy -mm-dd) order by e.hiredate;empe where e.hiredatebetween-(3)查询 EMP 表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序 排序。select e.ename,e.sal,m from emp e where m is not null order by e.sal,m desc;-第二部分：-1列出至少有一个雇员的所有部门。select

7、d.deptno from emp e right join dept d on d.deptno=e.deptno group by d.deptno having count(e.deptno) 0;-2 .列出薪金比“ SMITH”多的所有雇员。select * from emp;select e.ename from emp e where e.sal(select max(sal) from emp where ename=SMITH);-3列出所有雇员的姓名及其上级的姓名。-注意没有上司的雇员select e.ename,p.ename from emp e ,emp p wher

8、e e.mgr = p.empno(+);-4列出入职日期早于其直接上级的所有雇员。select e.ename,e.hiredate,p.ename,p.hiredate from emp e ,emp p where e.mgr = p.empno(+) and e.hiredate 1500;-8 .列出从事“ SALES (销售)工作的雇员的姓名，假定不知道销售部的部门编号。select e.ename ,d.dname from emp e ,dept d where e.deptno=d.deptno and d.dname=SALES;select e.ename,d.dname

9、,d.loc from emp e natural join dept d where d.dname=SALES;-9.列出薪金高于公司平均的所有雇员。select e.*,e.ename from emp e where (e.sal+nvl(m,0)(select avg(sal) from emp);-10.列出与“ SCOTT从事相同工作的所有雇员。()select * from emp;select e.job,e.e name from emp e where e.job in (select job from emp where en ame=SCOTT) and e.e na

10、meoSCOTT;-11.列出薪金等于在部门 30工作的所有雇员的薪金的雇员的姓名和薪金。select a.*, a.ename,a.sal from emp a where a.sal in (select e.sal from emp e where e.deptno=30 ) and (a.deptno30 or a.deptno is null);select e.ename,e.sal,e.deptno from emp e where exists (select 1 from emp p where e.sal = p.sal and p.deptno = 30) and (e.

11、deptno30 or e.deptno is null);-12.列出薪金高于在部门 30工作的所有雇员的薪金的雇员的姓名和薪金。select e.* from emp e where e.sal all(select distinct sal from emp where deptno=30 ) ; select e.* from emp e where e.sal(select max(sal) from emp where emp.deptno=30);-13.列出在每个部门工作的雇员的数量以及其他信息。select d.deptno,count(e.empno) from emp e

12、 right outer join dept d on d.deptno=e.deptno group by d.deptno;select d.deptno ,d.dname,count(e.deptno) from dept d left join emp e on d.deptno=e.deptno group by d.deptno,e.deptno,d.dname;select d.deptno,d.dname, count(e.deptno),d.locfrom dept d left outer join emp e on e.deptno=d.deptnogroup by d.

13、deptno,d.dname,d.loc;-14列出所有雇员的雇员名称、部门名称和薪金。()select e.e name,d.d name,e.sal from dept d ,emp e where e.de ptno = d.de ptno (+);-16列出分配有雇员数量的所有部门的详细信息即使是分配有0个雇员。select d.deptno,d.dname,d.loc,count(e.deptno) from dept d left join emp e on e.deptno=d.deptno group by d.deptno,d.dname,d.loc;-17列出各种类别工作的

14、最低工资。select job,min(sal) from emp group by emp.job;-18.列出各个部门的 MANAGER (经理)的最低薪金。select e.deptno,min(e.sal) from emp e where job=MANAGER group by e.deptno;-19列出按计算的字段排序的所有雇员的年薪。select e.ename,(e.sal+nvl(m,0)*12 年薪 from emp e order by 年薪 ;-第三部分：- 1 .找出各月倒数第 3 天受雇的所有员工 .select e.* from emp e where e.h

15、iredate=last_day(e.hiredate) -2; -2.找出早于 12 年前受雇的员工 .select e.ename from emp e where extract (year from e.hiredate) select ename from emp where months_between(sysdate,hiredate)144; select e.* from emp e where e.hiredate-3.以首字母大写的方式显示所有员工的姓名.select initcap(lower(e.ename) from emp e;-4.显示正好为 5 个字符的员工的姓名 .select e.ename from emp e where length(e.ename)=5;-5.显示不带有R的员工的姓名select e.ename from emp e where upper(e.ename) not like %R%;-6.显示所有员工姓名的前三个字符.select substr(e.ename,0,3) from emp e;-7.显示所有员工的姓名，用a替换所有Aselect replace(e.ename,A,a) from emp e;-8.显示满 10 年服务年限的员工的姓名和受雇日期 .:select e.ename ,e.