## 一．实验目的：

1. 掌握T-SQL语言的基本语法格式。
2. 掌握T-SQL语言的运算符和表达式。
3. 掌握T-SQL语言的基本流程控制语句。

## 二．实验内容：（所有题写到实验报告中）

1. 定义局部变量a、b、c、s1、s2并赋初值5、4、4.0、‘abc’、‘def’，分别求a与b的积、商、余数、按位与、按位或、按位异或，a与c的商，s1与s2的串连。(注意比较a与b的商和a与c的商有何不同。):
``````DECLARE @a INT, @b INT, @c FLOAT, @s1 CHAR(5), @s2 CHAR(5);
SELECT @a = 5, @b = 4, @c = 4.0, @s1 = 'abc', @s2 = 'def';
SELECt @a * @b, @a / @b, @a % @b, @a & @b, @a | @b, @a ^ @b;
PRINT @a / @c;
PRINT @s1 +@ s2;``````
1. 输出当前SQL Server的版本信息，输出执行上一条T-SQL语句所返回的错误代码:
``````PRINT @@VERSION;
PRINT @@ERROR;``````

``````DECLARE @sno_select CHAR(5), @sname_select CHRA(10);
SET @sno_select = '107';
SELECT @sname_select = sname FROM student WHERE sno = '@sno_select';
PRINT @sname_select;``````
1. 对score表，将某个课程号赋给某个变量，然后查询课程号为该变量的选课人数和最高成绩分别赋给另外两个变量并输出其值:
``````DECLARE @cno_select CHAR(5)，@s_num int, @max_degree float;
SET @cno_select = '3-105';
SELECT @s_num = COUNT(*), @max_degree = MAX(degree) FROM score WHERE cno = @cno_select;
PRINT @s_num;
PRINT @max_degree;``````

## 编写程序

1. 输出每位教师的教师号、姓名和职称级别。（职称级别：教授和副教授为高级职称；讲师为中级职称，助教为初级职称:
``````SELECT tno, tname, 职称级别 =
CASE prof
WHEN '教授' THEN '高级职称'
WHEN '副教授' THEN '高级职称'
WHEN '讲师' THEN '中级职称'
WHEN '助教' THEN '初级职称'
END
FROM teacher;``````

1. 按优、良、中、及格、不及格五级成绩等级输出学生的姓名、课程名和成绩等级，输出结果先按课程号升序排再按姓名升序排:
``````SELECT sname, cname, 成绩等级 =
CASE
WHEN degree >= 90 THEN '优'
WHEN degree >= 80 AND degree < 90 THEN '良'
WHEN degree >= 70 AND degree < 80 THEN '中'
WHEN degree >= 60 AND degree < 70 THEN '几个'
ELSE '不及格'
END
FROM student, cousce, score WHERE student.sno = score.sno AND course.cno = score.cno ORDER BY course.cno, sname;``````

``````DECLATE @avg_boy float, @avg_girl float, @rate float;
SELECT @avg_boy = AVG(degree) FROM student, score WHERE student.sno = score.sno AND ssex = '男';
SELECT @avg_girl = AVG(degree) FROM student, score WHERE student.sno = score.sno AND ssex = '女';
SET @rate = @avg_boy / @ avg_girl;
IF(@rate > 1.3)
PRINT '男生比女生成绩高多了'
ELSE IF(@rate < 0.8)
PRINT '女生比男生成绩高多了'
ELSE
PRINT '男生与女生成绩差不多'``````

``````create table xsrq
(spno char(4) default('3741'),
spname char(20) default('苹果MacBook Pro '),
scrq date)``````
``````DECLARE @i INT;
WHILE @i < 20
BEGIN
INSERT INTO xsrq (scrq) VALUES (DATEADD("DAY", @i, GETDATE()));
SET @i = @i +1;
END
SELECT * FROM sxsrq; # 显示插入数据``````