数据库原理与技术简明教程案例题库1

1大连东软信息学院计算机系课程数据库原理与技术简明教程案例题库二○一二2数据库系统案例及习题汇总一、授课选课系统案例及习题1．按如下要求设计数据库授课选课系统有如下的实体、属性及语义描述：每名学生可以学习若干门课程，每门课程可以有若干学生学习，每名学生学完一门课程后能得到一个成绩。每门课程只由一名教师讲授，每名教师只能教授一门课程。学生、课程和教师的信息如下：学生：学号，姓名，性别，年龄，所在系，所属班级课程：课程号，课程名，学分教师：教师号，教师姓名，性别，出生日期，职称，所在系（1）设计该授课系统的E-R图；（2）将该E-R图转换为关系模型结构；学生关系(学号，姓名，性别，年龄，所在系，所属班级)课程关系(课程号，课程名，学分)教师关系(教师号，姓名，性别，出生日期，所在系，职称)学生选课关系(学号，课程号，成绩)教师授课关系(教师号，课程号)学生学号姓名性别年龄所在系课程课程号课程名学分教师选课成绩mn教师号姓名出生日期所在系性别授课11所属班级职称3（3）指出转换结果中每个关系模式的候选码。学生关系的候选码为学号；课程关系的候选码为课程号；教师关系的候选码为教师号；学生选课关系的候选码为学号与课程号；教师授课关系的候选码为教师号与课程号；2．按照以上设计，创建如下关系，并完成相关习题：请根据如下四张表做后面的练习：学生表Student(Sno,Name,Sex,Age,Sdept)教师表Teacher(Tno,Name,Sex,Birthday,Prof,Dept)课程表Course(Cno,CName,Credit，Tno)成绩表Score(Sno,Cno,Grade)2.1按照要求用SQL语句创建下面四张表(1)学生表：表名——Student属性：Sno字符型，最大10个字符表示学生学号Sname字符型，最大8个字符表示学生姓名Sage整型表示学生年龄Ssex字符型，最大2个字符表示学生性别Sdept字符型，长度为20表示所在系约束：主码——Sno非空属性——SnamecreatetableStudent(Snochar(10)primarykey,Snamevarchar(8)notnull,Sageint,Ssexchar(2),Sdeptvarchar(20))(2)教师表：表名：Teacher属性：Tno字符型，最大8个字符表示教师代号TName字符型，最大10个字符表示教师姓名Sex字符型，2个字符表示教师性别Birthday日期时间类型表示出生日期Prof字符型，8个字符表示职称Dept字符型，长度为20表示所在系约束：主码——Cno非空属性——CnameCreatetableTeacher(Tnochar(8)primarykey,Tnamevarchar(10)notnull,4Sexchar(2),Birthdaydatetime,Profchar(8),Deptvarchar(20))(3)课程表：表名：Course属性：Cno字符型，最大5个字符表示课程编号Cname字符型，最大10个字符表示课程名Tno字符型，最大8个字符表示教师代号Credit整型表示学分约束：主码——Tno非空属性——TNameTno——外码createtableCourse(Cnovarchar(5)primarykey,Cnamevarchar(10)notnull,Tnovarchar(8)foreignkey(Tno)referencesTeacher(tno),Creditint)(4)选课表：表名——SC属性：Sno字符型，最大10个字符表示学生学号Cno字符型，最大5个字符表示课程号Grade整型表示成绩约束：主码——Sno，Cno；Sno，Cno均为外码createtableSC(Snovarchar(10),Cnovarchar(5),gradeint,primarykey(Sno,Cno),foreignkey(sno)referencesStudent(Sno),foreignkey(Cno)referencesCourse(Cno))2.2针对上面的四个基本表做如下的练习(1)向student表中插入记录（学号：04101，姓名：张三，性别：男，年龄：18，系别：计算机系）insertintostudentvalues(‘04101’,‘张三’,‘男’,18，‘计算机系’)(2)向student表中插入记录（学号：04102，姓名：李四）insertintostudent(sno,sname)values(‘04102’,‘李四’)5(3)向student表中插入记录（学号：04103，姓名：王五，系别：信管系）insertintostudent(sno,sname,sdept)values(‘04103’，‘王五’,‘信管系’)(4)向student表中插入记录（学号：04104，姓名：赵六，性别：女，年龄：null，系别：null）insertintostudentvlause(‘04104’,‘赵六’,‘女’,null,null)(5)修改student表中学号为04103的学生性别为女updatestudentsetsex=‘女’wheresno=‘04103’(6)修改student表中学号为04104的学生年龄为22，系别为计算机系updatestudentsetsage=22,sdept=‘计算机系’wheresno=‘04104’(7)修改年龄为null的学生年龄为20updatestudentsetsage=20wheresageisnull(8)将计算机系所有学生的年龄设为nullupdatestudentsetsage=nullwheresdept=‘计算机系’(9)删除student表中学号为04104的学生deletefromstudentwheresno=‘04104’(10)删除student表中计算机系的所有男生deletefromstudentwheresdept=‘计算机系’(11)查询全体学生的学号与姓名Selectsno,snamefromstudent(12)查询全体学生的姓名、学号、所在系Selectsname,sno,sdeptfromstudent(13)查询全体学生的详细记录Select*fromstudent(14)查询全体学生的姓名及出生年份Selectsname,year(getdate())-sagefromstudent(15)查询全体学生的姓名、出生年份、所在系，且要求用小写字母表示所有系名selectsname,’yearofbirth’,2005-sage,islower(sdept)fromstudentselectsnameNAME,‘yearofbirth’,BIRTH,2005-sageBIRTHDAY,islower(sdept)DEPARTMENTfromstudent(16)（消除重复行）查询选修了课程的学生学号Selectdistinctsnofromsc(17)查询计算机系所有学生Select*fromstudentwheresdept=’计算机系’(18)查询所有年龄在20岁以下的学生姓名及其年龄Selectsname,sagefromstudentwheresage20(19)查询考试成绩不及格的学生学号Selectsnofromscwheregrade60(20)查询年龄在20－23岁之间的学生的姓名、年龄和系别Selectsname,sage,sdeptfromstudentwheresagebetween20and236(21)查询年龄不在20－23岁之间的学生姓名、年龄和系别Selectsname,sage,sdeptfromstudentwheresagenotbetween20and23(22)查询信息系、数学系和计算机系的学生的姓名和性别Selectsname,sexfromstudentwheresdeptin(‘信息系’,‘数学系’,‘计算机系’)(23)查询既不是信息系、数学系，也不是计算机系的学生的姓名和性别Selectsname,sexfromstudentwheresdeptnotin(‘信息系’,‘数学系’,‘计算机系’)(24)查询学号为95001的学生的详细情况（可用like做）Select*fromstudentwheresnolike‘95001’或：Select*fromstudentwheresno=‘95001’(25)查询所有姓刘的学生的姓名、学号和性别Selectsname,sno,sexfromstudentwheresnamelike‘刘%’(26)查询姓李且全名为三个汉字的学生的姓名Selectsnamefromstudentwheresnamelike‘李__’(27)查询名字中第二个字为阳字的学生的姓名和学号Selectsname,snofromstudentwheresnamelike‘_阳_’(28)查询所有不姓刘的学生姓名Selectsnamefromstudentwheresnamenotlike‘刘%’(29)查询DB_Design课程的课程号和学分selectCno,CcreditfromCoursewhereCnamelike‘DB\_Design’ESCAPE’\’(30)查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况select*fromcoursewhereCnamelike‘DB\_%i__’ESCAPE’\’(31)查询缺少成绩的学生的学号和相应的课程号Selectsno,cnofromscwheregradeisnull(32)查询所有有成绩的学生的学号和课程号Selectsno,cnofromscwheregradeisnotnull(33)查询计算机系年龄20岁以下的学生姓名Selectsnamefromstudentwheresdept=’计算机系’andsage20(34)查询选修了c03号课程的学生的学号及成绩，查询结果按分数的降序排列。Selectsno,gradefromscwherecno=’c03’orderbygradedesc(35)查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。Select*fromstudentorderbysdeptasc,sagedesc(36)查询学生的总人数Selectcount(*)fromstudent(37)查询选修了课程的学生人数Selectcount(distinctsno)fromsc(38)计算c01号课程的学生平均成绩Selectavg(grade)fromscwherecno=’c01’(39)查询选修c01号课程的学生最高分数和最低分数。Selectmax(grade),min(grade)fromscwherecno=’c01’(40)求各个课程号及相应的选课人数。7Selectcno,count(sno)fromscgroupbycno(41)查询每名学生的选课门数和平均成绩。Selectsno,count(cno),avg(grade)fromscgroupbysno(42)查询选修了3门以上课程的学生学号。Selectsnofromscgroupbysnohavingcount(*)3(43)查询选课门数等于或大于4门的学生的平均成绩和选课门数Selectsno,avg(grade),count(cno)fromscgroupbysnohavingcount(cno)=4(44)查询每个学生及其选修课程的情况Selectsno,count(cno),avg(grade),max(grade),min(grade),sum(grade)fromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cnogroupbysno(