(oracle)exercise_answers.txt

讲了一些关于oracle的基本配置

								课堂练习答案
两表没有任何关联时会产生迪卡尔机：
select   first_name , name  from    s_emp , s_dept;

等值连接：
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）
select   first_name ,   name from  s_emp, s_dept   where s_emp.dept_id=s_dept.id;

练习二：每个员工所在的部门和部门所在的地区
select first_name , name   from s_emp, s_dept,  s_region  where  s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;

非等值连接：
练习三:查出每个员工和每个员工的工资级别
 select    a.ename , a.sal, b.grade from emp a , salgrade b  where a.sal between b.losal and b.hisal;
 select    a.ename , a.sal, b.grade from  emp a , salgrade b  where a.sal>=b.losal  and  a.sal<=b.hisal;

自连接：
select   first_name   , manager_id   from  s_emp;
练习四:查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select  e.first_name , m.first_name   from s_emp e , s_emp m  where   e.manager_id=m.id;

外连接：(防止空值时，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少；
select  e.first_name , m.first_name   from s_emp e , s_emp m  where   e.manager_id=m.id(+);
练习五：查看员工分部的部门：
select  distinct(deptno) from emp ;
找出没有员工的部门：(很经典的一个例子,用外连接来解决的标准做法,这是一种方式，用子查询也可以实现）
第一步： 
select     e.deptno , d.deptno  from emp e , dept d  where  e.deptno(+)=d.deptno;
第二步：
select     e.deptno , d.deptno  from emp e , dept d  where  e.deptno(+)=d.deptno   and   e.deptno is null;
练习六：查询员工有多少人有提成：
select  count( commission_pct ) from    s_emp ;
select   sum(commission_pct)/ count(*)   from     s_emp;

练习七：员工分部在多少个不同的部门：
select   count(dept_id)  from s_emp;
select   count(distinct dept_id) from   s_emp;

练习八：求各个部门的平均工资：
select  dept_id ,  avg(salary) aa    from    s_emp    group by   dept_id   order by  aa ;
select  dept_id ,  avg(salary) aa    from    s_emp    group by   dept_id    ;
//体会下句sql
select   region_id , count(*)  from  s_dept ****此句会有错,请多体会********
select   max(region_id)  , count(*) from  s_dept;  (强制语法上可以正确,但是不能保证结果也会正确）

练习九：求各个部门不同工种的平均工资：
select     dept_id , title,  avg(salary)  from s_emp   group   by dept_id , title  ;
练习十：查询哪些部门的平均工资比2000高:
select    dept_id,  avg(salary) aa  from s_emp   group by (dept_id)    having      avg(salary)>2000;

练习十一：除了42部门以外的部门的平均工资:
select   dept_id  ,  avg(salary)   from  s_emp  group by (dept_id ) having    dept_id!=42;
select   dept_id  ,  avg(salary)   from  s_emp   where   dept_id!=42  group by (dept_id ) ;(此种sql效率要高，先过滤再计算）
练习十二：求各个部门的平均工资:
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name)  ***
select   max(d.name)  , avg(e.salary)  , max(r.name)  
from s_emp e,   s_dept  d ,   s_region  r  
where  e.dept_id = d.id  and  d.region_id=r.id 
group  by   d.id ;

关于子查询:  Subqueries
练习十三：找出所有员工中，工资最低的那个员工：( 利用子查询 ）>
select    first_name,  salary    from s_emp   where   salary = (  select  min(salary)  from s_emp)    ;
//这样写会出错姓名和工资不一致
select max(first_name),  min(salary)  from s_emp;

练习十四：查询谁跟Smith的工种一样：
select   last_name from  s_emp  where last_name='Smith';
//下种写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题
select  last_name  , title  from s_emp   where title =(  select   title  from s_emp  where  last_name='Smith'  )    and  last_name <> 'Smith'  ;
//这种写法才考虑的比较全面
select  last_name  , title  from s_emp   where title   in   (  select   title  from s_emp  where  last_name='Smith'  )    and  last_name <> 'Smith'  ;
使用子查询时应注意：  单行子查询返回多个结果时会有错误    single-row  subquery returns  more  than one value

练习十五：查出哪些员工的工资比平均工资低：
select    *  from s_emp  where     salary   <  ( select  avg(salary)  from   s_emp)  ;
哪些部门的平均工资比32部门的平均工资要低：
第一步先查出各个部门的平均工资：
select  min(avg(salary  )  ) from   s_emp   group by  dept_id;
第二步再查出哪个部门的工资是最低的:
select    dept_id,  avg(salary)  from  s_emp   group by dept_id   having   avg(salary) =  (select  min(avg(salary)  ) from  s_emp  group by  dept_id ) ;
练习十六：哪个部门里没有员工(用子查询的方式来实现）：
select   deptno  from    dept    where   deptno   not  in ( select     deptno    from   emp );