oracle习题及答案

1. 查询工资大于12000的员工姓名和工资

Select last_name||' '||first_name,salary from employees where salary >12000;

2. 查询员工号为176的员工的姓名和部门号

Select last_name||' '||first_name,department_id from employees where employee_id=176;

3. 选择工资不在5000到12000的员工的姓名和工资

Select last_name||' '||first_name,salary from employees where salary not between 5000 and 12000;

4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间

Select last_name||' '||first_name,job_id,hire_date from employees where hire_date between '1-2月-98' and '1-5月-98';

5. 选择在20或50号部门工作的员工姓名和部门号

Select last_name||' '||first_name,department_id from employees where department_id in (20,50);

6. 选择在1994年雇用的员工的姓名和雇用时间

Select last_name||' '||first_name,hire_date from employees where hire_date like '?';

7. 选择公司中没有管理者的员工姓名及job_id

Select last_name||' '||first_name,job_id from employees where Manger_id is null;

8. 选择公司中有奖金的员工姓名，工资和奖金

Select last_name||' '||first_name,salary,commission_pct from employees where commission_pct is not null;

9. 选择员工姓名的第三个字母是a的员工姓名

Select last_name||' '||first_name from employees where last_name||' '||first_name like '___a%';

10. 选择姓名中有字母a和e的员工姓名 Select

last_name||'

'||first_name

from

employees

where

last_name||first_name like '%a%e%' or last_name||first_name like '%e%a%';

多表查询

11. 显示所有员工的姓名，部门号和部门名称。

Select e.last_name,d.department_id,d.department_name from employees e , departments d where (e.department_id=d.department_id); 12. 查询90号部门员工的job_id和90号部门的location_id

Select e.job_id,d.location_id from employees e, departments d where e.department_id=d.deparement_id and d.department_id=90; 13. 选择所有有奖金的员工的

last_name , department_name , location_id , city

Select e.last_name , d.department_name , l.location_id , city from employees

e,departments

d,locations

l

where

e.department_id=d.department_id AND d.location_id=l.location_id

AND commission_pct is not null;

14. 选择在Toronto工作的员工的

last_name , job_id , department_id , department_name

Select e.last_name , e.job_id , d.department_id , d.department_name from

employees

e,departments

d

,locations

l

where

e.department_id=d.department_id AND d.location_id=l.location_id AND l.city='Toronto';

15. 选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 employees kochhar Emp# 101 manager king Mgr# 100 Select e.employee_id \\\\from employees e,employees d where e.manager_id=d.employee_id(+);

6. 查询各部门员工姓名和他们的同事姓名，结果类似于下面的格式 Department_id 20 Last_name fay colleague hartstein Select e.department_id \d.last_name \e.last_name \from employees e join employees d on(d.department_id=e.department_id) d.last_name<>e.last_name;

where

分组查询

16. 组函数处理多行返回一行（true） 17. 组函数不计算空值（ false）

18. where子句在分组之前对检索进行过滤 ( true) 19. 查询公司员工工资的最大值，最小值，平均值，总和

Select max(salary),min(salary),avg(salary),sum(salary) from employees; 20. 查询各job_id的员工工资的最大值，最小值，平均值，总和 Select max(salary),min(salary),avg(salary),sum(salary) from employees group by job_id;

21. 选择具有各个job_id的员工人数

Select job_id,count(*) from employees group by job_id; 22. 查询员工最高工资和最低工资的差距（DIFFERENCE） Select max(salary)-min(salary) \23. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

Select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary) >=6000;

24. 查询所有部门的名字，location_id，员工数量和工资平均值 Select

d.department_name,d.location_id,count(e.employee_id),avg(e.salary) from

employees

e,departments

group

d

where by

e.department_id(+)=d.department_id

d.location_id,d.department_name;

25. 查询公司的人数，以及在1995-1998年之间，每年雇用的人数，结果类似下面的格式

total 30 1995 3 1996 4 1997 6 1998 7 Select count(employee_id),to_char(hire_date,'yyyy') \from employees where to_char(hire_date,'yyyy') between 1995 and 1998 group by to_char(hire_date,'yyyy') order by y;

Select

子查询

26. 查询和zlotkey相同部门的员工姓名和雇用日期

Select last_name,hire_date,department_id from employees where department_id=(select

department_id

from

employees

where

lower(last_name)='zlotkey') ;

27. 查询工资比公司平均工资高的员工的员工号，姓名和工资。 Select

employee_id,last_name,salary

from

employees

where

salary>(select avg(salary) from employees );

28. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

Select employee_id,last_name,department_id from employees where department_id =any (select department_id from employees where lower(last_name) like '%u%');

29. 查询在部门的location_id为1700的部门工作的员工的员工号，

本文来源：https://www.bwwdw.com/article/wz1t.html