SQL实验实验4至实验7的答案.docx
- 文档编号:25191853
- 上传时间:2023-06-06
- 格式:DOCX
- 页数:29
- 大小:392.32KB
SQL实验实验4至实验7的答案.docx
《SQL实验实验4至实验7的答案.docx》由会员分享,可在线阅读,更多相关《SQL实验实验4至实验7的答案.docx(29页珍藏版)》请在冰豆网上搜索。
SQL实验实验4至实验7的答案
SQL实验
实验4
1.用select语句查询departments和salary表中的所有数据:
selectsalary.*,departments.*
fromsalary,departments
2、查询departments中的departmentid:
selectdepartmentidfromdepartments
go
3、查询salary中的income,outcome:
selectincome,outcomefromsalary
go
4、查询employees表中的部门号,性别,要用distinct消除重复行:
selectdistinct(departmentid),sex
fromemployees
5、查询月收入高于2000元的员工号码:
selectemployeeidfromsalary
whereincome>2000
go
6、查询1970年以后出生的员工的姓名和住址:
selectname,address
fromemployees
wherebirthday>1970
go
7、查询所有财务部的员工的号码和姓名:
selectemployeeid,name
fromemployees
wheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务部')
go
8、查询employees员工的姓名,住址和收入水平,2000元以下显示为低收入,2000~3000元显示为中等收入,3000元以上显示为高收入:
selectname,address,
case
whenincome-outcome<2000then'低收入'
whenincome-outcome>3000then'高收入'
else'中等收入'
endas'收入等级'
fromemployees,salary
whereemployees.employeeid=salary.employeeid
go
9、计算salary表中员工月收入的评价数:
selectavg(income)as'平均收入'fromsalary
10、查找employees表中最大的员工号码:
selectmax(employeeid)as'最大员工号码'fromemployees
11、计算salary表中的所有员工的总支出:
selectsum(outcome)as'总支出'fromsalary
12、查询财务部雇员的最高实际收入:
selectmax(income-outcome)fromsalary,employees,departments
wheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'
go
13、查询财务部雇员的最低实际收入:
selectmin(income-outcome)fromsalary,employees,departments
wheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务部'
go
14、找出所用地址中含有“中山”的雇员的号码及部门号:
selectemployeeid,departmentid
fromemployees
whereaddresslike'%中山%'
go
15、查找员工号码中倒数第二个数字为0的员工的姓名,地址和学历:
selecteducation,address,name
fromemployees
whereemployeeidlike'%0_'
go
16、使用into字句,由表employees创建“男员工1”表,包括编号和姓名:
selectemployeeid,name
into男员工表
fromemployees
wheresex='1'
go
17、用子查询的方法查找收入在2500元以下的雇员的情况:
select*fromemployees
whereemployeeidin
(selectemployeeidfromsalarywhereincome<2500)
go
18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名:
SELECTNameFROMEmployeesWHEREEmployeeIDIN
(
SELECTEmployeeIDFROMSalary
WHEREEmployeeIDIN
(
SELECTEmployeeIdFROMEmployees
WHEREDepartmentIDIN
(
SELECTDepartmentIDFROMDepartments
WHEREDepartmentName='研发部'
)
)
ANDInCome>ALL
(
SELECTInComeFROMSalary
WHEREEmployeeIDIN
(
SELECTEmployeeIdFROMEmployees
WHEREDepartmentIDIN
(
SELECTDepartmentIDFROMDepartments
WHEREDepartmentName='财务部'
)
)
)
)
19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名:
selectname
fromemployees
whereBirthday (selectbirthday fromemployees wheredepartmentidin (selectdepartmentid fromdepartments wheredepartmentname='研发部' ) ) 20、查询每个员工的情况及其薪水的情况: selectemployees.*,departments.departmentname fromemployees,departments whereemployees.departmentid=departments.departmentid 21、使用内连接方法查找不在财务部工作的所有员工信息: selectemployees.* fromemployeesinnerjoindepartmentsonemployees.departmentid=departments.departmentid wheredepartmentname! ='财务部' 22、使用外连接方法查找出所有员工的月收入: selectemployees.*,salary.income fromemployeesjoinsalaryonemployees.employeeid=salary.employeeid 23、查找财务部雇员的最高收入: selectmax(income) fromsalary whereemployeeidin (selectemployeeid fromemployees wheredepartmentidin (selectdepartmentid fromdepartments wheredepartmentname='财务部' ) ) 24、查询财务部雇员的最高实际收入: selectmax(income-outcome) fromsalary whereemployeeidin (selectemployeeid fromemployees wheredepartmentidin (selectdepartmentid fromdepartments wheredepartmentname='财务部' ) ) 25、统计财务部收入在2500元以上的雇员人数: selectcount(employeeid) fromemployees wheredepartmentidin (selectdepartmentidfromdepartments wheredepartmentname='财务部') andemployeeidin ( selectemployeeid fromsalary whereincome>2500) 26、按部门列出在该部门工作的员工的人数: selectdepartmentid,count(*)as人数 fromemployees groupbydepartmentid 27、按员工的学历分组: selecteducation,count(*)as人数 fromemployees groupbyeducation 28、按员工的工作年份分组,统计年份人数: selectworkyear,count(*)as人数 fromemployees groupbyworkyear 29、按各雇员的情况收入由低到高排列: selectemployees.*,salary.income fromemployees,salary whereemployees.employeeid=salary.employeeid orderbyincome 30、将员工信息按出生时间从小到大排列: select* fromemployees orderbybirthday 31、在orderby字句中使用子查询,查询员工姓名,性别和工龄信息,要求按实际收入从大到小排列: selectname,sex,workyear,income-outcome fromsalary,employees wheresalary.employeeid=employees.employeeid orderbyincome-outcomedesc 视图部分 1、创建view1: Createviewview1 as selectemployees.employeeid,name,departmentname,(income-outcome)ascome fromemployees,departments,salary whereemployees.departmentid=departments.departmentidandemployees.employeeid=salary.employeeid 2、查询视图employeeid: 3、向视图view1中插入一行数据: insertintoview1values('111111','谎言','1','30000') 4、查看视图(没有影响)基本表: 实验5 1、定义一个变量,用于描述YGGL数据库的salary表中000001号员工的实际收入,然后查询该变量: declare@hyint set@hy=(selectincome-outcome fromsalary whereemployeeid='000001') select@hy 2、使用运算符“>”: selectname fromemployees wherebirthday>'1974-10-10' 3、判断姓名为“王林”的员工实际收入是否高于3000元,如果是则显示“高收入”,否则显示“收入不高于3000”: if((selectincome fromsalary,employees wheresalary.employeeid=employees.employeeidandemployees.name='刘明')>3000) selectincomeas'高收入' fromsalary,employees wheresalary.employeeid=employees.employeeidandemployees.name='刘明' else select'收入不高于' 4、使用循环输出一个“*”三角形: declare@iint declare@jint set@j=20 set@i=1 while@i<@j begin print(space((@j-@i)/2)+replicate('*',@i)) set@i=@i+2 end 4、按部门进行分类,使用if语句实现: Createfunctionhy1(@departmentid1char(3)) returnschar(10)as begin declare@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@hy1 end selectemployeeid,name,address,dbo.hy1(departmentid)fromemployees selectemployeeid,name,address, casedepartmentid when1then'财务部' when2then'人力资源部' when3then'经理办公室' when4then'研发部' when5then'市场部' endas部门号 fromemployees 6、自定义一个函数,计算一个数的阶层: createfunctionhy(@hy2int)returnsint as begin declare@iint set@i=@hy2 declare@jint set@j=1 while@i>1 begin set@j=@j*@i set@i=@i-1 end return(@j) end declare@hint exec@h=dbo.hy4 select@has'jiecheng' 7、/*生成随机数*/ selectrand() 8、/*平方*/ selectsquare(12) 9、/*求财务部收入最高的员工姓名*/ selectmax(name) fromemployees whereemployeeidin (selectemployeeid fromsalary whereemployeeidin (selectemployeeid fromemployees wheredepartmentidin (selectdepartmentid fromdepartments wheredepartmentname='财务部') ) ) selectavg(income)as'平均收入' fromsalary /*聚合函数与groupby一起使用*/ selectworkyear,count(*)as人数 fromemployees groupbyworkyear /*将字符组成字符串*/ selectchar(123) /*返回字符串左边开始的个字符*/ selectleft('abcdef',2) /*返回指定日期时间的天数*/ selectday(birthday) fromemployees whereemployeeid='010000' /*获取当前时间*/ selectgetdate() 实验6 1、创建索引: createuniqueindexhuangyan onemployees(employeeid) 2、/*用createindex语句创建主键*/ 3、重建表employees中employeeid列上的索引 alterindexhuangyan onemployeesrebuild 4、删除索引: 5、创建一个新表,使用一个复合列作为主键,作为表的约束,并为其命名: createtableemployees5 (employeeidchar(6)notnull, namechar(5)notnull, sextinyint, educationchar(4), constraintyanprimarykey(employeeid,name) ) 为新表添加一列: altertableemployees5 addaddresschar(10) 6、创建新表student,性别只能包含男或女: createtablestudent (号码char(6)notnull, 性别char (2)notnull check(性别in('男','女')) ) 7、创建新表: createtableemployees7 (学号char(10)notnull, 出生日期datetimenotnull check(出生日期>'1980-01-01') ) 8、创建一个规则: 9,创建salary2: createtablesalary2 (employeeidchar(6)notnullprimarykey, incomefloatnotnull, outcomefloatnotnull, foreignkey(employeeid) referencessalary(employeeid) onupdatecascade ondeletecascade ) 10、添加一个外键,salary与employees有相关记录,则拒绝更新employees: altertablesalary addconstraintkc_for foreignkey(employeeid) referencesemployees(employeeid) ondeletenoaction onupdatenoaction 实验7 1、工作年份大于6时,跟换科室到经理办公室(根据员工): CreatePROCUpdateDeptByYear (@EmpIdchar(6)) AS BEGIN DECLARE@yearint SELECT@year=WorkYearFromEmployeesWHEREEmployeeID=@EmpId IF(@year>6) UPDATEEmployees SETDepartmentID='3' WHEREEmployeeID=@EmpId END EXECUpdateDeptByYear'020010' SELECT*FROMEmployeesWHEREEmployeeid='020010' 2、根据每个员工的学历将收入提高元: CREATEPROCUpdateInComeByEdu@Employeeidchar(6) AS BEGIN UPDATESalary SETInCome=InCome+500 FROMSalary LEFTJOINEmployees ONSalary.EmployeeID=Employees.EmployeeID WHERESalary.Employeeid=@Employeeid END EXECUpdateInComeByEdu'020010' SELECT*FROMSalarywhereEmployeeID='020010' 3、游标: CREATEPROCEDUREEmployees_bili AS BEGIN DECLARE@iFLOAT DECLARE@jFLOAT DECLARE@EducationCHAR(10) DECLAREEmployees_cursorCURSOR FORSELECTEducationFROMEmployees SET@i=0 SET@j=0 OPENEmployees_cursor FETCHEmployees_cursorINTO@Education WHILE(@@FETCH_STATUS=0) BEGIN IF(@Education! ='大专') SET@i=@i+1 SET@j=@j+1 FETCHEmployees_cursorINTO@Education END CLOSEEmployees_cursor SELECT@iAS'本科及以上员工所占员工数' SELECT@jAS'员工总数' SELECT@i/@jAS'本科及以上员工所占比例' CLOSEEmployees_cursor END EXECEmployees_bili 4、使用命令的方式修改存储过程的定义: 5、对于YGGL数据库,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 实验 答案