13、ＳＱＬ综合应用学习1

/**/ /*
表1.Employee(人员表): 
EID Name Department Job Email Password 
10001 李明 SBB EG 
10003 李筠平 LUKE ITM 
11045 李洁 SBB EG 
10044 胡斐 MTD ETN 
10009 徐仲刚 SBB EG 
10023 李燕 SBB ETN 
20460 陆明生 MTD ETN 
20078 张青 MMM EG 
20001 李立 LUKE ETN 

表2.Training(培训表) 
CourseID EID Course Grade Order 
1 10001 T-SQL 60 
3 11045 Oracle 71 
2 20460 Java 34 
1 10003 T-SQL 59 
3 10001 Oracle 90 
2 20001 Java 12 
2 20078 Java 76 
2 10003 Java 78 
3 30001 Oracle 71 
3 20048 Oracle 36 

以下用T-SQL语句基于SQL   Server   2000完成 
1、 建立数据库training。 
2、 建表Employee与Training，分析表1和表2的结构，自行设置主键。 
3、 用SQL语句把上述两表的数据分别插入建好的表中，分别用一条SQL语句完成。 
4、 统计出各部门的人数和各部门姓“李”的人数，用一条SQL语句完成。 
5、 列出所有员工参加培训的情况，要求显示EID、Name、Department、Course，用一条SQL语句完成。 
6、 筛选出未参加培训的人员名单，按表1的格式显示，用一条SQL语句完成。 
7、 更新员工的Email，规则为：员工所在部门名称加员工姓名再加“@dhcc.com.cn”，用一条SQL语句完成。 
8、 列出所有各课成绩最高的员工信息，要求显示EID、Name、Department、Course、Grade，用一条SQL语句完成。 
9、 把所有表2有但表1没有的员工编号插入到表1中，用一条SQL语句完成。 
10、 分析表1与表2的关系，建立表1与表2之间的引用关系并实现级联操作。 
11、 用触发器实现第10题的相关操作。 
12、 统计列印各门课程成绩各分数段人数： 课程ID，课程名称，[100-85]，[84-70]，[69-60]，[ <60] 
13、 按各科不及格率的百分数从低到高和平均成绩从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用 "N行 "表示):   (就是分析哪门课程难) 
    课程ID,课程名称,平均成绩,不及格百分数 
14、根据成绩排出各课自己的名次，并更新到表2的Order列（允许出现并列的情况），用Procedure实现（需要用两种方式） 
a.用一条SQL语句完成。 
b.用游标完成。 
*/
-- solution 14
-- b 用游标完成.
DECLARE   @grade   INT , @course   VARCHAR ( 6 ), @id   CHAR ( 5 )
DECLARE  Training_cursor  CURSOR              -- 定义游标
FOR   SELECT  grade,course,id  FROM  Training         -- 赋值
OPEN  Training_cursor                     -- 打开游标,加载到内存.
FETCH   NEXT   FROM  Training_cursor
INTO   @grade , @course , @id
WHILE   @@FETCH_STATUS   =   0                 
BEGIN
     UPDATE  Training  SET  Order0 = ( SELECT   COUNT ( DISTINCT  Grade)  FROM  Training  WHERE  Grade >= @grade   AND  course = @course )  FROM  Training a  WHERE  id = @id
    FETCH   NEXT   FROM  Training_cursor                  
    INTO   @grade , @course , @id                       
END
CLOSE  Training_cursor                     -- 关闭游标
DEALLOCATE  Training_cursor                 -- 释放游标占用的内存.

-- a 一条语句完成.
UPDATE  Training  SET  Order0 = ( SELECT   COUNT ( DISTINCT  Grade)  FROM  Training  WHERE  Grade >= a.Grade  AND  course = a.course)  FROM  Training a
-- 以下为查询状态.
SELECT   * ,order00 = ( SELECT   COUNT ( DISTINCT  Grade)  FROM  Training  WHERE  Grade >= a.Grade  AND  course = a.course)  FROM  Training a  ORDER   BY  CourseID,order00
-- sql2005实现方式.
SELECT   * , DENSE_RANK()  OVER  (PARTITION  BY  CourseID  ORDER   BY  Grade  DESC ) AS   [ DENSE_RANK ]
FROM  Training
ORDER   BY  CourseID
-- solution 13
SELECT  CourseID [ 课程ID ] , MAX (Course) [ 课程名称 ] , AVG (Grade) [ 平均成绩 ] , CAST ( CAST ( SUM ( CASE   WHEN  Grade < 60   THEN   1   END ) AS   FLOAT ) / COUNT (CourseID) * 100   AS   VARCHAR ) + ' % '[ 不及格百分比 ]    FROM  Training  GROUP   BY  CourseID  ORDER   BY   [ 不及格百分比 ]   ASC  ,平均成绩  DESC

-- solution 12
SELECT  CourseID,Course,( CASE   WHEN  Grade  BETWEEN   85   AND   100   THEN  Grade  END ) [ 100-85 ] ,( CASE   WHEN  Grade  BETWEEN   84   AND   70   THEN  Grade  END ) [ 84-70 ] ,( CASE   WHEN  Grade  BETWEEN   60   AND   69   THEN  Grade  END ) [ 69-60 ] ,( CASE   WHEN  Grade  < 60   THEN  Grade  END ) [ <60 ]   FROM  Training  ORDER   BY  CourseID

-- solution 11
ALTER   TABLE   [ dbo ] . [ Training ]   DROP   CONSTRAINT  FK_training_Employee  -- 为了体现使用触发器,先删除回答10的外键约束.

ALTER   TRIGGER  Training_insert
ON  Training 
INSTEAD  OF   INSERT
AS  
BEGIN
     SET  NOCOUNT  ON  
     DECLARE   @eid   CHAR ( 5 ), @showMessage   VARCHAR ( 100 )
     SET   @eid = ( SELECT  eid  FROM  inserted)
     SET   @showMessage   =   @eid   + '  data is not exists '
     IF   NOT   EXISTS ( SELECT   1   FROM  Employee  WHERE  eid = @eid )
         RAISERROR  ( @showMessage ,  --  Message text.
            10 ,  --  Severity,
            1 ,  --  State,
           N ' number ' ,  --  First argument.
            @eid );
     ELSE
         INSERT   INTO  Training(CourseID,EID,Course,Grade)  SELECT  courseid,eid,course,Grade  FROM  inserted
END
-- 使用测试语句来测试.
INSERT   INTO  Training(courseid,eid,course,grade)  VALUES ( ' 1 ' , ' 10010 ' , ' T-sql ' , 80 )

SELECT   *   FROM  Training  -- 还是十条记录.没有插入成功.

-- solution 10
ALTER   TABLE  Employee  ALTER   COLUMN  eid  CHAR ( 5 )  NOT   NULL
GO  
ALTER   TABLE  Employee  ADD   CONSTRAINT   [ PK_employee_Employee ]   PRIMARY   KEY   CLUSTERED  
(
     [ eid ]   ASC
)
GO  
ALTER   TABLE  Training
ADD   CONSTRAINT  FK_training_eid  FOREIGN   KEY  (eid)
     REFERENCES  Employee (eid)
GO

-- solution 9
INSERT   INTO  Employee(eid)
SELECT  eid  FROM  Training a  WHERE   NOT   EXISTS  ( SELECT   1   FROM  Employee  WHERE  eid = a.eid)

-- solution 8
SELECT   MAX (a.eid)eid, MAX (a.NAME) [ Name ] , MAX (a.Department)Department,b.Course, MAX (b.Grade)Grade  FROM  Employee a  JOIN  Training b  ON  a.EID  =  b.EID  GROUP   BY  b.Course  ORDER   BY  Grade

-- solution 7
UPDATE  Employee  SET  Email = Department + NAME + ' @dhcc.com.cn '
-- solution 6
SELECT   *   FROM  Employee a  WHERE   NOT   EXISTS  ( SELECT   1   FROM  Training  WHERE  a.eid = eid)
SELECT   *   FROM  Employee  WHERE  eid  NOT   IN  ( SELECT   DISTINCT  EID  FROM  Training )

-- solution 5
SELECT  a.eid,a.NAME,a.Department,b.Course  FROM  Employee a  JOIN  Training b  ON  a.EID = b.EID  ORDER   BY  a.EID

--  solution 4
SELECT   *   FROM  
( SELECT   COUNT (NAME)合计人数, Department  FROM  Employee  GROUP   BY  Department)a  LEFT   JOIN  
( SELECT   COUNT (NAME)姓李的,Department  FROM  Employee  WHERE  NAME  LIKE   ' 李% ' GROUP   BY  Department ) b 
ON  a.Department = b.Department

-- solution 3
INSERT   INTO  Employee(eid,NAME,Department,job)
SELECT   ' 10001 ' ,  ' 李明 ' ,  ' SBB ' ,  ' EG '   UNION   ALL   SELECT   
' 10003 ' ,  ' 李筠平 ' ,  ' LUKE ' ,  ' ITM '   UNION   ALL   SELECT   
' 11045 ' ,  ' 李洁 ' ,  ' SBB ' ,  ' EG '   UNION   ALL   SELECT    
' 10044 ' ,  ' 胡斐 ' ,  ' MTD ' ,  ' ETN '   UNION   ALL   SELECT    
' 10009 ' ,  ' 徐仲刚 ' ,  ' SBB ' ,  ' EG '   UNION   ALL   SELECT    
' 10023 ' ,  ' 李燕 ' ,  ' SBB ' ,  ' ETN '   UNION   ALL   SELECT    
' 20460 ' ,  ' 陆明生 ' ,  ' MTD ' ,  ' ETN '   UNION   ALL   SELECT    
' 20078 ' ,  ' 张青 '  , ' MMM ' ,  ' EG '   UNION   ALL   SELECT    
' 20001 ' ,  ' 李立 ' ,  ' LUKE ' ,  ' ETN '
GO
INSERT   INTO  Training(CourseID, EID, Course, Grade) 
SELECT   1 ,  ' 10001 ' ,  ' T-SQL ' ,  60   UNION   ALL   SELECT   
3 ,  ' 11045 ' ,  ' Oracle ' ,  71   UNION   ALL   SELECT  
2 ,  ' 20460 ' ,  ' Java ' ,  34   UNION   ALL   SELECT  
1 ,  ' 10003 ' ,  ' T-SQL ' ,  59   UNION   ALL   SELECT  
3 ,  ' 10001 ' ,  ' Oracle ' ,  90   UNION   ALL   SELECT   
2 ,  ' 20001 ' ,  ' Java ' ,  12   UNION   ALL   SELECT   
2 ,  ' 20078 ' ,  ' Java ' ,  76   UNION   ALL   SELECT  
2 ,  ' 10003 ' ,  ' Java ' ,  78   UNION   ALL   SELECT  
3 ,  ' 30001 ' ,  ' Oracle ' ,  71   UNION   ALL   SELECT  
3 ,  ' 20048 ' ,  ' Oracle ' ,  36  

-- solution 2
USE  tranining
GO  
CREATE   TABLE  Employee(id  INT   IDENTITY ( 1 , 1 ),EID  CHAR ( 5 )  NOT   NULL  ,Name  NVARCHAR ( 4 ),Department  VARCHAR ( 8 ),Job  VARCHAR ( 3 ),Email  VARCHAR ( 50 ),Password  VARCHAR ( 100 ))
GO  
CREATE   TABLE  Training(id  INT   IDENTITY ( 1 , 1 ),CourseID  INT , EID  CHAR ( 5 ),Course  VARCHAR ( 6 ),Grade  INT  ,Order0  VARCHAR ( 50 ))

-- solution 1
CREATE   DATABASE  tranining 
ON  
(NAME = N ' traning_data ' ,
filename = N ' d:database raning_dat.mdf ' ,
size = 3072kb,
filegrowth = 1024kb)
LOG   ON  
(NAME = N ' traning_log ' ,
filename = N ' d:database raning_log.ldf ' ,
size = 1024kb,
filegrowth = 10 % )

 

 1、测试题:
实验一
1．根据程序写结果：
    declare @a BIGINT
    set @a=32768
    select @a,datalength(@a)
    go
要求：
(1)写出程序运行结果。
答:結果    32768,4
(2)把程序类型分别改为smallint、Tinyint、Bigint三种类型，求出程序运行结果。
答: SMALLINT,TINYINT 都会算术溢出,BIGINT 显示 32768,8
 
2.声明一个类型为REAL的局部变量，分别为其赋值为321.12、87654321.456，显示其结果。
答:
DECLARE @s REAL
SET @s=321.12
SELECT @s
SET @s=87654321.456 -- real 的 SQL-92 同义词为 float(24)。float(1~24) 的精度为7位数.超出显示为科学记数.
SELECT @s
GO

3.根据程序写结果：
    declare @c NVARCHAR(10)
    set @c='信息学院'
    select @c,datalength(@c)
    go
要求：
(1)写出程序运行结果。
答: 显示: '',1.因为如果未在数据定义或变量声明语句中指定 n，则默认长度为 1。如果在使用 CAST 和 CONVERT 函数时未指定 n，则默认长度为 30。
(2)将类型改为CHAR(2)、CHAR(3)、CHAR(5)、CHAR(10)结果为多少？
答:因为定义为char固定长度字符,而一个汉字占两个字节,所以显示这样.另数据类型长度分别显示为定义的指定长度.
显示值分别对应为,
信    2
信     3
信息     5
信息学院      10

(3)若将类型改为VARCHAR、VARCHAR(2)、VARCHAR(3)、VARCHAR(10)结果为多少？
答: 改可变长度字符,只存能放下的实际字符.
分别显示为
    0
信    2
信    2
信息学院    8

(4)若将类型改为NCHAR(2)、NCHAR(3)、NCHAR(5)、NCHAR(10)结果为多少？
答: 因为NCHAR(n),存储大小为两倍 n 字节.默认定义时为 1.
分别显示:
信息    4
信息学    6
信息学院     10
信息学院          20

(5)若将类型改为NVARCHAR、NVARCHAR(2)、NVARCHAR(10)结果为多少？
分别显示:
信    2
信息    4
信息学院    8
(6)比较CHAR、VARCHAR、NCHAR、NVARCHAR四种数据类型的区别。
答:    char定长字符,如果列的数据项大小一致,可使用.
varchar变长字符,如果列的数据项差异较大则使用,
NCHAR通用定长字符,用来存储通用性数据,如中文,英文.如果列数据项大小差不多,可使用
NVARCHAR通用变长字符,用来存储中文英文全,列数据项大小差异较大,则使用.

4．声明一个类型为日期时间型的变量，要求一：将今天的日期赋值给该变量，并显示其结果。要求二：将今天的日期接照月、日、年的格式赋值给该变量，并显示其结果。
答:
DECLARE @dt DATETIME
SET @dt=GETDATE()
SELECT @dt
SELECT MONTH(@dt)[月],DAY(@dt)[日],YEAR(@dt)[年]
GO

5．写结果：select lower('abc') +space(5) + rtrim(ltrim('你好!'))
答: abc     你好! (空格为连续5个)

6．写函数表达式和结果：计算字符串'SQL Server数据库管理系统'的长度
答: SELECT LEN('SQL Server数据库管理系统')

7．写函数表达式：求服务器当前的系统日期与时间
答:    SELECT GETDATE()

8．Mary的生日为1987/12/23日，请用日期函数计算Mary现在的年龄
答:    SELECT DATEDIFF(YEAR,'1987-12-23',GETDATE())

9．求：y= { x+10 x <0
        x   x=0
        x-10   x> 10
 
10．如果在student表中的男生多，则显示男生人数以及“男生多”信息。
SELECT CASE WHEN (SELECT COUNT(*)[num] FROM student WHERE sex=0)>
(SELECT COUNT(*)[num] FROM student)/2 THEN '男生多' END

11. 查看（课程号为'C801'）的平均分，如果分数等于或超过60分刚显示'数据结构平均分及格'和平均分值，否则显示'数据结构平均分不及格'.
SELECT CASE WHEN AVG(分值)>60 THEN '数据结构平均分及格' ELSE '数据结构平均分不及格' END,AVG(分值) FROM 课程 GROUP BY 课程号 HAVING 课程号='C801'


实验二
1、使用不带参数的存储过程
（1）创建一个存储过程my_proc，查询“学生表”中所有计算机系女生的学号、姓名、性别、年龄和所在院系。
（2）执行存储过程
（3）修改存储过程，使其能够查询计算机系女生的所有基本信息
答: (1)
CREATE PROC my_proc
AS
BEGIN
    SELECT 学号,姓名,性别,年龄,所在院系 FROM 学生表 WHERE 所在院系='计算机系'
END
(2) EXEC my_proc
(3)
ALTER PROC my_proc
AS
BEGIN
    SELECT * FROM 学生表 WHERE 所在院系='计算机系'
END

2、带输入参数的存储过程
（1）创建一个存储过程my_procsex，使其能够查询“学生表”中男学生或女学生的学号、姓名、性别、年龄和所在院系
（2）执行存储过程
答:
(1) CREATE PROC my_procsex (
@学号 VARCHAR(64)='',
@姓名 VARCHAR(64)='',
@性别 CHAR(2)='',
@年龄 int='',
@所在院系 VARCHAR(64)=''
)
AS
BEGIN
    SELECT * FROM 学生表 WHERE (学号=@学号 OR @学号='') AND (姓名=@姓名 OR @姓名='')...
END
(2) EXEC my_procsex

3、带输入/输出参数的存储过程
（1）创建一个存储过程my_procage，使其能够根据学生姓名，查询学生年龄。（考虑当学生不存在时给出提示信息）
（2）执行存储过程
答 (1)
ALTER PROC my_procage(@姓名 VARCHAR(16),@msg NVARCHAR(32) output)
AS
BEGIN
    IF (SELECT COUNT(*) FROM 学生表 WHERE 姓名=@姓名 OR @姓名 IS NULL)<1
    BEGIN
        SET @msg='此人员不存在!'
    END
    SELECT borndate FROM 学生表 WHERE 姓名=@姓名 OR @姓名 IS NULL
END
(2)
DECLARE @msg NVARCHAR(32)
EXEC my_procage '王五2',@msg OUTPUT
SELECT @msg