SQL编程练习题

1、.练习题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 NUL

2、L 是否是周六或周日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

3、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)，

4、日期：datetime，工资：decimal(6,1)。1.使用Transact-SQL语句完成如下各题：（1）显示所有职工的年龄。（2）求出各部门的党员人数。（3）显示所有职工的姓名和2004年1月份工资数。（4）显示所有职工的职工号、姓名和平均工资。（5）显示所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。（6）显示各部门名和该部门的所有职工平均工资。（7）显示所有平均工资高于1200的部门名和对应的平均工资。（8）显示所有职工的职工号、姓名和部门类型，其中财务处和人事处属管理部门，市场部属市场部门。答案：（1）SELECT 姓名,YEAR(GETDATE()-Y

5、EAR(出生日期) 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

6、平均工资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,depa

7、rt,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.姓名CA

8、SE 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(SELEC

9、T 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数据库上，用T

10、ransact-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

11、 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_S

12、CHEMA.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.V

13、IEWS 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数据库上，用T

14、ransact-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

15、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_bindr

16、ule 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 G

17、O 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 E

18、ND 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 CREAT

19、E 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 wo

20、rker 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，当删除职工记录时同步删