SQL实验实验4至实验7的答案Word文档下载推荐.docx
- 文档编号:15300529
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:26
- 大小:384.60KB
SQL实验实验4至实验7的答案Word文档下载推荐.docx
《SQL实验实验4至实验7的答案Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《SQL实验实验4至实验7的答案Word文档下载推荐.docx(26页珍藏版)》请在冰豆网上搜索。
selectemployeeidfromsalary
whereincome>
2000
6、查询1970年以后出生的员工的姓名和住址:
selectname,address
wherebirthday>
1970
7、查询所有财务部的员工的号码和姓名:
selectemployeeid,name
fromemployees
wheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='
财务部'
)
8、查询employees员工的姓名,住址和收入水平,2000元以下显示为低收入,2000~3000元显示为中等收入,3000元以上显示为高收入:
selectname,address,
case
whenincome-outcome<
2000then'
低收入'
whenincome-outcome>
3000then'
高收入'
else'
中等收入'
endas'
收入等级'
fromemployees,salary
whereemployees.employeeid=salary.employeeid
9、计算salary表中员工月收入的评价数:
selectavg(income)as'
平均收入'
fromsalary
10、查找employees表中最大的员工号码:
selectmax(employeeid)as'
最大员工号码'
fromemployees
11、计算salary表中的所有员工的总支出:
selectsum(outcome)as'
总支出'
12、查询财务部雇员的最高实际收入:
selectmax(income-outcome)fromsalary,employees,departments
wheresalary.employeeid=employees.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='
13、查询财务部雇员的最低实际收入:
selectmin(income-outcome)fromsalary,employees,departments
14、找出所用地址中含有“中山”的雇员的号码及部门号:
selectemployeeid,departmentid
whereaddresslike'
%中山%'
15、查找员工号码中倒数第二个数字为0的员工的姓名,地址和学历:
selecteducation,address,name
whereemployeeidlike'
%0_'
16、使用into字句,由表employees创建“男员工1”表,包括编号和姓名:
selectemployeeid,name
into男员工表
wheresex='
1'
17、用子查询的方法查找收入在2500元以下的雇员的情况:
select*fromemployees
whereemployeeidin
(selectemployeeidfromsalarywhereincome<
2500)
18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名:
SELECTNameFROMEmployeesWHEREEmployeeIDIN
(
SELECTEmployeeIDFROMSalary
WHEREEmployeeIDIN
(
SELECTEmployeeIdFROMEmployees
WHEREDepartmentIDIN
SELECTDepartmentIDFROMDepartments
WHEREDepartmentName='
研发部'
)
ANDInCome>
ALL
SELECTInComeFROMSalary
WHEREEmployeeIDIN
(
SELECTEmployeeIdFROMEmployees
WHEREDepartmentIDIN
SELECTDepartmentIDFROMDepartments
WHEREDepartmentName='
)
19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名:
selectname
whereBirthday<
all
(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
(selectemployeeid
wheredepartmentname='
24、查询财务部雇员的最高实际收入:
selectmax(income-outcome)
25、统计财务部收入在2500元以上的雇员人数:
selectcount(employeeid)
wheredepartmentidin
(selectdepartmentidfromdepartments
andemployeeidin
selectemployeeid
26、按部门列出在该部门工作的员工的人数:
selectdepartmentid,count(*)as人数
groupbydepartmentid
27、按员工的学历分组:
selecteducation,count(*)as人数
groupbyeducation
28、按员工的工作年份分组,统计年份人数:
selectworkyear,count(*)as人数
groupbyworkyear
29、按各雇员的情况收入由低到高排列:
selectemployees.*,salary.income
fromemployees,salary
orderbyincome
30、将员工信息按出生时间从小到大排列:
select*
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 实验 答案