oracle习题集-高级查询2

1.列出员工表中每个部门的员工数和部门编号

Select deptno,count(*) from emp group by deptno;

2.列出员工表中，员工人数大于3的部门编号和员工人数

Select deptno,count(*) from emp group by deptno having count(*)>3;

3.列出员工表中，员工人数大于3的部门的部门编号，部门名称和部门位置

答案1
(1). Select deptno,count(*) from emp group by deptno having count(*)>3;--x

(2).

select d.*

from dept d ,(Select deptno,count(*) from emp group by deptno having count(*)>3)x

where x.deptno=d.deptno;

答案2：
select * from dept 
where deptno in (select deptno from emp group by deptno having count(*)>3);

4.列出员工表中，员工人数大于3的部门的部门编号，名称，位置和员工人数

Select d.*,x.co 
from dept d，(select deptno,count(*) co from emp group by deptno having count(*)>3)x 
where d.deptno=x.deptno；
(注意看聚合函数的处理方式)

5. 列出员工表中每个部门的员工数（员工数必须大于3），和部门名称

select * from dept d,(select deptno,count(*) from emp group by deptno having count(*)>3)x where d.deptno=x.deptno;

6. 找出工资比JONES多的员工

Select * from emp where sal>(select sal from emp where ename=’JONES’);

7.列出所有员工的姓名和其上级的姓名（表的自连接）

Select w.ename,m.ename from emp w,emp m where w.mgr=m.empno;

8.以职位分组，找出平均工资最高的两种职位

select * 
from (select job,avg(sal) from emp group by job order by avg(sal) desc)
where rownum<=2;

9.查找出不在部门20，且比部门20中任何一个人工资都高的员工的姓名、部门名称

解法1    
select e.ename,e.sal,d.dname 
from emp e,dept d 
where e.deptno=d.deptno and e.deptno<> and e.sal>all(select sal from emp where deptno=);

解法2
select e.ename,e.sal,d.dname 
from emp e,dept d
where e.deptno=d.deptno and e.deptno<> and e.sal>(select max(sal) from emp where deptno=);

10.得到平均工资大于2000的工作职种

select job,avg(sal)

from emp

group by job

having avg(sal)>;

11. 分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500

解法1
Select deptno,avg(sal) from emp where sal>2000 group by deptno having avg(sal)>2500

解法2
select deptno ,avg (sal) avg

from emp

where sal>2000---x

group by deptno ;

select * from x where x.avg>2500;

12.得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置

--拿着最低工资和去找编号,拿到编号在去到编号部门,部门名称,部门位置
select min(sum(sal)) from emp3 group by deptno;---x

select deptno  from emp3

group by deptno

having sum(sal)=(select min(sum(sal)) from emp3 group by deptno)x;--w

select * 
from dept 
where deptno in(select deptno  from emp3

　　　　　　　　　 group by deptno

　　　　　　　　　 having sum(sal)=(select min(sum(sal)) from emp3 group by deptno));

13. 分部门得到平均工资等级为3级（等级表）的部门编号

select deptno ,avg(sal) from emp group by deptno --e

select e.* ,s.grade

from salgrade s,(select deptno ,avg(sal)avg_sal from emp group by deptno)e

where e.avg_sal between s.losal and s.hisal and s.grade=3;

14. 查找出部门10和部门20中，工资最高第3名到第5名的员工的员工名字，部门名字，部门位置

思路1:连接,排序,取3-5
思路2:排序,取3-5,连接
思路3:排序,连接,取3-5;(错误思路)
下面是思路2的解法:
select emp.*from emp where deptno in(10,20) order by sal desc;---排序--x

select rownum ro,x.* from (select emp.*from emp order by sal desc)x-- rownum加入--w

select *

from (select rownum ro,x.* from (select emp.*from emp order by sal desc)x)w

where ro >=3 and ro<=5; --取3-5行 --e

-- 表连接

select e.ename ,d.dname,d.loc

from dept d ,(select *

　　　　　　　　from (select rownum ro,x.* from (select emp.*from emp where deptno in(10,20) order by sal desc)x)w

　　　　　　　　where ro >=3 and ro<=5)e

where e.deptno=d.deptno;

15.查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入

解法1
select w.* from emp w,emp m

where w.mgr = m.empno and w.sal+nvl(w.comm,0) > m.sal+nvl(m.comm,0);


解法2
Select ename,sal+nvl(comm,0)

from emp w

where (sal+nvl(comm,0))>(select sal+nvl(comm,0) from emp  m where w.mgr=m.empno);

16 .查找出工资等级不为4级的员工的员工名字，部门名字，部门位置

解法1
select e.deptno,e.ename,s.grade from emp e ,salgrade s

where e.sal between s.losal and s.hisal;--x

select  x.ename,d.dname,d.loc,x.grade

from (select e.deptno,e.ename,s.grade from emp e ,salgrade s

where e.sal between s.losal and s.hisal)x,dept d

where x.deptno=d.deptno and x.grade <> 4;

解法2

Select e.ename,d.dname,d.loc,s.grade
 from emp e,dept d,salgrade s 
where e.deptno=d.deptno and e.sal between s.losal and s.hisal and s.grade<>4;

17. 查找出职位和‘MARTIN’或者‘SMITH’一样的员工的平均工资

select job from emp where ename in('MARTIN','SMITH');

select avg(sal) from emp

where job in(select job from emp where ename in('MARTIN','SMITH'))

18.查找出不属于任何部门的员工

Select * from emp where deptno is null;

19. 按照部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字，部门位置）

解法1
思路：子查询—行内视图

1. 列出每个部门的员工人数，并且按照员工人数进行降序排列   ---x

Select deptno,count(*) cou from emp group by deptno order by count(*) desc;

2. 将x与dept表做表连接    ---m

Select x.cou dept.* from x,dept where x.deptno=dept.deptno;

3.取出结果集的第二条到第五条记录

Select * from (select rownum ro,m.* from m) where m.ro>=2 and m.ro<=5;

4.替换，得到最终的结果。

Select * 
from (select rownum ro,m.* 
　　　　from (Select x.cou dept.* from (Select deptno,count(*) cou from emp group by deptno order by count(*) desc)x,dept 
　　　　where.deptno=dept.deptno)m) 
where m.ro>=2 and m.ro<=5;

解法2
select deptno,count(*)

from emp

group by deptno

order by count(*) desc --x

select rownum ro,x.*

from (select deptno,count(*)

from emp

group by deptno

order by count(*) desc)x --e

select d.deptno,d.loc

from (select rownum ro,x.*

    from (select deptno,count(*)

    from emp

    group by deptno

    order by count(*) desc)x)e,dept d

where e.deptno=d.deptno and (ro between 2 and 2)

解法3
思路2：

1.  先做表连接,表连接之后分组，求count(*)，最后安装count(*)进行降序排列，得到视图X

Select e.deptno,count(*),d.dname,d.loc

from emp e,dept d

where d.deptno=e.deptno

group by e.deptno,d.dname,d.loc

order by count(*) desc;

2.    取出视图x的第二到第五行

Select *

from （select rownum ro, x.* from x）

where ro>=2 and ro<=5;

3.    替换x,得到最终结果：

Select *

from （select rownum ro, x.*

       from（Select e.deptno,count(*),d.dname,d.loc

             from emp e,dept d

             where d.deptno=e.deptno

             group by e.deptno,d.dname,d.loc

             order by count(*) desc） x

             ）

where ro>=2 and ro<=5;

20. 查出KING所在部门的工作年限最大的员工名字

select *

from emp

where deptno =(select deptno from emp where ename='KING')

order by hiredate  -- x

select x.*,rownum ro

from (select *

     from emp

     where deptno =(select deptno from emp where ename='KING')

     order by hiredate)x   --w

select w.ename

from (select x.*,rownum ro

      from (select *

           from emp

           where deptno =(select deptno from emp where ename='KING')

           order by hiredate)x)w

where ro=1;

21 查出工资成本最高的部门的部门号和部门名称

select deptno,sum(sal)

from emp

group by deptno

order by sum(sal) desc --x

select x.deptno,d.dname

from (select deptno,sum(sal)

      from emp

      group by deptno

      order by sum(sal) desc)x,dept d

where x.deptno=d.deptno and rownum=

22. 显示与Blake在同一部门工作的雇员的工作和受雇日期，Blake不包含在内

Select ename,job,hiredate

from emp

where deptno=(select deptno from emp where ename=’BLAKE’) and ename<>’BLAKE’;

23.显示被King直接管理的雇员的姓名以及工资

select ENAME,SAL

from EMP

where MGR=(SELECT EMPNO FROM EMP WHERE ENAME=’KING’);

24.显示能获得与Scott一样工资和奖金的其他雇员的姓名、受雇日期以及工资。

SELECT ENAME,JOB,SAL

FROM EMP

WHERE (SAL,nvl（COMM，0）)=（select sal,nvl(comm,0)

                             from emp

                             where ename=’SCOTT’）AND ENAME<>’SCOTT’;

面试题1

create table emp5(

id number(2),

name varchar2(5)

)

create table sext(

id number(2),

sex varchar(2)

)

insert into emp5

select 1,'A' from dual union

select 2,'B' from dual union

select 3,'C' from dual union

select 4,'D' from dual ;

insert into sext

select 1,'男' from dual union

select 4,'女' from dual union

select 5,'男' from dual

 ;

1. 找出忘记填写性别的员工（用两种方法）

解法1
select e.*,s.sex

from  emp5 e left join sext s on e.id=s.id --x

select id ,name

from (select e.*,s.sex

     from  emp5 e left join sext s on e.id=s.id)x

where x.sex is null解法2   select * from emp1 where id not in(select id from sext);

解法3   select id from emp1 minus select id from sext;

面试题2

create table AAA(

mc varchar2(3),

sl number(3)

)

create table BBB(

mc varchar(3),

sl number(3)

)

insert into AAA

select 'A',100 from dual union

select 'B',120 from dual

insert into BBB

select 'A',10 from dual union

select 'A',20 from dual union

select 'B',10 from dual union

select 'B',20 from dual union

select 'B',30 from dual ;

1.用一条SQL语句计算出商品A，B目前还剩多少

用一条SQL语句计算出商品A，B目前还剩多少

select mc,sum(sl) sum_sl

from BBB

group by mc --x

select a.mc,a.sl-x.sum_sl

from AAA a,(select mc,sum(sl) sum_sl

            from BBB

            group by mc)x

where a.mc=x.mc;

面试题三

人员情况表（employee）中字段包括：员工号（ID），姓名（name）,年龄（age）,文化程度（wh）:包括四种情况（本科以上，大专，高中，初中以下），
现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。结过如下：

学历        年龄        人数        百分比

本科以上    20            34            14

大专        20            33            13

高中        20            33            13

初中以下    20            100            40

本科以上    21            50            20

Select wh as 学历,age as 年龄，count(*) as 人数，round(count(*)/(select count(*) from employee)*100) as 百分比

from employee

group by  age,wh;

面试题四

四张表：学生表student(sid, sname),教师表teacher(tid, tname),课程表course(cid,cname,ctype),选课表choose_course(ccid,sid,tid,cid)

创建表

create table  student(

sid number(2),

sname varchar2(5)

)

create table teacher(

tid number(2),

tname varchar2(5)

)

create table course(

cid number(2),

cname varchar2(5),

ctype varchar2(5)

)

create table choose_course(

ccid number(2),

sid number(2),

tid number(2),

cid number(2)

)

插入记录

Insert into student values(1, '小明');

Insert into student values(2, '小花');

Insert into teacher values(1, '陈红');

Insert into teacher values(2, '陈白');

Insert into course values(1, '语文' ,'文科');

Insert into course values(2, '数学','理科');

--小明选了陈红老师的语文

Insert into choose_course values(1,1,1,1);

--小明选了陈红老师的数学

Insert into choose_course values(2,1,1,2);

--小花选了陈红老师的数学

Insert into choose_course values(3,2,1,2);

--小明选了陈白老师的语文

Insert into choose_course values(4,1,2,1);

--小花选了陈红老师的语文

Insert into choose_course values(5,2,1,1);

1.查找陈红老师教的学生是哪些？

解法1
select distinct ch.sid from teacher t,choose_course ch

where t.tid=ch.tid and t.tname='陈红' --x

select s.sid,s.sname

from student s

where sid in(select distinct ch.sid from teacher t,choose_course ch

             where t.tid=ch.tid and t.tname='陈红'

             )


解法2
Select s.sid,s.sname

from student s,choose_course cc,teacher t

where s.sid=cc.sid and cc.tid=t.tid and t.tname=’陈红’；

2.找学生小明所有的文科老师

解法1
（1）学生表和选课表相连---x

select s.*,cc.*

from student s,choose_course cc

where s.sid=cc.sid;

(2)    将（1）的结果与course表相连---m

Select c.*,x.*

from course c,x

where c.cid=x.cid;

(3)    将（2）的结果与teacher表相连

Select t.*,m.*

from course teacher,m

where t.tid =m.tid;

(4)    替换，得到最终结果：

Select t.*

from teacher inner join （Select c.*,x.*

                          from course c,（select s.*,cc.*

                                          from student s,choose_course cc

                                          where s.sid=cc.sid）x

                          where c.cid=x.cid）m

                    on t.tid=m.tid

where m.sname=’小明’，m.type=’文科’;

解法2

Select *

from student s,choose_course cc, teacher t, course c

where s.sid=cc.sid and cc.tid=t.tid and cc.cid=c.cid and s.name=‘小明’and c.type=’文科’；

3.教的学生最少的老师是哪位？

最少的老师可以大于两个

教学生最少的个数-匹配的老师的id-找老师的姓名

select min(count(*))

from choose_course

group by tid  --x

select tid

from choose_course

group by tid

having count(*) = (select min(count(*))

                from choose_course

                group by tid

                );--w

select *

from teacher

where tid in (select tid

              from choose_course

              group by tid

              having count(*) = (select min(count(*))

                              from choose_course

                              group by tid

                              ))

面试题五

用一条SQL语句，查询出每门课都大于80分的学生姓名

Name        kecheng        fenshu

张三        语文        81

张三        数学        75

李四        语文        76

李四        数学        90

王五        语文        81

王五        数学        100

王五        英语        90

Select name from stu where name not in(select name from stu where fenshu<80);

华为面试题

一个表中的id有多条记录，把所有这个id的记录查出来，并显示共有多少条记录？

Select id,count(*) from 表名 group by id

巴特西

oracle习题集-高级查询2

最新文章

热门文章