SQL实验实验至实验的答案

SQL实验实验41用select语句查询departments和salary表中的所有数据：selectsalary.*,departments.*fromsalary,departments2、查询departments中的departmentid：selectdepartmentidfromdepartmentsgo3、查询salary中的income，outcome：selectincome,outcomefromsalarygo4、查询employees表中的部门号，性别，要用distinct消除重复行：selectdistinct(departmentid),sexfromemployees5、查询月收入高于2000元的员工号码：selectemployeeidfromsalarywhereincome>2000go6、查询1970年以后出生的员工的姓名和住址：selectname,addressfromemployeeswherebirthday>1970go7、查询所有财务部的员工的号码和姓名：selectemployeeid,namefromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')go8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，2000~3000元显示为中等收入，3000元以上显示为高收入：selectname,address,casewhenincome-outcome<2000then'低收入'whenincome-outcome>3000then'高收入'else'中等收入'endas'收入等级'fromemployees,salarywhereemployees.employeeid=salary.employeeidgo9、计算salary表中员工月收入的评价数：selectavg(income)as'平均收入'fromsalary10、查找employees表中最大的员工号码：selectmax(employeeid)as'最大员工号码'fromemployees11、计算salary表中的所有员工的总支出：selectsum(outcome)as'总支出'fromsalary12、查询财务部雇员的最高实际收入：selectmax(income-outcome)fromsalary,employees,departmentswheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'go13、查询财务部雇员的最低实际收入：selectmin(income-outcome)fromsalary,employees,departmentswheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'go14、找出所用地址中含有“中山”的雇员的号码及部门号：selectemployeeid,departmentidfromemployeeswhereaddresslike'%中山％'go15、查找员工号码中倒数第二个数字为0的员工的姓名，地址和学历：selecteducation,address,namefromemployeeswhereemployeeidlike'%0_'go16、使用into字句，由表employees创建“男员工1”表，包括编号和姓名：selectemployeeid,nameinto男员工表fromemployeeswheresex='1'go17、用子查询的方法查找收入在2500元以下的雇员的情况：select*fromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereincome<2500)go18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名：SELECTNameFROMEmployeesWHEREEmployeeIDIN(SELECTEmployeeIDFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='研发部'))ANDInCome>ALL(SELECTInComeFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='财务部'))))19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：selectnamefromemployeeswhereBirthday<all(selectbirthdayfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='研发部'))20、查询每个员工的情况及其薪水的情况：selectemployees.*,departments.departmentnamefromemployees,departmentswhereemployees.departmentid=departments.departmentid21、使用内连接方法查找不在财务部工作的所有员工信息：selectemployees.*fromemployeesinnerjoindepartmentsonemployees.departmentid=departments.departmentidwheredepartmentname!='财务部'22、使用外连接方法查找出所有员工的月收入：selectemployees.*,salary.incomefromemployeesjoinsalaryonemployees.employeeid=salary.employeeid23、查找财务部雇员的最高收入：selectmax(income)fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部'))24、查询财务部雇员的最高实际收入：selectmax(income-outcome)fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部'))25、统计财务部收入在2500元以上的雇员人数：selectcount(employeeid)fromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')andemployeeidin(selectemployeeidfromsalarywhereincome>2500)26、按部门列出在该部门工作的员工的人数：selectdepartmentid,count(*)as人数fromemployeesgroupbydepartmentid27、按员工的学历分组：selecteducation,count(*)as人数fromemployeesgroupbyeducation28、按员工的工作年份分组，统计年份人数：selectworkyear,count(*)as人数fromemployeesgroupbyworkyear29、按各雇员的情况收入由低到高排列：selectemployees.*,salary.incomefromemployees,salarywhereemployees.employeeid=salary.employeeidorderbyincome30、将员工信息按出生时间从小到大排列：select*fromemployeesorderbybirthday31、在orderby字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从大到小排列：selectname,sex,workyear,income-outcomefromsalary,employeeswheresalary.employeeid=employees.employeeidorderbyincome-outcomedesc视图部分1、创建view1：Createviewview1asselectemployees.employeeid,name,departmentname,(income-outcome)ascomefromemployees,departments,salarywhereemployees.departmentid=departments.departmentidandemployees.employeeid=salary.employeeid2、查询视图employeeid：3、向视图view1中插入一行数据：insertintoviewlvalues('111111','谎言'，'1','30000')4、查看视图(没有影响)基本表：实验51、定义一个变量，用于描述YGGL数据库的salary表中000001号员工的实际收入，然后查询该变量：declare@hyintset@hy=(selectincome-outcomefromsalarywhereemployeeid='000001')select@hy2、使用运算符“>”：selectname

fromemployeeswherebirthday>'1974-10-10'3、判断姓名为“王林”的员工实际收入是否高于3000元，如果是则显示“高收入”，否则显示“收入不高于3000”if((selectincome'刘明'刘明'from'刘明'刘明'wheresalary.employeeid==')>3000)selectincomeas'高收入'fromsalary,employeeswheresalary.employeeid==elseselect'收入不高于'4、使用循环输出一个“*”三角形：declare@iintdeclare@jintset@j=20set@i=1while@i<@jbeginprint(space((@j-@i)/2)+replicate('*',@i))set@i=@i+2end4、按部门进行分类，使用if语句实现：Createfunctionhy1(@departmentid1char(3))returnschar(10)asbegindeclare@hy1char(10)if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='1')set@hy1='财务部'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='2')set@hy1='人力资源部'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='3')set@hy1='经理办公室'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='4')set@hy1='研发部'if((selectdepartmentidfromdepartmentswhere@departmentid1=departmentid)='5')set@hy1='市场部'return@hy1endselectemployeeid,name,address,dbo.hy1(departmentid)fromemployeesselectemployeeid,name,address,casedepartmentidwhen1then'财务部'when2then'人力资源部'when3then'经理办公室'when4then'研发部'when5then'市场部'endas部门号fromemployees6、自定义一个函数，计算一个数的阶层createfunctionhy(@hy2int)returnsintasbegindeclare@iintset@i=@hy2declare@jintset@j=1while@i>1beginset@j=@j*@iset@i=@i-1endreturn(@j)enddeclare@hintexec@h=dbo.hy4select@has'jiecheng'7、/*生成随机数*/selectrand()8、/*平方*/selectsquare(12)9、/*求财务部收入最高的员工姓名*/selectmax(name)fromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')))selectavg(income)as'平均收入'fromsalary/*聚合函数与groupby一起使用*/selectworkyear,count(*)as人数fromemployeesgroupbyworkyear/*将字符组成字符串*/selectchar(123)/*返回字符串左边开始的个字符*/selectleft('abcdef',2)/*返回指定日期时间的天数*/selectday(birthday)fromemployeeswhereemployeeid='010000'/*获取当前时间*/selectgetdate()实验61、创建索引：createuniqueindexhuangyanonemployees(employeeid)2、/*用createindex语句创建主键*/3、重建表employees中employeeid歹U上的索弓丨alterindexhuangyanonemployeesrebuild4、删除索弓：5、创建一个新表，使用一个复合歹作为主键，作为表的约束，并为其命名createtableemployees5(employeeidchar(6)notnull,namechar(5)notnull,sextinyint,educationchar(4),constraintyanprimarykey(employeeid,name))为新表添加一歹：altertableemployees5addaddresschar(10)6、创建新表student，性别只能包含男或女：createtablestudent(号码char(6)notnull,性另别char(2)notnullcheck(性别in('男','女')))7、创建新表：createtableemployees7(学号char(10)notnull,出生日期datetimenotnullcheck(出生日期>'1980-01-01'))8、创建一个规则：9，创建salary2：createtablesalary2(employeeidchar(6)notnullprimarykey,incomefloatnotnull,outcomefloatnotnull,foreignkey(employeeid)referencessalary(employeeid)onupdatecascadeondeletecascade)10、添加一个外键，salary与employees有相关记录，贝U拒绝更新employees：altertablesalaryaddconstraintkc_forforeignkey(employeeid)referencesemployees(employeeid)ondeletenoactiononupdatenoaction实验71、工作年份大于6时，跟换科室到经理办公室(根据员工)：CreatePROCUpdateDeptByYear(@EmpIdchar(6))ASBEGINDECLARE@yearintSELECT@year=WorkYearFromEmployeesWHEREEmployeeID=@EmpIdIF(@year>6)UPDATEEmployeesSETDepartmentID='3'WHEREEmployeeID=@EmpIdENDEXECUpdateDeptByYear'020010'SELECT*FROMEmployeesWHEREEmployeeid='020010'2、根据每个员工的学历将收入提高元：CREATEPROCUpdateInComeByEdu@Employeeidchar(6)ASBEGINUPDATESalarySETInCome=InCome+500FROMSalaryLEFTJOINEmployeesONSalary.EmployeeID=Employees.EmployeeIDWHERESalary.Employeeid=@EmployeeidENDEXECUpdateInComeByEdu'020010'SELECT*FROMSalarywhereEmployeeID='020010'3、游标：CREATEPROCEDUREEmployees_biliASBEGINDECLARE@iFLOATDECLARE@jFLOATDECLARE@EducationCHAR(10)DECLAREEmployees_cursorCURSORFORSELECTEducationFROMEmployeesSET@i=0SET@j=0OPENEmployees_cursorFETCHEmployees_cursorINTO@EducationWHILE(@@FETCH_STATUS=0)BEGINIF(@Education!='大专')SET@i=@i+1SET@j=@j+1FETCHEmployees_cursorINTO@EducationENDCLOSEEmployees_cursorSELECT@iAS'本科及以上员工所占员工数'SELECT@jAS'员工总数'SELECT@i/@jAS'本科及以上员工所占比例'CLOSEEmployees_cursorENDEXECEmployees_bili4、使用命令的方式修改存储过程的定义：5、对于YGGL数据库，表Employees的EmployeelD歹U与表Salary的EmployeelD歹U应该满足参照的完整性规则，请用触发器实现两个表的参照完整性：CREATETRIGGERSalary_insertONSalaryFORINSERT,UPDATEASBEGINIF(SELECTEmployeeIDFROMINSERTED)NOTIN(SELECTEmployeeIDFROMEmployees)ROLLBACKENDCREATETRIGGEREmployeesupdateONEmployeesFORUPDATEASBEGINUPDATESalarySETEmployeeID=(SELECTEmployeeIDFROMINSERTED)WHEREEmployeeID=(SELECTEmployeeIDFROMDELETED)ENDCREATETRIGGEREmployeesdeleteONEmployeesFORDELETEASBEGINDELETEFROMSalaryWHEREEmployeeID=(SELECTEmployeeIDFROMDELETED)ENDINSERTINTOSalaryVALUES('000005',2000,1000)UPDATEEmployeesSETEmployeeID='000000'WHEREEmployeeID='990230'DELETEFROMEmployeesWHEREEmployeeID='000000'当修改表Employees时，若将Employees表中员工的工作时间增加1年，则将收入增加500，若增加2年则增加1000，依次增加。若工作时间减少则无变化：CREATETRIGGEREM_WORKYEARONEmployeesAFTERUPDATEASBEGINDECLARE@iINT,@jINTSET@i=(SELECTWorkYearFROMINSERTED)SET@j=(SELECTWorkYearFROMDELETED)IF(@i>@j)UPDATESalarySETInCome=InCome+(@i-@j)*500WHEREEmployeeIDIN(SELECTEmployeeIDFROMINSERTED)ENDUPDATEEmployeesSETWorkYear=7WHEREEmployeeid='990230'SELECT*FROMEmployeesWHEREEmployeeid='990230'创建UPDATE触发器，当Salary表中Income值增加500时，outcome值增加50：CREATETRIGGERSA_INCOMEONSalaryFORUPDATEASBEGINIF((SELECTInComeFROM