达内Oracle.doc

1、ORACLE 经典案例（ORACLE-E-001）员工信息综合查询定义ORACLE-F-001ORACLE-F-005 的综合练习：1.用 sqlplus 连接数据库时，为什么会出 Oracle not available 错误？2.找出员工的姓中（last_name）第三个字母是 a 的员工名字3.找出员工名字中含有 a 和 e 的4.找出所有有提成的员工，列出名字、工资、提出，显示结果按工资从小到大，提成从小到大5.42 部门有哪些职位6.哪些部门不是 Sales 部7.显示工资不在 1000 到 1550 之间的员工信息：名字、工资，按工资从大到小排序。8.显示职位为 Stock Cle

2、rk 和 Sales Representative，年薪在 14400 和 17400 之间的员工的信息：名字、职位、年薪。9.解释 select id ，commission_pct from s_emp where commission_pct is null 和 select id ， commission_pct from s_emp where commission_pct = null 的输出结果。10. select 语句的输出结果为select * from s_dept; select * from s_emp; select * from s_region; select

3、* from s_customer; 当前用户有多少张表，结果集有多少条记录。11. 判断 select first_name , dept_id from s_emp where salary 1450是否抱错，为什么？答案1.oracle server（即通常所说的数据库）是否启动，ORACLE_SID是否正确设置。2.select last_name from s_emp where last_name like _a%; 3.select first_name from s_emp where first_name like %a% and first_name like %e%; 比

4、较：select first_name from s_emp where first_name like %a%e%; 4.select first_name , salary , commission_pct from s_emp where commission_pct is not null order by salary desc , commission_pct; 5.select distinct title from s_emp where dept_id = 42 6.select id , name ,region_id from s_dept where name Sale

5、s 7.select first_name , salary from s_emp where salary not between 1000 and 1550 order by salary desc 8.select first_name , title , salary*12 ann_sal from s_emp where title in (Stock Clerk, Sales Representative and salary between 1200 and 1450;9.is null 判断是否为空，=null 判断某个值是否等于 null，null = null 和 null

6、 null都为 false。10. select select * from |table_name|; from user_tables; 11.隐式数据类型转换。（ORACLE-E-002）人力资源系统数据复杂查询定义ORACLE-F-006ORACLE-F-008 的综合练习：1.改变 NLS_LANG 的值，让 select to_char(salary*12,L99,999.99) from s_emp 输出结果的货币单位是￥和$ 2.列出每个员工的名字，工资、涨薪后工资（涨幅为 8%），元为单位进行四舍五入3.找出谁是最高领导，将名字按大写形式显示4.Ben 的领导是谁（Ben 向

7、谁报告）。5.Ben 领导谁。（谁向 Ben 报告）。6.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资7.哪些员工和 Biri(last_name)同部门8.哪些员工跟 Smith(last_name)做一样职位9.哪些员工跟 Biri(last_name)不在同一个部门10. 哪些员工跟 Smith(last_name)做不一样的职位11. 显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称12. 显示 Operations 部门有哪些职位13. 整个公司中，最高工资和最低工资相差多少14. 提成大于 0 的人数15. 显示整个公司的最高工资、最低

8、工资、工资总和、平均工资，保留到整数位。16. 整个公司有多少个领导17. 列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期答案1.setenv NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK setenv NLS_LANG AMERICAN_AMERICA.US7ASCII 2.select first_name , salary , round(salary*1.08) from s_emp;3.select upper(first_name) from s_emp where manager_id is null;4.selec

9、t e1.first_name from s_emp e1 , s_emp e2 where e2.manager_id = e1.id and e2.first_name = Ben; 5.select e1.first_name from s_emp e1 , s_emp e2 where e1.manager_id = e2.id and e2.first_name = Ben; 6.select e.first_name , e.salary , m.first_name , m.salary from s_emp e , s_emp m where e.manager_id = m.

10、id and e.salary m.salary; 7.select e1.first_name from s_emp e1 , s_emp e2 where e1.dept_id = e2.dept_id and e2.last_name = Biri and e1.last_name Biri; 8.select e1.first_name from s_emp e1 , s_emp e2 where e1.title = e2.title and e2.last_name = Smith and e1.last_name Smith; 9.select e1.first_name fro

11、m s_emp e1 , s_emp e2 where e1.dept_id = e2.dept_id(+) and e2.last_name(+) = Biri and e2.last_name is null; 10. select e1.first_name , e2.first_name from s_emp e1 , s_emp e2 where e1.title(+) = e2.title and e2.last_name(+) = Smith and e2.last_name is null; 11. select d.name dname , r.name rname , fi

12、rst_name , commission_pct from s_emp e , s_dept d , s_region r where e.dept_id = d.id and d.region_id = r.id and mission_pct is not null; 12. select distinct e.title from s_emp e , s_dept d where e.dept_id = d.id and d.name = Operations; 13. select max(salary) min(salary) from s_emp; 14. select coun

13、t(*) from s_emp where commission_pct 0; 15. select max(salary) , min(salary) , sum(salary) , round(avg(salary) from s_emp; 16. select count(distinct manager_id) from s_emp; 17. select e1.first_name , e1.start_date , e1.salary from s_emp e1 , s_emp e2 where e1.dept_id = e2.dept_id and e1.start_date e

14、2.start_date and e1.salary e2.salary; （ORACLE-E-003）复杂业务数据关联查询定义ORACLE-F-009ORACLE-F-011 的综合练习：1.各个部门平均、最大、最小工资、人数，按照部门号升序排列2.各个部门中工资大于 1500 的员工人数3.各个部门平均工资和人数，按照部门名字升序排列4.列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数5.该部门中工资高于 1000 的员工数量超过 2 人，列出符合条件的部门：显示部门名字、地区名称6.哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）7.哪些员工的工

15、资，介于 32 和 33 部门（33 高些）平均工资之间8.所在部门平均工资高于 1500 的员工名字9.列出各个部门中工资最高的员工的信息：名字、部门号、工资10. 最高的部门平均工资值的是多少11. 哪些部门的人数比 32 号部门的人数多12. Ben 的领导是谁（非关联子查询）13. Ben 领导谁（非关联子查询）14. Ben 的领导是谁（关联子查询）15. Ben 领导谁（关联子查询）16. 列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期（关联子查询）17. 哪些员工跟 Biri(last_name)不在同一个部门（非关联子查询）18. 哪些员工跟 Bi

16、ri(last_name)不在同一个部门（关联子查询）19. Operations 部门有哪些职位（非关联子查询）20. Operations 部门有哪些职位（关联子查询）答案1.select dept_id , avg(salary) asal , max(salary) asal , min(salary) isal , count(*) cnt from s_emp group by dept_id order by dept_id; 2.select dept_id , count(*) cnt from s_emp where salary 1500 group by dept_id

17、; 3.select max(d.name) dname , max(r.name) rname , avg(e.salary) avgsal from s_emp e , s_dept d , s_region r where e.dept_id = d.id and d.region_id = r.id group by dept_id order by dname , rname; 4.select e1.dept_id , e1.salary , count(*) cnt from s_emp e1 , s_emp e2 where e1.salary = e2.salary and

18、e1.dept_id = e2.dept_id and e1.id e2.id group by e1.dept_id , e1salary 5.select max(d.name) dname , max(r.name) rname , count(*) cnt from s_emp e , s_dept d , s_region r where e.dept_id = d.id and d.region_id = r.id and e.salary 1000 group by dept_id having count(*) 2; 6.select first_name , salary f

19、rom s_emp where salary (select avg(salary) from s_emp) order by salary desc; 7.select first_name , salary from s_emp where salary between (select avg(salary) from s_emp where dept_id = 32) and (select avg(salary) from s_emp where dept_id = 33); 8.select first_name , salary from s_emp where dept_id i

20、n (select dept_id from s_emp group by dpet_id having avg(salary) 1500); 9.select first_name , salary , dept_id from s_emp where (dept_id , salary) in (select dept_id , max(salary) from s_emp group by dept_id); 10. select max(avg(salary) from s_emp; 11. select dept_id , count(*) cnt from s_emp group

21、by dept_id having count(*) (select count(*) from s_emp where dept_id = 32); 12. select first_name from s_emp where id in (select manager_id from s_emp where first_name = Ben); 13. select first_name from s_emp where manager_id = (select id from s_emp where first_name = Ben); 14. select first_name fro

22、m s_emp o where exists (select 1 from s_emp i where first_name = Ben and i.manager_id = o.id); 15. select first_name from s_emp o where exists (select 1 from s_emp i where first_name = Ben and i.id = o.manager_id); 16. select first_name from s_emp o where exists (select 1 from s_emp i where i.dept_i

23、d = o.dept_id and o.start_date i.start_date and o.salary i.salary); 17. select first_name from s_emp where dept_id not in (select dept_id from s_emp where last_name = Biri); 18. select last_name from s_emp o where not exists (select 1 from s_emp i where i.dept_id = o.dept_id and i.last_name = Biri); 19. select distinct title from s_emp where dept_id in (select id from s_dept where name = Operations); 20. select distinct title from s_emp e where exists (select 1 from s_dept d where e.dept_id = d.id and name = Operations;