(Oracle)SQL知识与40个实例
SQL语句
用具体的练习题来学习吧!(本文使用oracle自带的表：emp表、dept表)
1. 查询部门30中的雇员信息。
select * from emp where deptno=30;
2. 查询佣金（comm）高于薪金(sal)的雇员信息。
select * fromemp 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 * fromemp 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 * fromemp where months_between(sysdate,hiredate)/12>25;
注：这里用months_between函数计算雇员工作的总月份。
select * fromemp 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排序。
