Oracle最新练习题

**

第一阶段题目：

**

以下练习题是根据初始化到数据库中的表执行的，请务必先执行如下建表语句。

```sql
create table s1_student( sno varchar2(10) primary key, sname varchar2(30), sbirthday varchar2(30), ssex varchar2(10),sclass varchar2(10) ); 
create table s1_teacher(  tno varchar2(10) primary key, tname varchar2(30),tsex varchar2(10),tbirthday varchar2(30),prof varchar2(30),depart varchar2(30) );  
create table s1_course( cno varchar2(10), cname varchar2(30),  tno varchar2(10) ); 
create table s1_score( sno varchar2(10), cno varchar2(10),  degree number );
create table s1_grade( low number, upp number,  rank varchar2(2) );

```sql
/*******初始化学生表s1_student******/ 
insert into s1_student values ('108', '曾华', '1997/9/1', '男', '95033');
insert into s1_student values ('105', '匡明', '1995/10/2', '男', '95031');
insert into s1_student values ('107', '王丽', '1996/1/23', '女', '95033');
insert into s1_student values ('101', '李军', '1996/2/20', '男', '95033');
insert into s1_student values ('109', '王芳', '1995/2/10', '女', '95031');
insert into s1_student values ('103', '陆君', '1994/6/3', '男', '95031');
commit; 

/*******初始化教师表s1_teacher*******/ 
insert into s1_teacher values ('804', '李诚','男','1979-12-2','副教授','计算机系'); 
insert into s1_teacher values ('856', '张旭','男','1985-3-12','讲师','电子工程系'); 
insert into s1_teacher values ('825', '王萍','女','1989-5-2','助教','计算机系'); 
insert into s1_teacher values ('831', '刘冰','女','1988-8-2','助教','电子工程系'); 
commit; 

/*******初始化课程表s1_course********/ 
insert into s1_course values ('3-105', '计算机导论', '825');
insert into s1_course values ('3-245', '操作系统', '804');
insert into s1_course values ('6-166', '数据电路', '856');
insert into s1_course values ('9-888', '高等数学', '800');
commit; 

/********初始化成绩表s1_score*********/
insert into s1_score values ('103', '3-245', 86);
insert into s1_score values ('105', '3-245', 75);
insert into s1_score values ('109', '3-245', 68);
insert into s1_score values ('103', '3-105', 92);
insert into s1_score values ('105', '3-105', 88);
insert into s1_score values ('109', '3-105', 76);
insert into s1_score values ('101', '3-105', 64);
insert into s1_score values ('107', '3-105', 91);
insert into s1_score values ('108', '3-105', 78);
insert into s1_score values ('101', '6-166', 85);
insert into s1_score values ('107', '6-106', 79);
insert into s1_score values ('108', '6-166', 81);
commit;  

/********初始化成绩分类表s1_grade*********/
insert into s1_grade values ('90', '100', 'A');
insert into s1_grade values ('80', '89', 'B');
insert into s1_grade values ('70', '79', 'C');
insert into s1_grade values ('60', '69', 'D');
insert into s1_grade values ('0', '59', 'E');
commit;

1.查询s1_student表中的所有记录的Sname、Ssex和Class列.

select sname,ssex,sclass from s1_student;

2.查询教师所有的单位即不重复的Depart列.

select distinct depart from s1_teacher;

3.查询s1_student表的所有记录.

select * from s1_student;

4.查询s1_score表中成绩在60到80之间的所有记录.

select * from s1_score where degree between 60 and 80;

5.查询s1_score表中成绩为85，86或88的记录.

select * from s1_score where degree in (85,86,88);

6.查询s1_student表中“95031”班或性别为“女”的同学记录.

select * from s1_student where sclass='95031' or ssex='女';

7.以Class降序查询s1_student表的所有记录.

select * from s1_student order by sclass desc;

8.以Cno升序、Degree降序查询s1_score表的所有记录。

select * from s1_score order by cno,degree desc;

9.查询“95031”班的学生人数。

select count(sno) from s1_student where sclass='95031';

10.查询s1_score表中的最高分的学生学号和课程号。

select sno,cno from s1_score where degree =(select max(degree) from s1_score);

11.查询‘3-105’号课程的平均分。

select avg(degree) from s1_score where cno='3-105';

12.查询s1_score表中至少有5名学生选修的并以3开头的课程的平均分数。

select cno,avg(degree) from s1_score where cno like '3%' group by cno 
having count(sno)>5;

13.查询最低分大于70，最高分小于90的Sno列。

select sno from s1_score group by sno having min(degree)>70 and max(degree)<90;

14.查询所有学生的Sname、Cno和Degree列。

select sname,cno ,degree from s1_student st,s1_score sc where st.sno=sc.sno(+);

15.查询所有学生的Sno、Cname和Degree列。

select sno,cname,degree from s1_score sc,s1_course co where sc.cno(+)=co.cno;

16.查询所有学生的Sname、Cname和Degree列。