广东医学院数据库课程设计一.docx
《广东医学院数据库课程设计一.docx》由会员分享，可在线阅读，更多相关《广东医学院数据库课程设计一.docx（21页珍藏版）》请在冰点文库上搜索。
广东医学院数据库课程设计一
课程设计
（一）
学校的教务数据库设计如下：
学生表1T_STUDENT
字段名称
字段类型
字段含义
STUNO
Varchar2（16）
学号（主键）
STUNAME
Varchar2（16）
姓名
STUSEX
Varchar2（6）
性别
STUBIR
DATE
出生日期
课程信息表2T_COURSE
字段名称
字段类型
字段含义
COURSENO
Varchar2（16）
课程号（主键）
COURSENAME
Varchar2（16）
课程名称
TEANO
Varchar2（16）
教师编号（外键）
教师信息表3T_TEACHER
字段名称
字段类型
字段含义
TEANO
Varchar2（16）
教师编号（主键）
TEANAME
Varchar2（16）
课程姓名
TEATITLE
Varchar2（16）
教师职称
考试成绩表4T_SCORE
字段名称
字段类型
字段含义
STUNO
Varchar2（16）
学号
COURSENO
Varchar2（16）
课程编号（外键）
TYPE
Varchar2（6）
考试类型（期中/期末）
SCORE
FLOAT
分数
T_STUDENT
STUNO
STUNAME
STUSEX
STUBIR
S001
郭莉芳
女
1987.2
S002
郑义
男
1988.2
S003
何月
女
1989.3
S004
王群
男
1990.7
….
T_COURSE
COURSENO
TEANAME
TEATITLE
C001
DB
T001
C002
VC++
T002
C003
VB
T009
C004
操作系统
T003
C005
JAVA
T006
……
T_TEACHER
TEANO
COURSENAME
TEANO
T001
梁天
教授
T002
王一
副教授
T009
卢洪
讲师
T003
张楠
副教授
T006
赵军
教授
……
T_SCORE
STUNO
COURSENO
TYPE
SCORE
S001
C001
期中
98
S001
C001
期末
78
S001
C002
期中
68
S001
C002
期末
79
….
创建教务数据库，创建用户，完成以下作业：
1）创建以上几张表，并给表中输入数据;
CREATETABLET_STUDENT（--学生表一
STUNOVARCHAR2（16）PRIMARYKEY,
STUNAMEVARCHAR2（16）,
STUSEXVARCHAR2（6）CHECK（STUSEXIN（'男','女'））,
STUBIRDATE）
CREATETABLET_COURSE（--课程信息表2
COURSENOVARCHAR2（16）PRIMARYKEY,
COURSENAMEVARCHAR2（16）,
TEANOVARCHAR2（16）NOTNULL,
FOREIGNKEY（TEANO）REFERENCEST_TEACHER（TEANO））
CREATETABLET_TEACHER（--教师信息表3
TEANOVARCHAR2（16）PRIMARYKEY,
TEANAMEVARCHAR2（16）,
TEATITLEVARCHAR2（16））
CREATETABLET_SCORE（--考试成绩表4
STUNOVARCHAR2（16）,
COURSENOVARCHAR2（16）NOTNULL,
TYPEVARCHAR2（6）NOTNULLCHECK（TYPEIN（'期中','期末'））,
SCOREFLOAT,
PRIMARYKEY（STUNO,COURSENO,TYPE）,
FOREIGNKEY（COURSENO）REFERENCEST_COURSE（COURSENO），
FOREIGNKEY（STUNO）REFERENCEST_STUDENT（STUNO））
2）查询T_STUDENT中所有的数据;
SELECT*FROMT_STUDENT
3）查询T_STUDENT中所有学生的姓名和性别;
SELECTSTUNAME,STUNO
FROMT_STUDENT
4）将学号和姓名显示，其中，姓名的格式为：
“姓名：
xxx”;//
SELECT'姓名'XXX,STUNAME,STUNO
FROMT_STUDENT
5）为了更好地体现各个学生的考试情况，将T_SCORE中的信息显示，分数显示为与60分的差;
SELECTSTUNO,COURSENO,TYPE,SCORE-60
FROMT_SCORE
6）将T_SCORE中的信息显示，分数显示为与60分的差值，列名为“差值”，如果第一条记录分数为空，会得出来什么结果。
SELECTSTUNO,COURSENO,TYPE,SCORE-60AS"差值"
FROMT_SCORE
（注意：
空值具有特殊性，包括空值的任何算术表达式都等于空）
7）将学号和姓名显示，其中，列名分别显示为“学生”和姓名;
SELECTSTUNOAS"学生",STUNAMEAS"姓名"
FROMT_STUDENT
8）将学号和姓名显示在一个列中，列名显示为：
信息;
SELECTCONCAT（STUNO,STUNAME）AS"信息"
FROMT_STUDENT
9）查询教师的职称种类;
SELECTTITLE
FROMT_TEACHER
10）查询女生的姓名;
SELECTSTUNAME
FROMT_STUDENT
WHERESTUSEX='女'
11）查询课程VB的信息;
SELECT*
FROMT_COURSE
WHERECOURSENAME='VB'
12）显示所有期中考试及格的记录;
SELECT*
FROMT_SCORE
WHERETYPE='期中'ANDSCORE-60>0
13）为了找出考试尖子，需要显示所有期末考试在90-100的考试记录（使用<,>betweenand）;
SELECT*
FROMT_SCORE
WHERETYPE='期末'ANDSCOREBETWEEN'90'AND'100'
14）学校要举行一帮一活动，让高分学生帮助低分学生。
查询90分以上的期末考试记录，以及不及格的期末考试记录;
SELECT*
FROMT_SCORE
WHERETYPE='期末'ANDSCORENOTBETWEEN'60'AND'90'
15）利用BETWEEN谓词显示所有期末考试在65-80的考试记录;
SELECT*
FROMT_SCORE
WHERETYPE='期末'ANDSCOREBETWEEN'65'AND'80
16）使用IN谓词，显示分数是60,70,80的考试记录;
SELECT*
FROMT_SCORE
WHERESCOREIN（'60','70','80'）
17）查询姓李的学生资料;
SELECT*
FROMT_STUDENT
WHERESTUNAMELIKE'李%'
18）查询姓“王”，名字为一个字的学生，并将这类学生的详细信息显示出来;
SELECT*
FROMT_STUDENT
WHERESTUNAMELIKE'王%_'
19）查询性别为空的学生资料;
SELECT*
FROMT_STUDENT
WHERESTUSEX=NULL
20）用升序显示学生S001的所有期末考试成绩;
SELECTSCORE
FROMT_SCORE
WHERESTUNO='S001'ANDTYPE='期末'
ORDERBYSCOREASC
21）用降序显示课程C001的所有期末考试成绩，对于相等的成绩，则按学生编号升序显示;
SELECTSCORE
FROMT_SCORE
WHERECOURSENO='C001'ANDTYPE='期末'
ORDERBYSCOREDESC,STUNODESC
22）查询姓名为“郭莉芳”的考试成绩;
SELECTSCORE
FROMT_SCORE
WHERESTUNOIN
（SELECTSTUNOFROMT_STUDENTWHERESTUNAME='郭莉芳'
）
23）显示各个教师及其讲授课程的详细情况;
SELECTT_TEACHER.TEANAME,T_COURSE.COURSENAME
FROMT_TEACHER,T_COURSE
WHERET_TEACHER.TEANO=T_COURSE.TEANO
24）查询名为“梁天”的教师没有上过的课程;
SELECTT_COURSE.COURSENAME
FROMT_TEACHER,T_COURSE
WHERET_TEACHER.TEANO=T_COURSE.TEANOANDT_TEACHER.TEANAME!
='梁天'
25）课程“大学物理”，有哪些学生选过？
请列出这些学生的姓名;
SELECTT_STUDENT.STUNAME
FROMT_STUDENT,T_COURSE,T_SCORE
WHERET_STUDENT.STUNO=T_SCORE.STUNO
ANDT_SCORE.COURSENO=T_COURSE.COURSENO
ANDT_COURSE.COURSENAME='大学物理'
26）查询学号为”S002”的学生，参加课程“C001”考试的成绩，显示格式为：
期中成绩期末成绩总评成绩;
其中，总评成绩=期中成绩*0.4+期末成绩*0.6
SELECTFIRST.SCOREAS"期中成绩",SECOND.SCOREAS"期末成绩"，（FIRST.SCORE*0.4+SECOND.SCORE*0.6）AS"总评成绩"
FROMT_SCOREFIRST,T_SCORESECOND
WHEREFIRST.STUNO=SECOND.STUNO
ANDFIRST.STUNO='S001'
ANDFIRST.COURSENO='C001'
ANDSECOND.COURSENO='C001'
ANDFIRST.TYPE='期中'
ANDSECOND.TYPE='期末'
27）查询课程“大学物理”是哪一位老师教的，列出其姓名;
SELECTT_TEACHER.TEANAME
FROMT_TEACHER,T_COURSE
WHERET_TEACHER.TEANO=T_COURSE.TEANO
ANDT_COURSE.COURSENAME='大学物理'
28）使用左外连接完成27）;
SELECT教师姓名
fromt_teacherleftjoint_course
ont_teacher.teano=t_course.teanoandt_course.coursename='大学物理';
29）使用右外连接完成27）;
SELECT教师姓名
fromt_courserightjoint_teacher
ont_teacher.teano=t_course.teanoandt_course.coursename='大学物理';
30）查询T_STUDENT内所有人的姓名和性别;
SELECTSTUNAME,STUSEX
FROMT_STUDENT
31）将学号和姓名用下划线连接，显示在一列;
SELECTSTUNAME||'_'||STUNO
FROMT_STUDENT
32）显示教授的所有资料;
SELECTT_TEACHER.*,T_COURSE.COURSENAME
FROMT_TEACHER,T_COURSE
WHERET_TEACHER.TEANO=T_COURSE.TEANO
ANDT_TEACHER.TEATITLE='教授'
33）显示姓张的男生的姓名;
SELECTSTUNAME
FROMT_STUDENT
WHERESTUNAMELIKE'张%'
34）将所有的分数显示为与60分的差值，同时也显示原分数;
SELECTSCORE-60AS"分数差值",SCOREAS"原分数"
FROMT_SCORE
35）查询高级职称以下的教师姓名，高级职称以下为副教授和讲师;
SELECTTEANAME
FROMT_TEACHER
WHERETEATITLEIN（'副教授','讲师'）
36）学校需要请学生对教授的教学作评价，因此需要通知相关学生。
请查询出：
教授所教过的课程，有哪些学生选过？
列出他们的姓名;
SELECTDISTINCTCOURSENAME,STUNAME
FROMT_STUDENT,T_TEACHER,T_SCORE,T_COURSE
WHERET_STUDENT.STUNO=T_SCORE.STUNO
ANDT_SCORE.COURSENO=T_COURSE.COURSENO
ANDT_COURSE.TEANO=T_TEACHER.TEANO
ANDT_TEACHER.TEATITLE='教授'
37）查询郭莉芳的哪些科目期末考试没有及格？
列出这些科目的名称和分数;
SELECTT_COURSE.COURSENAME,T_SCORE.SCORE
FROMT_STUDENT,T_COURSE,T_SCORE
WHERET_STUDENT.STUNO=T_SCORE.STUNO
ANDT_COURSE.COURSENO=T_SCORE.COURSENO
ANDT_STUDENT.STUNAME='郭莉芳'
ANDT_SCORE.SCORE<60
38）统计学生姓名的数量;
SELECTCOUNT（STUNAME）
FROMT_STUDENT
39）查询学校有多少名教师;
SELECTCOUNT（TEANAME）
FROMT_TEACHER
40）查询为“梁天”的教师讲了多少门课;
SELECTCOUNT（COURSENAME）
FROMT_TEACHER,T_COURSE
WHERET_TEACHER.TEANO=T_COURSE.TEANO
ANDT_TEACHER.TEANAME='梁天'
41）查询参加过考试的学生数量;
SELECTCOUNT（DISTINCTSTUNO）
FROMT_SCORE
42）查询郭莉芳选了多少门课;
SELECTCOUNT（COURSENAME）
FROMT_STUDENT,T_COURSE,T_SCORE
WHERET_STUDENT.STUNO=T_SCORE.STUNO
ANDT_SCORE.COURSENO=T_COURSE.COURSENO
ANDT_STUDENT.STUNAME='郭莉芳'
43）查询课程C002的期末考试平均分;
SELECTAVG（SCORE）
FROMT_SCORE
WHERECOURSENO='C002'ANDTYPE='期末'
44）查询课程C003的期中考试总分;
SELECTSUM（SCORE）
FROMT_SCORE
WHERECOURSENO='C003'ANDTYPE='期中'
45）查询学校所有考试记录的总分;
SELECTSUM（SCORE）
FROMT_SCORE
46）查询课程C004的期末考试最高分;
SELECTMAX（SCORE）
FROMT_SCORE
WHERECOURSENO='C004'
47）查询每个教师讲授的课程数量，并将其姓名和课程数量显示出来;
SELECTTEANAME,COUNT（COURSENAME）
FROMT_TEACHER,T_COURSE
WHERET_TEACHER.TEANO=T_COURSE.TEANO
GROUPBYTEANAME
48）查询郭莉芳每门课的平均分，显示课程名称和平均分;
SELECTCOURSENAME,AVG（SCORE）
FROMT_COURSE,T_STUDENT,T_SCORE
WHERET_STUDENT.STUNO=T_SCORE.STUNO
ANDT_SCORE.COURSENO=T_COURSE.COURSENO
ANDT_STUDENT.STUNAME='郭莉芳'
GROUPBYCOURSENAME
49）学校要查询哪门课的授课效果最好，请查询各门课程平均分的最大值;
SELECTMAX（AVG（SCORE））
FROMT_SCORE
GROUPBYCOURSENAME
50）向T_TEACHER表中添加一条记录，教师编号为T009,教师姓名为汤路名，职称为教授;
INSERTINTOT_TEACHER
VALUES（'T009','汤路名','教授'）
51）由于有些学生表现比较优秀，拟将其升级为教师。
向T_TEACHER表中添加一些记录，这些记录为：
平均分75分以上的学生和学号、姓名，他们的职称为助教;
INSERTINTOT_TEACHER（TEANO,TEANAME,TEATITLE）
SELECTSTUNO,STUNAME,'助教'
FROMT_STUDENT
WHERESTUNOIN
（SELECTSTUNO
FROMT_SCORE
GROUPBYSTUNO
HAVINGAVG（SCORE）>75
）
52）删除T_STUDENT表中的女生记录;
DELETEFROMT_STUDENT
WHERESTUSEX='女'
53）删除T_COURSE表中的全部记录;
DELETEFROMT_SCORE
54）删除女生的分数记录;
DELETEFROMT_SCORE
WHERESTUNOIN
（SELECTSTUNOFROMT_STUDENTWHERESTUSEX='女'
）
55）将所有的学生分数增加5分;
UPDATET_SCORE
SETSCORE=SCORE+5
56）将所有的学生分数增加5分，类型变为“正常考试”;
UPDATET_SCORESETSCORE=SCORE+5,TYPE='正常考试'
57）将所有女生的性别变为GIRL;
UPDATET_STUDENT
SETSTUSEX='GIRL'WHERESTUSEX='女'
58）创建一个员工工资表，包含各个员工的编号、姓名、性别、出生年月、工资。
性别为默认“男”。
编号为主键;
CREATETABLESTAFF_WAGE（
STAFFNOVARCHAR2（16）PRIMARYKEY,
STAFFNAMEVARCHAR2（16）,
STAFFSEXCHAR（6）DEFAULT'男',
BIRTHDATE,
WAGENUMBER（6,2））
59）将54创建的员工工资表改名;
ALTERTABLESTAFF_WAGERENAMETOSTAFFWAGE
60）创建一个员工工资表，包含各个员工的编号、姓名、性别、出生年月、工资。
其中，员工的编号来自所有学生的学号，员工的姓名来自所有学生姓名，员工的性别来自学生性别，出生年月来自学生的出生年月，工资为0
（利用：
CREATETABLE表名【列名1（，列名2…..）】
AS
子查询）
CREATETABLEWAGESAMPLE（
STAFFNO,
STAFFNAME,
STAFFSEX,
BIRTH,
WAGE）
ASSELECTSTUNO,STUNAME,STUSEX,STUBIR,0
FROMT_STUDENT
61）在T_TEACHER表中增加性别和出生年月列;
ALTERTABLET_TEACHERADD（TEASEXCHAR（6）,TEABIRBIR）
62）在T_TEACHER表中删除性别和出生年月列;
ALTERTABLET_TEACHERDROPTEASEX,TEABIR
63）在T_TEACHER表中，将TEANAME重命名为“教师姓名”;
ALTERTABLET_TEACHERRENAMECOLUMNTEANAMETO"教师姓名"
64）在T_TEACHER表中，将TEATITLE的数据类型改为VARCHAR2（30）;
ALTERTABLET_TEACHERMODIFYTEATITLEVARCHAR2（30）
65）创建一个员工工资表，包含各个员工的编号、姓名、性别、出生年月、工资。
编号和姓名组合为主键;
CREATETABLESTAFF_WAGE（
STAFFNOVARCHAR2（16）NOTNULL,
STAFFNAMEVARCHAR2（16）NOTNULL,
STAFFSEXCHAR（6）,
BIRTHDATE,
WAGENUMBER（6,2）,
PRIMARYKEY（STAFFNO,STAFFNAME））
66）创建一个员工工资表，包含各个员工的编号、姓名、性别、出生年月、工资。
编号为主键,姓名唯一;
CREATETABLESTAFF_WAGE（
STAFFNOVARCHAR2（16）PRIMARYKEY,
STAFFNAMEVARCHAR2（16）UNIQUE,
STAFFSEXCHAR（6）,
BIRTHDATE,
WAGENUMBER（6,2））
67）将T_STUDENT表中的STUNAME指定为唯一性约束;
ALTERTABLET_STUDENTADDUNIQUE（STUNAME）
68）将65题中指定的唯一性约束删除;
ALTERTABLET_STUDENTDROPUNIQUE（STUNAME）
69）创建一个员工工资表，包含各个员工的编号、姓名、性别、出生年月、工资。
编号为主键,性别只能是男或者女;
CREATETABLESTAFF_WAGE（
STAFFNOVARCHAR2（16）PRIMARYKEY,
STAFFNAMEVARCHAR2（16）,
STAFFSEXCHAR（6）CHECKIN（'男','女'）,
BIRTHDATE,
WAGENUMBER（6,2））
70）T_SCORE中的分数，要求在0-100之间;
ALTERTABLET_SCOREADDCONSTRAINTSCORECHECKCHECK（SCOREIN（0,100））
71）为T_STU
升级会员 