oracel练习题

1. 查询20部门的所有员工信息。
   select * from emp where deptno='20';
2. 查询所有工种为CLERK的员工的员工号、员工名和部门号。
   select empno,ename,deptno from emp where job='CLERK';
3. 查询奖金（COMM）高于工资（SAL）的员工信息。
   select * from emp where comm>sal;
4. 查询奖金高于工资的20%的员工信息
   select * from emp where nvl(comm,0)>nvl((sal*0.2),0)
5. 查询10号部门中工种为MANAGER和20部门中工种为CLERK的员工的信息。
   select * from emp where (deptno='10' and job like 'MANAGER') or (deptno='20' and job like 'CLERK')
6. 查询所有工种不是MANAGER和CLERK的员工信息
   select * from emp where job not in ('MANAGER','CLERK');
7. 查询有奖金的员工的不同工种。
   select distinct job from emp where nvl(comm,0) <> 0;
8. 查询所有员工工资与奖金的和。
   select sum(nvl(sal,0)+nvl(comm,0)) from emp;
   查询每个员工工资与奖金的和
   select ename sum(nvl(sal,0)+nvl(comm,0)) from emp group by ename;
9. 查询没有奖金或奖金低于100的员工信息。
   select * from emp where nvl(comm,0)<100;
10. 查询各月倒数第3天(倒数第2天)入职的员工信息。
    select * from emp where hiredate=last_day(hiredate)-2;
11. 查询工龄大于或等于25年的员工信息。
    select * from emp where to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')>=25;
    11.查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
    select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp;
    select initcap(ename) from emp;
12. 查询员工名正好为6个字符的员工的信息。
    select * from emp where length(ename)=6;
13. 查询员工名字中不包含字母“Ｓ”的员工。
    select * from emp where ename not like '%S%';
14. 查询员工姓名的第二字母为“M”的员工信息。
    select * from emp where ename like '_M%';
15. 查询所有员工姓名的前三个字符。
    select substr(ename,1,3) from emp;
16. 查询所有员工的姓名，如果包含字母“S”，则用“s”替换。
    select replace(ename,'S','s') from emp where ename like '%S%';
17. 查询员工的姓名和入职日期，并按入职日期从先到后进行排序。
    select ename,hiredate from emp order by hiredate asc;
18. 显示所有员工的姓名、工种、工资和奖金，按工种降序排序，
    select ename,job,sal,comm from emp order by job desc;
19. 显示所有员工的姓名、入职的年份和月份,按入职日期所在的月份排序，若月份相同则按入职的年份排序。
    select ename,to_char(hiredate,'mm') 月,to_char(hiredate,'yyyy') 年 FROM emp order by 月,年

---

select ename,extract(month from hiredate),extract(year from hiredate) from emp order by extract(month from hiredate),extract(year from hiredate);

20. 查询在2月份入职的所有员工信息。
    select * from emp where extract(month from hiredate)=2;
21. 查询所有员工入职以来的工作期限，用“XX年XX月XX日”的形式表示。
    select floor(months_between(sysdate,hiredate)/12)||'年'||floor(mod(months_between(sysdate,hiredate),12))||'月'||ceil((mod(months_between(sysdate,hiredate),12)-trunc(mod(months_between(sysdate,hiredate),12)))*30) ||'天' as "入职年限"from emp;
22. 查询至少有一个员工的部门信息。
    select * from dept where deptno in (select deptno from emp group by deptno);
    select distinct d.* from emp e,dept d where e.deptno=d.deptno;
23. 查询至少有两个员工的部门信息。
    select * from dept where deptno in( select deptno from emp group by deptno having count(*)>1);
24. 查询工资比SMITH员工工资高的所有员工信息。
    select * from emp where sal>(select sal from emp where ename='SMITH');
25. 查询所有员工的姓名及其直接上级的姓名。
    select e1.ename 员工姓名,e2.ename 上级姓名 from emp e1,emp e2 where e1.mgr=e2.empno;
26. 查询入职日期早于其直接上级领导的所有员工信息。
    select e1.* from emp e1,emp e2 where e1.mgr=e2.empno and e1.hiredate<e2.hiredate;
27. 查询所有部门及其员工信息，包括那些没有员工的部门。
    select dept.,emp. from dept left join emp on dept.deptno=emp.deptno;

---

select dept.,emp. from emp right join dept on dept.deptno=emp.deptno;

28. 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。
    select emp.,dept. from emp left join dept on emp.deptno=dept.deptno;
29. 查询所有工种为CLERK的员工的姓名及其部门名称。
    select emp.ename,dept.dname,emp.job from emp,dept where emp.job='CLERK' and emp.deptno=dept.deptno;
30. 查询最低工资大于2500的各种工作。
    select job,min(sal) from emp group by job having nvl(min(sal),0)>2500;
31. 查询平均工资低于2000的部门及其员工信息。
    select t1.* from emp t1,dept t2 where t1.deptno=t2.deptno and t1.deptno in (select deptno from emp group by deptno having nvl(avg(sal),0)<2000);
32. 查询在SALES部门工作的员工的姓名信息。
    select t1.ename from emp t1,dept t2 where t1.deptno=t2.deptno and t2.dname='SALES';
33. 查询工资高于公司平均工资的所有员工信息。
    select * from emp where sal > (select nvl(avg(sal),0) from emp);
34. 查询出与SMITH员工从事相同工作的所有员工信息。
    select * from emp where job in (select job from emp where ename='SMITH');
35. 列出工资等于30部门中某个员工的工资的所有员工的姓名和工资。
    select ename,sal from emp where sal in (select sal from emp where deptno='30');
36. 查询工资高于30部门工作的所有员工的工资的员工姓名和工资。
    select * from emp where sal >all(select sal from emp where deptno=30);
37. 查询每个部门中的员工数量、平均工资和平均工作年限。
    SQL> select dept.deptno,dept.dname,avg(sal),count(emp.empno),floor(avg(months_between(sysdate,hiredate)/12)) from dept left join emp on emp.deptno=dept.deptno group by dept.deptno,dept.dname order by dept.deptno desc;
38. 查询从事同一种工作但不属于同一部门的员工信息。
    select distinct * from emp t1,emp t2 where t1.job=t2.job and t1.deptno <> t2.deptno;

---

SQL> select a.ename,a.job,a.deptno,b.ename,b.job,b.deptno from emp a,emp b where a.job=b.job and a.deptno<>b.deptno;

SQL> select distinct e1.* from emp e1 join emp e2 on e1.job=e2.job and e1.deptno<>e2.deptno;

39. 查询各个部门的详细信息以及部门人数、部门平均工资。
    select t1.,t2.sumEmp,t2.avgSal from dept t1 left join( select deptno,count() sumEmp,nvl(avg(sal),0) avgSal from emp group by deptno) t2 on t1.deptno=t2.deptno;

---

！！！组合函数的列名不能直接被表名.列名使用，需要给组合函数列起个别名

40. 查询各种工作的最低工资。
    select nvl(min(sal),0) from emp group by job;
41. 查询各个部门中不同工种的最高工资。
    select nvl(max(sal),0) from emp group by deptno,job;
42. 查询10号部门员工及其领导的信息。
    select e1.,e2. from emp e1 full join emp e2 on e1.mgr=e2.empno where e1.deptno='10';
43. 查询各个部门的人数及平均工资。
    select count(*),trunc(nvl(avg(sal),0),2) from emp group by deptno;
44. 查询工资为某个部门平均工资的员工的信息。
    select * from emp where sal in (select trunc(nvl(avg(sal),0),2) from emp group by deptno);
45. 查询工资高于本部门平均工资的员工的信息。
    select e1.* from emp e1 where e1.sal>( select trunc(nvl(avg(sal),0),2) from emp e2 where e1.deptno=e2.deptno );!!!!!!!!!!关联子查询 研究一哈
    select * from emp left join (select deptno,nvl(avg(sal),0) salary from emp group by deptno) t on t.deptno=emp.deptno where emp.sal>t.salary;
    --
    查询工资高于所有人平均工资的员工的信息
    select * from emp where sal>(select avg(sal) from emp); !!!非关联子查询
    --
    查询工资高于所有人平均工资的员工的信息和所有人的平均工资
    select e2.*,(select avg(sal) from emp e1) from emp e2 where sal>(select avg(sal) from emp);
    !!!!多次使用同一张表需要起别名，否则找不到表，会报错

---

46. 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
    select e1.*,(select trunc(nvl(avg(sal),0),2) from emp e2 where e1.deptno=e2.deptno) from emp e1 where sal>(select trunc(nvl(avg(sal),0),2) from emp e2 where e1.deptno=e2.deptno);

---

47. 查询工资高于20号部门某个员工工资的员工的信息。
    select * from emp where sal > any( select sal from emp where deptno='20' );
48. 统计各个工种的员工人数与平均工资。
    select job,count(*) 员工人数,trunc(nvl(avg(sal),0),2) 平均工资 from emp group by job;
49. 统计每个部门中各工种的人数与平均工资。
    select deptno,job,count(*) 员工人数,trunc(nvl(avg(sal),0),2) 平均工资 from emp group by deptno,job order by deptno,job;
50. 查询其他部门中工资、奖金与30号部门某员工工资、奖金都相同的员工的信息。
    SQL> select * from emp cross join(select sal,comm from emp where deptno=30)t where emp.deptno<>30 and emp.sal=t.sal and emp.comm=t.comm;

---

SQL> select * from emp,(select sal,comm from emp where deptno=30)t where emp.deptno<>30 and emp.sal=t.sal and emp.comm=t.comm;

51. 查询部门人数大于5的部门的员工信息。
    select * from emp where deptno in( select deptno from emp group by deptno having count(*)>5) ;
52. 查询所有员工工资都大于1000的部门的信息。
    select * from emp where deptno in( select deptno from emp group by deptno having min(sal)>1000);

---

SQL> select * from dept where deptno not in(select deptno from emp where sal <1000);

53. 查询所有员工工资都大于1000的部门的信息及其员工信息。
    select * from emp t1 full join dept t2 on t1.deptno=t2.deptno where t1.deptno in (select deptno from emp group by deptno having min(sal)>1000);
54. 查询所有员工工资都在900～3000之间的部门的信息。
    SQL> select * from dept where deptno in(select deptno from emp group by deptno having min(sal)>900 and max(sal)<3000);

---

55. 查询有工资在900～3000之间的员工所在部门的员工信息。
    SQL> select * from emp where deptno in (select distinct deptno from emp where sal between 900 and 3000);
56. 查询每个员工的领导所在部门的信息。
    select * from dept where deptno in( select e2.deptno from emp e1 inner join emp e2 on e1.mgr=e2.empno group by e2.deptno);

---

SQL> select * from dept where deptno in(select distinct e1.deptno from emp e1,emp e2 where e1.empno=e2.mgr);

57. 查询人数最多的部门信息。
    select * from dept where deptno in( select deptno from ( select deptno,count() from dept group by deptno order by count() desc) where rownum=1);
58. 查询30号部门中工资排序前3名的员工信息。
    select * from ( select * from emp where deptno='30' order by sal desc) where rownum<4;
    SQL> select emp.,row_number()over(order by sal desc) from emp where deptno=30 and rownum<4;
    SQL> select * from (select emp.,row_number()over(order by sal desc) r from emp) where r<4;
    !!!为每一个查询生成的表添加隐藏列rownum行号。row_number()over()函数可以直接生成列rownum。rownum行号从第一行比较，若不满足条件则结束比较查找过程，所以rownum不用<比较符号。利用别名，生成新列，可以比较<符号
59. 查询所有员工中工资排序在5到10名之间的员工信息。
    SQL> select * from(select t.*,rownum rn from (select * from emp order by sal desc)t where rownum<11)m where m.rn>4;

---

SQL> select * from (select emp.*,row_number()over(order by sal desc) rn from emp where rownum<11)t where t.rn>4;

60. 查询指定年份之间入职的员工信息。(1980-1985)
    select * from emp where to_char(hiredate,'yyyy') between 1980 and 1985 order by hiredate;
    SQL> select * from emp where hiredate between to_date('1980','yyyy') and to_date('1990','yyyy');

---

1、row_number() over (order by col_1[,col_2 ...])
按照col_1[,col_2 ...]排序,返回排序后的结果集，并且为每一行返回一个不相同的值。

2、row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
先按照col_n[,col_m ...进行分组，再在每个分组中按照col_1[,col_2 ...]进行排序(升序)，最后返回排好序后的结果集

---

drop table if exists student;
create table student(
sid int primary key,
sname varchar(4) ,
sage datetime,
ssex varchar(2)
)

drop table if exists teacher;
create table teacher(
tid int primary key,
tname varchar(5)
)

drop table if exists course;
create table course(
cid int primary key,
cname varchar(5),
tid int ,
foreign key (tid) references teacher(tid)
)

drop table if exists sc
create table sc(
sid int ,
cid int ,
score int ,
foreign key(sid) references student(sid),
foreign key(cid) references course(cid)
)

insert into Student values(01 , N'赵雷' , '1990-01-01' , N'男')
insert into Student values(02 , N'钱电' , '1990-12-21' , N'男');
insert into Student values(03 , N'孙风' , '1990-05-20' , N'男');
insert into Student values(04 , N'李云' , '1990-08-06' , N'男');
insert into Student values(05 , N'周梅' , '1991-12-01' , N'女');
insert into Student values(06 , N'吴兰' , '1992-03-01' , N'女');
insert into Student values(07 , N'郑竹' , '1989-07-01' , N'女');
insert into Student values(08 , N'王菊' , '1990-01-20' , N'女');

insert into Course values(01 , N'语文' , 02);
insert into Course values(02 , N'数学' , 01);
insert into Course values(03 , N'英语' , 03);

insert into Teacher values(01 , N'张三');
insert into Teacher values(02 , N'李四');
insert into Teacher values(03 , N'王五');
insert into Teacher values(03 , N'李五');

insert into SC values(01 , 01, 80);
insert into SC values(01 , 02 , 90);
insert into SC values(01 , 03 , 99);
insert into SC values(02 , 01 , 70);
insert into SC values(02 , 02 , 60);
insert into SC values(02 , 03 , 80);
insert into SC values(03 , 01 , 80);
insert into SC values(03 , 02 , 80);
insert into SC values(03 , 03 , 80);
insert into SC values(04 , 01 , 50);
insert into SC values(04 , 02 , 30);
insert into SC values(04 , 03 , 20);
insert into SC values(05 , 01 , 76);
insert into SC values(05 , 02 , 87);
insert into SC values(06 , 01 , 31);
insert into SC values(06 , 03 , 34);
insert into SC values(07 , 02 , 89);
insert into SC values(07 , 03 , 98);

---

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

---

select student.,sc. from student,sc where student.sid in (select distinct sc.sid from sc,(select * from sc where cid=1)c1,(select * from sc where cid=2)c2 where sc.sid=c1.sid and sc.sid=c2.sid and c1.sid=c2.sid and c1.score>c2.score) and student.sid=sc.sid；

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
   mysql> select student.*,t.a from student,(select sid,avg(score)a from sc group by sid having avg(score)>60)t where student.sid=t.sid;
3. 查询在 SC 表存在成绩的学生信息
   mysql> select * from student where sid in(select distinct sid from sc);
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
   mysql> select student.sname,t.s,t.c from student left join (select sid,count(cid)c,sum(score)s from sc group by sid)t on t.sid=student.sid;
5. 查询「李」姓老师的数量
6. 查询学过「张三」老师授课的同学的信息
   mysql> select * from student where sid in(select sc.sid from teacher,course,sc where teacher.tid=course.tid and course.cid=sc.cid and teacher.tid=1);
7. 查询没有学全所有课程的同学的信息
   mysql> select * from student where sid in (select sid from sc group by sid having count(cid)=3);
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
   mysql> select * from student,sc where sc.sid=student.sid and sc.cid in (select cid from sc where sid=1);
9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
   mysql> select * from student,sc where sc.sid=student.sid and sc.cid in (select cid from sc where sid=1) and student.sid!=1;
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
    mysql> select * from student where sid not in(select sid from student where sid in(select sc.sid from teacher,course,sc where teacher.tid=course.tid and course.cid=sc.cid and teacher.tid=1));
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
    select student.sid,sname,"avg(score)" from student right join( select sid,avg(score) from sc where score<60 group by sid having count(score)>=2)t on t.sid=student.sid;
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14. 查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
    15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
    16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
18. 查询各科成绩前三名的记录
19. 查询每门课程被选修的学生数
20. 查询出只选修两门课程的学生学号和姓名
21. 查询男生、女生人数
22. 查询名字中含有「风」字的学生信息
23. 查询同名同性学生名单，并统计同名人数
24. 查询 1990 年出生的学生名单
25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
30. 查询不及格的课程
31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
32. 求每门课程的学生人数
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
36. 查询每门功成绩最好的前两名
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
38. 检索至少选修两门课程的学生学号
39. 查询选修了全部课程的学生信息
40. 查询各学生的年龄，只按年份来算
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
42. 查询本周过生日的学生
43. 查询下周过生日的学生
44. 查询本月过生日的学生
45. 查询下月过生日的学生