对Oracle数据库的相关练习

作业
Oracle基本操作练习题  
Scotte用户下的两张数据表描述：
员工表(emp)：
(
  empno NUMBER(4)notnull,  --员工编号，表示唯一
  ename VARCHAR2(10),    --员工姓名
  job VARCHAR2(9),    --员工工作职位
  mgr NUMBER(4),      --员工上级领导编号
  hiredate DATE,      --员工入职日期
  sal NUMBER(7,2),    --员工薪水
  comm NUMBER(7,2),    --员工奖金
  deptno NUMBER(2)    --员工部门编号
) ;

部门表（dept）：
(
  deptno NUMBER(2)notnull,  --部门编号
  dname VARCHAR2(14),      --部门名称
  loc VARCHAR2(13)      --部门地址
);


一、表的查询[所有小数都保留1位有效数字]
   1.查询出emp表中总工资大于2500的员工姓名，基本工资，奖金，总工资
    select ename,sal+nvl(comm,0) as sal from emp where sal>2500
         
   
   2.请查询出emp表中姓名长度超过4位并且姓名中包含m或M字符的员工信息。
   select * from emp where ename like '%M%' and length(ename)>4
   
   3.请查询出emp表中最高级别领导的姓名。
   select ename from emp where MGR is null;
   
   4.请查询出emp表中所有领导的姓名。
   select ename from emp where empno in (select mgr from emp group by mgr);
   
   5.请查询出emp表中同SCOTT一年入职的员工信息
   select * from emp where extract(year from hiredate)=(select extract(year from hiredate) from emp where ename='SCOTT')
   
   
   6.请查询出所有12月份入职的员工信息
   select * from emp where extract(month from hiredate)=12
   
   
   7.请查询出emp中姓名后2个字符是ES的员工信息
   select * from emp where ename like '%ES';
   
   8.请查询出所有1981入职员工的总工资，平均工资，人数
   select substr(hiredate,8,9) from emp;
   
   select sum(sal+nvl(comm,0)) 总工资,avg(sal+nvl(comm,0)) 平均工资,count(*) 总人数 from emp
    where substr(hiredate,8,9)=81;
   9.请查询出不同年份入职员工的总工资，平均工资，人数和年份[只显示年]
   select sum(sal),avg(sal),count(*),extract(year from hiredate) from emp group by extract(year from hiredate)
   10.请查询出各部门总工资，平均工资，部门名称，并在部门内部排序，给出名次
   select deptno,sum(sal*12+nvl(comm,0))总工资，round(avg(sal*12+nvl(comm,0)))平均工资，row_number() over(order by (round(avg(sal*12+nvl(comm,0)))) desc)from emp group by deptno;
   11.请查询出emp表中所有员工姓名，总工资，部门名称
   select ename,sal*12+nvl(comm,0),dname from emp,dept;
   
   12.请查询出emp中各部门员工的总人数，总工资，平均工资，并根据平均工资倒序
   select deptno as 部门名称，
          count(*) as 总人数，
          sum(sal+nvl(comm,0)) as 总工资，
          avg(sal+nvl(comm,0)) as 平均工资       
    from emp group by deptno order by avg(sal+nvl(comm,0)) desc;
    
    select * from emp;
   13.请查询出各个部门员工的总人数，平均工资，部门名称
   select d.dname 部门名称,count(*) 总人数,avg(e.sal+nvl(comm,0)) 平均工资 from emp e,dept d
   where d.deptno=e.deptno
   group by d.dname
   
   14.请查询出各部门的名称，最高工资，最低工资，平均工资，并且平均工资保留1位有效数字，最后按照平均工资倒序
   select DNAME as 部门名称,max(SAL) as 最高工资, min(SAL)as 最低工资,round(avg(SAL),1) as 平均工资 from emp e left join dept d
   on e.deptno = d.deptno
   group by DNAME
   order by avg(SAL) desc;
   
   15.请查询出部门名称为SALES的所有员工信息
   select * from dept
   select * from emp
   select empno,ename,job,mgr,hiredate,round(sal,1),round(comm,1),deptno from emp where deptno=(select deptno from dept where dname='SALES')
   16.请查询出没有员工的部门名称
   select dname from dept where deptno not in (select deptno from emp group by deptno)
   
   17.请查询出1981年入职人数超过2人的部门名称，入职人数
   select dname 部门名称,count(*) from emp,dept
   where emp.deptno=dept.deptno and extract(year from hiredate)=1981
   group by dname
   having count(*)>2
   
   
   18.请查询出各部门工资最高的2个人信息，部门名称及工资
   select 姓名,部门名称,工资 from
   (
     select 
     ename as 姓名,
     dname as 部门名称,
     sal+nvl(comm,0) as 工资，
     row_number() over(partition by dname order by sal+nvl(comm,0) desc) as 排名
     from emp e left join dept d
     on e.deptno=d.deptno
    )where 排名<=2
   19.请查询出同一个岗位有2个人以上工资在1000以上的岗位名称和人数
   select *from emp ;
   select job,count(*) 人数 from emp where sal>1000
   group by job
   having count(*)>2
   
   
   
   20.请查询与SCOTT在同一个部门的所有员工姓名，部门名称和岗位信息
   select e.ename as 员工姓名,d.dname as 部门名称,e.job as 岗位信息 from dept d left join emp e on d.deptno=e.deptno
   where d.dname=(select d.dname from dept d left join emp e on d.deptno=e.deptno where e.ename='SCOTT')


二、函数的熟练使用
   1.查询出当前日期，当前时间
   SELECT SYSDATE FROM DUAL ;
   2.将当前日期转换为制定格式,如:2014-11-15 15:30
   SELECT to_char(sysdate,'yyyy-mm-dd') from dual
   3.分别取出当前日期的年，月，日
   select substr(to_char(sysdate,'yyyymmdd'),0,4),substr(to_char(sysdate,'yyyymmdd'),5,2),substr(to_char(sysdate,'yyyymmdd'),7,2) from dual
  
 4.请计算出字符串'hello world!'的长度
   select length('hello world!') from dual;
   
 5.请截取出字符串'hello world!'中的'world'字符串
 select substr('hello world!'，7,5) from dual;
 
   6.请判断字符串'hello world!'中是否包含l字符
   select count(*) as 一为包含零为不包含 from dual 
   where instr('hello world!','l') between 1 and length('hello world!');
   
   select count(*) as 一为包含零为不包含 from dual 
   where 'hello world!' like '%l%';
   7.请判断字符串'hello world!'中是不是只包含一个l字符
   select instr('hello world!','l'),instr('hello world!','l',-1) from dual ;
   
   8.请去掉' hello world! '左边的空格
   select ltrim('hello world!') from dual;
   
   9.请将字符串'hello World'中字符l替换为L
   select replace('hello world','l','L')from dual;
   10.将字符串152.568转换为数字并保留5位有效数字，其中2位是小数
   select to_char('152.568','99G999D99') from  dual
   
三、
1.找出部门10中所有经理、部门20中所有办事员以及既不是经理又不是办事员但其薪金大于或等于2000的所有雇员的详细资料
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK') or (job<>'MANAGER'and job<>'CLERK'and sal>=2000);
2.找出收取佣金的雇员的不同工作
select distinct JOB from emp where comm>=0


3.显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。
select ename 雇员名称,hiredate 入职时间,to_char(hiredate,'yyyy') 服务年限 from emp order by hiredate asc
4.显示在一个月为30天的情况下所有雇员的日薪金，忽略余数
select ename,sal+nvl(comm,0) 日薪金 from emp;

5.以年、月和日显示所有雇员的服务年限
select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')-to_date(to_char(hiredate,'yyyy-mm-dd'), 'yyyy-mm-dd') 入职天数,trunc(months_between(sysdate,hiredate)) 入职月份,to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') 入职年份 from emp

6.列出薪金高于在部门30工作的所有雇员的姓名和薪金
select * from emp
where sal>(
      select max(sal) from emp where deptno=30
)

7.列出各种类别工作的最低工资
select job as 工作类型,min(sal) as 最低工资 from emp group by job ;
8.列出薪金水平处于第四位的雇员信息
select * from (select emp.*,rank() over (order by sal desc) i from emp )where i = 4
9.显示所有雇员的姓名以及满10年服务年限的日期
select ename,hiredate,add_months(hiredate,12*10) from emp
10.找出奖金高于薪金60%的雇员
select * from emp where comm>(sal * 0.6);

四、
1.查询出emp表中总工资大于2500的员工姓名，基本工资，奖金，总工资。
  select ename 员工姓名，sal 基本工资，nvl(comm,0) 奖金，sal+nvl(comm,0) 总工资 from emp where (sal+nvl(comm,0))>2500
  
  select * from emp
2.请查询出各个部门员工的总人数，平均工资，部门名称
select dname 部门名称,count(*) 总人数,nvl(avg(sal),0) 平均工资 from dept t left outer join emp e on t.deptno=e.deptno  group by e.deptno,dname

3.根据当前时间日期，查询前天是什么时间
select to_char(sysdate-2,'yyy-mm-dd hh:mi:ss') from dual ;
4.分别取出当前日期的年，月，日
select to_char(sysdate,'yyyy') 年,to_char(sysdate,'mm') 月,to_char(sysdate,'dd') 日 from dual;

5.请将编号为7499的员工工资调整为2600元
select * from emp
select empno as 编号，sal as 工资 from emp where empno='7499'
update emp set sal='2600' where empno='7499'


6.创建一个新表为emp,里面有编号，姓名，待遇，入职时间，并以员工的姓名，待遇，然后用"姓名:xxx,待遇:xxx"格式显示
create table emp1
as 
select empno,ename,hiredate,sal from emp;
select '编号：'||empno 编号,'姓名:'||ename 姓名,'入职时间:'||hiredate 入职时间,'待遇:'||sal 待遇 from emp1;       


7.根据上一题的emp，获取第二个员工的姓名长度
select ename,length(ename) 长度,rn 序号 from(
   select ename,row_number() over(order by empno)rn from emp1
   
)where rn=2

8.根据6题的emp表，查询出所有员工姓名，待遇并显示，但是待遇用#隐藏
select ename 姓名,replace(sal,sal,'#') 待遇 from emp1;

9.根据6题的emp表，--查询出第一个员工和第二个员工相隔多少天入职


10.根据6题的emp表，请根据工资待遇排序，然后查询出排名基数位的员工信息
select * from 
(select ename as 姓名,sal as 待遇,row_number() over (order by sal desc) as rn from emp)
where mod(rn,2)=1;

五、
1.找出奖金高于薪水60%的员工信息。
select * from emp
where nvl(comm,0)>sal*0.6;

2.找出部门10中的职位为（MANAGER）和部门20中所有职位为（CLERK）的详细资料。   
select * from emp e,dept t where e.deptno=t.deptno and 
((e.deptno=10 and job='MANAGER') or (e.deptno=20 and job='CLERK'));

3.统计各部门的薪水总和。  
select dname,sum(sal) from emp e left outer join dept t on e.deptno=t.deptno group by dname;
select deptno,sum(sal) from emp group by deptno;
select * from emp;
4.找出部门10中的职位为（MANAGER），部门20中所职位为（CLERK）以及职位不是（MANAGER）但其薪水大于或等2000的所有员工的详细资料。   

5.列出各种工作的最低工资。

6.列出各个部门的职位为（MANAGER）的最低薪水。 

7.找出没奖金或奖金低于300的员工。  

8.显示所有员工的姓名,首字母大写。 

9.显示正好为5个字符的员工的姓名。 

10.显示不带有“R”的员工姓名。   

六、
1、请查询出dept表中每个部门的编号，员工总人数，最高工资，最低工资，总工资和平均工资，并按照部门编号倒叙排序。

2、请查询出dept表中每个部门的编号，每个部门的总年薪(包括奖金)以及每个部门的平均年薪。

3、请查询出每个部门的编号和各个部门员工的平均工作年限。

4、请查询出员工受雇日期早于自身上级领导的所有员工的编号，姓名，上级姓名和部门名称。

5、请查询出最低工资大于1500的各种工种以及从事此工资的全部员工人数。

6、请查询出部门名称为“SALES”的员工的姓名、工资，并按照雇佣日期的先后进行排序。

7、请查询出基本工资高于平均工资的所有员工姓名，员工工作，上级领导姓名，所在部门地址信息。

8、查询出每个雇员所在月倒数第二天被雇佣的雇员信息。

9、查询出所有已经被雇佣了35年以上的员工信息。

10、统计出emp表中共有多少个职位。  

七、 
1、请查询出emp表中第1-3条员工的信息

2、请查询出工资待遇在2000以上的员工姓名，工资

3、显示姓名正好为5个长度的所有员工   

4、显示带有'R'的员工的详细   

5、显示员工姓名的前三个字符  

6、显示所有员工的姓名，用‘a’替换所有的'A'   

7、显示一个月为30天的情况下，所有员工的日薪，忽略余数  

8、请查询出工资待遇大于平均工资的员工信息

9、显示所有员工的姓名、工作和薪金，按工作的降序排列，工作相同则按照薪金的升序排列  
  
10、显示一个月为30天的情况下，所有员工的日薪，忽略余数  

八、
1、请查询出各部门的平均工资

2、请查询出各部门的总人数

3、请查询出各部门工资最高的员工信息

4、请查询出所有员工的姓名和总工资（基本工资+奖金）

5、请查询出所有员工的姓名以及至今入职了多少个月，并按倒序排序

6、请查询出姓名含有“T”的员工信息

7、请查询出姓名以“T”结尾的员工信息

8、请查询出工资最高的前三名员工，并按倒序排序

9、请查询出工资高于平均工资的所有员工信息

10、请按部门编号查询出所有员工信息，并显示偶数位的员工信息

九、
1、查询出emp表中的员工姓名，基本工资，奖金，总工资并按总工资从高到低排序。

2、查询出emp表中的员工比平均工资要高的基本信息

3、查询出emp表中1981年进来公司的员工基本信息

4、查询出emp表中姓名后1个字符是S的员工信息

5、查询出emp表中没有奖金的员工的姓名和基本工资

6、查询出emp表中在RESEARCH部门担任CLERK这职务的员工信息

7、查询出emp中员工编号7369的员工部门中的所有员工姓名，部门名称和岗位信息

8、查询出emp中工资排在第3到第6的员工信息。

9、查询出人数最多的部门名称和平均工资。

10、请查询出emp表中同SCOTT一年入职的员工信息及所在部门。   
   
   
十、创建以下数据表，并按要求完成以下题目。
--班级表(编号,名称)
create table cls(
  cno number primary key,
  cname varchar2(20)       
) ;

--老师表(编号,姓名,性别,班级编号)
create table teacher(
  tno number primary key,
  tname varchar2(20),
  tsex char(2) default '男' check(tsex='男' or tsex='女'),
  tcno number,
  foreign key(tcno) references cls(cno)
)
--学生表(编号,姓名,性别,年龄,入学时间,地址,在读状态,班级编号)
create table stu(
  sno number primary key,  
  sname varchar2(20),
  ssex char(2) default '男' check(ssex='男' or ssex='女'),
  sage number,
  sintime date,
  --记录学生状态的列,1的时候为在读,0的时候则已经退学
  sstatus number default 1 check(sstatus = 1 or sstatus = 0),
  scno number,
  foreign key(scno) references cls(cno)
)
--分数表(编号,成绩,学生编号)
create table mark(
  mno number primary key,
  mcj number(4,1) check(mcj >= 0),
  msno number,
  foreign key(msno) references stu(sno)
)

--添加初始化数据

--插入班级测试数据
insert into cls 
  select 1,'ST01' from dual 
  union 
  select 2,'ST02' from dual;

--插入老师测试数据
insert into teacher values(1,'涂老师','男',1);
insert into teacher values(2,'刘老师','女',1);
insert into teacher values(3,'陈老师','男',2);
insert into teacher values(4,'李老师','女',2);
insert into teacher values(5,'蔡老师','女',1);

--插入学生测试数据
insert into stu values(1,'小一','男',20,to_date('2016-6-13','yyyy-mm-dd'),default,1);
insert into stu values(2,'小二','男',21,to_date('2016-6-13','yyyy-mm-dd'),default,1);
insert into stu values(3,'小三','女',20,to_date('2016-6-13','yyyy-mm-dd'),default,1);
insert into stu values(4,'小四','男',19,to_date('2016-6-15','yyyy-mm-dd'),default,1);
insert into stu values(5,'小五','女',19,to_date('2016-6-15','yyyy-mm-dd'),default,1);
insert into stu values(6,'小六','男',21,to_date('2016-6-15','yyyy-mm-dd'),default,1);
insert into stu values(7,'小七','男',21,to_date('2016-6-20','yyyy-mm-dd'),0,1);
insert into stu values(8,'小八','女',20,to_date('2016-6-20','yyyy-mm-dd'),default,1);
insert into stu values(9,'小九','男',23,to_date('2016-6-21','yyyy-mm-dd'),default,1);
insert into stu values(10,'小十','男',21,to_date('2016-6-21','yyyy-mm-dd'),0,1);

insert into stu values(11,'大一','男',20,to_date('2016-7-3','yyyy-mm-dd'),default,2);
insert into stu values(12,'大二','男',21,to_date('2016-7-3','yyyy-mm-dd'),default,2);
insert into stu values(13,'大三','女',20,to_date('2016-7-3','yyyy-mm-dd'),default,2);
insert into stu values(14,'大四','男',19,to_date('2016-7-6','yyyy-mm-dd'),default,2);
insert into stu values(15,'大五','女',19,to_date('2016-7-6','yyyy-mm-dd'),default,2);
insert into stu values(16,'大六','男',21,to_date('2016-7-9','yyyy-mm-dd'),default,2);
insert into stu values(17,'大七','男',21,to_date('2016-7-9','yyyy-mm-dd'),default,2);
insert into stu values(18,'大八','女',20,to_date('2016-7-9','yyyy-mm-dd'),default,2);
insert into stu values(19,'大九','男',23,to_date('2016-7-13','yyyy-mm-dd'),0,2);
insert into stu values(20,'大十','男',21,to_date('2016-7-13','yyyy-mm-dd'),default,2);

--插入分数测试数据
insert into mark values(1,90,1);
insert into mark values(2,80,1);
insert into mark values(3,92,2);
insert into mark values(4,91,3);
insert into mark values(5,58,4);
insert into mark values(6,64,5);
insert into mark values(7,72,11);
insert into mark values(8,65,12);
insert into mark values(9,58,12);
insert into mark values(10,96,13);
insert into mark values(11,91,15);
insert into mark values(12,53,7);
insert into mark values(13,61,7);


题目：
1.请查询出名字带'小'的21岁以上在读男生信息

2.请查询出‘ST01’班级的所有在读女生的姓名与性别以及'ST02'班级所有在读男生的姓名与性别

3.请查询出和“小二”同学入学日期中的“日”相同的所有在读学生信息

4.请统计各班在读男生人数以及女生人数并按人数倒序排序

5.请查询出所有考试两次的在读学员信息

6.请查询名为“小一”所在班级的所有教师信息

7.请查询出班级学生平均分最高的班级名称

8.请查询出所有班级的在读学生的姓名、分数

9.请查询出所有涂老师所在班级的在读学生的姓名、分数

10.请统计各班级在读学生人数以及教师人数


//由于时间关系没能完全做完这些练习