SQL编程练习题分解

练习题 1、分别创建存储过程和函数实现两个数相除并且显示结果如果第二个 数是0则显示消息“not to DIVIDE BY ZERO”不为0则显示结果。

2、分别编写存储过程和函数计算1到100的累加和。 3、分别创建存储过程和函数显示EMP表中工资最高及工资最低的员工的 姓名、编号并在之后标出“工资最高”或“工资最低”字样。

4、创建一个函数Emp_Avg根据员工号返回员工所在部门的平均工资。

5、创建表jobday表结构如下DayID nvarchar (50) 主键日期格式yyyyMMddWeek 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 0 AC_MGR AD_ASST AD_PRES AD_VP

0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 1 0 0 0 0 0 0 1 0 0 0.

FI_ACCOUNT 0 0 2 1 1 0FI_MGR HR_REP IT_PROG MK_MAN 。。。。。。

MK_REP

程序设计题

假设在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 salary GROUP BY 职工号 （5）． SELECT 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.部门号 （6）．SELECT depart.部门名, AVG(salary.工资) AS ‘平均工资’

FROM worker,depart,salary

WHERE worker.部门号=depart.部门号 AND worker.职工号= salary.职工号

GROUP BY depart.部门名 （7）．SELECT depart.部门名, AVG(salary.工资) AS ‘平均工资’

FROM worker,depart,salary

WHERE 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.部门号 GO

2．在前面建立的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(职工号,日期) GO

3．在前面建立的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’ GO

4．在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：

(1) 实施worker表的“性别”字段默认值为“男”的约束。 (2) 实施salary表的“工资”字段值限定在0～9999的约束。 (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 sex

5．在前面建立的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,

本文来源：https://www.bwwdw.com/article/j95f.html