Oracle函数练习题

1.查询部门编号为10的员工信息

　　select * from emp where deptno = 10;

2.查询年薪大于3万的人员的姓名与部门编号

　　select e.ename,e.deptno from emp e where (e.sal+nvl(e.comm,0))*12 >30000;

3.查询佣金为null的人员姓名与工资

　　select e.ename,e.sal from emp e where e.comm is null;

4.查询工资大于1500且and含有佣金的人员姓名

　　select e.ename from emp e where e.sal > 1500 and e.comm is not null;

5.查询工资大于1500或or含有佣金的人员姓名

　　select e.ename from emp e where e.sal > 1500 or e.comm is not null;

6.查询姓名里面含有S员工信息（姓名、工资）

　　select e.ename,e.sal from emp e where e.ename like('%S%');

7.求姓名以J开头第二个字符O的员工姓名与工资

　　select e.ename,e.sal from emp e where e.ename like('JO%');

8.求包含%的雇员姓名

　　select e.ename from emp e where e.ename like('%/%%') escape('/');

9.使用in查询部门名称为SALES和RESEARCH的雇员姓名、工资、部门编号

　　

10.使用exists查询部门名称为SALES和RESEARCH的雇员姓名、工资、部门编号

　　

 

1.查询DEPT表显示所有部门名称

　　select d.dname from dept d;

2.查询EMP表显示所有雇员名及其全年收入（月收入=工资+补助），处理NULL行，并指定列别名为“年收入”

　　select e.ename,(e.sal+nvl(e.comm,0))*12 "年收入" from emp e;

3.查询显示不存在雇员的所有部门号

　　select e.deptno from emp e where e.ename is null;

4.查询EMP表显示工资超过2850的雇员姓名和工资

　　select e.ename,e.sal from emp e where e.sal>2850;

5.查询EMP表显示工资不在1500~2850之间的所有雇员及工资

　　select e.ename,e.sal from emp e where e.sal not between '1500' and '2850';

6.查询EMP表显示代码为7566的雇员姓名及所在部门编号

　　select e.ename,e.deptno from emp e where e.mgr = '7566';

7.查询EMP表显示部门10和30中工资超过1500的雇员名及工资

　　select e.ename,e.sal from emp e where e.deptno in('10','30') and e.sal>1500;

8.查询EMP表显示第2个字符为‘A’的所有雇员名及其工资

　　select e.ename,e.sal from emp e where e.ename like('_A%');

9.查询EMP表显示补助非空的所有雇员名及其补助

　　select e.ename,e.comm from emp e where e.comm is not null;

10.查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序

　　select e.ename,e.sal,e.hiredate from emp e order by e.ename asc;

11.查询EMP表显示在1981/2/1~1981/5/1之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期升序进行排序

　　

12.查询EMP表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序

 　　

 

1.显示没有上级管理的公司首脑

　　select e.ename,nvl(to_char(e.mgr),'no mgr') from emp e where e.mgr is null;

2.显示员工雇佣期满6个月后下一个星期五的日期

　　select e.ename,next_day(add_months(e.hiredate,6),'星期五') from emp e;

3.给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30%

　　select decode(deptno,10,e.sal*1.1,20,e.sal*1.2,30,e.sal*1.3) from emp e;

4.查询82年员工

　　

5.查询39年工龄人员

6.显示员工雇佣期6个月后下一个星期一的日期

　　select next_day(add_months(e.hiredate,6),'星期一') from emp e;

7.查询10号部门中编号最新入职的员工，工龄最长的员工的个人信息

　　

8.从“software”中找到“f”的位置，用*左或右填充到15位，去除其中的‘a’

　　

9.查询员工的奖金，如果不为NULL显示“有奖金”，为null则显示“无奖金”

　　select ename,comm,decode(comm,null,'无奖金','有奖金') from emp;

10.查询显示当前日期，列标题显示为Date。显示六个月后的日期，下一星期日的日期，该月最后一天的日期

　　

11.查询EMP表按管理员编号升序排列，如果管理者编号为空则把为空的在最前显示

　　select * from emp Order by mgr asc nulls first;

12.求部门平均薪水

　　select deptno,avg(sal) from emp group by deptno;

13.按部门求出工资大于1300人员的部门编号、平均工资、最小佣金、最大佣金，并且最大佣金大于100

　

14.找出每个部门的平均、最小、最大薪水

　　select avg(e.sal),min(e.sal),max(e.sal) from emp e group by deptno;

15.查询出雇员名，雇员所在部门名称，工资等级

　　

16.求平均薪水最高的部门的部门编号

　　①求出部门和部门下的平均薪水

　　    select e.deptno,avg(e.sal) from emp e group by deptno;

　　②求出平均薪水中的最高薪水

　　    select max(t.msal) from (select avg(e.sal) msal from emp e group by deptno) t;

　　③求平均薪水最高的部门的部门编号

　   　

17.求部门平均薪水的等级

　　

18.求部门平均的薪水等级

19.求薪水最高的前5名雇员

20.求薪水最高的第6到10名雇员

 

行转列：

第一题

create table test(
   id number(10) primary key,
   type number(10) ,
   t_id number(10),
   value varchar2(5)
);
insert into test values(100,1,1,'张三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');

insert into test values(101,1,2,'刘二');
insert into test values(201,2,2,'男');
insert into test values(301,3,2,'30');

insert into test values(102,1,3,'刘三');
insert into test values(202,2,3,'女');
insert into test values(302,3,3,'10');

select * from test;
/*
需求
将表的显示转换为
姓名      性别     年龄
--------- -------- ----
张三       男        50
*/

 第二题

create table tmp(rq varchar2(10),shengfu varchar2(5));

insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','胜');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-09','负');
insert into tmp values('2005-05-10','胜');
insert into tmp values('2005-05-10','负');
insert into tmp values('2005-05-10','负');

/*
转换成：
          胜 负
2005-05-09 2 2
2005-05-10 1 2

*/

 

第三题

create table STUDENT_SCORE
(
  name    VARCHAR2(20),
  subject VARCHAR2(20),
  score   NUMBER(4,1)
);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

/*
姓名   语文  数学  英语
王五    89    56    89
*/
方式一：

 

方式二：