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<>20 and e.sal>all(select sal from emp where deptno=20);
解法2
select e.ename,e.sal,d.dname 
from emp e,dept d
where e.deptno=d.deptno and e.deptno<>20 and e.sal>(select max(sal) from emp where deptno=20);

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

select job,avg(sal) 
from emp 
group by job 
having avg(sal)>2000;

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=1

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