附录C数据库上机实验Word下载.docx

1、取值范围：男，女Sage年龄微整型（tinyint）15-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”Sid身份证号取值不重Sdate入学日期日期默认为系统当前日期表C-2 Course表结构Cno课程号Cname课程名Credit学时数整型取值大于0Semester学分小整型表C-3 SC表结构主键，引用Student的外键主键，引用Course的外键Grade成绩取值范围为0-100表C-4 Teacher表结构Tno教师号普通编码定长字符串，长度为8Tname教师名Salary工资定点小数，小数点前4位，小数点后2位3. 写出实现如下功能的SQL语句，并执行所

2、写代码，查看执行结果。（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的约束,修改完数据类型后再重新添加约束项ALTER TABLE Course DROP C

3、ONSTRAINT 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 CONSTRAINT DF_Student_Sdate_3B75D760A

4、LTER 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=女 ） ,Sage tinyint check（Sage 14 AND Sa

5、ge0）, Semester tinyint, create table SC（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 Sno FROM SC WHERE Cno=或者SELECT Sname,Sdept FROM Stude

6、nt WHERE EXISTS （SELECT * FROM SCWHERE Sno = Student.Sno AND Cno = ） 12.查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。SELECT Sname,Cno,Grade FROM Student,SC WHERE Student.Sno=SC.Sno AND Grade80 ORDER BY Grade DESC13.查询与VB在同一学期开设的课程的课程名和开课学期。SELECT c2.Cname,c2.Semester FROM Course c1,Course c2 WHERE c1.Cname=c2

7、.Cname AND c1.Cname=VB AND c2.Cname!=14.查询与李勇年龄相同的学生的姓名、所在系和年龄。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 Sage FROM Student WHERE Sname=） AND Sname!15.查询哪些课程没有学生选修，列出课程号和课程名。SEL

8、ECT 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.查询计算机系哪些学生没有选课，列出学生姓名。SELECT Sname FROM Student WHERE Sdept= AND Sno NOT IN（SE

9、LECT 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.列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩。SELECT TOP 3 WITH TIES Sname, Sdept, Grade FROM Student S JOIN

10、 SC on S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = ORDER BY Grade DESC20.查询选课门数最多的前2位学生，列出学号和选课门数。SELECT top 2 Sno,COUNT（Cno） AS 选课数 FROM SC GROUP BY Sno ORDER BY COUNT（Cno） DESC21.查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于18，则显示“偏小”；如果年龄在18-22，则显示“合适”；如果年龄大于22，则显示“偏大”。 SELECT Sno,Sage,CASEW

11、HEN Sage=18 AND Sage100 THEN 人多WHEN COUNT（Sno）0 THEN 人少=100 AND 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中。 局部临时表：#新表名。局部于当前连接，生命期同连接期。 全局临时表： #新表名。可在所有连接中使用，生命期同用户连接期 永

12、久表： 新表名，存储在磁盘上 格式：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=24.统计每个系的女生人数，并将结果保存到新表Girls中。SELECT Sdept,Ssex,COUNT（Ssex） AS 人数 Into Girls FROM Student WHERE Ssex=GROUP BY Sdept,Ssex 25.用子查询实现如下查询

13、：（1）查询选了“C001”课程的学生姓名和所在系。SELECT Sname,Sdept FROM Student WHERE Sno IN （SELECT Sno FROM SC WHERE Cno= ）（2）查询通信工程系成绩80分以上的学生的学号和姓名。SELECT Sno,Sname FROM Student WHERE Sdept=通信工程系 AND Sno IN （SELECT Sno FROM SC WHERE Grade80）（3）查询计算机系考试成绩最高的学生的姓名。SELECT Sname FROM Student,SC WHERE SC.Sno=Student.Sno A

14、ND 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课程的考试成绩高于该课程平均成绩的学生的学号和成绩。SELECT Sno, Grade FROM SC WHERE Cno = AND Grade

15、（ SELECT AVG（Grade） FROM SC WHERE Cno = 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（Grade） FROM SC,Course,Student WHERE SC.Cno=Course.Cno AND Student.Sno=SC.Sno AND S

16、dept=28.查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。SELECT Sname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno AND Cname= AND Grade（SELECT AVG（Grade） FROM SC,Course WHERE Course.Cno=SC.Cno AND Cname=29.查询没选VB的学生姓名和所在系。SELECT Sname,Sdept FROM Student WHERE Sno NOT IN （SELECT Sno FROM S

17、C,Course WHERE SC.Cno=Course.Cno AND Cname=SELECT Sname,Sdept FROM Student WHERE NOT EXISTS （SELECT * FROM SC,Course WHERE Course.Cno=SC.Cno AND Sno=Student.Sno AND Cname=30.查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。SELECT Cname,Semester,Credit FROM Course c1 WHERE NOT EXISTS（SELECT * FROM Course c2 WHERE c1.Se

18、mester=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 WHERE grade=（SELECT max（grade）FROM scWHERE cno=o ） order BY SC.Cno AScSELECT sc1.sno,Sname,Cno ,Grade FRO

19、M 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 Student WHERE Sno in （SELECT Sno FROM SC GROUP BY Sno having COUNT（*）=（SELECT COUNT（*） FROM Course）SELE

20、CT 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：整型，允许空值。COL2：普通编码定长字符型，长度为10，不允许空值。COL3：普通编码定长字符型，长度为10，允许空值。试写出按行插入如下数据的语句（空白处表示是空值）。COL1COL2COL3B11B2C22B3create

21、table test（C0L1 int ,C0L2 char（10） not null,C0L3 char（10）insert into test（C0L2） values（B1insert into test values（1,B2C2insert into test（C0L1,C0L2） values（2,B334.利用23题建立的VB_Grade表，将信息管理系选了VB课程的学生姓名、所在系和考试成绩插入到VB_Grade表中。INSERT INTO VB_Grade SELECT Sname,Sdept,Grade FROM Student,SC,Course WHERE Studen

22、t.Sno=SC.Sno AND SC.Cno=Course.Cno AND Sdept=信息管理系 AND Cname=35.将所有选修C001课程的学生的成绩加10分。UPDATE SC SET Grade=Grade+10 WHERE Cno=36.将计算机系所有学生的“计算机文化学”的考试成绩加10分。UPDATE SC SET Grade=Grade+10 WHERE Sno in （SELECT SC.Sno FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno 计算机文化学37.修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加10分；如果是信息管理系的学生则增加5分，其他系的学生不加分。UPDATE SC SET Grade=Grade+CASE SdeptWHEN