SQL编程练习题

练习题1数是0消息“nottoDIVIDEBYZERO0则显示结果。21到100的累加和。3EMP表中工资最高及工资最低的员工的在之后标出“工资最高”或“工资最低”字样。4、创建一个函数Emp_Avg5、创建表jobdayDayIDnvarchar(50)期格式yyyyMMddWeeknvarchar(1)NOTNULLIsJobDayintNOTNULL01jobday表中写入该年对应isjobday列中使用0或1进行标识。6雇佣的各项工作每年的雇佣job_id199519961997199819992000----------------------------------------------------------------------------AC_ACCOUNT000000AC_MGR000000AD_ASST000000AD_PRES000000AD_VP000000FI_ACCOUNT002110FI_MGR000000HR_REP000000IT_PROG001110MK_MAN010000MK_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,departWHEREworker.党员否=1ANDworker.部门号=depart.部门号GROUPBYdepart.部门名（3）．SELECTworker.姓名,salary.工资FROMworker,salaryWHEREworker.职工号=salary.职工号ANDYEAR(salary.日期)=2004ANDMONTH(salary.日期)=1（4）.SELECT职工号,,AVG(工资)AS‘平均工资’FROMsalaryGROUPBY职工号（5）．SELECTworker.职工号,worker.姓名,depart.部门名,salary.工资AS‘2004年2月工资’FROMworker,depart,salaryWHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号ANDYEAR(salary.日期)=2004ANDMONTH(salary.日期)=2ORDERBYworker.部门号（6）．SELECTdepart.部门名,AVG(salary.工资)AS‘平均工资’FROMworker,depart,salaryWHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号GROUPBYdepart.部门名（7）．SELECTdepart.部门名,AVG(salary.工资)AS‘平均工资’FROMworker,depart,salaryWHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号GROUPBYdepart.部门名HAVINGAVG(salary.工资)1200（8）.USEfactoryGOSELECTworker.职工号,worker.姓名CASEdepart.部门名WHEN‘财务处’THEN‘管理部门’WHEN‘人事处’THEN‘管理部门’WHEN‘市场部’THEN‘市场部门’ENDAS‘部门类型’FROMworker,departWHEREworker.部门号=depart.部门号GO2．在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：（1）在worker表中的“部门号”列上创建一个非聚集索引，若该索引已存在，则删除后重建。（2）在salary表的“职工号”和“日期”列创建聚集索引，并且强制惟一性。答案：（1）SETNOCOUNTOFFUSEfactoryIFEXISTS(SELECTnameFROMsysindexesWHEREname=’depno’DROPINDEXworker.depnoGOCREATEINDEXdepnoONworker(部门号)GO(2)SETNOCOUNTOFFUSEfactoryIFEXISTS(SELECTnameFROMsysindexesWHEREname=’no_date’DROPINDEXsalary.no_dateGOCREATEUNIQUECLUSTEREDINDEXno_dateONsalary(职工号,日期)GO3．在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：（1）建立视图View1，查询所有职工的职工号、姓名、部门名和2004年2月份工资，并按部门名顺序排列。（2）建立视图View2，查询所有职工的职工号、姓名和平均工资。（3）建立视图View3，查询各部门名和该部门所有职工平均工资。（4）显示视图View3的定义答案：（1）USEfactoryGOIFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWSWHERETABLE_NAME=’View1’)DROPVIEWView1GOCREATEVIEWView1ASSELECTTOP15worker.职工号,worker.姓名,depart.部门名,salary.工资AS‘2004年2月工资’FROMworker,depart,salaryWHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号ANDYEAR(salary.日期)=2004ANDMONTH(salary.日期)=2ORDERBYworker.部门号GOSELECT*FROMView1GO（2）USEfactoryGOIFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWSWHERETABLE_NAME=’View2’)DROPVIEWView2GOCREATEVIEWView2ASSELECTworker.职工号,worker.姓名,AVG(salary.工资)AS‘平均工资’FROMworker,salaryWHEREworker.职工号=salary.职工号GROUPBYworker.职工号,worker.姓名GOSELECT*FROMView2GO（3）USEfactoryGOIFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.VIEWSWHERETABLE_NAME=’View3’)DROPVIEWView3GOCREATEVIEWView3ASSELECTdepart.部门名,AVG(salary.工资)AS‘平均工资’FROMworker,depart,salaryWHEREworker.部门号=depart.部门号ANDworker.职工号=salary.职工号GROUPBYdepart.部门名GOSELECT*FROMView3GO（4）USEfactoryGOEXECsp_helptext‘View3’GO4．在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：(1)实施worker表的“性别”字段默认值为“男”的约束。(2)实施salary表的“工资”字段值限定在0～9999的约束。(3)实施depart表的“部门号”字段值惟一的非聚集索引的约束。(4)为worker表建立外键“部门号”，参考表depart的“部门号”列。(5)建立一个规则sex：@性别='男'OR@性别='女'，将其绑定到worker表的“性别”列上。(6)删除(1)小题所建立的约束。(7)解除(5)小题所建立的绑定并删除规则sex。答案：（1）ALTERTABLEworkerADDCONSTRAINTdefault_sexDEFAULT‘男’FOR性别（2）ALTERTABLEsalaryADDCONSTRAINTcheck_salaryCHECK(工资0AND工资9999)（3）ALTERTABLEdepartADDCONSTRAINTunique_departUNIQUENONCLUSTERED(部门号)（4）ALTERTABLEworkerADDCONSTRAINTFK_worker_noFOREIGNKEY(部门号)REFERENCESdepart(部门号)（5）CREATERULEsexAS@性别='男'OR@性别='女'EXECsp_bindrule‘sex’,’worker.性别’（6）ALTERTABLEworkerDROPCONSTRAINTdefault_sex（7）EXECsp_unbindrule’worker.性别’DROPRULEsex5．在前面建立的factory数据库上，用Transact-SQL语句完成下列各题：(1)创建一个为worker表添加职工记录的存储过程Addworker。(2)创建一个存储过程Delworker删除worker表中指定职工号的记录。(3)显示存储过程Delworker。(4)删除存储过程Addworker和Delworker。答案：（1）USEfactoryGOCREATEPROCEDUREAddworker@noint=NULL,@namechar(10)=NULL,@sexchar(2)=NULL,@birthdaydatetime=NULL,@nabit=NULL,@wtimedatetime=NULL,@depnoint=NULLASIF@noISNULLOR@nameISNULLOR@sexISNULLOR@birthdayISNULLOR@depnoISNULLBEGINPRINT‘请重新输入该职工信息!’PRINT‘你必须提供职工号、姓名、性别、出生日期、部门号’RETURNENDBEGINTRANSACTIONINSERTINTOworkerVALUES(@no,@name,@sex,@birthday,@na,@wtime,@depno)IF@@error0BEGINROLLBACKTRANRETURNENDCOMMITTRANSACTIONPRINT‘职工’+@name+’的信息成功添加到表worker中’执行下列语句，可验证存储过程的正确性：USEfactoryGOAddwoeker20,’陈立’,’女’,’55/03/08’,1,’75/10/10’,4GOSELECT*FROMworkerGO（2）USEfactoryGOCREATEPROCEDUREDelworker@noint=NULLASIF@noISNULLBEGINPRINT‘必须输入职工号！’RETURNENDBEGINTRANSACTIONDELETEFROMw