《附录C 数据库上机实验.doc》由会员分享，可在线阅读，更多相关《附录C 数据库上机实验.doc（15页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流附录C 数据库上机实验.精品文档.附录C 上机实验C.1 第4章上机实验-陈宇超，仅供参考-下列实验均使用SQL Server 的SSMS工具实现。1用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：l 数据库名为：学生数据库l 主要数据文件的逻辑文件名为：Students_data，存放在D:Data文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB，增长方式为自动增长，每次增加1MB。l 日志文件的逻辑文件名字为：Students_log，也存放在D:Data文件夹下，初始大
2、小为：2MB，增长方式为自动增长，每次增加10%。2选用已建立的“学生数据库”，写出创建满足表C-1到4-4条件的表的SQL语句，并执行所写代码。（注：“说明”部分不作为表定义内容）表C-1 Student表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname姓名普通编码定长字符串，长度为10非空Ssex性别普通编码定长字符串，长度为2取值范围：男，女Sage年龄微整型（tinyint）取值范围：15-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”Sid身份证号普通编码定长字符串，长度为10取值不重Sdate入学日期日期默认为系统当前日期表C-
3、2 Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型表C-3 SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型取值范围为0-100表C-4 Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串，长度为10非空Salary工资定点小数，小数点前4位，
4、小数点后2位3. 写出实现如下功能的SQL语句，并执行所写代码，查看执行结果。（1） 在Teacher表中添加一个职称列，列名为：Title，类型为nchar(4)。ALTER TABLE Teacher ADD Title VARCHAR(4)（2） 为Teacher表中的Title列增加取值范围约束，取值范围为：教授，副教授，讲师。ALTER TABLE Teacher ADD CONSTRAINT Title CHECK(Title IN(教授,副教授,讲师)（3） 将Course表中Credit列的类型改为：tinyint。注意：这里首先需要先删除Credit的约束,修改完数据类型后再
5、重新添加约束项ALTER TABLE Course DROP CONSTRAINT CK_Course_Credit_2B3F6F97ALTER TABLE Course ALTER COLUMN Credit TINYINTALTER TABLE Course ADD CONSTRAINT Credit CHECK (Credit0)（4） 删除Student表中的Sid和Sdate列。注意：这里首先需要先删除约束 ALTER TABLE Student DROP CONSTRAINT UQ_Student_CA1E5D79B3204FCFALTER TABLE Student DROP C
6、ONSTRAINT DF_Student_Sdate_3B75D760ALTER TABLE Student DROP COLUMN Sdate,Sid（5） 为Teacher表添加主键约束，其主键为：Tno。ALTER TABLE Teacher ADD PRIMARY KEY(Tno)建立数据库主要文件和日志文件我们可以看到D盘增加了主要数据文件.mdf和日志文件.ldf给students_data建立表格create table Student(Sno char(7),Sname char(10) not null,Ssex char(2) check( Ssex=男 or Ssex=女
7、 ) ,Sage tinyint check(Sage 14 AND Sage0), Semester tinyint, )create table SC(Sno char(7),Cno char(10),Grade tinyint check( Grade=0 AND Grade20011. 查询选了C002课程的学生姓名和所在系。 SELECT Sname,Sdept FROM Student st,SC sc WHERE st.Sno=sc.Sno AND Cno=C002 或者 SELECT Sname,Sdept FROM Student WHERE Sno IN(SELECT Sn
8、o FROM SC WHERE Cno=C002)或者SELECT Sname,Sdept FROM Student WHERE EXISTS (SELECT * FROM SCWHERE Sno = Student.Sno AND Cno = C002) 12. 查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。SELECT Sname,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno AND Grade80 ORDER BY Grade DESC13. 查询与VB在同一学期开设的课程的课程名和开课学期。SELECT c
9、2.Cname,c2.Semester FROM Course c1,Course c2 WHERE c1.Cname=c2.Cname AND c1.Cname=VB AND c2.Cname!=VB14. 查询与李勇年龄相同的学生的姓名、所在系和年龄。SELECT s2.Sname,s2.Sdept,s2.Sage FROM Student s1,Student s2WHERE s1.Sage=s2.Sage AND s1.Sname=李勇 AND s2.Sname!=李勇或者SELECT Sname,Sdept,Sage FROM Student WHERE Sage=(SELECT S
10、age FROM Student WHERE Sname=李勇) AND Sname!=李勇15. 查询哪些课程没有学生选修，列出课程号和课程名。SELECT Course.Cno,Course.Cname FROM Course LEFT JOIN SC on SC.Cno=Course.Cno WHERE SC.Cno IS NULL16. 查询每个学生的选课情况，包括未选课的学生，列出学生的学号、姓名、选的课程号。SELECT Student.Sno,Student.Sname,Cno FROM Student LEFT JOIN SC ON SC.Sno=Student.Sno17.
11、查询计算机系哪些学生没有选课，列出学生姓名。SELECT Sname FROM Student WHERE Sdept=计算机系 AND Sno NOT IN(SELECT Sno FROM SC)或者SELECT Sname FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = 计算机系 AND SC.Cno IS NULL18. 查询计算机系年龄最大的三个学生的姓名和年龄。SELECT Top 3 Sname,Sage FROM Student WHERE Sdept=计算机系 ORDER BY Sage DESC19. 列
12、出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。SELECT TOP 3 WITH TIES Sname, Sdept, Grade FROM Student S JOIN SC on S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = VBORDER BY Grade DESC20. 查询选课门数最多的前2位学生，列出学号和选课门数。SELECT top 2 Sno,COUNT(Cno) AS 选课数 FROM SC GROUP BY Sno ORDER BY COUNT(Cno) DESC21. 查询计
13、算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。 SELECT Sno,Sage,CASEWHEN Sage=18 AND Sage22 THEN 偏大ENDAS 年龄情况 FROM Student22. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40100，则显示“一般”；如果此门课程的选课人数在140，则显示“人少”；如果此门课程没有人选，则显示“
14、无人选”。 SELECT Course.Cno,COUNT(Sno)AS 选课人数,CASEWHEN COUNT(Sno)100 THEN 人多WHEN COUNT(Sno)0 THEN 人少WHEN COUNT(Sno)=40 THEN 一般WHEN COUNT(Sno)=0 THEN 无人选 endAS 人数情况 FROM SC RIGHT JOIN Course ON Course.Cno=SC.Cno GROUP BY Course.Cno23. 查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade中。v 局部临时表：#新表名。局部于当前连接，生命期
15、同连接期。v 全局临时表： #新表名。可在所有连接中使用，生命期同用户连接期v 永久表： 新表名，存储在磁盘上v 格式：SELECT 选择列表INTO 新表名 FROM 子句 SELECT Sname,Sdept,Grade INTO VB_Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname=VB24. 统计每个系的女生人数，并将结果保存到新表Girls中。SELECT Sdept,Ssex,COUNT(Ssex) AS 人数 Into Girls FROM Student
16、WHERE Ssex=女GROUP BY Sdept,Ssex 25. 用子查询实现如下查询：（1）查询选了“C001”课程的学生姓名和所在系。SELECT Sname,Sdept FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno=C001 )（2）查询通信工程系成绩80分以上的学生的学号和姓名。SELECT Sno,Sname FROM Student WHERE Sdept=通信工程系 AND Sno IN (SELECT Sno FROM SC WHERE Grade80)（3）查询计算机系考试成绩最高的学生的姓名。SELEC
17、T Sname FROM Student,SC WHERE SC.Sno=Student.Sno AND Sdept=计算机系 AND Grade in(SELECT MAX(Grade) FROM SC,Student WHERE SC.Sno=Student.Sno AND Sdept=计算机系)（4）查询年龄最大的男生的姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Ssex=男 AND Sage in(SELECT MAX(Sage) FROM Student WHERE Ssex=男)26. 查询C001课程的考试成绩高于该课
18、程平均成绩的学生的学号和成绩。SELECT Sno, Grade FROM SC WHERE Cno = C001 AND Grade ( SELECT AVG(Grade) FROM SC WHERE Cno = C001)27. 查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。SELECT Sname,Sdept,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Sdept=计算机系 AND Grade(SELECT AVG(Gra
19、de) FROM SC,Course,Student WHERE SC.Cno=Course.Cno AND Student.Sno=SC.Sno AND Sdept=计算机系)28. 查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。SELECT Sname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname=VB AND Grade(SELECT AVG(Grade) FROM SC,Course WHERE Course.Cno=SC.Cno AND Cname
20、=VB)29. 查询没选VB的学生姓名和所在系。SELECT Sname,Sdept FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC,Course WHERE SC.Cno=Course.Cno AND Cname=VB)或者SELECT Sname,Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC,Course WHERE Course.Cno=SC.Cno AND Sno=Student.Sno AND Cname=VB)30. 查询每个学期学分最高的课程信息，列出开课学期、课程名
21、和学分。SELECT Cname,Semester,Credit FROM Course c1 WHERE NOT EXISTS(SELECT * FROM Course c2 WHERE c1.Semester=c2.Semester AND c1.Creditc2.Credit )31. 查询每门课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。SELECT Cname,sname,SC.Cno,Grade FROM student join sc on student.sno=sc.sno join course on o=o WHER
22、E grade=(SELECT max(grade)FROM scWHERE cno=o ) order BY SC.Cno ASc或者SELECT sc1.sno,Sname,Cno ,Grade FROM SC sc1,student WHERE not exists(SELECT * FROM SC sc2 WHERE sc1.Cno=sc2.Cno AND sc1.Gradesc2.Grade) AND Grade IS NOT NULL AND Student.Sno=sc1.Sno order BY Cno ASC32. 查询选了全部课程的学生姓名SELECT Sname FROM
23、 Student WHERE Sno in (SELECT Sno FROM SC GROUP BY Sno having COUNT(*)=(SELECT COUNT(*) FROM Course)或者SELECT Sname FROM student WHERE NOT exists(SELECT * FROM courseWHERE NOT exists(SELECT * FROM SC WHERE Sno=student.sno AND cno=Course.Cno)33. 创建一个新表，表名为test，其结构为（COL1, COL 2, COL 3），其中，COL1：整型，允许空值。
24、COL2：普通编码定长字符型，长度为10，不允许空值。COL3：普通编码定长字符型，长度为10，允许空值。试写出按行插入如下数据的语句（空白处表示是空值）。COL1COL2COL3B11B2C22B3create table test(C0L1 int ,C0L2 char(10) not null,C0L3 char(10)insert into test(C0L2) values(B1)insert into test values(1,B2,C2)insert into test(C0L1,C0L2) values(2,B3)34. 利用23题建立的VB_Grade表，将信息管理系选了V
25、B课程的学生姓名、所在系和考试成绩插入到VB_Grade表中。INSERT INTO VB_Grade SELECT Sname,Sdept,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Sdept=信息管理系 AND Cname=VB35. 将所有选修C001课程的学生的成绩加10分。UPDATE SC SET Grade=Grade+10 WHERE Cno=C00136. 将计算机系所有学生的“计算机文化学”的考试成绩加10分。UPDATE SC SET Grade=Grad
26、e+10 WHERE Sno in (SELECT SC.Sno FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Sdept=计算机系 AND Cname=计算机文化学)37. 修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。UPDATE SC SET Grade=Grade+CASE SdeptWHEN 通信工程系 THEN 10WHEN 信息管理系 THEN 5else 0endFROM Student,SC,Cours
27、e WHERE SC.Sno=Student.Sno AND Course.Cno=SC.Cno AND Cname=VB38. 删除成绩小于50分的学生的选课记录。DELETE FROM SC WHERE Grade 5039. 删除计算机系VB考试成绩不及格学生的VB选课记录。DELETE FROM SC FROM SC,Student,Course WHERE SC.Sno=Student.SnoAND SC.Cno=Course.Cno AND Sdept=计算机系 ANDCname=VB AND Grade=60 GROUP BY SnoGO3. 利用第2题建立的视图，完成如下查询。（1）查询考试成绩大于等于90分的学生的姓名、课程名和成绩。 SELECT Sname,Cname,Grade FROM v2 WHERE Grade=90（2）查询选课门数超过3门的学生的学号和选课门数。SELECT Sname,Cname,Grade FROM v2 WHERE Grade=90（3）查询计算机系选课门数超过3门的学生的姓名和选课门数。 SELECT Sname,Cnum FROM Stu