/* 表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
