(Oracle)SQL知识与40个实例

SQL语句

用具体的练习题来学习吧!(本文使用oracle自带的表：emp表、dept表)

1.  查询部门30中的雇员信息。

  select * from emp where deptno=30;

2.  查询佣金（comm）高于薪金(sal)的雇员信息。

select * from emp where comm>sal;

3.  查询佣金高于薪金60%的雇员信息。

  select * from emp where comm>sal*0.6;

4.  查询部门10中所有经理（MANAGER）和部门20中所有办事员(CLERK)的信息。

  select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 andjob='CLERK');

5.  查询部门10中所有经理、部门20中所有办事员和既不是经理又不是办事员但薪金大于2000的所有雇员信息。

select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or(job<>upper('manager') and job<>upper('clerk') and sal>2000);

注:本例中用到字符型函数upper把小写转化为大写 ，还要注意不等于<>的用法，不等于还有另外两种（!=和^=）。

6.  列出所有办事员的姓名、编号、部门。

  select ename,empno,dname from emp e,dept d where e.deptno=d.deptno andjob=upper('clerk');

  注：本例中用到了多表连接查询。

7.  查询收取佣金的雇员的不同工作。

  select distinct job from emp where comm>0;

  注：distinct用来把重复的排除掉。

8.  查询佣金低于100的雇员。

  select ename from emp where nvl(comm,0)<100;

  注：用nvl函数来对为空值的comm进行处理，若comm为空，则把空值转化为0来与100比较。

9.  查询各月最后一天受雇的雇员信息。

  select * from emp  wherehiredate=last_day(hiredate);

注：这个例子比较有技巧，大家要好好琢磨。

10.查询工作年限大于25年的雇员信息。

select * from emp where months_between(sysdate,hiredate)/12>25;

注：这里用months_between函数计算雇员工作的总月份。

select * from emp where hiredate<add_months(sysdate,-12*25);

注：日期是能够比较大小的，本例中用add_months函数把当前日期计算到25年前。

11.查询只有首字母大写的雇员姓名。

   select ename from emp where ename=initcap(ename);

   注：本例中用initcap函数把ename转化为首字母大写的形式来与ename比较。

12.查询正好为6个字符的雇员姓名。

   select ename from emp where length(ename)=6;

   注：本例中用length函数求ename的长度。

13.查询不带‘R’的雇员姓名。

   select ename from emp where ename not like '%R%';

   注：注意like的用法，%代表0个或多个任意字符，_代表一个任意字符。

14.查询所有雇员的姓名的前3个字符。

   select substr(ename,1,3) from emp;

   注：本例中用substr函数提取ename的前三个字符。

15.查询所有雇员姓名并用‘a’替换‘A’。

   select replace(ename,'A','a')from emp;

   注：本例中用replace函数来把A替换为a。

16.查询所有雇员的姓名和工龄满10年时的日期。

   select ename as 姓名,add_months(hiredate,12*10)as 工龄满10年时的日期 from emp;

   注：本例中要注意列名的重命名，期中as可以省略。

17.查询雇员的信息，按姓名排序。

   select * from emp order by ename;

注：排序就用order by,默认为升序，若要降序，要加desc关键字。

18.根据工作年限查询雇员姓名，并将最老的雇员排在前面。

   select ename from emp order by hiredate;

19.查询雇员的姓名、工作、薪金，按工作的降序排序，工作相同时按工资升序排序。

   select ename,job,sal from emp order by job desc , sal;

   注：本例中的排序用法大家要记住嗷！

20.查询所有雇员的姓名和入职的年份和月份，按雇员受雇日所在月排序，将最早年份的排在前面。

   select ename as 姓名,to_char(hiredate,'yyyy')as 年份,to_char(hiredate,'mm') as 月份 from emp order by hiredate;

   注：注意本例中对日期hiredate的处理。

21.查询雇员的日薪金（假定每月30天）。

   select ename as 姓名,sal/30 as日薪 from emp;

22.查询在2月份入职的雇员信息。

   select * from emp where to_char(hiredate,'mm')=02;

23.查询每个雇员加入公司的天数。

   select ename 姓名,sysdate-hiredate as工作天数 from emp;

   注：两个日期相减得出它们之间的天数。

24.查询姓名中包含‘A’的所有雇员姓名。

   select ename from emp where ename like '%A%';

25.以年、月、日查询每位雇员的工作年限。

   select ename as 姓名,months_between(sysdate,hiredate)/12as 工作年数,months_between(sysdate,hiredate) as 工作月数,sysdate-hiredate as 工作天数 from emp;

26.查询至少有一个雇员的部门。

   select dname from dept where deptno in(select distinct deptno from emp);

   注：本例很有技巧，大家多看一分钟，哈哈！

27.查询薪金比‘SMITH’多的雇员信息。

   select * from emp where sal>(select sal from emp whereename='SMITH');

28.查询雇员姓名以及其直接上级的姓名。

   select a.ename as 姓名,b.ename as 上级姓名 from emp a,emp b where a.mgr=b.empno;

   注：本例可以类似的看做多表连接查询，只不过是两个相同的表。

29.查询入职日期早于其直接上级的雇员信息。

   select a.* from emp a,emp b where a.hiredate<b.hiredate anda.mgr=b.empno;

   select * from emp e where hiredate<(select hiredate from emp whereempno=e.mgr);

   注：提供两种做法供大家参考。

30.查询部门名称和这些部门的雇员以及没有雇员的部门。

   select dname,ename from dept d left join emp e on e.deptno=d.deptnoorder by dname;

   注：本例中要注意left join 的用法，就是首先提取dept表中所有的dname，再加上与dapt和emp相匹配的数据项。

31.查询各种工作类别的最低薪金，显示最低薪金大于1500的记录。

   select job,min(sal) from emp group by job having min(sal)>1500;

   注：按组查询就用group by，having条件在分组后执行。

32.查询薪金高于公司平均工资的雇员信息。

   select * from emp where sal>(select avg(sal) from emp);

33.查询与‘SCOTT’工作相同的雇员信息。

   select * from emp where job=(select job from emp where ename='SCOTT');

34.查询某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金。

   select ename,sal from emp where sal>(select max(sal) from emp wheredeptno=30);

35.查询某些雇员的姓名和薪金，条件是他们的薪金高于部门30中任一雇员的薪金。

   select ename,sal from emp where sal>(select min(sal) from emp wheredeptno=30);

36.查询每个部门的信息以及该部门中雇员的数量。

 select d.deptno,dname,count(ename) from dept dleft join emp e on(e.deptno=d.deptno) group by dname,d.deptno order byd.deptno;

37.查询从事同一种工作但属于不同部门的雇员信息。

   select a.ename,a.job,a.deptno,b.deptno,b.job,b.ename from emp a,emp bwhere a.job=b.job and a.deptno!=b.deptno;

38.查询各个部门的经理的最低薪金。

   select deptno,min(sal) from emp where job='MANAGER' group by deptno;

39.查询所有雇员的年薪并按年薪排序；

   select ename,(sal+nvl(comm,0))*12 as 年薪 fromemp order by 年薪;

40.查询薪金水平为4的雇员信息。

   select * from (select ename,sal,rank() over(order by sal desc) as gradefrom emp) where grade=4;

   注：本例中值得学习的是rank() over()的用法，rank为跳跃排序，若1,1 则接下来就是3.本例就是用rank多数据按sal排序。