oracle 例题 两篇

我又来咯，老样子，两篇

第一篇

create table a1 (classid number primary key,
classname varchar2(20));

insert into a1 values(1,'G1T01');
insert into a1 values(2,'G1T02');
insert into a1 values(3,'G1T03');
insert into a1 values(4,'G1T04');
insert into a1 values(5,'G1T05');
insert into a1 values(6,'G1T06');
insert into a1 values(7,'G1T07');

create table b1
(
studentid varchar2(20) primary key,
studentname varchar2(20),
studentage number,
studentsex varchar2(10),
studentaddress varchar2(50),
classid number references a1(classid)
)

insert into b1 values('2010001','Jack',21,'男','湖北襄樊',1);
insert into b1 values('2010002','Scott',22,'男','湖北武汉',2);
insert into b1 values('2010003','Lucy',23,'女','湖北武汉',3);
insert into b1 values('2010004','Alan',19,'女','湖北襄樊',4);
insert into b1 values('2010005','Bill',20,'男','湖北襄樊',5);
insert into b1 values('2010006','Bob',21,'男','湖北宜昌',6);
insert into b1 values('2010007','Colin',22,'女','湖北襄樊',6);
insert into b1 values('2010008','Fred',19,'男','湖北宜昌',5);
insert into b1 values('2010009','Hunk',19,'男','湖北武汉',4);
insert into b1 values('2010010','Jim',18,'男','湖北襄樊',3);

create table c0
(
studentid varchar2(20) references b1(studentid),
score number
)

insert into c0 values('2010001',90);
insert into c0 values('2010002',80);
insert into c0 values('2010003',70);
insert into c0 values('2010004',60);
insert into c0 values('2010005',75);
insert into c0 values('2010006',85);

select * from a1
select * from b1
select * from c0

commit;

1.查询出学生的编号，姓名，成绩
select c0.*,b1.studentname from c0 left join b1 on c0.studentid=b1.studentid
2.查询参加过考试的学生信息
select * from b1 where studentid in(select distinct studentid from c0
)
3.查询出学生的编号、姓名、所在班级名称、成绩
select b1.studentid,b1.studentname,a1.classname,c0.score from a1,b1,c0 where a1.classid=b1.classid and b1.studentid=c0.studentid
4.查询出年龄大于19岁的学生编号、姓名、成绩
select b1.studentid,b1.studentname,c0.score from b1,c0 where b1.studentid=c0.studentid and studentage>19
5.查询出姓名中包含有c的学生编号、姓名、成绩
select b1.studentid,b1.studentname,c0.score from b1,c0 where b1.studentid=c0.studentid and studentname like'%c%'
6.查询出成绩大于80分的学生编号、姓名、班级名称
select b1.studentid,b1.studentname,a1.classname from a1,b1,c0 where a1.classid=b1.classid and b1.studentid=c0.studentid and c0.score>80 
7.查询出所有学生的信息和成绩信息
select * from b1 left join c0 on b1.studentid=c0.studentid 
8.查询出每个班的学生的成绩的平均分，最高分，最低分
select a1.classname,avg(score),max(score),min(score) from a1,b1,c0 where a1.classid=b1.classid and b1.studentid=c0.studentid group by classname
9.查询显示出班级的平均分大于80的班级名称、平均分，并按照平均分降序显示
select a1.classname,avg(score) from a1,b1,c0 where a1.classid=b1.classid and b1.studentid=c0.studentid group by classname having avg(score)>80 order by avg(score) desc

10.查询出和Jim住在同一个地方的学生的基本信息
select * from b1 where studentname<>'Jim' and studentaddress =(select studentaddress from b1 where studentname='Jim'
)
11.查询出班级编号大于3的学生基本信息
select * from b1 where classid>3
12.查询出班级编号大于3的学生的平均分
select avg(score) from c0 where studentid in(select studentid from b1 where classid>3
)
13.查询出班级编号大于3的男生的学生信息
select * from b1 where classid>3 and studentsex='男'

14.查询男、女生的平均成绩、最高分、最低分
select studentsex,avg(score),max(score),min(score) from b1,c0 where b1.studentid=c0.studentid group by studentsex
15.将参加过考试的学生的年龄更改为20

update b1 set studentage=20 where studentid in(select distinct studentid from c0)
16.查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）
select classname,avg(score) from a1,b1,c0 where a1.classid=b1.classid and b1.studentid=c0.studentid group by classname

17.删除姓名包含“c”字符的学生成绩
delete from c0 where studentid in(select studentid from b1 where studentname like'%c%' 
)
18.查询出G1T07班学生的编号、姓名、班级名称、成绩

select a.*,c0.score from (select b1.studentid,b1.studentname,a1.classname  from a1,b1 where b1.classid=a1.classid and a1.classname='G1T01'
) a left join c0 on a.studentid=c0.studentid
19.查询出年龄在20-25之间的学生的编号、姓名、年龄、成绩
select a.*,c0.score from (select studentid,studentname,studentage from b1 where studentage between 20 and 25
) a left join c0 on a.studentid=c0.studentid
20.查询出成绩最高的学生的编号、姓名、成绩、所在班级名称

select b1.studentid,b1.studentname,a1.classname,c0.score from a1,b1,c0 where a1.classid=b1.classid and b1.studentid=c0.studentid and b1.studentid=(select studentid from c0 where score=(select max(score) from c0
))

21.查询统计出每个班的平均分、显示平均分超过70分的班级名称、并按照降序显示信息
select a1.classname,avg(score) from a1,b1,c0 where a1.classid=b1.classid and b1.studentid=c0.studentid group by a1.classname having avg(score)>70 order by avg(score) desc

第二篇

create table aa1(  
num number primary key ,  
name varchar2(30) not null,  
addr varchar2(30) not null unique,  -- 地址
zip varchar2(30) not null,    -- 雇员编号
tel varchar2(30) not null,    -- 电话
email varchar2(30) unique,  
depno number not null,  
birth date not null,  
sex varchar2 (5)
);

create table bb1(  
depno number primary key ,  
depName varchar2(30) unique not null,  
remark varchar2(50)
);

create table cc1(  
num number primary key ,
inCome number not null,  --工资
outCome number not null  --奖金
);

insert into aa1 values 
(1,'王林','武汉大学','430074','87598405',null,2,to_date('1985-2-1','yyyy-mm-dd'),'男');
insert into aa1 values 
(2,'王芳 ','华中科大','430073','62534231',null,1,to_date('1966-3-28','yyyy-mm-dd'),'男');
insert into aa1 values 
(3,'张晓','武汉理工大','430072 ','87596985',null,1,to_date('1972-12-9','yyyy-mm-dd'),'男');
insert into aa1 values 
(4,'王小燕','武汉交大','430071','85743261','lili@sina.com',1 ,to_date('1950-7-30','yyyy-mm-dd'),'女');
insert into aa1 values 
(5,'李华',' 华中农大','430070','87569865',null,5,to_date('1962-10-18','yyyy-mm-dd'),'男');
insert into aa1 values 
(6,'李明','华中师大','430075','85362143','zhujun@sina.com ',5,to_date('1955-09-28','yyyy-mm-dd'),'男');
insert into aa1 values 
(7,'田丽','中南财大','430076','85693265','zgming@sohu.com',3,to_date('1968-08-10','yyyy-mm-dd'),'女');
insert into aa1 values 
(8,'吴天','武汉电力','430077','36985612 ','zjamg@china.com',5,to_date('1964-10-01','yyyy-mm-dd'),'男');
insert into aa1 values 
(9,'刘备',' 武汉邮科院','430078','69865231',null,3,to_date('1967-04-02','yyyy-mm-dd'),'男');
insert into aa1 values 
(10,'赵云','学府家园','430071','68592312 ',null,4,to_date('1968-11-18','yyyy-mm-dd'),'男');
insert into aa1 values 
(11,'貂禅','湖北工大',' 430074','65987654', null,4,to_date('1959-09-03','yyyy-mm-dd'),'女');

insert into bb1 values(1,'财务部',null);
insert into bb1 values(2,'人力资源部',null);
insert into bb1 values(3,'经理办公室',null);
insert into bb1 values(4,'研发部',null);
insert into bb1 values(5,'市场部',null);

insert into cc1 values (1,2100.7,123.09);
insert into cc1 values (2,1582.62,88.03);
insert into cc1 values (3,2569.88,185.65);
insert into cc1 values (4,1987.01 ,79.58);
insert into cc1 values (5,2066.15 ,108.0);
insert into cc1 values (6,2980.7, 210.2);
insert into cc1 values (7,3259.98 ,281.52);
insert into cc1 values (8,2860.0,198);
insert into cc1 values (9,2347.68,180);
insert into cc1 values (10,2531.98,199.08);
insert into cc1 values (11,2240.0,121.0);

select * from aa1
select * from bb1
select * from cc1

commit;

1. 查询每个雇员的所有记录
select * from aa1
2. 查询前5个会员的所有记录
select * from aa1 where rownum<=5 
3. 查询每个雇员的地址和电话
select addr,tel from aa1
4. 查询num为001的雇员地址和电话
select addr,tel from aa1 where num=001
5. 查询表aa1表中女雇员的地址和电话，使用AS子句将结果列中各列的标题分别指定为地址、电话
select addr as 地址,tel as 电话 from aa1 where sex='女'
6. 计算每个雇员的实际收入
select num,income+outcome from cc1 
7. 找出所有姓王的雇员的部门号（部门号不能重复显示）；
select distinct depno from aa1 where name like'王%'
8. 找出所有收入在2000－3000元之间的雇员编号
select zip from aa1,cc1 where aa1.num=cc1.num and (income+outcome) between 2000 and 3000
9. 查找在财务部工作的雇员情况
select * from aa1 where depno=(select depno from bb1 where depname='财务部'
)
10. 查找在财务部且年龄不低于研发部任一个雇员年龄的雇员的姓名

select name from aa1 where birth<(select min(birth) from aa1 where depno=(select depno from bb1 where depname='研发部'
)) and depno=(select depno from bb1 where depname='财务部'
)

11. 查找比所有财务部雇员收入都高的雇员的姓名

select name from aa1 where num in(select num from cc1 where income+outcome >(select max(income+outcome) from cc1,bb1,aa1 where aa1.num=cc1.num and
 aa1.depno=bb1.depno and aa1.depno =(select depno from bb1 where depname='财务部'
)))

12. 查找每个雇员的情况及薪水情况
select * from aa1 left join cc1 on aa1.num=cc1.num

13. 查找财务部收入在2200元以上的雇员姓名及其薪水详细情况
select name,cc1.* from aa1,cc1 where aa1.num=cc1.num and 
income+outcome>2200 and depno=(select depno from bb1 where depname='财务部'
)

14. 求财务部雇员的平均实际收入
select avg(income+outcome) from aa1,cc1 where aa1.num=cc1.num and 
 depno=(select depno from bb1 where depname='财务部'
)
15. 求财务部雇员的总人数
select count(*) from aa1,cc1 where aa1.num=cc1.num and 
 depno=(select depno from bb1 where depname='财务部'
)
16. 求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数

select a.*,bb1.depname from (select depno a,count(depno) b from aa1 group by depno 
) a left join bb1 on bb1.depno=a.a

17. 求部门的平均薪水大于2500的部门信息（要求显示，部门号、部门名称和平均工资）

select a.*,bb1.depname from (select depno,avg(income+outcome) from aa1,cc1 where aa1.num=cc1.num group by depno having avg(income+outcome)>2500
) a left join bb1 on a.depno=bb1.depno