搜索
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_pct100orcommissionisNULL（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_namefromdepartmentsdjoinemployeeseond.manager_id=e.employee_id（14）查询所有部门及其员工信息，包括那些没有员工的部门。Selectdepartment_name,first_namefromdepartmentsdleftjoinemployeeseond.deparment_id=e.department_id（15）查询所有员工及其部门信息，包括那些还不属于任何部门的员工。Selecte.first_name,d.department_nameFromemployeesleftjoindepartmentsone.department_id=d.department_id;（16）查询所有员工的员工号、员工名、部门名称、职位名称、工资和奖金。Selecte.employee_id,e.first_name,d.department_name,j.job_title,e.salary,e.salary*e.commission_pct奖金Fromdepartmentsdjoinemployeeseond.department_id=e.department_idJoinjobsjonj.job_id=e.job_id;（17）查询至少有一个员工的部门信息。Selectdistinctdepartments.*fromdepartmentsdjoinemployeeseone.employee_idisnotnull;select*fromemployeeswheredepartment_idin(selectdistinctdepartment_idfromemployees)select*fromdepartmentsdwhereexists(select1fromemployeeswheredepartment_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)fromemployeesgroupbydepartment_id;（22）统计各个职位的员工人数与平均工资。Selectjob_id,count(employee_id),avg(salary)fromemployeesgroupbyjob_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_idanddepartment_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.*,avgsalFromemployeesejoin(selectdepartment_id,avg(salary)avgsalfromemployeesgroupbydepartment_id)dOne.department_id=d.department_idAnde.salaryd.avgsal（33）查询工资高于50号部门某个员工工资的员工的信息。Select*fromemployeeswheresalaryany(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(sa