SQL实验实验至实验的答案

ＳＱＬ实验实验41.用select语句查询departments和salary表中的所有数据：selectsalary.*,departments.*fromsalary,departments2、查询departments中的departmentid：selectdepartmentidfromdepartmentsgo3、查询salary中的income，outcome：selectincome,outcomefromsalarygo4、查询employees表中的部门号，性别，要用distinct消除重复行：selectdistinct(departmentid),sexfromemployees5、查询月收入高于2000元的员工号码：selectemployeeidfromsalarywhereincome2000go6、查询1970年以后出生的员工的姓名和住址：selectname,addressfromemployeeswherebirthday1970go7、查询所有财务部的员工的号码和姓名：selectemployeeid,namefromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')go8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，2000~3000元显示为中等收入，3000元以上显示为高收入：selectname,address,casewhenincome-outcome2000then'低收入'whenincome-outcome3000then'高收入'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(selectemployeeidfromsalarywhereincome2500)go18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名：SELECTNameFROMEmployeesWHEREEmployeeIDIN(SELECTEmployeeIDFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='研发部'))ANDInComeALL(SELECTInComeFROMSalaryWHEREEmployeeIDIN(SELECTEmployeeIdFROMEmployeesWHEREDepartmentIDIN(SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='财务部'))))19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：selectnamefromemployeeswhereBirthdayall(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(selectemployeeidfromsalarywhereincome2500)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中插入一行数据：insertintoview1values('111111','谎言','1','30000')4、查看视图（没有影响）基本表：实验51、定义一个变量，用于描述YGGL数据库的salary表中000001号员工的实际收入，然后查询该变量：declare@hyintset@hy=(selectincome-outcomefromsalarywhereemployeeid='000001')select@hy2、使用运算符“”：selectnamefromemployeeswherebirthday'1974-10-10'3、判断姓名为“王林”的员工实际收入是否高于3000元，如果是则显示“高收入”，否则显示“收入不高于3000”：if((selectincomefromsalary,employeeswheresalary.employeeid=employees.employeeidandemployees.name='刘明')3000)selectincomeas'高收入'fromsalary,employeeswheresalary.employeeid=employees.employeeidandemployees.name='刘明'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=