最新oracle习题及答案.docx

1、最新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 n

2、ot 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.选择在

3、1994年雇用的员工的姓名和雇用时间Select last_name| |first_name,hire_date from employees where hire_date like %94;7.选择公司中没有管理者的员工姓名及job_idSelect 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 commissi

4、on_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

5、_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_idSelect 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 ,

6、department_name , location_id , citySelect 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 , de

7、partment_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.选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employeesEmp#managerMgr#kochhar101king100Se

8、lect e.employee_id employees,e.last_name Emp#,d.manager_id Mgr#,d.last_name manger from employees e,employees d where e.manager_id=d.employee_id(+);6. 查询各部门员工姓名和他们的同事姓名，结果类似于下面的格式Department_idLast_namecolleague20fayhartsteinSelect e.department_id Department_id, d.last_name Last_name, e.last_name col

9、league from employees e join employees d on(d.department_id=e.department_id) where d.last_namee.last_name; 分组查询16.组函数处理多行返回一行（true）17.组函数不计算空值（ false）18.where子句在分组之前对检索进行过滤 ( true)19.查询公司员工工资的最大值，最小值，平均值，总和Select max(salary),min(salary),avg(salary),sum(salary) from employees;20.查询各job_id的员工工资的最大值，最小

10、值，平均值，总和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) DIFFERENCE from employees;23.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不

11、计算在内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 d where e.department_id(+)=d.department

12、_id group by d.location_id,d.department_name;25.查询公司的人数，以及在1995-1998年之间，每年雇用的人数，结果类似下面的格式total1995199619971998303467Select count(employee_id),to_char(hire_date,yyyy) y;count(*) from employees where to_char(hire_date,yyyy) between 1995 and 1998 group by to_char(hire_date,yyyy) order by y;Select 子查询26

13、.查询和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 );

14、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的部门工作的员工的员工号，department_id和job_idSelect employee_id,department_id,job_id from employees wh

15、ere department_id = any (select department_id from departments where location_id=1700);30.查询管理者是king的员工姓名和工资Select last_name,salary from employees where manager_id=(select employee_id from employees where last_name=King); 创建和管理表31.创建表deptnameNull?typeidNumber(7)nameVarchar2(25)Create table dept ( id

16、 Number(7),name varchar(25);32.将表departments中的数据插入表dept中Insert into dept( SELECT department_id, department_name FROM departments);33.创建表empnameNull?typeidNumber(7)First_nameVarchar2(25)Last_nameVarchar2(25)Dept_idNumber(7)Create table emp (id Number(7),First_name varchar(25),Last_name varchar(25),De

17、pt_id number(7);34.将列Last_name的长度增加到50Alter table emp modify (last_name varchar2(50); 35.查询数据字典视图user_tables检查刚才的操作Select * from user_tables;36.根据表employees创建employees2Create table employees2 asselect * from employees;37.删除表empDrop table emp;38.将表employees2重命名为empRename employees2 to emp;39.在表dept和e

18、mp中添加新列test_column，并检查所作的操作Alter table emp add(test_column varchar(10); 单行函数40.显示系统时间Select to_char(sysdate DD-MON-YYYY)from dual;41.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）Select employee_id,last_name| |first_name,salary,salary*1.2 newsalary from employees;42.将员工的姓名按首字母排序，并写出姓名的长度（length）Select las

19、t_name,length(last_name) from employees order by substr(last_name,1,1) desc;43.查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。Select last_name| |first_name,months_between(sysdate,hire_date) worked_month from employees;44.查询员工的姓名和工资，按下面的形式显示Last_nameSALARYking$24000Select lower(last_name) last_name,lpad(sal

20、ary,15,$) Salary from employees;45.查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列Select last_name| |first_name,months_between(sysdate,hire_date) worked_month from employees order by worked_month desc;46.做一个查询，产生下面的结果 earns monthly but wants Dream SalaryKing earns $24000 monthly but wants $72000Select K

21、ing| earns |lpad(salary,6,$)| monthly but wants |lpad(salary*3,6,$) Dream Salary from employees;47.做一个查询，产生下面的结果add_month(6)+4Last_nameHire_datereiewking17-jun-87Monday,the twenty-first of December , 1987Select lower(last_name) Last_name,hire_date,to_char(add_months(hire_date,6)+4,DAY,MONTH,YYYY) Hi

22、re_date from employees where last_name like King;9做一个查询，产生下面的结果Employees_and_their_salarysKing*其中每一个*代表一千元。Select rpad(last_name,salary/1000+length(last_name),$) from employees where last_name like King;48.使用decode函数，按照下面的条件：job gradeAD_PRES AST_MAN BIT_PROG CSA_REP DST_CLERK E产生下面的结果Last_nameJob_id

23、GradekingAD_PRESASelect last_name,job_id,Decode (job_id ,AD_PRES, A , ST_MAN, B , IT_PROG, C , SA_REP, D , ST_CLERK, E ,) Grade from employees;49.将第9题的查询用case函数再写一遍。Select last_name,job_id, Case job_id when AD_PRES then A When ST_MAN then B When IT_PROG then C When SA_REP then D When ST_CLERK then E end Grade from employees;