oracle数据库测验,Oracle数据库部分测试题

数据库部分测试题

表结构

student课程，姓名，年龄，性别，系号sc学好，课程号，成绩course课程号，课程名，学时数

数据自定义

1、编写sql语句，查询系号6的同学的学号，姓名，课程号，成绩2、编写sql语句，查询比学号是1同学年龄大3岁的同学的学号，姓名，年龄。3、请用sql语句创建一个视图ST-VIEW，检索选修课程在5门以上的学生的系号，学号，姓名，最低分，最高分，平均分和哦选课程数，其结果要求按照系号，平均分排序(降序)。

创建数据库：

sqlplus /

as sysdba    #进入Oracle

startup             #启动oracle

create

table student (xuehao char(15) ,xingming char(20),nianling int,xingbie

int,xihao int );

create

table sc (xuehao char(15),kechenghao int, chengji int);

create

table course (kechenghao char(15),kechengming char(15), xueshishu int);

insert

into student values('S1','a',15,1,01);

insert

into student values('S2','b',16,2,01);

insert

into student values('S3','c',13,1,05);

insert

into student values('S4','d',14,1,02);

insert

into student values('S5','e',15,2,03);

insert

into student values('S6','f',16,2,03);

insert

into student values('S7','g',16,1,06);

insert

into student values('S8','h',17,1,04);

insert

into student values('S9','i',14,2,06);

insert

into student values('S10','j',13,1,03);

insert

into student values('S11','k',15,2,02);

insert

into sc values('S1',1,69);

insert

into sc values('S1',2,89);

insert

into sc values('S1',3,68);

insert

into sc values('S1',4,88);

insert

into sc values('S1',5,93);

insert

into sc values('S1',6,100);

insert

into sc values('S2',1,79);

insert

into sc values('S2',2,60);

insert

into sc values('S2',5,76);

insert

into sc values('S2',6,70);

insert

into sc values('S3',1,83);

insert

into sc values('S3',2,88);

insert

into sc values('S3',3,67);

insert

into sc values('S3',4,85);

insert

into sc values('S3',5,61);

insert

into sc values('S3',6,91);

insert

into sc values('S4',1,83);

insert

into sc values('S4',2,75);

insert

into sc values('S4',3,82);

insert

into sc values('S4',4,81);

insert

into sc values('S4',5,89);

insert

into sc values('S4',6,74);

insert

into sc values('S5',1,95);

insert

into sc values('S5',2,80);

insert

into sc values('S5',3,69);

insert

into sc values('S5',4,60);

insert

into sc values('S5',5,92);

insert

into sc values('S5',6,91);

insert

into sc values('S6',1,68);

insert

into sc values('S6',2,69);

insert

into sc values('S6',3,92);

insert

into sc values('S6',4,86);

insert

into sc values('S7',3,79);

insert

into sc values('S7',4,67);

insert

into sc values('S7',5,98);

insert

into sc values('S7',6,69);

insert

into sc values('S8',1,72);

insert

into sc values('S8',2,76);

insert

into sc values('S8',3,82);

insert

into sc values('S8',4,92);

insert

into sc values('S9',3,92);

insert

into sc values('S9',4,89);

insert

into sc values('S9',5,77);

insert

into sc values('S9',6,67);;

insert

into sc values('S10',4,86);

insert

into sc values('S10',5,94);

insert

into sc values('S10',6,65);

insert

into sc values('S11',1,99);

insert

into sc values('S11',3,92);

insert

into sc values('S11',3,89);

insert

into course values('1','1a',48);

insert

into course values('2','2b',64);

insert

into course values('3','3c',56);

insert

into course values('4','4d',48);

insert

into course values('5','5f',64);

insert

into course values('6','6e',48);

1、编写sql语句，查询系号6的同学的学号，姓名，课程号，成绩:

select

t1.xuehao,t1.xingming,t2.kechenghao,t2.chengji from student t1,sc t2 where

t1.xuehao=t2.xuehao and xihao=6;

2、编写sql语句，查询比学号是1同学年龄大3岁的同学的学号，姓名，年龄:

select xuehao,xingming,nianling from student where nianling>(select nianling from student where xuehao='S1')+3;

select t1.xuehao,t1.xingming,t1.nianling from student t1 where nianling>(select nianling from student where xuehao='S3')+3;

3、请用sql语句创建一个视图ST-VIEW，检索选修课程在5门以上的学生的系号，学号，姓名，最低分，最高分，平均分和哦选课程数，其结果要求按照系号，平均分排序(降序):

create view st_view as

with a as (select * from  (select xuehao,count(kechenghao) as kechengsu, min(chengji) as min_chengji,max(chengji) as max_chengji,avg(chengji) as avg_chengji from sc group by xuehao) where kechengsu >= 5) select t1.xihao,t1.xuehao,t1.xingming,a.min_chengji,a.max_chengji,a.avg_chengji,a.kechengsu from student t1,a where a.xuehao = t1.xuehao order by 1,6 desc;

4、请用SQL语言检索至少选修了“学号为S3的学生选修的全部课程”的学生的学号和姓名：

with a as (select xuehao,count(kechenghao) as kechengsu from sc group by xuehao) select a.xuehao,t1.xingming from a, student t1  where a.xuehao=t1.xuehao and kechengsu=(select kechengsu from a where xuehao='S3');