SQL编程练习题.docx
- 文档编号:26511508
- 上传时间:2023-06-20
- 格式:DOCX
- 页数:14
- 大小:18.24KB
SQL编程练习题.docx
《SQL编程练习题.docx》由会员分享,可在线阅读,更多相关《SQL编程练习题.docx(14页珍藏版)》请在冰豆网上搜索。
SQL编程练习题
练习题
1、分别创建存储过程和函数实现两个数相除并且显示结果如果第二个数是0则显示消息“nottoDIVIDEBYZERO”不为0则显示结果。
2、分别编写存储过程和函数计算1到100的累加和。
3、分别创建存储过程和函数显示EMP表中工资最高及工资最低的员工的姓名、编号并在之后标出“工资最高”或“工资最低”字样。
4、创建一个函数Emp_Avg根据员工号返回员工所在部门的平均工资。
5、创建表jobday表结构如下DayIDnvarchar(50)主键日期格式yyyyMMddWeeknvarchar
(1)NOTNULL星期几中文表示IsJobDayintNOTNULL是否是周六或周日0表示不是1表示是编写存储过程实现根据参数值传入的年份向jobday表中写入该年对应的每一天的日期及各日期所对应的是星期几并在isjobday列中使用0或1进行标识。
6、编写存储过程根据参数值传入的年份统计该年份以后被雇佣的各项工作每年的雇佣人数。
显示效果如下
job_id199519961997199819992000
----------------------------------------------------------------------------
AC_ACCOUNT000000
AC_MGR000000
AD_ASST000000
AD_PRES000000
AD_VP000000
FI_ACCOUNT00211
0FI_MGR000000
HR_REP000000
IT_PROG001110
MK_MAN010000
MK_REP001000.
。
。
。
。
。
。
程序设计题
假设在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‘年龄’FROMworker
(2).SELECTdepart.部门名,count(*)AS‘党员人数’
FROMworker,depart
WHEREworker.党员否=1ANDworker.部门号=depart.部门号
GROUPBYdepart.部门名
(3).SELECTworker.姓名,salary.工资
FROMworker,salary
WHEREworker.职工号=salary.职工号ANDYEAR(salary.日期)=2004ANDMONTH(salary.日期)=1
(4).SELECT职工号,,AVG(工资)AS‘平均工资’
FROMsalary
GROUPBY职工号
(5).SELECTworker.职工号,worker.姓名,depart.部门名,salary.工资AS‘2004年2月工资’
FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号ANDYEAR(salary.日期)=2004ANDMONTH(salary.日期)=2
ORDERBYworker.部门号
(6).SELECTdepart.部门名,AVG(salary.工资)AS‘平均工资’
FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GROUPBYdepart.部门名
(7).SELECTdepart.部门名,AVG(salary.工资)AS‘平均工资’
FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GROUPBYdepart.部门名
HAVINGAVG(salary.工资)>1200
(8).USEfactory
GO
SELECTworker.职工号,worker.姓名
CASEdepart.部门名
WHEN‘财务处’THEN‘管理部门’
WHEN‘人事处’THEN‘管理部门’
WHEN‘市场部’THEN‘市场部门’
ENDAS‘部门类型’
FROMworker,depart
WHEREworker.部门号=depart.部门号
GO
2.在前面建立的factory数据库上,用Transact-SQL语句完成下列各题:
(1)在worker表中的“部门号”列上创建一个非聚集索引,若该索引已存在,则删除后重建。
(2)在salary表的“职工号”和“日期”列创建聚集索引,并且强制惟一性。
答案:
(1)SETNOCOUNTOFF
USEfactory
IFEXISTS(SELECTnameFROMsysindexesWHEREname=’depno’
DROPINDEXworker.depno
GO
CREATEINDEXdepnoONworker(部门号)
GO
(2)SETNOCOUNTOFF
USEfactory
IFEXISTS(SELECTnameFROMsysindexesWHEREname=’no_date’
DROPINDEXsalary.no_date
GO
CREATEUNIQUECLUSTEREDINDEXno_dateONsalary(职工号,日期)
GO
3.在前面建立的factory数据库上,用Transact-SQL语句完成下列各题:
(1)建立视图View1,查询所有职工的职工号、姓名、部门名和2004年2月份工资,并按部门名顺序排列。
(2)建立视图View2,查询所有职工的职工号、姓名和平均工资。
(3)建立视图View3,查询各部门名和该部门所有职工平均工资。
(4)显示视图View3的定义
答案:
(1)USEfactory
GO
IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWS
WHERETABLE_NAME=’View1’)
DROPVIEWView1
GO
CREATEVIEWView1
ASSELECTTOP15worker.职工号,worker.姓名,depart.部门名,
salary.工资AS‘2004年2月工资’
FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
ANDYEAR(salary.日期)=2004ANDMONTH(salary.日期)=2
ORDERBYworker.部门号
GO
SELECT*FROMView1
GO
(2)USEfactory
GO
IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWS
WHERETABLE_NAME=’View2’)
DROPVIEWView2
GO
CREATEVIEWView2
ASSELECTworker.职工号,worker.姓名,AVG(salary.工资)AS‘平均工资’
FROMworker,salary
WHEREworker.职工号=salary.职工号
GROUPBYworker.职工号,worker.姓名
GO
SELECT*FROMView2
GO
(3)USEfactory
GO
IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWS
WHERETABLE_NAME=’View3’)
DROPVIEWView3
GO
CREATEVIEWView3
ASSELECTdepart.部门名,AVG(salary.工资)AS‘平均工资’
FROMworker,depart,salary
WHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号
GROUPBYdepart.部门名
GO
SELECT*FROMView3
GO
(4)USEfactory
GO
EXECsp_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)ALTERTABLEworker
ADDCONSTRAINTdefault_sexDEFAULT‘男’FOR性别
(2)ALTERTABLEsalary
ADDCONSTRAINTcheck_salaryCHECK(工资>0AND工资<9999)
(3)ALTERTABLEdepart
ADDCONSTRAINTunique_departUNIQUENONCLUSTERED(部门号)
(4)ALTERTABLEworker
ADDCONSTRAINTFK_worker_noFOREIGNKEY(部门号)REFERENCESdepart(部门号)
(5)CREATERULEsexAS@性别='男'OR@性别='女'
EXECsp_bindrule‘sex’,’worker.性别’
(6)ALTERTABLEworker
DROPCONSTRAINTdefault_sex
(7)EXECsp_unbindrule’worker.性别’
DROPRULEsex
5.在前面建立的factory数据库上,用Transact-SQL语句完成下列各题:
(1)创建一个为worker表添加职工记录的存储过程Addworker。
(2)创建一个存储过程Delworker删除worker表中指定职工号的记录。
(3)显示存储过程Delworker。
(4)删除存储过程Addworker和Delworker。
答案:
(1)USEfactory
GO
CREATEPROCEDUREAddworker
@noint=NULL,
@namechar(10)=NULL,
@sexchar
(2)=NULL,
@birthdaydatetime=NULL,
@nabit=NULL,
@wtimedatetime=NULL,
@depnoint=NULL
AS
IF@noISNULLOR@nameISNULLOR@sexISNULLOR@birthdayISNULLOR@depnoISNULL
BEGIN
PRINT‘请重新输入该职工信息!
’
PRINT‘你必须提供职工号、姓名、性别、出生日期、部门号’
RETURN
END
BEGINTRANSACTION
INSERTINTOworkerVALUES(@no,@name,@sex,@birthday,@na,@wtime,@depno)
IF@@error<>0
BEGIN
ROLLBACKTRAN
RETURN
END
COMMITTRANSACTION
PRINT‘职工’+@name+’的信息成功添加到表worker中’
执行下列语句,可验证存储过程的正确性:
USEfactory
GO
Addwoeker20,’陈立’,’女’,’55/03/08’,1,’75/10/10’,4
GO
SELECT*FROMworker
GO
(2)USEfactory
GO
CREATEPROCEDUREDelworker
@noint=NULL
AS
IF@noISNULL
BEGIN
PRINT‘必须输入职工号!
’
RETURN
END
BEGINTRANSACTION
DELETEFROMworkerWHERE职工号=@no
IF@@error<>0
BEGIN
ROLLBACKTRAN
RETURN
END
COMMITTRANSACTION
PRINT‘成功删除职工号为’+CAST(@noASCHAR
(2))+’的职工记录’
执行下列语句,可验证存储过程的正确性:
USEfactory
GO
Delwoeker20
GO
SELECT*FROMworker
GO
(3)USEfactory
GO
EXECsp_helptextDelworker
GO
(4)USEfactory
GO
IFEXISTS(SELECTnameFROMsysobjects
WHEREname=’Addworker’ANDtype=’P’
DROPPROCEDUREAddworker
GO
IFEXISTS(SELECTnameFROMsysobjects
WHEREname=’Delworker’ANDtype=’P’
DROPPROCEDUREDelworker
GO
6.在前面建立的factory数据库上,用Transact-SQL语句完成下列各题:
(1)在表depart上创建一个触发器depart_update,当更改部门号时同步更改worker表中对应的部门号。
(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应职工的工资记录。
(3)删除触发器depart_update。
答案:
(1)USEfactory
GO
IFEXISTS(SELECTnameFROMsysobjects
WHEREtype=’TR’ANDname=’depart_update’
DROPTRIGGERdepart_update
GO
CREATETRIGGERdepart_updateONdepart
FORUPDATE
AS
DECLARE@olddepnoint,@newdepnoint
SELECT@olddepno=部门号FROMdeleted
SELECT@newdepno=部门号FROMinserted
UPDATEworker
SET部门号=@newdepno
WHERE部门号=@olddepno
GO
(2)USEfactory
GO
IFEXISTS(SELECTnameFROMsysobjects
WHEREtype=’TR’ANDname=’worker_delete’
DROPTRIGGERworker_delete
GO
CREATETRIGGERworker_deleteONworker
FORDELETE
AS
DECLARE@noint
SELECT@no=职工号FROMdeleted
DELETEFROMsalary
WHERE职工号=@no
GO
(3)USEfactory
GO
DROPTRIGGERdepart_update
GO
P
山有木兮木有枝,心悦君兮君不知。
____佚名《越人歌》
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 编程 练习题