Oracle习题集答案分享

Oracle day10

时间安排：

8：00 — 8：45（测试） 8：55— 9：40(测试)

10：10—10：55（测试讲解）11：05— 11：50 （测试讲解）

测试题：

--1.查询入职日期在2003-1-1到2013-12-31之间的员工信息(to_date ,to_char)

select COUNT(*) from employees --101 推荐使用 where to_char(hire_date,'yyyy-mm-dd') between '2003-01-01' and '2013-12-31';

select COUNT(*) from employees --101

where hire_date between

to_date('2003-01-01','yyyy-mm-dd') and

to_date('2013-12-31','YYYY-MM-DD');

insert into employees

(last_name,email,job_id,hire_date)values('1','1','it',to_date('2013-12-31 12:24:20','yyyy-mm-dd hh24:mi:ss'));

--2. 查询出30号部门工作为IT_PROG的员工信息 where 条件 select * from employees

where department_id = 30

and job_id = 'IT_PROG';

--3. 把入职生日看作是员工生日，求出这个月过生日的员工。 to_char()

select * from employees

where to_char(hire_date,'mm') = to_char(sysdate,'mm')

--4. 查询出名字中含有”L”的员工信息 【模糊查询 like % _】

select * from employees where first_name like '%L%'; --5. 求出2003年各个月入职的员工个数。【to_char group by count 】

select to_char(hire_date,'mm'),count(*) from

employees where to_char(hire_date,'yyyy') = '2003'

group by to_char(hire_date,'mm');

--6. 查询各个部门的平均工资。【group by avg()】

select department_id,avg(salary) from employees group by department_id

--7. 按照入职日期由新到旧的排序员工信息[order by desc

[asc]]

select * from employees

order by hire_date desc;

--*8. 查询员工的信息，附加上其上级的和部门名字（员工信息要求全部显示）。 select * from employees worker left join employees leader

on worker.manager_id = leader.employee_id

left join departments dept on worker.department_id = dept.department_id;

--9. 显示于David相同工资和部门的员工(多列子查询)

select * from employees

where (job_id,department_id)in(select

job_id,department_id from employees where

first_name = 'David')

--10. 查询出工资最高的三名员工信息 (rownum order by) select * from (

select * from employees

where salary is not null

order by salary desc) where rownum <= 3;

--11. 查询出工资最高的6~10名员工信息。

select * from ( select rownum rn ,a.* from ( select * from employees

where salary is not null

order by salary desc)a

where rownum <= 10)

where rn >= 6;

--*12. 查询出没有员工的部门信息。

select * from departments

where department_id not in (

select department_id from employees

where department_id is not null

group by department_id);

--13. 显示部门内最低工资比100部门最低工资要高的部门编号以及部门内最低工资。(group by min())

select department_id,min(salary) from employees group by department_id

having min(salary) > (select min(salary) from

employees where department_id = 100 )

--14. 显示员工的的姓名，工资及工资级别。(非等值连接)

select first_name,salary,grade from employees a ,salgrade b

where a.salary between b.losal and b.hisal; --15. 查询工资与任何一个部门最低工资相等的雇员姓名,工资(子查询 in 多行子查询)

select * from employees where salary in(

select min(salary) from employees

group by department_id);

--*16. 查看所有部门(包含没有员工的部门)的雇员编号、姓名、所在部门编号以及部门名称

select * from departments a

left join employees b

on a.department_id = b.department_id;

--17. 修改数据：所有员工的工资全部上涨10%

update employees set salary = salary * 1.1;

--18.添加一条员工的信息，员工的姓名为（名字：william,工资4000，入职日期）

insert into employees

(first_name,sal,hireDate)values('william',4000,sysdate);