1、第11章 SQL练习答案1实训题根据人力资源管理系统数据库中数据信息,完成下列操作。(1)查询100号部门的所有员工信息。Selsect * from employees where department_id = 100(2)查询所有职位编号为“SA_MAN”的员工的员工号、员工名和部门号。Select employee_id,first_name,last_name,department_id from employees where job_id= SA_MAN(3)查询每个员工的员工号、工资、奖金以及工资与奖金的和。Select employee_id,salary,commissio
2、n_pct,salary*(1+nvl(commission_pct,0) from employees(4)查询40号部门中职位编号为“AD_ASST”和20号部门中职位编号为“SA_REP”的员工的信息。 Select * from employees where department_id=40 and job_id= AD_ASST OR department_id=20 and job_id= SA_REP;(5)查询所有职位名称不是“Stock Manager”和“Purchasing Manager”,且工资大于或等于2000的员工的详细信息。Select * from empl
3、oyees where job_id not in( Stock Manager, Purchasing Manager) and salary=2000(6)查询有奖金的员工的不同职位编号和名称。 Select distinct job_id, job_title from jobs where job_id in (select job_id from employees where job_id is not null)(7)查询没有奖金或奖金低于100元的员工信息。Select * from employees where salary*commission_pct100 or com
4、mission is NULL(8)查询员工名(first_name)中不包含字母“S”的员工。Select first_name from employees where first_name not like %S%(9)查询员工的姓名和入职日期,并按入职日期从先到后进行排序。Select first_name,last_name,hire_date from employees order by hire_date;(10)显示所有员工的姓名、职位、工资和奖金,按职位降序排序,若职位相同则按工资升序排序。Select first_name,last_name,job_id,salary
5、,salary*commission_pet from employees order by job_id desc ,salary asc;(11)查询所有员工的姓名及其直接上级的姓名。Select a.first_name,b.first_name from employees a join employees b on b.employee_id = a.manage_id (12)查询入职日期早于其直接上级领导的所有员工信息。 select * from employees a where hire_date(select salary from employees where emp
6、loyee_id = 100);(19)查询工资高于公司平均工资的所有员工信息。Select * from employees where salary(select avg(salary) from employees)(20)查询各个部门中不同职位的最高工资。Select job_id,max(salary) from employees group by job_id(21)查询各个部门的人数及平均工资 Select department_id,count(*),avg(salary ) from employees group by department_id; (22)统计各个职位的
7、员工人数与平均工资。Select job_id ,count(employee_id),avg(salary) from employees group by job_id;(23)统计每个部门中各职位的人数与平均工资。Select department_id,job_id,count(*),avg(salary) from employees group by department_id,job_id;(24)查询最低工资大于5000元的各种工作。Select job_id,job_title from jobs where job_id in(Select job_id from empl
8、oyees group by job_id having min(salary)5000);(25)查询平均工资低于6000元的部门及其员工信息。 Select e.*,d.* from employees e join departments d on e.department_id=d.department_id and department_id in(select department_Id from employees group by employee_id having avg(salary)(select max(salary) from employees deparment
9、_id=30);(29)查询每个部门中的员工数量、平均工资和平均工作年限。Select count(*),avg(salary),avg(round(sysdate-hire_date)/365) from employees group by department_id(30)查询工资为某个部门平均工资的员工的信息。Select * from employees where salsry in(select avg(Salary) from employees group by department_id)(31)查询工资高于本部门平均工资的员工的信息。Select * from emplo
10、yees e1 where salary(select avg(salary) from employees e2 where e2.department_id=e1.department_id )(32)查询工资高于本部门平均工资的员工的信息及其部门的平均工资。Select e.*,avgsalFrom employees e join (select department_id,avg(salary) avgsal from employees group by department_id) dOn e.department_id=d.department_id And e.salaryd
11、.avgsal(33)查询工资高于50号部门某个员工工资的员工的信息。Select *from employees where salaryany(select salary from employees where department_id=50):(34)查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息。Select * from employees where (salary,nvl(commission_pct) ) in(Select salary,nvl(commission_pct) from employees where department_id=10)(35
12、)查询部门人数大于10的部门的员工信息。Select * from employees where department_id in(select department_id from employees group by department_id having count(*)10);查询所有员工工资都大于10000元的部门的信息Select * from department where department_id in (select department_id from employees group by department_id having min(salary)10000)
13、(36)查询所有员工工资都大于5000元的部门的信息及其员工信息。(37)查询所有员工工资都在4000元8000元之间的部门的信息。Select * from departments where department_id in(Select department_id from employees group by department_id having min(salary)=4000 and max(salary)=all(select count(*) from employees group by department_id )(39)查询30号部门中工资排序前3名的员工信息。Se
14、lect * from employee where department_id=30 and salary is not null and rownum=3 order by salary desc (40)查询所有员工中工资排序在510名之间的员工信息。Select * from (Select rownum rn,employee_id,salary from (Select employee_id,salary from employees where salary is not null order by salary desc) e1 )e2Where rn between 5 a
15、nd 10(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月份入职的所有员工信息。(资料素材和资料部分来自网络,供参考。可复制、编制,期待你的好评与关注)
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1