当前位置:文档之家› SQL编程练习题

SQL编程练习题

SQL编程练习题
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 0 0 0 0 0 0

AD_ASST 0 0 0 0 0 0

AD_PRES 0 0 0 0 0 0

AD_VP 0 0 0 0 0 0

FI_ACCOUNT 0 0 2 1 1

0FI_MGR 0 0 0 0 0 0

HR_REP 0 0 0 0 0 0

IT_PROG 0 0 1 1 1 0

MK_MAN 0 1 0 0 0 0

MK_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 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

(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,

@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 @@error<>0

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 @@error<>0

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,当删除职工记录时同步删除salary 表中对应职工的工资记录。

(3) 删除触发器depart_update。

答案:

(1)USE factory

GO

IF EXISTS (SELECT name FROM sysobjects

WHERE type=’TR’ AND name=’depart_update’

DROP TRIGGER depart_update

GO

CREATE TRIGGER depart_update ON depart

FOR UPDATE

AS

DECLARE @olddepno int,@newdepno int

SELECT @olddepno=部门号 FROM deleted

SELECT @newdepno=部门号 FROM inserted

UPDATE worker

SET 部门号=@newdepno

WHERE 部门号=@olddepno

GO

(2)USE factory

GO

IF EXISTS (SELECT name FROM sysobjects

WHERE type=’TR’ AND name=’worker_delete’ DROP TRIGGER worker_delete

GO

CREATE TRIGGER worker_delete ON worker

FOR DELETE

AS

DECLARE @no int

SELECT @no=职工号 FROM deleted

DELETE FROM salary

WHERE 职工号=@no

GO

(3) USE factory

GO

DROP TRIGGER depart_update

GO

相关主题
相关文档 最新文档