创建数据表与数据输入
Part I.使用SQL Server Management Studio创建数据表和输入数据 1.在SQL Server Management Studio中创建数据表 P69 1.（1）-（6）
2.为数据表输入数据 P76 4.（1）-（4）
3.数据浏览
P77
1.（1）-（2）
2.（1）-（3）
Part II.使用SQL语句创建数据表和输入数据 1.使用SQL语句创建数据表 P72.例3-2
2．使用SQL语句输入数据 P82.例3.8
习题：P.105 （1）（2）insert 数据操作Insert、Update、Delete
P105 3.上机练习题 （2）（4）
Insert (100001, 1000, 2002-12-18 0:00:00) (100002, 2000, 2010-3-20 0:00:00)
Update (100001, 1500, 2002-12-18 0:00:00) (100002, 2000, 2012-9-25 0:00:00)
Delete Transact-SQL语句基础1 1.将teaching数据库中score表的studentno列设置为引用表student的外键。 ALTER TABLE Score ADD CONSTRAINT FK_score_student FOREIGN KEY (studentno) REFERENCES student(studentno) 2.将teaching数据库中cla表的claname创建UNIQUE约束。 ALTER TABLE cla ADD CONSTRAINT UQ_cla UNIQUE(claname) 执行如下插入语句，查看提示信息
INSERT INTO cla VALUES(‘090602’, ’计算机0902’, ’计算机学院’, ’马文斐’) 3.为teaching数据库中student表的birthday列创建CHECK约束，规定学生的年龄在17-25岁之间。
ALTER TABLE student ADD CONSTRAINT CK_birthday CHECK(YEAR(GETDATE())-YEAR(birthday)) BETWEEN 17 AND 25 执行如下插入语句，查看提示信息
INSERT INTO student(studentno, sname, sex, birthday, clano) VALUES (‘0922221328’, ’张源’, ’男’, ’1983-04-05’, ’090501’) 提示：表达式YEAR(GETDATE())-YEAR(birthday) 4.为teaching数据库创建规则prof_rule，规定教师职称取值只能为’助教’,’讲师’,’副教授’,’教授’，并将其绑定到teacher表的prof列上。
CREATE RULE prof_rule AS @prof IN(’助教’,’讲师’,’副教授’,’教授’) EXEC sp_bindrule ‘prof_rule’, ‘teacher.prof’
执行如下插入语句，查看提示信息
INSERT INTO teacher VALUES(‘t05002’, ’张源’, ’软件工程’, ’工程师’, ’计算机学院’) 提示：表达式IN(职称列表) 5.编写程序，输出在1-3000之间能被17整除的最大数值
提示：可使循环控制变量从最大值开始，逐步减少，第一个满足被17整除的数值即为所求解的结果，可通过BREAK语句跳出循环。（如果使循环控制变量从小到大逐步增加，则循环次数将大大增加，程序执行效率将下降。）
PRINT ‘1-3000之间能被17整除的最大数值为：’ +CAST(@i AS CHAR(4)) DECLARE @s INT, @i INT SELECT @s=0, @i=3000 WHILE @i>=1 BEGIN
IF @i%17=0
BEGIN
PRINT ‘1-3000之间能被17整除的最大数值为：’ + CAST(@i AS CHAR(4))
BREAK
END @i = @i-1 END Transact-SQL语句基础2 1.利用Transact-SQL语句声明一个长度为16的nchar型变量bookname，并赋初值为“SQL Server数据库编程”，打印该变量。
2.编程求50-100之间的所有能被3整除的奇数之和。
3.编写程序，声明CHAR型变量@ch，并赋初值。判断字符变量@ch中存放的是字母、数字字符还是其他字符，并输出相关信息。（提示：UPPER函数可以将小写字母转换为大写字母）
4.编写程序，求解如下分数序列的前20项之和并打印输出结果。
S=2/1+3/2+5/3+8/5+13/8+21/13+…
分析数列的规律：后一项的分子为前一项的分子和分母之和，后一项的分母则为前一项的分子，通过循环即可实现累加。注意：声明分子和分母为浮点型数据NUMERIC(20,7).
5.查询编号为c06108, c08106, c05109课程的学生总评成绩等级，平时成绩usually*0.2+期末成绩final*0.8>=90的为优秀，80-90为良好，70-80为中等，60-70为及格，
3.查询teacher表中每一位教授的教师号、姓名和专业名称。
4.利用现有的表生成新表，新表中包括学号、学生姓名、课程号和总评成绩。其中：总评成绩=final*0.8+usually*0.2 5.查询student表中所有年龄大于20岁的男生的姓名和年龄。 6.查询计算机学院教师的专业名称。
7.查询Email使用126邮箱的所有学生的学号、姓名和电子邮箱地址。 8.查询score表中选修’c05109’或’c05103’课程，并且课程期末成绩在90~100分之间的学生姓名和期末成绩。
9.查询student表中所有学生的基本信息，查询结果按班级号clano升序排列，同一班级中的学生按入学成绩point降序排列。 10.查询选修’c05109’课程，并且期末成绩在前5名的学生学号、课程号和期末成绩。（提示：TOP 5） 数据检索2
1.按性别分组，求出student表中每组学生的平均年龄。 2.统计每个学生期末成绩的平均分。
3.输出student表中没有职称的职工的教师号、姓名、专业和部门。 4.查询选修课程且期末成绩不为空的学生人数。
5.查询每名学生的学号、选修课程数目、总成绩，并将查询结果存放到生成的“学生选课统计表”中。 6.查询各班学生的人数。
7.查询各课程期末成绩的最高分和最低分。
8.查询教两门及以上课程的教师编号、任课班级数。
9.查询课程编号以’c05’开头、被3名及以上学生选修且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。 10.查询所有08级学生的期末成绩平均分，要求利用COMPUTE BY方法显示每一名学生的学生编号、课程号、期末成绩的明细表，以及期末成绩平均分的汇总表。
11.查询所有女生入学成绩的最高分，要求利用COMPUTE BY方法既显示明细又显示汇总结果。 SQL语句的高级应用1
1.查询每一位教授的教师号、姓名和讲授的课程名称。 表：teacher,course,teach_cla 2.分别统计每个学生期末成绩高于75分的课程门数。表：student,score 3.计算每个学生获得的学分。表：student,score,course 4.获取入学时间在2008年到2009年之间的所有学生中入学年龄小于19岁的学生的学号、姓名及所修课程的课程名称。表：student, stu_course 5.查询09级学生的学号、姓名、课程及学分。表：student,stu_course 6.查询所有班级的期末成绩平均分，并按照平均分降序排列。表：score,student 7.查询教师基本信息和教授课程信息，其中包括未分配课程的教师信息。 表：teacher,teach_cla 8.查询’090501’班级中选修了’韩晋升’老师讲授的课程的学生的学号、姓名、课程名和期末成绩。表：score,student,course,teach_cla,teacher 9.查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序。表：score,course 10.查询两门及以上课程的期末成绩超过80分的学生的姓名及其平均成绩。 表：student,score SQL语句的高级应用2 – 使用子查询
1.输出student表中年龄大于女生平均年龄的男生的所有信息。 2.查询入学考试成绩最高的学生的学号、姓名和入学成绩。 3.查询所有教授’c05127’号课程的教师信息。
4.查询同时教授’c05127’和’c05109’号课程的教师信息。
5.查询至少选修了姓名为’韩吟秋’的学生所选修课程中的一门课的学生的学号和姓名。
6.查询没有被任何学生选修的课程编号、课程名称和学分。
7.查询’C语言’课程期末成绩比’电子技术’课程期末成绩高的所有学生的学号和姓名。
8.查询所有班级期末平均成绩的最高分，并将其赋值给变量，通过PRINT语句输出。 视图与索引
使用SQL语言
1.创建一个视图v_teacher，查询所有“计算机学院”的教师信息。
CREATE VIEW v_teacher AS SELECT * FROM teacher WHERE department=\'计算机学院\' GO SELECT * FROM v_teacher
2.创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且按照平均分降序排列。
CREATE VIEW v_avgstu AS SELECT TOP 100 student.studentno,sname,avg(final) AS \'平均分\' FROM student JOIN score ON student.studentno=score.studentno GROUP BY student.studentno,sname ORDER BY avg(final) GO SELECT * FROM v_avgstu
3.修改v_teacher的视图定义，添加WITH CHECK OPTION选项。
ALTER VIEW v_teacher AS SELECT * FROM teacher WHERE department=\'计算机学院\' WITH CHECK OPTION
4.通过视图v_teacher向基表teacher中分别插入数据(‘05039’, ‘张馨月’,’计算机应用’,’讲师’,’计算机学院’)和(‘06018’, ‘李诚’,’机械制造’,’副教授’,’机械学院’)，并查看插入数据情况。
INSERT INTO v_teacher VALUES (\'05039\', \'张馨月\',\'计算机应用\',\'讲师\',\'计算机学院\') GO SELECT * FROM teacher
消息550，级别16，状态1，第1 行
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。 语句已终止。
5.通过视图v_teacher将基表teacher中教师编号为05039的教师职称修改为”副教授”。
UPDATE v_teacher SET prof=\'副教授\' WHERE teacherno=\'05039\' GO SELECT * FROM teacher
6.在course表的cname列上创建非聚集索引IDX_name。
CREATE NONCLUSTERED INDEX IDX_name ON course(cname)
7.在student表的studentno和clano列上创建唯一索引UQ_stu，若该索引已经存在，则删除后重建，并输出student表中的记录，查看输出结果的顺序。 CREATE UNIQUE INDEX UQ_stu ON student(studentno,clano)
8.教材P.200-204（节7.5.2）使用SQL Server Management Studio创建视图，按照样图进行操作。
