附录C 数据库上机实验

附录C 上机实验C。1 第4章上机实验-陈宇超，仅供参考-下列实验均使用SQL Server 的SSMS工具实现。1用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：l 数据库名为：学生数据库l 主要数据文件的逻辑文件名为:Students_data，存放在D：Data文件夹下（若D:盘中无此文件夹,请先建立此文件夹，然后再创建数据库。），初始大小为:5MB,增长方式为自动增长,每次增加1MB。l 日志文件的逻辑文件名字为：Students_log，也存放在D：Data文件夹下，初始大小为：2MB，增长方式为自动增长，每次增加10.2选用已建立的“学生数据库”,写出创建满足表C1到4-4条件的表的SQL语句，并执行所写代码。（注：“说明”部分不作为表定义内容）表C1 Student表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname姓名普通编码定长字符串，长度为10非空Ssex性别普通编码定长字符串，长度为2取值范围:男，女Sage年龄微整型（tinyint)取值范围：15-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系"Sid身份证号普通编码定长字符串，长度为10取值不重Sdate入学日期日期默认为系统当前日期表C2 Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型表C-3 SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键,引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型取值范围为0-100表C4 Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串,长度为10非空Salary工资定点小数，小数点前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的约束,修改完数据类型后再重新添加约束项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 CONSTRAINT 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='女 ） ，Sage tinyint check(Sage >14 AND Sage<46),Sdept varchar（20) default 计算机系,Sid char（10） unique,身份证号Sdate date default getdate（)primary key(Sno)）create table Course（Cno char(10) primary key，Cname varchar(20） not null,Credit int check （Credit0）, Semester tinyint, )create table SC(Sno char(7),Cno char（10）,Grade tinyint check( Grade>=0 AND Grade<=100)foreign key(Sno) references Student(Sno)，foreign key(Cno） references Course（Cno）)create table Teacher(Tno char（8） not null，Tname char(10) not null，Salary decimal（6,2）C。2 第5章上机实验本实验均在SQL Server 的SSMS工具中实现.首先在已创建的“学生数据库"中创建本章表5153所示的Student、Course和SC表，并插入表5456所示数据，然后编写实现如下操作的SQL语句,执行所写的语句，并查看执行结果。有关建表及所需要的数据，见本章末尾1. 查询SC表中的全部数据.SELECT * FROM SC2. 查询计算机系学生的姓名和年龄。SELECT Sname, Sage FROM Student WHERE Sdept='计算机系'3. 查询成绩在7080分的学生的学号、课程号和成绩。SELECT Sno,Cno，Grade FROM SC WHERE Grade between 70 AND 804. 查询计算机系年龄在1820岁的男生姓名和年龄。SELECT Sname,Sage FROM Student WHERE Sdept=计算机系 AND Sage BETWEEN 18 AND 20 AND Ssex=男'5. 查询C001课程的最高分。SELECT MAX（grade) AS 最高成绩 FROM SC WHERE Cno=C001'6. 查询计算机系学生的最大年龄和最小年龄。SELECT MAX（Sage) AS 年长,MIN(Sage) AS 年幼 FROM Student WHERE Sdept='计算机系'7. 统计每个系的学生人数.SELECT Sdept，COUNT（Sno） AS 人数 FROM Student GROUP BY Sdept8. 统计每门课程的选课人数和最高成绩.SELECT Cno，COUNT(Sno） AS 人数，MAX(Grade) AS 最高成绩 FROM SC GROUP BY Cno9. 统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。SELECT Sno,COUNT(Cno) AS 选课数量,SUM（Grade） AS 考试总成绩 FROM SC GROUP BY Sno10. 列出总成绩超过200的学生的学号和总成绩。SELECT Sno,SUM（Grade) AS 考试总成绩 FROM SC GROUP BY Sno HAVING SUM（Grade)20011. 查询选了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=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 Grade>80 ORDER BY Grade DESC13. 查询与VB在同一学期开设的课程的课程名和开课学期。SELECT c2.Cname，c2。Semester FROM Course c1,Course c2 WHERE c1。Cname=c2。Cname AND c1.Cname=VB AND c2。Cname！='VB'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. 查询哪些课程没有学生选修，列出课程号和课程名.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. 查询计算机系哪些学生没有选课,列出学生姓名。SELECT Sname FROM Student WHERE Sdept='计算机系' AND Sno NOT IN（SELECT Sno FROM SC)或者SELECT Sname FROM Student S LEFT JOIN SC