《SQL编程练习题.docx》由会员分享，可在线阅读，更多相关《SQL编程练习题.docx（9页珍藏版）》请在三一文库上搜索。
1、练习题】、分别创建存储过程和函数实现两个数相除并旦显示结果如果第二个数是。则显示消息“not to DIVIDE BY ZERO w 不为0则显示结果。2、分别编写存储过程和函数计算】到】00的累加和。3、分别创建存储过程和函数显示EMP表中工资最高及工资最低的员工的、编号并在之后标 出工资最高”或“工资最低”字样。4、创建一个函数Emp.Avg根据员工号返回员工所在部门的平均工资。5、创建表jobdoy表结构如下DoylD nvorchor (50)主键 日期格式yyyyMMdd Week nvQrchor (1) NOT NULL星期几 中文表示IsJobDoy int NOT NULL是
2、否 是周六或周日0表示 不是1表示是编写存储过程实现根据参数值传入的年份向jobdoy表中写入该年对应的每一天 的日期及各日期所对应的是星期几并在isjobdoy列中使用0或】进行标识。6、编写存储过程根据参数值传入的年份统计该年份以后被雇佣的各项工作每年的雇佣人数。 显示效果如下jobjd 199519961997199819992000AC ACCOUNTAC_MGR AD_ASST AD.PRES AD_VP FLACCOUNT 0FLMGR HR_REP IT.PROG MK.MAN MK REP00000000000000000000000000000002 1 1000000000
3、0000011100 1 000000 1000.资料.程序设计题假设在factory数据库中已创建了如下3个表，(1)职工表worka,其结构为：职工号:int,： chor(8),性别：chor，出生日期：dotetime,党员否：bit,参加 工作： datetime,部门号：into(2)部门表deport,其结构为：部门号：int,部门名:char( 10)o(3)职工工资表SQlory,其结构为：职工号:in-chor(8),日期:datetime,工资:decimal(6J)o1 使用Tcnsoct-SQL语句完成如下各题：(1) ,显示所有职工的年龄。(2) .求出各部门的党员
4、人数。(3) .显示所有职工的和2004年1月份工资数。(4) ,显示所有职工的职工号、和平均工资。(5) .显示所有职工的职工号、部门名和2004年2月份工资，并按部门名顺序排列。(6) .显示各部门名和该部门的所有职工平均工资。(7) .显示所有平均工资高于1200的部门名和对应的平均工资。(8) ,显示所有职工的职工号、和部门类型，其中财务处和人事处属管理部门，市场部属市场 部门。答案：(1) SELECT,YEAR(G曰DATE()-YEAR(出生日期)AS年龄,FROM worker(2) .SELECT deport.部门名 zcount(*) AS “党员人数FROM worke
5、cdepartWHERE worker党员否二 1 AND worker部 | IT号二 depo什部 n 号GROUP BY deport.部门名(3) SELECT worker.zSQlory.工资FROM workecsalaryWHERE worker.职工号二 solory.职工号 AND YEAR(salary. S 期)二 2004 ANDMONTH(SQlory.0 期)二 1(4) .SELECT职工号“AVG (工资)AS平均工资FROM salaryGROUP BY职工号(5) - SELECT worke匚职工号，worker/deport部门名，solory.工资
6、AS2004 年 2 月工 资，FROM workerzdepartzsalaryWHERE worker部 i号二 deport部号 AND worker职工号二 solciry.职工号 ANDYEAR (salary. H 期)=2004 AND MONTH (salary. 0 期)二 2ORDER BY worker.部门号(6) .SELECT depart.部门名 rAVG(SQlQy.工资)AS 平均工资FROM workerzdepartzsalaryWHERE worke部 D 号二 deport部号 AND worker职工号二 SQIciry.职工号GROUP BY de
7、port.部门名(7) . SELECT deport.部门名AVG(SQlQry.工资)AS 平均工资FROM workerzdepartzsalaryWHERE worke部 D 号二 deport部号 AND worker职工号二 SQIciry.职工号GROUP BY deport 部门名HAVING AVG(solQy工资)】200(8) . USE factoryGOSELECT worker.职工号,worker.CASE deport.部门名WHEN 财务处THEN 管理部门WHEN 人事处THEN,管理部门WHEN 市场部THEN 市场部门END AS部门类型FROM wor
8、kecdepartWHERE worker部 n 号二 deport.部 门号GO2.在前面建立的factory数据库上，用TronsQct-SQL语句完成下列各题：(1)在worke表中的部门号”列上创建一个非聚集索引，若该索引已存在，则删除后 重 建。(2)在solory表的“职工号”和“日期”列创建聚集索引，并旦强制惟一性。 答案：(1) S曰 NOCOUNTOFFUSE factoryIF EXISTS (SELECT name FROM sysindexes WHERE name= depnoJDROP INDEX worker.depnoGOCREATE INDEX depno O
9、N worker(部门号)GO(2) SET NOCOUNT OFFUSE factoryIF EXISTS (SELECT name FROM sysindexes WHERE name= no.dateJDROP INDEX salary.no_doteGOCREATE UNIQUE CLUSTERED INDEX no.date ON solory)职工号，日期) GO(3) 前面建立的factory数据库上，用TronsQct-SQL语句完成下列各题：(1)建立视图Viewl,查询所有职工的职工号、部门名和2004年2月份工资，并按部门名 顺序排列。(2)建立视图View2,查询所有职
10、工的职工号、和平均工资。(3)建立视图View3,查询各部门名和该部门所有职工平均工资。(4)显示视图View3的定义答案：(1) USE factoryGOIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME= Viewl)DROP VIEW ViewlGOCREATE VIEW ViewlAS SELECT TOP 15 worker.职工号worker.,depart.部门名，salary.!资 AS u2004 年 2月工资FROM workerzdepartsalaryWHERE wor
11、ker部门号二 deport.部门号 AND worker.职工号二 solciry.职工号 ANDYEAR(salary.H 期)二 2004 AND MONTH (salary.0 期)二 2ORDER BY worker.部门号GOSELECT * FROM ViewlGO(2) USE factoryGOIF EXISTS (SELECT TABLE_NAME FROM INFORMATION.SCHEMA.VIEWS WHERETABLE.NAME=* View2)DROP VIEW View2GOCREATE VIEW View2AS SELECT worker.职工号,worke
12、r.zAVG(solory.工资)AS 平均工资FROM workerzsalaryWHERE worke.职工号=SQlQry.职工号GROUP BY worker.职工号worker.GOSELECT * FROM View2GO(3) USE factoryGOIF EXISTS (SELECT TABLE_NAME FROM INFORMATION.SCHEMA.VIEWS WHERE TABLE.NAME= View31)DROP VIEW View3GOCREATE VIEW View3AS SELECT depo什部门名，AVGfsolQry.工资)AS平均工资FROM work
13、ecdepart.salaryWHERE worker部门号二 deport部门号 AND worker职工号二 solory.职工号GROUP BY depent 部门名GOSELECT * FROM View3GO(4) USE factoryGOEXEC sp.helptext 4View3JGO4 .在前面建立的factory数据库上，用TronsQct-SQL语句完成下列各题：(1)实施worker表的“性别”字段默认值为“男”的约束。(2)实施SQlory表的“工资”字段值限定在09999的约束。(3)实施dep。什表的“部门号”字段值惟一的非聚集索引的约束。(4)为worker表
14、建立外键“部门号”，参考表depo什的“部门号”列。(5)建立一个规则sex:性别二男R性别二女将其绑定到worker表的“性别”列 o(6)删除(】)小题所建立的约束。(刀解除小题所建立的绑定并删除规则sex。答案：(1) ALTER TABLE workerADD CONSTRAINTdefault_sex DEFAULT ,男FOR 性别(2) ALTER TABLE salaryADD CONSTRAINT check.salary CHECK(工资0 AND 工资 V9999)(3) ALTER TABLE departADD CONSTRAINT uniqueAdepart UNI
15、QUE NONCLUSTERED喑B门号)(4) ALTER TABLE workerADD CONSTRAINT FK_worker_no FOR日GN KEY (部门号)REFERENCES deport (部 门号)(5) CREATE RULE sex AS 性别二男 OR 性别二女EXEC spAbindrule sexworker性另(6) ALTER TABLE workerDROP CONSTRAINT default.sex(7) EXEC sp.unbindrule 5 worker.性别 DROP RULE sex5 .在前面建立的factory数据库上，用Tronsoc
16、t-SQL语句完成下列各题：(1)创建一个为worker表添加职工记录的存储过程Addworker。(2)创建一个存储过程Delworker删除worker表中指定职工号的记录。(3)显示存储过程Delworker。(4)删除存储过程 Addworker 和 Delworker。答案：(1) USE factoryGOCREATE PROCEDURE Addworkerno int 二 NULLname char(10)=NULLsex char(2)=NULLbirthday datetime=NULLzno bit 二 NULL.wtime datetime二NULLdep no int
17、二 NULLASIF no IS NULL OR name IS NULL OR sex IS NULL OR birthday IS NULL OR depno IS NULLBEGINPRINT 请重新输入该职工信息!PRINT你必须提供职工号、性别、出生日期、部门号R 日 URNENDBEGIN TRANSACTIONINSERT INTO worker VALUESInoAame.sexAbirthday.na.wtimeAepno)IF error0BEGINROLLBACK TRANRETURNENDCOMMIT TRANSACTIONPRINT 职工+nome+J的信息成功添加到
18、表worker中执行下列语句，可验证存储过程的正确性：USE factoryGOAddwoeker20/ 立女/ 55/03/08W 75/10/10J4GOSELECT * FROM workerGO(2) USE factoryGOCREATE PROCEDURE Delworkerno int=NULLASIF no IS NULLBEGINPRINT必须输入职工号！RETURNENDBEGIN TRANSACTIONDELETE FROM worker WHERE 职工号二 noIF error0BEGINROLLBACK TRANRETURNENDCOMMIT TRANSACTION
19、PRINT成功删除职工号为+CAST(noASCHAR)+的职工记录执行下列语句，可验 证存储过程的正确性：USE factoryGODelwoeker 20GOSELECT * FROM workerGO USE factoryGOEXEC sp.helptext DelworkerGO USE factoryGOIF EXISTS (SELECT name FROM sysobjectsWHERE name- Addworker AND type= P?DROP PROCEDURE AddworkerGOIF EXISTS (SELECT name FROM sysobjectsWHERE
20、 name=- Delworker? AND type 二P?DROP PROCEDURE DelworkerGO6.在前面建立的factory数据库上，用TcnsQct-SQL语句完成下列各题：(1)在表depart 士创建一个触发器deporLupdote,当更改部门号时同步更改worker表中 对应的部门号。(2)在表worker 士创建一个触发器worker_delete,当删除职一匚记录时同步删除salary表中 对应职工的工资记录。册U除触发器deparLupdateo答案：(1) USE factoryGOIF EXISTS (SELECT name FROM sysobject
21、sWHERE type=* TR- AND name=5 depart_update?DROP TRIGGER deparfAupdateGOCREATE TRIGGER depart.update ON departFOR UPDATEASDECLARE olddepno int.n ewdepno intSELECT olddepno 二部门号 FROM deletedSELECT newdepno 二部门号 FROM insertedUPDATE workerSET 部门号二 newdepnoWHERE 部门号二 olddepnoGO(2) USE factoryGOIF EXISTS (SELECT name FROM sysobjectsWHERE type= TR AND name=5 workerAdelete?DROP TRIGGER workerAdeleteGOCREATE TRIGGER worker_delete ON workerFOR DELETEASDECLARE no intSELECT no 二职工号 FROM deletedDELETE FROM salaryWHERE职工号二noGO USE factoryGODROP TRIGGER depart_updateGO