团队项目要求：

自由组建3~6人的团队（给团队起个有创意的名称，设计自己团队LOGO，团队成员名单，选择一个项目（可自己命题，经老师同意），团队负责人，任务分工，等），完成一个项目设计与开发。若条件可以，建议采用高级语言或开发工具（如：JAVA等或H5等）完成综合设计开发。

完成时提交：数据库应用系统设计文档，项目团队演示小录像及相关照片、截屏等。

《SQL数据库设计与开发实训》示例：

    设计《我班同学数据库》

经初步分析，现有关系数据库如下：

数据库名：我班同学数据库

同学表(学号 char(6)，姓名，性别，出生日期，民族，身份证号，宿舍号)

宿舍表(宿舍号 char(6)，宿舍电话,可住人数,已住人数)

健康表([健康码] varchar(18) ,[日期] [datetime] ,[体温] float ,[体测地点] varchar(50) ,[备注] ,[报告] )

（项目团队可以考虑增加数据库表及其应用功能）

用SQL语言实现下列功能的sql语句代码并上机调试完成实训：

1.创建数据库[我班同学数据库]代码。

参考：

create database [我班同学数据库]

go

use [我班同学数据库]

go

2.创建数据表[宿舍表]代码；

   宿舍表(宿舍号char(6)，宿舍电话,可住人数,已住人数)

   要求使用：主键(宿舍号)、宿舍电话：以8786开头的8位电话号码

创建数据表[健康表]代码；

健康表([健康码] varchar(18) ,[日期] [datetime] ,[体温] float ,[体测地点] varchar(50) ,[备注]  ,[报告] )     说明：[健康码]可以是学号或身份证号，等。

参考;

create table 宿舍表

([宿舍号] char(6) primary key,

[宿舍电话] char(7) check([宿舍电话] like '633[0-9][0-9][0-9][0-9]') ,

[可住人数]  int ,

[已住人数]  int )

create table 健康表

([健康码] varchar(18) ,

[日期] [datetime] ,

[体温] float ,

[体测地点] varchar(50) ,

[备注]  text ,

[报告] [nvarchar](50) NULL )

INSERT INTO [dbo].[健康表]([健康码] ,[日期] ,[体温] ,[体测地点] ,[备注])  VALUES    (190501,GETDATE(),36.7,'广州从化','正常')

3.创建数据表[同学表]代码；

   同学表(学号char(6)，姓名，性别，出生日期，民族，身份证号，宿舍号)

   要求使用：主键(学号)、外键(宿舍号)、默认(民族)、非空(民族，姓名，年龄)、唯一(身份证号)、检查(性别)

参考：

create table 同学表

(学号 char(6) primary key,

姓名 nchar(4)not null,

性别 nchar(1)check(性别 in ('男', '女')),

出生日期 datetime,

民族 nchar(8)default '汉族' not null,

身份证号 char(18)unique,

宿舍号 char(6)references 宿舍表(宿舍号)

)

4.将下列宿舍信息添加到宿舍表的代码

       宿舍号         宿舍电话   可住人数,已住人数

        1-101         87861235       6     0

        1-102         87865566       6     0

        2-201         87866688       6     0

参考：insert 宿舍表 ([宿舍号],[宿舍电话] ,[可住人数],[已住人数]) values('1-101', '6331157',6,0)

        修改 宿舍号为1-101的 宿舍电话：87863366

        删除  宿舍号为1-106的 宿舍信息

输入住进1-101宿舍的学生信息信，并同时修改对应宿舍已住人数。

例：INSERT INTO [我班同学数据库].[dbo].[同学表]

           ([学号],[姓名],[性别],[出生日期] ,[民族],[身份证号],[宿舍号])

     VALUES   (190501,'张小明','男','2000-02-15','汉族','4401061234555669', '1-101')

update宿舍表  set 已住人数=已住人数+1 where 宿舍号='1-101'

按上面要求，输入6个学生的信息，并安排住进适合的房间。

     模拟输入若干健康表的记录，【健康码]可以是学号或身份证号。注意与输入学生的数据信息相对应。

参考：

INSERT INTO [我班同学数据库].[dbo].[同学表]

           ([学号],[姓名],[性别],[出生日期] ,[民族],[身份证号],[宿舍号])

     VALUES

(190503,'张小芳','女','1999-03-25','汉族','4401061234566669', '1-102')

update宿舍表  set 已住人数=已住人数+1 where 宿舍号='1-102'

INSERT INTO [dbo].[健康表]([健康码] ,[日期] ,[体温] ,[体测地点] ,[备注])  VALUES    (190502,GETDATE(),36.6,'广州从化','正常')

5.创建视图[同学表视图]代码；

   同学表视图(学号, 姓名, 性别, 年龄, 民族, 身份证号, 宿舍号, 宿舍电话)

参考：

create view [同学表视图] as

select 学号, 姓名, 性别, YEAR(GETDATE())-YEAR(出生日期) AS 年龄, 民族, 身份证号, 同学表.宿舍号, 宿舍电话

from 同学表,宿舍表

where 同学表.宿舍号=宿舍表.宿舍号

6.从同学表视图中查询姓张的男同学的姓名、性别、年龄、宿舍电话。

参考： select 姓名, 性别, 年龄, 宿舍电话,宿舍号

 from同学表视图

 where 姓名 like '张%' and 性别='男'

7.从同学表视图中查询女同学的最大年龄、最小年龄、平均年龄。

参考：select 最大年龄 = max(年龄), 最小年龄 = min(年龄), 平均年龄 = avg(年龄)

 from同学表视图  where 性别='女'

  从健康表中查询各人的最高体温、最低体温、平均体温。

select [健康码] ,最高体温= max(体温),最低体温= min(体温),平均体温= avg(体温)

 from 健康表   GROUP BY [健康码]

8.创建带参数的存储过程[某宿舍同学]：姓名, 性别,年龄，宿舍电话

  执行此过程，查询'101'宿舍情况

参考：

create procedure [某宿舍同学]

@宿舍号varchar(6)

as

select姓名,性别,年龄,宿舍电话,宿舍号

from同学表视图

where宿舍号= @宿舍号

go

调试参考：execute [某宿舍同学] '1-101'

9. 创建[体温触发器] ，要符合要求。规则要求要与实际符合。

参考程序：

  CREATE TRIGGER [dbo].[体温触发器]

ON [dbo].[健康表]

FOR INSERT,UPDATE

AS

BEGIN

DECLARE @体温 float,@日期 datetime

SELECT @日期=日期, @体温=体温 FROM INSERTED

IF  @体温>37.3

PRINT  '华夏小信  警告！体温异常！！'+STR(@体温,6,3)

UPDATE健康表 

SET报告='警告！体温异常！！'   WHERE  体温>37.3 and @日期=日期

END

体验触发器

INSERT INTO [dbo].[健康表]([健康码] ,[日期] ,[体温] ,[体测地点] ,[备注])  VALUES    (190502,GETDATE(),37.99,'广州从化','？！')

10.  设计一个函数, 根据日期、体温产生转换报告，要符合要求。

参考：

CREATE  FUNCTION  [dbo].[转换报告](@体温 float,@日期 datetime)

RETURNS  CHAR(60)

AS

BEGIN

DECLARE @报告 CHAR(60),@报告日期 CHAR(30)

SET @报告日期=CONVERT(CHAR(20), @日期)

SELECT  @报告= CASE

 WHEN @体温>=39 THEN '体温严重异常！!  '+@报告日期

  WHEN @体温>=37.3 AND @体温<39 THEN '体温异常!  '+@报告日期

  WHEN @体温<37.3  THEN ' 正常 '

  WHEN @体温 IS NULL THEN'没有体测'

 END

RETURN (@报告)

 END

调试参考：

DECLARE  @体温  float,@日期 datetime

SET  @体温=37.6

SET @日期=GETDATE()

PRINT dbo.转换报告(@体温,@日期)

11.  创建 [dbo].[健康报告视图]，要符合要求。

参考：

CREATE  VIEW [dbo].[健康报告视图]

AS

select [健康码] ,最高体温= max(体温),最低体温= min(体温),平均体温= avg(体温), dbo.转换报告(体温,日期)  AS  报告

 from 健康表

 GROUP BY [健康码],dbo.转换报告(体温,日期)

12. 创建【输出健康报告表】储存过程，要符合要求。

参考：

CREATE  PROC  输出健康报告表

@JNUM VARCHAR(18)

AS

DECLARE @NUM VARCHAR(18),@NAME VARCHAR(12),@HMAX float,@HMIN  float ,@HAVG float, @报告 VARCHAR(60)

DECLARE Cr1 CURSOR  FOR

SELECT健康码,姓名,最高体温,最低体温,平均体温,报告

FROM [健康报告视图]  K, 同学表 T

WHERE K.健康码=T.学号  AND  K.健康码>=@JNUM

OPEN Cr1

PRINT SPACE(20)+'=============  大学生健康报告 ============='

PRINT '  '

PRINT '健康码   姓名         最高体温     最低体温    平均体温     报告（建议结论）'

PRINT REPLICATE('-',96)

FETCH NEXT FROM Cr1 INTO @NUM,@NAME ,@HMAX,@HMIN ,@HAVG , @报告

WHILE @@FETCH_STATUS=0

BEGIN

         PRINT@NUM+SPACE(3)+CONVERT(CHAR(6),@NAME)+SPACE(3)+STR(@HMAX)+SPACE(3)+STR(@HMIN)+SPACE(3)+STR(@HAVG )+SPACE(8)+ @报告

         PRINT REPLICATE('-',96)

         FETCH NEXT  FROM Cr1 INTO @NUM,@NAME ,@HMAX,@HMIN ,@HAVG , @报告

END

CLOSE Cr1

DEALLOCATE Cr1

EXEC  输出健康报告表 00000

13.设置一SQL身份验证的用户账户：登录名：U班主任，密码：888666，数据库用户名：U读者，权限：可查询本数据库中所有表、视图、内嵌表值函数的数据，执行所有的存储过程。请写出账户、权限设置的T_SQL脚本。

  参考：

exec sp_addlogin 'U班主任', '888', '我班同学数据库'

exec sp_grantdbaccess 'U班主任', 'U读者'

exec sp_addrolemember 'db_datareader', 'U读者'