SQL编程练习题

练习题1、分别创建存储过程和函数实现两个数相除并且显示结果如果第二个 数是0则显示消息“not to DIVIDE BY ZERO”不为0则显示结果。2、分别编写存储过程和函数计算1到100的累加和。3、分别创建存储过程和函数显示EMP表中工资最高及工资最低的员工的 姓名、编号并在之后标出“工资最高”或“工资最低”字样。4、创建一个函数Emp_Avg根据员工号返回员工所在部门的平均工资。5、创建表jobday表结构如下DayID nvarchar (50) 主键日期格式yyyyMMddWeek nvarchar (1) NOT NULL 星期几中文表示IsJobDay int NOT NULL 是否是周六或周日0表示不是1表示是编写存储过程实现根据参数值传入的年份向jobday表中写入该年对应 的每一天的日期及各日期所对应的是星期几并在isjobday列中使用0 或1进行标识。6、编写存储过程根据参数值传入的年份统计该年份以后被 雇佣的各项工作每年的雇佣人数。显示效果如下job_id 1995 1996 1997 1998 1999 2000- - - - - - -AC_ACCOUNT 0 0 0 0 0 0AC_MGR 0 0 0 0 0 0AD_ASST 0 0 0 0 0 0AD_PRES 0 0 0 0 0 0AD_VP 0 0 0 0 0 0FI_ACCOUNT 0 0 2 1 10FI_MGR 0 0 0 0 0 0HR_REP 0 0 0 0 0 0IT_PROG 0 0 1 1 1 0MK_MAN 0 1 0 0 0 0MK_REP 0 0 1 0 0 0.。程序设计题假设在factory数据库中已创建了如下3个表，（1）职工表worker，其结构为：职工号：int，姓名：char(8)，性别：char(2)，出生日期：datetime，党员否：bit，参加工作：datetime，部门号：int。（2）部门表depart，其结构为：部门号：int，部门名：char(10)。（3）职工工资表salary，其结构为：职工号：int，姓名：char(8)，日期：datetime，工资：decimal(6,1)。1.使用Transact-SQL语句完成如下各题：（1）显示所有职工的年龄。（2）求出各部门的党员人数。（3）显示所有职工的姓名和2004年1月份工资数。（4）显示所有职工的职工号、姓名和平均工资。（5）显示所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。（6）显示各部门名和该部门的所有职工平均工资。（7）显示所有平均工资高于1200的部门名和对应的平均工资。（8）显示所有职工的职工号、姓名和部门类型，其中财务处和人事处属管理部门，市场部属市场部门。答案：（1）SELECT 姓名,YEAR(GETDATE()-YEAR(出生日期) AS 年龄 FROM worker（2）SELECT depart.部门名,count(*) AS 党员人数 FROM worker,depart WHERE worker.党员否=1 AND worker.部门号=depart.部门号 GROUP BY depart.部门名（3）SELECT worker.姓名,salary.工资 FROM worker,salary WHERE worker.职工号= salary.职工号 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=1（4）. SELECT 职工号,AVG(工资) AS 平均工资FROM salaryGROUP BY 职工号（5） SELECT worker.职工号,worker.姓名, depart.部门名, salary.工资 AS 2004年2月工资FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号= salary.职工号 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2ORDER BY worker.部门号（6）SELECT depart.部门名, AVG(salary.工资) AS 平均工资FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号= salary.职工号 GROUP BY depart.部门名（7）SELECT depart.部门名, AVG(salary.工资) AS 平均工资FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号= salary.职工号 GROUP BY depart.部门名HAVING AVG(salary.工资)1200（8）. USE factory GO SELECT worker.职工号,worker.姓名CASE depart.部门名 WHEN 财务处 THEN 管理部门WHEN 人事处 THEN 管理部门WHEN 市场部 THEN 市场部门END AS 部门类型FROM worker,depart WHERE worker.部门号=depart.部门号GO2在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：（1）在worker表中的“部门号”列上创建一个非聚集索引，若该索引已存在，则删除后重建。（2）在salary表的“职工号”和“日期”列创建聚集索引，并且强制惟一性。答案：（1）SET NOCOUNT OFF USE factory IF EXISTS(SELECT name FROM sysindexes WHERE name=depno DROP INDEX worker.depno GO CREATE INDEX depno ON worker(部门号) GO(2) SET NOCOUNT OFF USE factory IF EXISTS(SELECT name FROM sysindexes WHERE name=no_date DROP INDEX salary.no_date GO CREATE UNIQUE CLUSTERED INDEX no_date ON salary(职工号,日期) GO3在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：（1）建立视图View1，查询所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。（2）建立视图View2，查询所有职工的职工号、姓名和平均工资。（3）建立视图View3，查询各部门名和该部门所有职工平均工资。（4）显示视图View3的定义答案：（1）USE factory GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=View1) DROP VIEW View1 GO CREATE VIEW View1 AS SELECT TOP 15 worker.职工号,worker.姓名,depart.部门名,salary.工资 AS 2004年2月工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2 ORDER BY worker.部门号 GO SELECT * FROM View1 GO（2）USE factory GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=View2) DROP VIEW View2 GO CREATE VIEW View2 AS SELECT worker.职工号,worker.姓名,AVG(salary.工资) AS 平均工资 FROM worker,salary WHERE worker.职工号=salary.职工号 GROUP BY worker.职工号,worker.姓名 GO SELECT * FROM View2 GO（3）USE factory GO IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=View3) DROP VIEW View3 GO CREATE VIEW View3 AS SELECT depart.部门名, AVG(salary.工资) AS 平均工资 FROM worker,depart,salary WHERE worker.部门号=depart.部门号 AND worker.职工号=salary.职工号 GROUP BY depart.部门名 GO SELECT * FROM View3 GO（4）USE factory GO EXEC sp_helptext View3 GO4在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：(1) 实施worker表的“性别”字段默认值为“男”的约束。(2) 实施salary表的“工资”字段值限定在09999的约束。(3) 实施depart表的“部门号”字段值惟一的非聚集索引的约束。(4) 为worker表建立外键“部门号”，参考表depart的“部门号”列。(5) 建立一个规则sex：性别=男 OR 性别=女，将其绑定到worker表的“性别”列上。(6) 删除(1)小题所建立的约束。(7) 解除(5)小题所建立的绑定并删除规则sex。答案：（1）ALTER TABLE worker ADD CONSTRAINT default_sex DEFAULT 男 FOR 性别（2）ALTER TABLE salary ADD CONSTRAINT check_salary CHECK(工资0 AND 工资9999)（3）ALTER TABLE depart ADD CONSTRAINT unique_depart UNIQUE NONCLUSTERED(部门号)（4）ALTER TABLE worker ADD CONSTRAINT FK_worker_no FOREIGN KEY (部门号) REFERENCES depart(部门号)（5）CREATE RULE sex AS 性别=男 OR 性别=女 EXEC sp_bindrule sex,worker.性别（6） ALTER TABLE worker DROP CONSTRAINT default_sex（7）EXEC sp_unbindrule worker.性别 DROP RULE sex5在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：(1) 创建一个为worker表添加职工记录的存储过程Addworker。(2) 创建一个存储过程Delworker删除worker表中指定职工号的记录。(3) 显示存储过程Delworker。(4) 删除存储过程Addworker和Delworker。答案： （1）USE factory GO CREATE PROCEDURE Addworker no int=NULL, name char(10)=NULL, sex char(2)=NULL, birthday datetime=NULL, na bit=NULL, wtime datetime=NULL, depno int=NULL AS IF no IS NULL OR name IS NULL OR sex IS NULL OR birthday IS NULL OR depno IS NULL BEGIN PRINT 请重新输入该职工信息! PRINT 你必须提供职工号、姓名、性别、出生日期、部门号 RETURN END BEGIN TRANSACTION INSERT INTO worker VALUES(no,name,sex,birthday,na,wtime,depno) IF error0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 职工+name+的信息成功添加到表worker中 执行下列语句，可验证存储过程的正确性： USE factory GO Addwoeker 20,陈立,女,55/03/08,1,75/10/10,4 GO SELECT * FROM worker GO （2）USE factory GO CREATE PROCEDURE Delworker no int=NULL AS IF no IS NULL BEGIN PRINT 必须输入职工号！ RETURN END BEGIN TRANSACTION DELETE FROM worker WHERE 职工号=no IF error0 BEGIN ROLLBACK TRAN RETURN END COMMIT TRANSACTION PRINT 成功删除职工号为+CAST(no AS CHAR(2)+的职工记录 执行下列语句，可验证存储过程的正确性： USE factory GO Delwoeker 20 GO SELECT * FROM worker GO （3）USE factory GO EXEC sp_helptext Delworker GO（4）USE factory GO IF EXISTS (SELECT name FROM sysobjects WHERE name=Addworker AND type=P DROP PROCEDURE Addworker GO IF EXISTS (SELECT name FROM sysobjects WHERE name=Delworker AND type=P DROP PROCEDURE Delworker GO 6在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：(1) 在表depart上创建一个触发器depart_update，当更改部门号时同步更改worker表中对应的部门号。(2) 在表worker上创建一个触发器worker_delete，当删除职工记录时同