oracle习题及答案.doc

查询工资大于12000的员工姓名和工资 Select last_name|| ||first_name,salary from employees where salary 12000; 查询员工号为176的员工的姓名和部门号 Select last_name|| ||first_name,department_id from employees where employee_id=176; 选择工资不在5000到12000的员工的姓名和工资 Select last_name|| ||first_name,salary from employees where salary not between 5000 and 12000; 选择雇用时间在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; 选择在20或50号部门工作的员工姓名和部门号 Select last_name|| ||first_name,department_id from employees where department_id in (20,50); 选择在1994年雇用的员工的姓名和雇用时间 Select last_name|| ||first_name,hire_date from employees where hire_date like %94; 选择公司中没有管理者的员工姓名及job_id Select last_name|| ||first_name,job_id from employees where Manger_id is null; 选择公司中有奖金的员工姓名，工资和奖金 Select last_name|| ||first_name,salary,commission_pct from employees where commission_pct is not null; 选择员工姓名的第三个字母是a的员工姓名 Select last_name|| ||first_name from employees where last_name|| ||first_name like ___a%; 选择姓名中有字母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%; 多表查询 显示所有员工的姓名，部门号和部门名称。 Select e.last_name,d.department_id,d.department_name from employees e , departments d where (e.department_id=d.department_id); 查询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; 选择所有有奖金的员工的 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; 选择在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.