Oracle 例题 三篇

我发现有些题目可以有多种不同的理解，导致答案会有多个，

我只写了我自己理解的的答案，也没有写多个。

第一篇

create table qq (
  course_id varchar2(20) default null, --课程id
  course_name varchar2(20) default null, --课程名
  tea_id varchar2(20) default null  --任课老师 
);

insert  into qq values('01','语文','1003');
insert  into qq values('02','数学','1001');
insert  into qq values('03','英语','1004');
insert  into qq values('04','体育','1002');
insert  into qq values('05','音乐','1002');

create table ww(
  stu_id varchar2(20) default null, --学生id
  course_id varchar2(20) default null, --课程id
  course number default null --成绩
)

insert  into ww values 
('001','01',94);
insert  into ww values 
('002','01',74);
insert  into ww values 
('004','01',85);
insert  into ww values 
('005','01',64);
insert  into ww values 
('006','01',71);
insert  into ww values 
('007','01',48);
insert  into ww values 
('008','01',56);
insert  into ww values 
('009','01',75);
insert  into ww values 
('010','01',84);
insert  into ww values 
('011','01',61);
insert  into ww values 
('012','01',44);
insert  into ww values 
('013','01',47);
insert  into ww values 
('014','01',81);
insert  into ww values 
('015','01',90);
insert  into ww values 
('016','01',71);
insert  into ww values 
('017','01',58);
insert  into ww values 
('018','01',38);
insert  into ww values 
('019','01',46);
insert  into ww values 
('020','01',89);
insert  into ww values 
('001','02',63);
insert  into ww values 
('002','02',84);
insert  into ww values 
('004','02',93);
insert  into ww values 
('005','02',44);
insert  into ww values 
('006','02',90);
insert  into ww values 
('007','02',55);
insert  into ww values 
('008','02',34);
insert  into ww values 
('009','02',78);
insert  into ww values 
('010','02',68);
insert  into ww values 
('011','02',49);
insert  into ww values 
('012','02',74);
insert  into ww values 
('013','02',35);
insert  into ww values 
('014','02',39);
insert  into ww values 
('015','02',48);
insert  into ww values 
('016','02',89);
insert  into ww values 
('017','02',34);
insert  into ww values 
('018','02',58);
insert  into ww values 
('019','02',39);
insert  into ww values 
('020','02',59);
insert  into ww values 
('001','03',79);
insert  into ww values 
('002','03',87);
insert  into ww values 
('004','03',89);
insert  into ww values 
('005','03',99);
insert  into ww values 
('006','03',59);
insert  into ww values 
('007','03',70);
insert  into ww values 
('008','03',39);
insert  into ww values 
('009','03',60);
insert  into ww values 
('010','03',47);
insert  into ww values 
('011','03',70);
insert  into ww values 
('012','03',62);
insert  into ww values 
('013','03',93);
insert  into ww values 
('014','03',32);
insert  into ww values 
('015','03',84);
insert  into ww values 
('016','03',71);
insert  into ww values 
('017','03',55);
insert  into ww values 
('018','03',49);
insert  into ww values 
('019','03',93);
insert  into ww values 
('020','03',81);
insert  into ww values 
('001','04',54);
insert  into ww values 
('002','04',100);
insert  into ww values 
('004','04',59);
insert  into ww values 
('005','04',85);
insert  into ww values 
('007','04',63);
insert  into ww values 
('009','04',79);
insert  into ww values 
('010','04',34);
insert  into ww values 
('013','04',69);
insert  into ww values 
('014','04',40);
insert  into ww values 
('016','04',94);
insert  into ww values 
('017','04',34);
insert  into ww values 
('020','04',50);
insert  into ww values 
('005','05',85);
insert  into ww values 
('007','05',63);
insert  into ww values 
('009','05',79);
insert  into ww values 
('015','05',59);
insert  into ww values 
('018','05',87);

create table ee (
  stu_id varchar(20) default null, --学生
  stu_name varchar(20) default null, --学生姓名
  birthday date default null, --出生日期
  sex varchar(20) default null --性别
)

insert  into ee values 
('001','彭于晏',to_date('1995-05-16','yyyy-mm-dd'),'男');
insert  into ee values 
('002','胡歌',to_date('1994-03-20','yyyy-mm-dd'),'男');
insert  into ee values 
('003','周杰伦',to_date('1995-04-30','yyyy-mm-dd'),'男');
insert  into ee values 
('004','刘德华',to_date('1998-08-28','yyyy-mm-dd'),'男');
insert  into ee values 
('005','唐国强',to_date('1993-09-10','yyyy-mm-dd'),'男');
insert  into ee values 
('006','陈道明',to_date('1992-11-12','yyyy-mm-dd'),'男');
insert  into ee values 
('007','陈坤',to_date('1999-04-09','yyyy-mm-dd'),'男');
insert  into ee values 
('008','吴京',to_date('1994-02-06','yyyy-mm-dd'),'男');
insert  into ee values 
('009','郭德纲',to_date('1992-12-05','yyyy-mm-dd'),'男');
insert  into ee values 
('010','于谦',to_date('1998-08-23','yyyy-mm-dd'),'男');
insert  into ee values 
('011','潘长江',to_date('1995-05-27','yyyy-mm-dd'),'男');
insert  into ee values 
('012','杨紫',to_date('1996-12-21','yyyy-mm-dd'),'女');
insert  into ee values 
('013','蒋欣',to_date('1997-11-08','yyyy-mm-dd'),'女');
insert  into ee values 
('014','赵丽颖',to_date('1990-01-09','yyyy-mm-dd'),'女');
insert  into ee values 
('015','刘亦菲',to_date('1993-01-14','yyyy-mm-dd'),'女');
insert  into ee values 
('016','周冬雨',to_date('1990-06-18','yyyy-mm-dd'),'女');
insert  into ee values 
('017','范冰冰',to_date('1992-07-04','yyyy-mm-dd'),'女');
insert  into ee values 
('018','李冰冰',to_date('1993-09-24','yyyy-mm-dd'),'女');
insert  into ee values 
('019','邓紫棋',to_date('1994-08-31','yyyy-mm-dd'),'女');
insert  into ee values 
('020','宋丹丹',to_date('1991-03-01','yyyy-mm-dd'),'女');

create table rr (
  tea_id varchar(20) default null, --'老师id'
  tea_name varchar(20) default null --'老师姓名'
);

insert  into rr values 
('1001','张高数');
insert  into rr values 
('1002','李体音');
insert  into rr values 
('1003','王子文');
insert  into rr values 
('1004','刘丽英');

select * from qq 
select * from ww
select * from ee
select * from rr

1. 查询姓名中带"冰"的学生名单
select stu_name from ee where stu_name like'%冰%'
2. 查询姓"王"的老师的个数
select count(*) from rr where tea_name like'王%'
3. 检索课程编号为"04"且分数小于60的学生学号，结果按分数降序排列
select stu_id from ww where course_id=04 and course<60 order by course desc
4. 查询数学成绩不及格的学生和其对应的成绩
select stu_name,course from ww,ee where ww.stu_id=ee.stu_id
 and course_id=(select course_id from qq where course_name='数学'
) and course<60
5. 查询课程编号为"02"的总成绩
select sum(course) from ww where course_id=02
6. 查询参加考试的学生个数
select count(*) from (select distinct stu_id from ww
)
7. 查询各科成绩最高和最低的分数，以如下的形式显示：课程号，最高分，最低分
select course_id,max(course),min(course) from ww group by course_id
8. 查询每门课程有多少学生参加了考试(有考试成绩)
select course_id,count(course_id) from ww group by course_id
9. 查询男生、女生人数
select sex,count(sex) from ee group by sex
10. 查询平均成绩大于60分学生的学号和平均成绩
select stu_id,avg(course) from ww group by stu_id having avg(course)>60 
11. 查询至少选修两门课程的学生学号
select stu_id from ww group by stu_id having count(stu_id)>=2
12. 查询同姓(假设每个学生姓名的第一个字为姓)的学生名单并统计同姓人数
select substr(stu_name,1,1),count(*) from ee group by substr(stu_name,1,1) having count(*)>1

13. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select course_id,avg(course) from ww group by course_id order by avg(course) asc,course_id desc
14. 统计参加考试人数大于等于15的学科
select course_id from ww group by course_id having count(course_id)>=15
15. 查询学生的总成绩并进行排名
select stu_id,sum(course) from ww group by stu_id order by sum(course) desc 
16. 查询平均成绩大于60分的学生的学号和平均成绩
select stu_id,avg(course) from ww group by stu_id having avg(course)>60
17. 查询一共参加两门课程且其一门为语文课程的学生的id和姓名
select st.stu_id,st.stu_name,count(*) countscore from ee st
        left join ww sc
        on st.stu_id = sc.stu_id
        right join 
         (select * from ww where course_id = (select course_id from qq where course_name = '语文')) b
        on sc.stu_id = b.stu_id
        group by st.stu_id,st.stu_name
        having count(*) = 2

18. 查询所有课程成绩小于60分的学生的学号、姓名

select stu_id,stu_name from ee where stu_id in(select a.a from (select stu_id a,count(course_id) b from ww group by stu_id
) a,(select stu_id a,count(course_id) b from ww where course<60 group by stu_id
) b where a.a=b.a and a.b=b.b)
19. 查询出只选修了两门课程的全部学生的学号和姓名
select ee.stu_id,ee.stu_name from ee,ww where ee.stu_id=ww.stu_id group by ee.stu_id,ee.stu_name having count(ww.stu_id)=2

20. 查询两门以上不及格课程的同学的学号 及其平均成绩

select stu_id,avg(course) from ww where stu_id in(select stu_id  from ww where course<60 group by stu_id having count(course_id) >2
) group by stu_id

21. 查询所有学生的学号、姓名、选课数、总成绩
select a.*,ee.stu_name from (select stu_id,count(stu_id),sum(course) from ww group by stu_id
) a left join ee on a.stu_id=ee.stu_id  

22. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.*,ee.stu_name from (select stu_id,avg(course) from ww group by stu_id having avg(course)>85
) a left join ee on a.stu_id=ee.stu_id 
23. 查询学生的选课情况:学号，姓名，课程号 ，课程名称
select ww.stu_id,stu_name,ww.course_id,course_name from ww,ee,qq where 
ww.stu_id=ee.stu_id and ww.course_id=qq.course_id 

24. 查询出每门课程的及格人数和不及格人数

select a.*,b.及格 from (select course_id 课程,count(course_id) 不及格 from ww where course<60 group by course_id
) a left join (select course_id 课程,count(course_id) 及格 from ww where course>=60 group by course_id
) b on a.课程=b.课程

25. 检索"01"课程分数小于60，按分数降序排列的学生信息
 
select ee.* from ww,ee where ww.stu_id=ee.stu_id and course<60 and course_id=01 order by course desc

26. 查询任何一门课程成绩在70分以上的学生的姓名、课程名称和分数
select stu_name,course_name,course from qq,ww,ee where ww.stu_id=ee.stu_id and ww.course_id=qq.course_id and course>70
27. 查询两门及以上不及格课程的同学的学号，姓名及其平均成绩

select a.*,ee.stu_name from (select stu_id,avg(course) from ww where stu_id in(select stu_id  from ww where course<60 group by stu_id having count(course_id) >2
) group by stu_id) a left join ee on a.stu_id=ee.stu_id
28. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.stu_id,a.course_id,a.course from ww a,ww b where  a.course_id<>b.course_id and a.course=b.course
 

第二篇

create table u (
  id number not null primary key, -- '主键id',
  name varchar2(255) default null, -- '名字 默认为空',
  age number default null, -- '年龄 默认为空',
  salary number(10) default null, -- '工资 默认为空',
  leader number default null ,-- '领导 默认为空',
  menpai varchar2(255) default null --'门派 默认为空',
)

insert into u values(1,'张3丰',25,10000,0,'武当');
insert into u values(2,'张无忌',25,8000,0,'华山');
insert into u values(3,'岳不群',25,6500,0,'嵩山');
insert into u values(4,'东方不败',25,12000,0,'日月神教');
insert into u values(5,'令狐葱',25,4000,0,'武当');
insert into u values(6,'景天',25,2000,0,'华山');
insert into u values(7,'龙葵',25,10000,0,'嵩山');
insert into u values(8,'茂茂',25,10000,0,'日月神教');
insert into u values(9,'白豆腐',25,6500,0,'长白山');
insert into u values(10,'Farke',25,10000,0,'武当');
insert into u values(11,'Alex',25,10000,0,'Java');

create table i(
d_id number not null  primary key, -- '主、外键',
d_name varchar2(255) not null,  -- '部门名字',
adress varchar2(255) not null -- '地址',
)

insert into i values(1,'武当','河南');
insert into i values(2,'华山','湖北');
insert into i values(3,'嵩山','深圳');
insert into i values(4,'日月神教','广州');
insert into i values(5,'长白山','乌鲁木齐');
insert into i values(6,'雇佣兵','新疆');

select * from u 
select * from i

commit
1.update set 根据id修改age
update u set age=27 where id=2;
update u set age=28 where id=3;
update u set age=23 where id=4;
update u set age=29 where id=5;
update u set age=30 where id=6;
update u set age=28 where id=7;
update u set age=28 where id=8;
update u set age=26 where id=9;
update u set age=23 where id=10;

2.查询所有人员信息
select * from u
3.只查询人员的姓名和年龄
select name,age from u
4.查询年龄为28岁的有哪些人员
select * from u where age=28
5.查询60岁以下的人员有哪些人员
select * from u where age<60
6.查询27岁以上并且工资大于8000的人员有哪些
select * from u where salary>8000 and age>27
7.查询姓[张]的人员有哪些
select * from u where name like'张%'
8.查询哪些人员属于 武当/华山/嵩山
select * from u where menpai in('武当','华山','嵩山')
9.查询工资在 5000-8900 的人员有哪些
select * from u where salary between 5000 and 8900
10.查询所有人员,要求按工资倒序排列
select * from u order by salary desc 
11.查询令狐葱的领导人是谁（号码）（as领导）
select leader as 领导 from u where name='令狐葱'
12.查询人员表中最高工资是多少
select max(salary) from u
13.查询人员表中最低工资是多少
select min(salary) from u

14.查询所有人员的平均工资是多少
select avg(salary) from u

15.查询所有人员的工资总和是多少
select sum(salary) from u

16.查询目前有多少个人员
select count(*) from u
17.查询当前武林中有哪些门派 （去重显示）
select distinct menpai from u 
18.查询 武当派 最高工资是谁
select name from u where menpai='武当' and salary=(select max(salary) from u where menpai='武当'
)
19.查询各门派的平均工资是多少
select menpai,avg(salary) from u group by menpai
20.查询当前武林中有哪些门派的平均工资大于8000 并按工资倒序排列
select menpai,avg(salary) from u group by menpai having avg(salary)>8000
order by avg(salary) desc

21.查询当前人员表的中的第3条数据到第7条数据
select * from (select rownum a,u.* from u where rownum<=7
)  where a>=3
22.查询哪些门派下没有弟子
select d_name from i where d_name not in(select  menpai from u
)
23.查询 各门派 的 工资总和 按正序排列
select menpai,sum(salary) from u group by menpai order by sum(salary)
24.将武当派 张3丰 修改为 张三丰
update u set name='张三丰' where name='张3丰'
25.将所有门派员工工资上调10%,但不包括Alex.
update u set salary = salary+salary*0.1 where  name !='Alex';

26.查看哪些门派已登记地理位置.
select DISTINCT adress,d_name from u,i where menpai=d_name
27.查询所有人员门派的位置信息,不存在位置信息则null
select a.*,i.adress from (select distinct menpai from u)
 a left join i on a.menpai=i.d_name
28.在湖北省内的门派中的人员有哪些
select u.* from u,i where menpai=d_name and adress='湖北'
29.在河南省内门派中的工资小于5000,年龄大于20岁的人员有哪些
select u.* from u,i where menpai=d_name and adress='河南' and salary<5000 and age>20

30.删除工资重复的人员,请保留年龄最大的一个人
delete from u where id in( (select id from u where salary in(select salary from u group by salary having count(*)>1)
  and age not in (select max(age) from u group by salary having count(*)>1)));

第三篇

create table j (
  goods_id number primary key ,   --产品的编号
  goods_name varchar2(120) not null,  --产品的名字
  cat_id number  not null,     --档目
  brand_id number  not null,   
  goods_sn varchar2(150) not null ,
  goods_number number  not null,  -- 该店商品的数量
  shop_price number  not null ,   -- 该店商品的价格
  market_price number  not null, --市场价格
  click_count number  not null    --点击量
) ;
 
insert into j values(1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9);
insert into j values(4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0);
insert into j values(3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3);
insert into j values(5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3);
insert into j values(6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0);
insert into j values(7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0);
insert into j values(8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10);
insert into j values(9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20);
insert into j values(10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11);
insert into j values(11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0);
insert into j values(12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13);
insert into j values(13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13);
insert into j values(14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6);
insert into j values(15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8);
insert into j values(16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3);
insert into j values(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2);
insert into j values(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0);
insert into j values(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7);
insert into j values(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14);
insert into j values(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4);
insert into j values(22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16);
insert into j values(23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17);
insert into j values(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35);
insert into j values(25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0);
insert into j values(26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0);
insert into j values(27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0);
insert into j values(28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0);
insert into j values(29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0);
insert into j values(30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1);
insert into j values(31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5);
insert into j values(32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);

create table k (
    cat_id number primary key, --档目
    cat_name varchar2(90) not null,  --名称
    parent_id  number 
);

insert into k values(1,'手机类型',0);
insert into k values(2,'CDMA手机',1);
insert into k values(3,'GSM手机',1);
insert into k values(4,'3G手机',1);
insert into k values(5,'双模手机',1);
insert into k values(6,'手机配件',0);
insert into k values(7,'充电器',6);
insert into k values(8,'耳机',6);
insert into k values(9,'电池',6);
insert into k values(11,'读卡器和内存卡',6);
insert into k values(12,'充值卡',0);
insert into k values(13,'小灵通/固话充值卡',12);
insert into k values(14,'移动手机充值卡',12);
insert into k values(15,'联通手机充值卡',12);

select * from j
select * from k

commit;

1:查询id为32的商品
select * from j where goods_id=32
2:不属第3栏目的所有商品（k中id为3）(栏目是cat_id)
select * from j where cat_id<>3
3:本店价格高于3000元的商品
select * from j where shop_price>3000
4:本店价格低于或等于100元的商品
select * from j where shop_price<=100
5:取出第4栏目或第11栏目的商品
select * from j where cat_id in(4,11)
6:取出100<=市场价格<=500的商品
select * from j where market_price between 100 and 500
7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)
select * from j where cat_id not in(3,11)
select * from j where cat_id<>3 and cat_id<>11
8:取出市场价格大于100且小于300,或者该店价格大于4000且小于5000的商品
select * from j where shop_price between 100 and 300 or market_price between 4000 and 5000
9:取出第3个栏目下面该店价格<1000或>3000,并且点击量>5的系列商品
select * from j where (shop_price >3000 and click_count>5) or (shop_price <1000 and click_count>5)
10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select * from k where cat_id=1
11:取出名字以"诺基亚"开头的商品
select * from j where goods_name like'诺基亚%'
12:取出名字为"诺基亚nxx"的手机
select * from j where goods_name like'诺基亚n__'
13:取出名字不以"诺基亚"开头的商品
select * from j where goods_name not like'诺基亚%'
14:取出第3个栏目下面该店价格在<1000或者>3000,并且点击量>5 "诺基亚"开头的系列商品
select * from j where  (shop_price >3000 and click_count>5 and goods_name like'诺基亚%') or (shop_price <1000 and click_count>5 and goods_name like'诺基亚%')

15:把goods表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx' (要产品id和改后的名字)
select  goods_id,concat('HTC',substr(goods_name,4))
from j where goods_name like '诺基亚%';
16:这个店积压的货款 (总钱数)
select sum(goods_number*shop_price) from j 
17:查询该店的商品比市场价所节省的价格大于200的产品
select * from j where  market_price-shop_price>200
18:查询该店每个栏目下挤压的货款
select cat_id,sum(goods_number*shop_price) from j group by cat_id
19:查询该店每个栏目下挤压的货款 > 20000
select cat_id,sum(goods_number*shop_price) from j group by cat_id
having sum(goods_number*shop_price)>20000
20:查询出编号为19的商品的栏目名称
select cat_name from k where cat_id =(select cat_id from j where goods_id=19
)