wtxtools

C++博客 首页 新随笔 联系 聚合 管理
  11 Posts :: 8 Stories :: 2 Comments :: 0 Trackbacks

以下是今天oracle sql 课堂中做的一些小练习,希望会对同学们有点帮助!
(注:sql语句查询结果均以上课时例题表为基础,如表中数据变化,查询结果会有所不同)

两表没有任何关联时会产生迪卡尔机:
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 );

posted on 2006-11-07 10:43 传统的浪漫 阅读(1145) 评论(1)  编辑 收藏 引用

评论

# re: ORACLE练习集 2008-11-03 13:05 谢谢
谢谢你!  回复  更多评论
  


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理