第三章上机练习

来源：互联网发布：ipad看美剧用什么软件编辑：程序博客网时间：2024/05/21 08:47

上机练习一：

DECLARE @tag nvarchar(1)  SET @tag = '★'PRINT @tagPRINT @tag + @tagPRINT @tag + @tag + @tagPRINT @tag + @tag + @tag + @tagPRINT @tag + @tag + @tag + @tag + @tagGO

上机练习二：

DECLARE @NO int                                          -- 学号SET @NO = 3DECLARE @date datetime                             -- 出生日期DECLARE @year int                                       -- 出生年份-- 获得学号是20011的学生姓名和年龄SELECT StudentName 姓名,               FLOOR(DATEDIFF(DY, BornDate, GETDATE())/365) 年龄   FROM student  WHERE StudentNo=@NO-- 查询输出比学号是20011的学生大1岁和小1岁的学生信息SELECT  @date=BornDate FROM Student   -- 使用SELECT赋值  WHERE StudentNo=@NOSET @year = DATEPART(YY, @date)             SELECT *  FROM Student    WHERE DATEPART(YY,BornDate) = @year + 1                 OR DATEPART(YY,BornDate) = @year - 1GO

上机练习三：

DECLARE @name nvarchar(50)      --姓名DECLARE @score decimal(5,2)       --分数SELECT TOP 1  @score=StudentResult,@name=stu.StudentName    FROM Result r   INNER JOIN Student stu ON r.StudentNo=stu.StudentNo   INNER JOIN Subject sub ON r.SubjectNo=sub.SubjectNo   WHERE r.StudentNo='20012' AND sub.SubjectName='Java Logic'   ORDER BY ExamDate DESCPRINT '学生姓名:' + @nameIF (@score > 85)  PRINT '考试等级:' + '优秀'ELSE IF (@score > 70)  PRINT '考试等级:' + '良好'ELSE IF (@score > 60)  PRINT '考试等级:' + '中等'ELSE  PRINT '考试等级:' + '差'

上机练习四：

DECLARE @date datetime  --考试时间DECLARE @subNO int      --课程编号SELECT @subNO=SubjectNo FROM SubjectWHERE SubjectName='C# OOP'SELECT  @date=max(ExamDate) FROM Result WHERE SubjectNo=@subNOPRINT '加分前学生的考试成绩如下：'SELECT 学号=StudentNo,成绩等级=CASE   WHEN StudentResult BETWEEN 0 AND 59 THEN '你要努力了！！！' WHEN StudentResult BETWEEN 60 AND 69 THEN '★' WHEN StudentResult BETWEEN 70 AND 79 THEN '★★' WHEN StudentResult BETWEEN 80 AND 89 THEN '★★★' ElSE '★★★★'                                END         FROM Result        WHERE SubjectNo=@subNO AND ExamDate=@date DECLARE @n intWHILE(1=1) --条件永远成立   BEGIN    SELECT @n=COUNT(*) FROM Result     WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<60 --统计不及格人数    IF (@n>0)       UPDATE Result SET StudentResult=StudentResult+2 FROM Result       WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<=98  --每人加2分    ELSE       BREAK  --退出循环  END

上机练习五：

USE MySchool  GOIF  EXISTS(SELECT * FROM  sysobjects  WHERE  name ='Admin')DROP TABLE AdminGOCREATE TABLE Admin(  --创建表[LoginId]  [nvarchar](50) NOT NULL,[LoginPwd] [nvarchar](50) NOT NULL)ALTER TABLE Admin    --添加主健约束ADD CONSTRAINT PK_Admin PRIMARY KEY (LoginId)GOINSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST1','123')    --插入数据INSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST2','123456') --插入数据GOUPDATE Admin SET [LoginPwd]='1234567' WHERE [LoginId]='TEST2' --更新数据GO

0 0