MySQL数据库原理及应用答案实训题.docx

《MySQL数据库原理及应用答案实训题.docx》由会员分享，可在线阅读，更多相关《MySQL数据库原理及应用答案实训题.docx（13页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、MySQL数据库原理及应用答案实训题课后习题名称：学生选课管理系统（stuCourse）/*-项目3-*/*为学生选课管理系统创建名为“stucourse”的数据库。 */create database stucourse;/*为学生选课管理系统数据库（stucourse）创建表，表结构如下，字段名参考表3-26表3-30。（1）创建学生表，学生student (学号，姓名，性别，年龄，系别)。（2）创建教师表，教师teacher (教师编号，姓名，职称，工资，系别，课程号)。（3）创建课程表，课程courseinfo (课程编号，课程名称，教材编号，测试时间，系别)。（4）创建选课表，选课s

2、course (学号，分数，课程编号，教师编号)。（5）创建教材表，教材bookinfo (教材编号，教材名称，出版社，价格，数量) */ create table student( sid char(10) not null primary key, sname char(8) not null, sex char(2) null, age int null, dept varchar(20) null)create table teacher( tid char(10) not null primary key, tname char(8) not null, tpassword char

3、(8) not null, title varchar(20) , salary float, dept varchar(20), cid char(10)create table courseinfo( cid char(10) primary key not null, cname varchar(20), cbook char(10), ctest datetime, dept varchar(10) create table bookinfo ( bid char(10) primary key, bname varchar(30), bpublish varchar(30), bpr

4、ice double, quantity int)create table scourse( sid char(10) not null , score float null, cid char(5) null, tid char(10)/*向上述表中插入数据*/insert into student values(1001, 宋江, 男, 25, 计算机系),(3002, 张明, 男, 23, 生物系 ),(1003, 李小鹏, 男, 26, 计算机系 ),(1004, 郑冬, 女, 25, 计算机系 ),(4005, 李小红, 女, 27, 工商管理 ),(5006, 赵紫月, 女, 24

5、, 外语系); insert into teacher values(3102, 李明, 初级, 2500, 计算机系, C1),(3108, 黄小明, 初级, 4000, 生物系, C3 ),(4105, 张小红, 中级, 3500, 工商管理, C2),(5102, 宋力月, 高级, 3500, 物理系, C4 ),(3106, 赵明阳, 初级, 1500, 地理系, C2),(7108, 张丽, 高级, 3500, 生物系, C3 ),(9103, 王彬, 高级, 3500, 计算机系, C1),(7101, 王力号, 初级, 1800, 生物系, C1 ); insert into c

6、ourseinfo values(C1, 计算机基础, b1231, 2009-4-6, 计算机系),(C2, 工商管理基础, b1232, 2009-7-16, 工商管理),(C3, 生物科学, b1233, 2010-3-6, 生物系),(C4, 大学物理, b1234, 2009-4-26, 物理系 ),(C5, 数据库原理, b1235, 2010-2-6, 计算机系); insert into bookinfo values(b1231, Image Processing, 人民出版社, 34.56, 8 ),(b1232, Signal Processing, 清华出版社, 51.

7、75, 10),(b1233, Digital Signal Processing, 邮电出版社, 48.5, 11 ),(b1234, The Logic Circuit, 北大出版社, 49.2, 40),(b1235, SQL Techniques, 邮电出版社, 65.4, 20 ); insert into scourse values(1001, 87, C1, 3102),(1001, 77, C2, 4105 ),(1001, 63, C3, 3108 ),(1001, 56, C4, 3108),(3002, 78, C3, 3108),(3002, 78, C4, 5102

8、 ),(1003, 89, C1, 9103),(1004, 56, C2, 3106),(4005, 87, C4, 5102 ),(5006, null, C1, 7101);/*在教师表student中，将所有学生年龄增加1岁。*/update student set age=age+1;/*在教师表teacher中，将教师“黄小明”的称职由“初级”改为“中级”。*/update teacher set title=中级 where tname=黄小明;/*在教师表teacher中，删除张小红教师的记录。*/delete from teacher where tname=张小红/*在教材

9、表bookinfo中，删除“邮电出版社”的图书*/delete from bookinfo where bname=邮电出版社;/*-项目4-*/*查询全体学生的学号、姓名和年龄。*/select sid,sname,age from student/*查询选修了课程的学生号。*/select distinct sid from scourse/*查询选修课程号C3的学号和成绩。*/select sid,scorefrom scourse where cid=C3/*查询成绩高于85分的学生的学号、课程号和成绩。*/select sid,cid,score from scourse where

10、 score85/*查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。*/select sid,cid,scorefrom scoursewhere cid not in(C1,C2)select sid,cid,scorefrom scoursewhere cid!=C1 and cid!=C2/*查询工资在15002000之间的教师的教师号、姓名及职称。*/select tid,tnname,title from teacher where salary=1500 and salary85/*查询选课在二门以上且各门课均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。*/s

11、elect sid,sum(score) as 总分from scoursewhere score=60group by sidhaving count(*)2order by sum(score) desc/*查询所有选课学生的学号、姓名、选课名称及成绩。*/select student.sid,sname,cid,scorefrom student,scoursewhere student.sid=scourse.sid/*查询选修C1课程且成绩在60以上的所有学生的学号、姓名和分数。*/select student.sid,sname,scorefrom student,scoursew

12、here student.sid=scourse.sid and scourse.cid=C1 and score60/*查询与李明教师职称相同的教师号、姓名。*/select tid,tname,titlefrom teacherwhere title=(select title from teacher where tname=李明)/*-项目5-*/ /*查询选修了C1或C2且分数大于等于85分的学生和学号。*/select sid,cid,score from scourse where(cid=C1 or cid=C2) and score=85/*查询工资不在15002000之间的

13、教师的教师号、姓名及职称。*/select tid,tname,title from teacherwhere salary not between 1500 and 2000/*从表bookinfo中查询书的名称和单价,使书的单价精确个位即可。*/select bname,round(bprice,0) as round_pricefrom bookinfoorder by bname/*使用系统函数编写T-SQL语句，查询七月份考试的课程名称和考试时间。*/select cname,ctestfrom courseinfowhere month(ctest)=4 /*日期采用字符型月份的形

14、式时：datename(month,ctest)=JULY*/order by ctest/*从bookinfo表中查询所有的书名，数量以及单价信息，并要求所有书名用大写字母表示。*/Select left(upper(bname),20)as BOOK,quantity, bpriceFrom bookinfo /*从bookinfo表中查询所有以“Processing”结尾的书名、数量以及单价信息。*/Select bname,quantity,bpriceFrom bookinfoWhere right(bname,10)=ProcessingOrder by bname /*从book

15、info表中查询所有的书名、单价信息以及将书名中的字符串“Processing”替换为”Pro.”后的结果。*/select bname,bprice,replace(bname,Processing,Pro.) as Replacefrom bookinfoorder by bname /*由于种种原因，考试时间要推迟一个星期，将四月份考试的科目推迟一个星期。*/update courseinfo set ctest=dateadd(day,7,ctest) where month(ctest)=4 /*查询课程名称，当前日期，考试时期和考试日期距当前日期还有多少天，并按剩下天数的多少进行排

16、序。*/select cname,getdate() as currentdate, ctest,datediff(day,getdate(),ctest) as remaindayfrom courseinfoorder by remainday desc/*-项目6-*/*1. 在SC表中，查询学号为1001的学生的最低分。如果成绩大于90分，显示“成绩优秀”；如果成绩大于60分，显示“成绩合格”；否则显示“不合格”。*/declare marks int select marks=min(score) from scourse where sid=1001if marks90 print

17、 成绩优秀else if marks60 print 成绩合格 else print 成绩不合格/*2. 在courseinfo表里增加学分列（credit）,并用表中的学分列插入数据。输出C1这门课的学分信息（学分在2和3之间、学分大于4及学分小于1的信息）。*/1)、向表里增加列alter table scourse add credit char(4) null /向表里增加字段的语法/*alter table sc /删除字段的语法drop column creditalter table sc /修改字段的大小alter column sno char(15) */2）、更新数据up

18、datescourse set credit= case when cid=C4 then 1 when cid=C3 then 2 when cid=C2 then 3 when cid=C1 then 4 end select * from scourse3）、输出语句Declare var char(4)select var = (select distinct credit from scourse where cid=C1) if var1 if var 4 print 学分在23之间 else print 学分大于4 else print 学分小1分/*3. 调整课程的学分，对学分

19、为2的调整为3，对学分为1的调整为2，其他的学分调整为1。*/update scourseset credit= case when credit=2 then credit+1 when credit=1 then credit+1 else 1 endselect credit from scourse/*4. 事务的使用。在SC表中，学号为“1001”学生的平均成绩如果小于75，则该学生的每门成绩以5%的比例提高，当平均成绩大于等于75，或者所有课程都及格时，终止操作。*/*5.游标的使用。某一选修课程考试结束后，教师录入学生的成绩后，出于某些原因（如试卷本身可能存在缺陷），老师需要将该

20、课程所有的学生成绩加5分（但是总分不能超过100分），修改后的成绩如果介于55分59分之间，将这些学生的成绩修改为60分。*/*在SC表中，学号为1001学生的平均成绩如果小于75，则该学生的每门成绩以5%的比例提高，当平均成绩大于等于75或者所有课程都及格时，终止操作。*/*(分析:本题可以分四个步骤,请根据以下提示完成此题.)1)、为了不影响scourse表的其他应用，首先将学号为1001的学生的所有信息复制到新表the_scourse*/select * into the_scoursefrom scoursewhere sid=1001/*2)、查询复制后表the_sc中的数据及平均成

21、绩*/select * from the_scourseselect avg(score) from the_scourse /*3)、更新表the_scourse中的数据*/while(select avg(score)from the_scourse)=60 break end/*4)、查询更新后表the_scourse中的数据及平均成绩*/select * from the_scourseselect avg(score) from the_scourse/*-项目6-*/1. 在stucourse数据库中创建一个的触发器，该触发器不允许的courseinfo表中的cname列进行更新。2. 在stucourse数据库中创建一个触发器，当学生选课时，计算已选的课程门数，超过5门不允许进行选课操作。/*-项目7-*/1. 在stucourse数据库中创建新视图v_score_avg。要求计算每个同学的选课成绩的平均分。2. 在stucourse数据库中使用CREATE INDEX语句为表stu创建一个非聚集索引，索引字段s_name，索引名为IX_STUDENT_name。3. 在stucourse数据库中为表courseinfo创建一个复合索引，按照cname为降序，ctest为升序进行排序。请勿盗版 尊重作者