实验二 数据对象的定义、单表数据查询
实验内容
1、建立数据库Student_DB，并在此数据库中用命令方式创建数据表(注意表中列的相关约束)：
学生表：Student(Sno, Sname, Ssex, Sage, Sdept);
课程表：Course(Cno, Cname, Cpno, Ccredit);
学生选课表：SC(Sno, Cno, Grade)
学生表的逻辑设计：
字段名 |
类型 |
长度 |
主码/外码 |
描述 |
是否可空 |
Sno |
char |
9 |
主码 |
学号 |
× |
Sname |
char |
20 |
|
姓名 |
× |
Ssex |
char |
2 |
|
性别 |
√ |
Sage |
smallint |
|
|
年龄 |
√ |
Sdept |
char |
20 |
|
系别 |
√ |
课程表的逻辑设计：
字段名 |
类型 |
长度 |
主码/外码 |
描述 |
是否可空 |
Cno |
char |
4 |
主码 |
课程号 |
× |
Cname |
char |
40 |
|
课程名 |
× |
Cpno |
char |
4 |
外码 |
先修课程号 |
√ |
Ccredit |
smallint |
|
|
学分 |
√ |
选课表的逻辑设计：
字段名 |
类型 |
长度 |
主码/外码 |
描述 |
是否可空 |
Sno |
char |
9 |
外码 |
学号 |
× |
Cno |
char |
4 |
外码 |
课程号 |
× |
说明：选课表的主码：(Sno, Cno)
2、用SQL语言完成如下的操作（请给出SQL语句及相应执行结果的截图）：
（1）将学生表中年龄的数据类型改为整型。
Alter table student alter column sage int;
（2）向SC表中增加成绩（Grade）列，其数据类型为短整型。
Alter table SC add Grade smallint;
（3）用SSMS方式向三个表中填充数据（参照课本P79页）(此题可不用给出结果图)。
（4）对Student表按学号降序建唯一索引。
Create index stu_index on student( sno desc);
（5）对Student表按学生姓名列建聚集索引，观察能否建立成功，如未成功请分析原因。
Create cluster index sname_index on student (sname) ;
（6）删除第(4)题建的索引。
Drop index stu_index;
3、用SQL语句完成如下对三个表的查询（请给出SQL语句及执行结果的截图）：
（1）查询所有课程的课程号和课程名。
（2）查询全体学生的记录。
（3）查询学生表中的所有的系。
（4）查询全体学生的姓名、出生年份和所在的院系，并为出生年份和所在院系两列分别起别名为sbirth，department，并将系名全部转换成小写字母。
（5）求数学系学生的学号和姓名。
（6）查询所有考试成绩在80分以下的学生学号、课程号。
（7）查询学分在2~7之间的课程信息。
（8）查询课程名为数据库课程的课程号和学分。
（9）查询没有先修课的课程号、课程名及学分。
（10）查询信息系学生性别为男且年龄小于20岁的学生姓名。
（11）查询选修了3号课程的学生的学号及成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。
（12）查询课程的总数。
（13）查询选修2号课程的学生平均成绩和最高成绩，并分别为平均成绩和最高成绩起别名为AVG,MAX。
（14）查询每个学生的选课门数，要求输出学生学号及选课的门数。
（15）查询选修了2门及以上的课程的学生学号。
实验过程、存在的问题及解决办法：
2、用SQL语言完成如下的操作（请给出SQL语句及相应执行结果的截图）
（1）将学生表中年龄的数据类型改为整型。
Alter table student alter column sage int;
（2）向SC表中增加成绩（Grade）列，其数据类型为短整型。
Alter table SC add Grade smallint;
（3）用SSMS方式向三个表中填充数据（参照课本P79页）(此题可不用给出结果图)。
（4）对Student表按学号降序建唯一索引。
Create unique index stu_index on student( sno desc);
（5）对Student表按学生姓名列建聚集索引，观察能否建立成功，如未成功请分析原因。
Create clustered index sname_index on student (sname) ;
原因分析：
聚集索引是通过设置主码来完成的，每个表的主码都是聚集索引，一个表只能有一个聚集索引，非聚集索引可以有多个。因此无法重复创建聚焦索引。
（6）删除第(4)题建的索引。
Drop index stu_index on student;
3、用SQL语句完成如下对三个表的查询（请给出SQL语句及执行结果的截图）：
（1）查询所有课程的课程号和课程名。
select Cno,Cname
from Course
（2）查询全体学生的记录。
select *
from student
（3）查询学生表中的所有的系。
select Sdept
from student
（4）查询全体学生的姓名、出生年份和所在的院系，并为出生年份和所在院系两列分别起别名为sbirth，department，并将系名全部转换成小写字母。
select Sname,2018-Sage as sbirth,
Sdept as department,Lower(Sdept) departments
from student
（5）求数学系学生的学号和姓名。
select Sno,Sname,Sdept
from Student
where Sdept='MA'
（6）查询所有考试成绩在80分以下的学生学号、课程号。
select Sno,Cno
from SC
where grade<=80
（7）查询学分在2~7之间的课程信息。
select Ccredit,Cname
from Course
where Ccredit between 2 and 7
（8）查询课程名为数据库课程的课程号和学分。
select Cno,Ccredit
from Course
where Cname='数据库'
（9）查询没有先修课的课程号、课程名及学分。
select Cno,Ccredit,Cname
from Course
where Cpno is NULL
（10）查询信息系学生性别为男且年龄小于20岁的学生姓名。
select Sname
from Student
where Ssex='男' and Sage<20 and Sdept='CS'
（11）查询选修了3号课程的学生的学号及成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。
select Sno,Grade
from SC
where Cno='3'
Order by Grade desc,Sno
（12）查询课程的总数。
select COUNT(*) as 课程总数
from course
（13）查询选修2号课程的学生平均成绩和最高成绩，并分别为平均成绩和最高成绩起别名为AVG,MAX。
select AVG(grade) as avg,MAX(Grade) as max
from sc
where cno='2'
（14）查询每个学生的选课门数，要求输出学生学号及选课的门数。
select Sno,COUNT(Cno) as 总门数
from sc
group by Sno
（15）查询选修了2门及以上的课程的学生学号。
select Sno
from sc
group by Sno
having COUNT(*)>2
实验总结：
1、掌握基本表的定义、结构的修改及删除；
2、掌握索引的建立及删除；
3、掌握Select语句的基本语法；
4、掌握Group By子句的作用和使用方法；
5、掌握聚集函数的使用；
6、掌握Order By子句的作用和使用方法；