SQL实验实验4至实验7的答案.docx
- 文档编号:2383453
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:26
- 大小:384.60KB
SQL实验实验4至实验7的答案.docx
《SQL实验实验4至实验7的答案.docx》由会员分享,可在线阅读,更多相关《SQL实验实验4至实验7的答案.docx(26页珍藏版)》请在冰豆网上搜索。
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中插入一行数
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 实验 答案