《SQL编程练习题》由会员分享，可在线阅读，更多相关《SQL编程练习题（8页珍藏版）》请在金锄头文库上搜索。
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 NULL
2、 是否是周六或周日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
3、 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()-YE
5、AR(出生日期) AS 年龄 FROM worker（2）SELECT depart.部门名,count(*) AS 党员人数 FROM worker,depart（3） 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月
6、工资FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号= salary.职工号 AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2（6）SELECT depart.部门名, AVG(salary.工资) AS 平均工资FROM worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号= salary.职工号 （7）SELECT depart.部门名, AVG(salary.工资) AS 平均工资FRO
7、M worker,depart,salaryWHERE worker.部门号=depart.部门号 AND worker.职工号= salary.职工号 HAVING AVG(salary.工资)1200（8）. USE factory GO WHEN 财务处 THEN 管理部门WHEN 人事处 THEN 管理部门WHEN 市场部 THEN 市场部门END AS 部门类型FROM worker,departGO2在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：（1）在worker表中的“部门号”列上创建一个非聚集索引，若该索引已存在，则删除后重建。（2）在sal
8、ary表的“职工号”和“日期”列创建聚集索引，并且强制惟一性。答案：（1）SET NOCOUNT OFF USE factory IF EXISTS(SELECT name FROM sysindexes WHERE name=depno GO CREATE INDEX depno ON worker(部门号) GO(2) SET NOCOUNT OFF USE factory IF EXISTS(SELECT name FROM sysindexes WHERE name=no_date GO CREATE UNIQUE CLUSTERED INDEX no_date ON salary(职
9、工号,日期) GO3在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：（1）建立视图View1，查询所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。（2）建立视图View2，查询所有职工的职工号、姓名和平均工资。（3）建立视图View3，查询各部门名和该部门所有职工平均工资。（4）显示视图View3的定义答案：（1）USE factory GO WHERE TABLE_NAME=View1) DROP VIEW View1 GO CREATE VIEW View1 AS SELECT TOP 15 worker.职工号,worker.姓名
10、,depart.部门名,salary.工资 AS 2004年2月工资 FROM worker,depart,salary AND YEAR(salary.日期)=2004 AND MONTH(salary.日期)=2 GO SELECT * FROM View1 GO（2）USE factory GO WHERE TABLE_NAME=View2) DROP VIEW View2 GO CREATE VIEW View2 AS SELECT worker.职工号,worker.姓名,AVG(salary.工资) AS 平均工资 FROM worker,salary GO SELECT * FR
11、OM View2 GO（3）USE factory GO WHERE TABLE_NAME=View3) DROP VIEW View3 GO CREATE VIEW View3 AS SELECT depart.部门名, AVG(salary.工资) AS 平均工资 FROM worker,depart,salary GO SELECT * FROM View3 GO（4）USE factory GO EXEC sp_helptext View3 GO4在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：(1) 实施worker表的“性别”字段默认值为“男”的约束
12、。(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 CONSTRAI
13、NT 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,（6） ALTER TABLE worker DROP CONSTRAINT default_sex（7）EXEC sp_unbindrule DROP RULE sex5在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：(1) 创建一个为worker表添加职工记录的存储过程Addworker。(2) 创建一个存储过程Delworker删除worker表中指定职工号的记录。(3) 显示存储过程Delwor
