《补充作业答案.doc》由会员分享,可在线阅读,更多相关《补充作业答案.doc(5页珍藏版)》请在金锄头文库上搜索。
1、学籍管理系统数据库中的表。表Student_course,Student,Course,Tearch_course和Teacher。各个表的具体结构如下:表1 student表(学生信息表)字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号snamechar8NOT NULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系spnochar8NULL专业代码(外键)classnochar4NULL班级号entimesmalldate4NULL学生
2、入校时间homevarchar40NULL学生家庭住址telvarchar40NULL学生联系电话表2 course表(课程信息表)字段名称类 型宽 度允许空值主 键说 明cnochar10NOT NULL是课程编号spnochar8NULL专业代码(外键)cnamechar20NOT NULL课程名称ctnotinyint1NULL课程类型编号(外键)experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分表3 student_course表(学生选课成绩表)
3、字段名称类 型宽 度允许空值主 键说 明snochar8NOT NULL是学生学号tcidsmallint2NOT NULL是上课编号scoretinyint1NULL学生成绩表4 teacher表(教师信息表)字段名称类 型宽 度允许空值主 键说 明tnochar8NOT NULL是教师编号tnamechar8NOT NULL教师姓名sexchar2NULL教师性别birthdaysmalldate4NULL教师出生日期dnochar6NULL教师所在院系pnotinyint1NULL教师职务或职称编号homevarchar40NULL教师家庭住址zipcodechar6NULL邮政编码te
4、lvarchar40NULL联系电话emailvarchar40NULL电子邮件表5 teacher_course表(教师上课课表)字段名称类 型宽 度允许空值主 键说 明tcidsmallint2NOT NULL是上课编号tnochar8NULL教师编号(外键)spnochar8NULL专业代码(外键)classnochar4NULL班级号cnochar10NOT NULL课程编号(外键)semesterchar6NULL学期schoolyearchar10NULL学年classtimevarchar40NULL上课时间classroomvarchar40NULL上课地点weektimeti
5、nyint1NULL每周课时数1 简单查询操作对EDUC数据库实现以下查询:(1)求计算机系的学生学号和姓名;select sno,snamefrom studentwhere sdept=IS(2)求选修C1 课程的学生学号和成绩,并要求对查询结果按成绩的降序排列,如果成绩相同则按学号的升序排列;select sno,scorefrom student_coursewhere cno=101ORDER BY score DESC,sno(3)求选修课程C1 且成绩在8090 之间的学生学号和成绩,并将成绩乘以系数0.75 输出;select sno,score*0.75from studen
6、t_coursewhere cno=101 AND (score=80)(4)求计算机系和数学系的姓张的学生的信息;SELECT *FROM student where (sdept=CS OR sdept=MS )AND sname LIKE 张%(5)求缺少了成绩的学生的学号和课程号。SELECT sno,cnoFROM student_course where score IS NULL(6)将成绩大于90分的学生成绩存入永久成绩表;将成绩小于90存入临时成绩表中。SELECT score into prescoreFROM student_course where score 90SE
7、LECT score into #posscoreFROM student_course where score 90 and student_course.sno=student.sno(4)查询每一门课的间接先行课。SELECT o,second.spnoFROM course first, course secondWHERE first.spno= o and second.spno is not NULL3子查询操作,在数据库EDUC中实现查询:(1)求选修了高等数学的学生学号和姓名;SELECT sno,sname from studentwhere sno in (SELECT
8、sno from student_coursewhere cno in ( SELECT cnofrom coursewhere cname=高等数学)SELECT student.sno,sname from student,course,student_coursewhere student.sno=student_course.sno AND student_o=oAND ame=高等数学(2)求C1 课程的成绩高于张三的学生学号和成绩;SELECT sno,scorefrom student_coursewhere cno = 101 AND score SOME(SELECT sco
9、refrom student_coursewhere cno =101 and sno in(SELECT snofrom studentwhere sname=张楠)(3)求其他系中比计算机系某一学生年龄小的学生信息(即求其它系中年龄小于计算机系年龄最大者的学生);some(any)均可以SELECT *FROM studentwhere sdept != CS and sagesome(select max(sage)from student where sdept=CS) (4)求其他系中比计算机系学生年龄都小的学生信息;SELECT *FROM studentwhere sdept !
10、= CS and sagesome(select min(sage)from student where sdept=CS) (5)求选修了C2 课程的学生姓名;select snamefrom student where sno in (select sno from student_course where cno=102)(6)求没有选修C2 课程的学生姓名;select snamefrom student where sno not in (select sno from student_course where cno=102)(7)查询选修了全部课程的学生的姓名;select sn
11、amefrom student where sno in (select sno from student_course where cno is not null)(8)求至少选修了学号为“1103”的学生所选修的全部课程的学生学号和姓名。select sno,snamefrom student where sno in (select snofrom student_course where cno in (select cnofrom student_coursewhere sno= 1103 )4使用子句的查询操作,在数据库EDUC中实现查询:(1)求学生的总人数。SELECT count(sno)FROM studen(2)求选修了课程的学生人数。SELECT count(distinct sno)FROM student_course(3)求课程的课程号和选修该课程的人数。SELECT cno,count(sno)FROM student_courseGroup by cno(4)求选修课超过3 门课