第11章 SQL练习答案.docx

第11章 SQL练习答案.docx

《第11章 SQL练习答案.docx》由会员分享，可在线阅读，更多相关《第11章 SQL练习答案.docx（7页珍藏版）》请在冰豆网上搜索。

第11章SQL练习答案

1．实训题

根据人力资源管理系统数据库中数据信息，完成下列操作。

（1）查询100号部门的所有员工信息。

Selsect*fromemployeeswheredepartment_id=100

（2）查询所有职位编号为“SA_MAN”的员工的员工号、员工名和部门号。

Selectemployee_id,first_name,last_name,department_idfromemployeeswherejob_id=‘SA_MAN’

（3）查询每个员工的员工号、工资、奖金以及工资与奖金的和。

Selectemployee_id,salary,commission_pct,

salary*（1+nvl（commission_pct,0）fromemployees

（4）查询40号部门中职位编号为“AD_ASST”和20号部门中职位编号为“SA_REP”的员工的信息。

Select*fromemployeeswheredepartment_id=40andjob_id=’AD_ASST’ORdepartment_id=20andjob_id=’SA_REP’;

（5）查询所有职位名称不是“StockManager”和“PurchasingManager”，且工资大于或等于2000的员工的详细信息。

Select*fromemployeeswherejob_idnotin（’StockManager’,’PurchasingManager’）andsalary>=2000

（6）查询有奖金的员工的不同职位编号和名称。

Selectdistinctjob_id,job_titlefromjobswherejob_idin（selectjob_idfromemployeeswherejob_idisnotnull）

（7）查询没有奖金或奖金低于100元的员工信息。

Select*fromemployeeswheresalary*commission_pct<100orcommissionisNULL

（8）查询员工名（first_name）中不包含字母“S”的员工。

Selectfirst_namefromemployeeswherefirst_namenotlike‘%S%’

（9）查询员工的姓名和入职日期，并按入职日期从先到后进行排序。

Selectfirst_name,last_name,hire_datefromemployeesorderbyhire_date;

（10）显示所有员工的姓名、职位、工资和奖金，按职位降序排序，若职位相同则按工资升序排序。

Selectfirst_name,last_name,job_id,salary,salary*commission_petfromemployeesorderbyjob_iddesc,salaryasc;

（11）查询所有员工的姓名及其直接上级的姓名。

Selecta.first_name,b.first_namefromemployeesajoinemployeesbonb.employee_id=a.manage_id

（12）查询入职日期早于其直接上级领导的所有员工信息。

select*fromemployeesawherehire_date<（selecthire_datefromemployeesbb.employee_id=a.manage_id）

（13）查询各个部门号、部门名称、部门所在地以及部门领导的姓名。

Selectd.department_id,d.department_name,d.location,e.first_namefromdepartmentsdjoinemployeese

ond.manager_id=e.employee_id

（14）查询所有部门及其员工信息，包括那些没有员工的部门。

Selectdepartment_name,first_namefromdepartmentsdleftjoinemployeeseond.deparment_id=e.department_id

（15）查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

Selecte.first_name,d.department_name

Fromemployeesleftjoindepartmentsone.department_id=d.department_id;

（16）查询所有员工的员工号、员工名、部门名称、职位名称、工资和奖金。

Selecte.employee_id,e.first_name,d.department_name,j.job_title,e.salary,e.salary*mission_pct奖金

Fromdepartmentsdjoinemployeeseond.department_id=e.department_id

Joinjobsjonj.job_id=e.job_id;

（17）查询至少有一个员工的部门信息。

Selectdistinctdepartments.*fromdepartmentsdjoinemployeese

one.employee_idisnotnull;

select*fromemployeeswheredepartment_idin（selectdistinctdepartment_idfromemployees）

select*fromdepartmentsdwhereexists（select1fromemployees

wheredepartment_id=d.department_id）

（18）查询工资比100号员工工资高的所有员工信息。

Select*fromemployeeswheresalary>（selectsalaryfromemployeeswhereemployee_id=100）;

（19）查询工资高于公司平均工资的所有员工信息。

Select*fromemployeeswheresalary>（selectavg（salary）fromemployees）

（20）查询各个部门中不同职位的最高工资。

Selectjob_id,max（salary）fromemployeesgroupbyjob_id

（21）查询各个部门的人数及平均工资

Selectdepartment_id,count（*）,avg（salary）fromemployees

groupbydepartment_id;

（22）统计各个职位的员工人数与平均工资。

Selectjob_id,count（employee_id）,avg（salary）

fromemployees

groupbyjob_id;

（23）统计每个部门中各职位的人数与平均工资。

Selectdepartment_id,job_id,count（*）,avg（salary）fromemployeesgroupbydepartment_id,job_id;

（24）查询最低工资大于5000元的各种工作。

Selectjob_id,job_titlefromjobswherejob_idin（

Selectjob_idfromemployeesgroupbyjob_idhavingmin（salary）>5000）;

（25）查询平均工资低于6000元的部门及其员工信息。

Selecte.*,d.*fromemployeesejoindepartmentsdone.department_id=d.department_idand

department_idin（selectdepartment_Idfromemployeesgroupbyemployee_idhavingavg（salary）<6000）;

（26）查询在“Sales”部门工作的员工的姓名信息。

Selecte.first_name||e.last_namefromemployeesejoindepartmentsdone.department_id=d.department_idwhered.department_name=‘Sales’;

Select*fromemployeewheredepartment_idin（selectdepartment_dfromdepartmentswheredepartment_name=’Sales’）

（27）查询与140号员工从事相同工作的所有员工信息。

Select*fromemployeeswherejob_idin（selectjob_idfromemployeeswhereemployee_id=140）;

（28）查询工资高于30号部门中所有员工的工资的员工姓名和工资。

Selectfirst_name,last_name,salaryfromemployeeswheresalary>（selectmax（salary）fromemployeesdeparment_id=30）;

（29）查询每个部门中的员工数量、平均工资和平均工作年限。

Selectcount（*）,avg（salary）,avg（round（（sysdate-hire_date）/365））fromemployeesgroupbydepartment_id

（30）查询工资为某个部门平均工资的员工的信息。

Select*fromemployeeswheresalsryin（selectavg（Salary）fromemployeesgroupbydepartment_id）

（31）查询工资高于本部门平均工资的员工的信息。

Select*fromemployeese1wheresalary>（selectavg（salary）fromemployeese2wheree2.department_id=e1.department_id）

（32）查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

Selecte.*,avgsal

Fromemployeesejoin（selectdepartment_id,avg（salary）avgsalfromemployeesgroupbydepartment_id）d

One.department_id=d.department_id

Ande.salary>d.avgsal

（33）查询工资高于50号部门某个员工工资的员工的信息。

Select*fromemployeeswheresalary>any（selectsalaryfromemployeeswheredepartment_id=50）:

（34）查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息。

Select*fromemployeeswhere（salary,nvl（commission_pct））in（

Selectsalary,nvl（commission_pct）fromemployeeswheredepartment_id=10

）

（35）查询部门人数大于10的部门的员工信息。

Select*fromemployeeswheredepartment_idin（selectdepartment_idfromemployeesgroupbydepartment_idhavingcount（*）>10）;

查询所有员工工资都大于10000元的部门的信息

Select*fromdepartmentwheredepartment_idin（selectdepartment_idfromemployeesgroupbydepartment_idhavingmin（salary）>10000）

（36）查询所有员工工资都大于5000元的部门的信息及其员工信息。

（37）查询所有员工工资都在4000元～8000元之间的部门的信息。

Select*fromdepartmentswheredepartment_idin（

Selectdepartment_idfromemployeesgroupbydepartment_idhavingmin（salary）>=4000andmax（salary）<=8000）

（38）查询人数最多的部门信息。

Select*fromdepartment_idwheredepartment_idin（

Selectdepartment_idfromemployeesgroupbydepartment_idhaving

Count（*）>=all（

selectcount（*）fromemployeesgroupbydepartment_id）

）

（39）查询30号部门中工资排序前3名的员工信息。

Select*fromemployeewheredepartment_id=30andsalaryisnotnullandrownum<=3orderbysalarydesc

（40）查询所有员工中工资排序在5~10名之间的员工信息。

Select*from

（

Selectrownumrn,employee_id,salaryfrom（

Selectemployee_id,salaryfromemployeeswheresalaryisnotnullorderbysalarydesc）e1

）e2

Wherernbetween5and10

（41）向employees表中插入一条记录，员工号为1000元，入职日期为2002年5月10日，email为*******************.cn，其他信息与员工号为150的员工相同。

（42）将各部门员工的工资修改为该员工所在部门平均工资加1000。

（43）查询各月倒数第2天入职的员工信息。

（44）查询工龄大于或等于10年的员工信息。

（45）查询员工信息，要求以首字母大写的方式显示所有员工姓（last_name）和员工名（first_name）。

（46）查询员工名（first_name）正好为6个字符的员工的信息。

（47）查询员工名（first_name）的第2个字母为“M”的员工信息。

（48）查询所有员工名（first_name），如果包含字母“s”，则用“S”替换。

（49）查询在2月份入职的所有员工信息。

（资料素材和资料部分来自网络，供参考。

可复制、编制，期待你的好评与关注）