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 '%94';

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 st_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 st_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 st_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.选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，

结果类似于下面的格式

Select e.employee_id "employees",st_name "Emp#",d.manager_id "Mgr#",st_name "manger" from employees e,employees d where

e.manager_id=d.employee_id(+);

6. 查询各部门员工姓名和他们的同事姓名，结果类似于下面的格式

Select e.department_id "Department_id", st_name "Last_name", st_name "colleague" from employees e join employees d on(d.department_id=e.department_id) where st_name<>st_name;