Oracle 例题 四篇

这些大多只涉及了增删改查，分组等简单问题

第一篇

create table m (id number,name varchar2(100),job varchar2(100),mana number,time date,sal number,comm number,nid number)
insert into m values (1,'孙悟空','销售',4,to_date('1980-06-05','yyyy-mm-dd'),800,null,1);
insert into m values (2,'猪八戒','销售',4,to_date('1981-0101','yyyy-mm-dd'),1600,600,1);
insert into m values (3,'沙僧','销售',4,to_date('1981-08-07','yyyy-mm-dd'),1250,500,1);
insert into m values (4,'唐僧','销售经理',8,to_date('1981-05-07','yyyy-mm-dd'),2985,null,1);
insert into m values (5,'刘备','项目经理',null,to_date('1981-09-28','yyyy-mm-dd'),1250,3333,3);
insert into m values (6,'关羽','程序员',5,to_date('1981-05-01','yyyy-mm-dd'),2500,null,3);
insert into m values (7,'张飞','程序员',5,to_date('1981-06-09','yyyy-mm-dd'),2860,null,3);
insert into m values (8,'观音','CEO',null,to_date('1981-11-17','yyyy-mm-dd'),9999,null,1);
insert into m values (9,'白骨精','人事',8,to_date('1981-09-08','yyyy-mm-dd'),1500,0,2);
insert into m values (10,'蜘蛛精','人事',8,to_date('1981-12-03','yyyy-mm-dd'),950,null,2);
insert into m values (11,'黑熊精','市场',8,to_date('1981-12-03','yyyy-mm-dd'),3000,null,2);

create table n (id number,name varchar2(100),loc varchar2(100))
insert into n values(1,'神仙','天庭');
insert into n values(2,'妖怪','盘丝洞');
insert into n values(3,'普通人','北京');
insert into n values(4,'赛亚人','外星球');

select * from m
select * from n

commit

1.查询工资大于等于3000的员工姓名和工资(sal)
select name,sal from m where sal>3000
2.查询1号部门的员工姓名和工作
select name,job from m where nid=1
3.查询薪资大于等于3000的员工姓名和薪资(sal+comm)
select name,(sal+nvl(comm,0)) from m where (sal+nvl(comm,0))>3000
4.查询2号部门的员工姓名和工作
select name,job from m where nid=2
5.查询奖金等于3333的员工姓名,工资和工作
select name,sal,job from m where comm=3333
6.查询1号部门工资大于2000的员工信息
select * from m where nid=1
and sal>2000
7.查询3号部门或工资等于9999的员工信息
select * from m where sal=9999 or nid=3
8.查询出CEO和项目经理的名字
select name from m where job='CEO' or job='项目经理'
9.查询工资为2985,1500和9999的员工信息
select * from m where sal in(2985,1500,9999)
10.查询工资不等于2985,1500,9999的员工信息
select * from m where sal not in(2985,1500,9999)
11.查询工资在1000到2000之间的员工信息
select * from m where sal between 1000 and 2000
12.查询工资在1000到2000以外的员工信息
select * from m where sal not between 1000 and 2000
13.查询有领导的员工姓名和领导id
select name,mana from m where mana is not null
14.查询没有领导的员工姓名
select name from m where mana is null
15.查询员工表中出现了哪几种不同的工作
select distinct job from m
16.查询姓孙的员工姓名
select name from m where name like'孙%'
17.查询名字最后一个字是精的员工信息
select * from m where name like'%精'
18.查询工作中包含销售的员工信息
select * from m where job like'%销售%'
19.查询工作中第二个字是售的员工信息
select * from m where job like'_售%'

20.查询名字中包含僧的员工并且工资高于2000的员工信息
select * from m where name like'%僧%' and sal>2000

21.查询1号和2号部门中工作以市开头的员工信息
select * from m where (job like'市%' and nid=1) or (job like'市%' and nid=2)
22.查询所有员工的姓名和工资 按照工资升序排序
select name,sal from m order by sal asc
23.查询所有员工的姓名和工资 按照工资降序排序
select name,sal from m order by sal desc

24.查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序
select name,sal,nid from m order by nid desc,sal asc
25.查询员工表中3号部门工资高于1500的员工信息
select * from m where sal>1500 and nid=3
26.查询2号部门员工或者没有领导的员工信息
select * from m where nid=2 or mana is null 
27.查询有领导的员工姓名,工资按照工资降序排序
select * from m where mana is not null order by sal desc
28.查询2号和3号部门的员工姓名和入职日期time 按照入职日期降序排序
select name,time from m where nid in(2,3) order by time desc
29.查询名字中包含僧和包含精的员工姓名
select name from m where name like'%精%' or name like'%僧%'
30.查询工资高于2000的工作有哪几种?
select distinct job from m where sal>2000
31.查询工资最高的前三个员工
select * from (select * from  m order by sal desc
) where rownum <=3
32.查询员工表按照id排序, 第2页的5条数据
select * from (select rownum l,m.* from m where rownum <=10
) where l>=5
33.查询员工表按照id排序, 第3页的4条数据
select * from (select rownum l,m.* from m where rownum <=12
) where l>=9
34.查询3号部门工资最低的员工姓名和工资
select name,sal from m where nid=3 and sal=(select min(sal) from m where nid=3
) 
35.查询工作不是人事的员工中工资降序第二页的3条数据

select * from (select rownum l,m.* from (select * from m where job<>'人事'
) m where rownum<=6) where l>=4
36.查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)
select name,sal,(sal*5) 年终奖 from m
37.给3号部门所有员工涨薪5块钱
update m set sal=sal+5 where nid=3
38.查询没有领导的员工和3号部门的员工,工资降序取前三条

select * from (select * from m where nid=3 or mana is null order by sal desc
) where rownum<=3
39.查询2号部门的最高工资
select max(sal) from m where nid=2
40.查询有领导的员工中工资在1000到2000之间的人数
select count(*) from m where mana is not null and sal between 1000 and 2000
41.查询3号部门的工资总和
select sum(sal) from m where nid=3
42.查询程序员和销售的总人数
select count(*) from m where job ='程序员' or job='销售'
43.查询1号部门有领导的员工的平均工资
select avg(sal) from m where nid=1 and mana is not null
44.查询1号部门的最低工资和最高工资
select max(sal),min(sal) from m where nid=1
45.查询和销售相关的工作人数
select count(*) from m where job like'%销售%'
46.查询工资不是1500和3000的员工人数
select count(*) from m where sal not between 1500 and 3000
47.查询1号部门出现了哪几种工作
select distinct job from  m where nid=1
48.查询每个部门的平均工资
select nid,avg(sal) from m group by nid
49.查询每种工作的人数
select job,count(job) from m group  by job
50.查询每个部门的最高工资
select nid,max(sal) from m group by nid
51.查询每种工作的最低工资
select job,min(sal) from m group by job

52.查询每个部门的工资总和
select nid,sum(sal) from m  group by nid
53.查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
select nid,sum(sal) from m where mana is not null  group by nid having sum(sal)>5400
 

第二篇

create table t(
deptno number primary key, -- 部门编号
dname varchar2(14) , -- 部门名字
loc varchar2(13) --地址
);

insert into t values(10,'财务部','北京');
insert into t values(20,'研发部','上海');
insert into t values(30,'销售部','广州');
insert into t values(40,'行政部','深圳');

create table y(
empno number primary key, -- 员工编号
ename varchar2(100), --– 员工姓名 -
job varchar2(90), --– 岗位
mgr number, --– 直接领导编号
hiredate date, --– 雇佣日期，入职日期
sal number, --– 薪水/工资
comm number, --– 提成/奖金
deptno number not null --– 部门编号
);
 

insert into y values(7369,'刘一','职员',7902,to_date('1980-12-17','yyyy-mm-dd'),800,null,20);
insert into y values(7499,'陈二','推销员',7698,to_date('1981-02-20','yyyy-mm-dd'),1600,300,30);
insert into y values(7521,'张三','推销员',7698,to_date('1981-02-22','yyyy-mm-dd'),1250,500,30);
insert into y values(7566,'李四','经理',7839,to_date('1981-04-02','yyyy-mm-dd'),2975,null,20);
insert into y values(7654,'王五','推销员',7698,to_date('1981-09-28','yyyy-mm-dd'),1250,1400,30);
insert into y values(7698,'赵六','经理',7839,to_date('1981-05-01','yyyy-mm-dd'),2850,null,30);
insert into y values(7782,'孙七','经理',7839,to_date('1981-06-09','yyyy-mm-dd'),2450,null,10);
insert into y values(7788,'周八','分析师',7566,to_date('1987-06-13','yyyy-mm-dd'),3000,null,20);
insert into y values(7839,'吴九','总裁',null,to_date('1981-11-17','yyyy-mm-dd'),5000,null,10);
insert into y values(7844,'郑十','推销员',7698,to_date('1981-09-08','yyyy-mm-dd'),1500,0,30);
insert into y values(7876,'郭十一','职员',7788,to_date('1987-06-13','yyyy-mm-dd'),1100,null,20);
insert into y values(7900,'钱多多','职员',7698,to_date('1981-12-03','yyyy-mm-dd'),950,null,30);
insert into y values(7902,'大锦鲤','分析师',7566,to_date('1981-12-03','yyyy-mm-dd'),3000,null,20);
insert into y values(7934,'木有钱','职员',7782,to_date('1983-01-23','yyyy-mm-dd'),1300,null,10);

select * from t
select * from y
commit
1．列出至少有一个员工的所有部门。
select deptno from y group by deptno having count(deptno)>=1
2．列出薪金比"刘一"多的所有员工。(薪金=工资+奖金)
select * from y where sal+nvl(comm,0)>(select sal+nvl(comm,0) from y where ename='刘一'
)
3．列出所有员工的姓名及其直接上级的姓名。
select b.ename 员工,a.ename 领导 from y a,y b where a.empno=b.mgr
4．列出受雇日期早于其直接上级的所有员工。
select b.ename 员工 from y a,y b where a.empno=b.mgr and a.hiredate<b.hiredate

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select * from t left join y on t.deptno=y.deptno
6．列出所有job为“职员”的姓名及其部门名称。
select ename,dname from y,t where t.deptno=y.deptno and job='职员'
7．列出最低薪金大于1500的各种工作。
select a.job from (select job,min(sal) k from y group by job 
) a where a.k>1500
8．列出在部门 “销售部” 工作的员工的姓名，假定不知道销售部的部门编号。
select ename from y where deptno =(select deptno from t where dname='销售部'
)
9．列出薪金高于公司平均薪金的所有员工。
select * from y where sal>(select avg(sal) from y
)
10．列出与"周八"从事相同工作的所有员工。
select ename from y where job=(select job from y where ename='周八'
) and ename<>'周八'
11．列出薪金等于部门30中员工的薪金的姓名和薪金。(不能是本人)
 
select b.a,b.b from (select empno a,sal+nvl(comm,0) b from y where deptno=30
) a ,(select empno a,sal+nvl(comm,0) b from y
) b where a.a<>b.a and a.b=b.b
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal+nvl(comm,0) from y where sal+nvl(comm,0)>(select max(sal+nvl(comm,0)) from y where deptno=30 
)
13．列出在每个部门工作的员工数量、平均工资。
select deptno,count(deptno),avg(sal) from y group by deptno
14．列出所有员工的姓名、部门名称和工资。
select ename,dname,sal from y,t where y.deptno=t.deptno
15．列出各种工作的最低工资。
select job,min(sal) from y group by job
16．列出各个部门的’经理’的最低薪金。
select deptno,min(sal+nvl(comm,0)) from y where job='经理' group by deptno
17. 显示出薪水最高人的职位。
select job from y where sal=(select max(sal) from y  
)
18. 查出y表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,ename,sal from y where sal>=3000
19. 查询出所有薪水在’陈二’之上的所有人员信息。
select * from y where sal>(select sal from y where ename='陈二'
)
20.查询出y表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水
select empno,ename,sal from y where deptno=20 and  sal>2000

21.查询出y表中所有的工作种类（无重复）
select distinct job from y
22.查询出所有奖金（comm）字段不为空的人员的所有信息。
select * from y where comm is null
23.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
select * from y where sal between 800 and 2500 
select * from y where sal >=800 and sal<=2500
24.查询出最早工作的那个人的名字、入职时间和薪水。
select ename,hiredate,sal from y where hiredate=(select min(hiredate) from y 
)
25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
select * from y where empno=7521 or empno=7900 or empno=7782
select * from y where empno in(7521,7900,7782)
26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select * from y where ename like'%张%' and sal>1000
27.查询出名字第三个汉字是“多”的所有员工信息。
select * from y where ename like'__多' 
28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select * from y order by sal asc,hiredate desc

第三篇

create table d(
student_id varchar222(255) primary key,
student_name varchar222(255) not null,
student_birthday date not null,
student_sex varchar222(255) not null
);

create table f(
student_id varchar22(255),
course_id varchar22(255),
grade number not null
); 

create table g(
    course_id varchar22(255) primary key,
    course_name varchar22(255),
    teacher_id varchar22(255) not null
);

create table h(
teacher_id varchar2(255) primary key,
teacher_name varchar2(255)
); 

insert into d values('0001' , '猴子' , to_date('1989-01-01','yyyy-mm-dd') , '男');
insert into d values('0002' , '猴子' , to_date('1990-12-21','yyyy-mm-dd') , '女');
insert into d values('0003' , '马云' , to_date('1991-12-21','yyyy-mm-dd') , '男');
insert into d values('0004' , '王思聪' , to_date('1990-05-20','yyyy-mm-dd') , '男');

insert into f values('0001' , '0001' , 80);
insert into f values('0001' , '0002' , 90);
insert into f values('0001' , '0003' , 99);
insert into f values('0002' , '0002' , 60);
insert into f values('0002' , '0003' , 80);
insert into f values('0003' , '0001' , 80);
insert into f values('0003' , '0002' , 80);
insert into f values('0003' , '0003' , 80);

insert into g values('0001' , '语文' , '0002');
insert into g values('0002' , '数学' , '0001');
insert into g values('0003' , '英语' , '0003');

insert into h values('0001' , '孟扎扎');
insert into h values('0002' , '马化腾');
insert into h values('0003' , null);  --这里的教师姓名是空值（null）
insert into h values('0004' , '');      --这里的教师姓名是空字符串（''）

select * from d 
select * from f
select * from g
select * from h

1.查询姓“猴”的学生名单
select * from d where student_name like'猴%'
2.查询姓名中最后一个字是“猴”的学生名单
select * from d where student_name like'%猴'
3.查询姓名中带“猴”的学生名单
select * from d where student_name like'%猴%'

4.查询姓“孟”老师的个数
select count(*) from h where teacher_name like'孟%'
5.查询课程编号为 0002 的总成绩
select sum(grade) from f where course_id=0002
6.查询选了课程的学生人数
select count(*) from (select distinct student_id from f
) 
7.查询各科成绩最高和最低的分
select course_id,max(grade),min(grade) from  f group by course_id
8.查询每门课程被选修的学生数
select course_id,count(course_id) from f group by course_id
9.查询男生、女生人数
select student_sex,count(student_sex) from d group by student_sex
10.查询平均成绩大于70分学生的学号和平均成绩
select student_id,avg(grade) from f group by student_id having avg(grade)>70
11.查询至少选修两门课程的学生学号
select student_id from f group by student_id having count(course_id)>=2
12.查询同名同性学生名单并统计同名人数
select student_name,count(*) from d
 group by student_name having count(*)>1
13.查询不及格的课程并按课程号从大到小排列 
select course_name from g,f where g.course_id=f.course_id and  grade<60 order by f.course_id desc
14.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select student_id,avg(grade) from f group by student_id order by 
avg(grade) asc,student_id desc
15.检索课程编号为 0004 且分数小于60的学生学号，结果按分数降序排列
select student_id from f where course_id =0004 and grade<60 order by grade desc
16.统计每门课程的学生选修人数(超过2人的课程才统计)
select course_id,count(course_id) from f group by course_id having count(course_id)>2 
17.查询学生的总成绩并进行排名(倒序)
select student_id,sum(grade) from f group by  student_id order by sum(grade) desc
18.查询平均成绩大于60分的学生的学号和平均成绩
select student_id,avg(grade) from f group by student_id having avg(grade)>60

第四篇

create table ab (id number,name varchar2(100),age number,sex varchar2(50))
insert into ab values(1000,'周孝勇',16,'男');
insert into ab values(1001,'林晓华',12,'男');
insert into ab values(1002,'张小红',12,'女');
insert into ab values(1003,'李小军',14,'女');
insert into ab values(1004,'李大军',14,'男');
insert into ab values(1005,'曾小贤',14,'男');
insert into ab values(1006,'张伟',15,'男');
insert into ab values(1007,'吕小布',15,'男');
insert into ab values(1008,'徐晓美',15,'女');
insert into ab values(1009,'张美玉',16,'女');
 
create table ac (id number,name varchar2(50),sex varchar2(10),age number)
insert into ac values(1,'魏平','男',35);
insert into ac values(2,'林春燕','女',28);
insert into ac values(3,'蒋思山','男',36);
insert into ac values(4,'曹初曼','女',37);
insert into ac values(5,'徐怜南','女',34);
insert into ac values(6,'段水珂','女',39);
insert into ac values(7,'杨步美','女',30);
insert into ac values(8,'李广','男',58);
insert into ac values(9,'张绣','男',21);
insert into ac values(10,'石镇厢','男',66);

create table ad (bid number,name varchar2(100),cid number)
insert into ad values (301,'高等数学',1);
insert into ad values (302,'线性代数',2);
insert into ad values (303,'概率论',3);
insert into ad values (304,'计算机基础',4);
insert into ad values (305,'计算机组成原理',9);
insert into ad values (306,'软件工程',5);
insert into ad values (307,'数据库',6);
insert into ad values (308,'c语言',7);
insert into ad values (309,'Java基础',8);
insert into ad values (310,'Javaweb',10);
insert into ad values (311,'c++',1);
insert into ad values (312,'单片机',2);
insert into ad values (313,'数字逻辑',3);
insert into ad values (314,'数字电路',4);
insert into ad values (315,'操作系统',5);
insert into ad values (316,'编辑原理',6);
insert into ad values (317,'HTML',7);
insert into ad values (318,'篮球',8);

create table af (bid number,cid number,score number)
insert into af values (1000,301,98);
insert into af values (1001,302,97);
insert into af values (1002,303,82);
insert into af values (1003,304,88);
insert into af values (1004,305,70);
insert into af values (1005,306,54);
insert into af values (1006,307,62);
insert into af values (1007,308,93);
insert into af values (1008,309,98);
insert into af values (1009,310,89);
insert into af values (1000,311,54);
insert into af values (1001,312,97);
insert into af values (1002,313,99);
insert into af values (1003,314,66);
insert into af values (1004,315,95);

commit

（1）查询学生表所有数据
select * from ab
（2）查询姓“李”学生
select * from ab where name like'李%'
（3）查询男性教师人数
select sex,count(*) from ac where sex='男' group by sex
（4）查询各科成绩最高和最低的分，并以课程ID，课程最高分，课程最低分形式显示
select cid,max(score),min(score) from af group by cid
（5）查询各科成绩最高和最低的分，并以课程ID，课程名称，授课老师，最高分，最低分形式显示。
select a.*,b.n,b.m from (select cid a,max(score) b,min(score) c from af group by cid
) a left join  (select ad.bid k,ad.name n,ac.name m from ad,ac where ad.cid=ac.id
) b on a.a=b.k
（6）查询学过“魏平老师”所教的所有课的同学的学号、姓名

select id,name from ab where id in(select bid from af where cid in(select bid from ad where cid =(select id from ac where name='魏平'
)))
（7）查询没学过"林春燕"老师所教的课的学生的学号、姓名
select id,name from ab where id not in(select bid from af where cid in(select bid from ad where cid =(select id from ac where name='林春燕'
)))
（8）查询学过课程ID为‘302’和‘303’的学生的学号、姓名
select id,name from ab where id in(select bid from af where cid in (302,303)
)
（9）查询某门课程成绩小于等于60分的学生的学号、姓名
select id,name from ab where id in(select bid from af where score<=60
)
（10）查询至少有一门课与学号为“1001”的学生所学课程相同的学生的学号和姓名

select id,name from ab where id in(select bid from af where bid<>1001 and cid in(select cid from af where bid=1001
))
（11）查询学过学号为1003同学所有课程的同学的学号

select * from (select bid from af group by bid having count(*)=(select count(*) from af where bid=1003
) ) a where a.bid in(select bid from af where cid in(select cid from af where bid=1003
)) and a.bid<>1003

（12）查询不同老师所教不同课程平均分从高到低显示
select ac.name,ad.name,avg(score) from af,ad,ac where af.cid=ad.bid and ad.cid=ac.id group by ac.name,ad.name
（13）查询选修“段水珂”老师所授课程的学生中成绩最高的学生姓名，课程名及其成绩

select ab.name,af.score,ad.name from af,ab,ad where ab.id=af.bid and af.cid=ad.bid and  af.cid in(select bid from ad where cid=(select id from ac where name='段水珂'
))  and score=(select max(score) from af where cid in(select bid from ad where cid=(select id from ac where name='段水珂'
)))