问:
1.列出emp 表中各部门号,最高工资,最低工资
答:
2.列出emp表中各部门job为'CLERK'的员工的最低工资,最高工资
答:
3.对于emp表中最低工资小于1000的部门,列出job为'CLERK'的员工的部门号,最低 工资,最高工资
答:
4.根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资
答:
5.列出'张三'所在部门中每个员工的姓名和部门号
答:
6.列出每个员工的姓名,工作,部门号,部门名
答:
7.列出emp中工作为'CLERK'的员工的姓名,工作,部门号,部门名
答:
8.对于emp中有管理者的员工,列出姓名,管理者姓名(管理者外键为mgr)
答:
9.对于dept表中,列出所有部门名,部门号,同时列出个部门工作为'CLERK'的员工名与 工作
答:
10.对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序
答:
11.对于emp,列出各个部门中平均工资高于本部门平均水平的员工数和部门号,按部门 号排序
答:
12.对于emp中工资高于本部门平均水平,人数多于1人的,列出部门号,人数,按部门号排序
答:
13.对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数
oracle sql面试题求助?
答案:4 悬赏:10
解决时间 2021-03-15 00:11
- 提问者网友:南佳人~
- 2021-03-13 23:53
最佳答案
- 二级知识专家网友:走,耍流氓去
- 2021-03-14 01:12
--1
select max(sal) ,min(sal) from emp group by deptno;
--2
select max(sal) ,min(sal) from emp where job='CLERK' group by deptno;
--3
select deptno,max(sal) ,min(sal) from emp where job='CLERK' and deptno=(select deptno from emp group by deptno having min(sal)<1000) group by deptno;
--4
select ename,deptno,sal from emp order by deptno desc,sal
--5
select ename,deptno from emp where deptno = (select deptno from emp where ename='张三')
--6
select e.ename,e.job,e.deptno,d.dname from emp e left join dept d on d.deptno=e.deptno
--7
select e.ename,e.job,e.deptno,d.dname from emp e left join dept d on d.deptno=e.deptno where e.job='CLERK'
--8
select e.ename,m.mname from emp e left join mgr m on m.mgr=e.mgr
--9
select * from (select ename, job from emp where job='CLERK') a union all select dname, deptno from dept ;
--10
select e.deptno,e.ename,e.sal from emp e left join (select deptno, avg(sal) SV from emp group by deptno) b on
b.deptno=e.deptno where e.sal>b.SV order by e.deptno ;
--11
select count(e.deptno),e.deptno from emp e left join (select deptno, avg(sal) SV from emp group by deptno) b on
b.deptno=e.deptno where e.sal>b.SV group by e.deptno order by e.deptno;
--12
select e.deptno,count(e.deptno) from emp e left join (select deptno, avg(sal) SV from emp group by deptno) b on
b.deptno=e.deptno where e.sal>b.SV group by e.deptno having count(e.deptno)>1 order by e.deptno;
select max(sal) ,min(sal) from emp group by deptno;
--2
select max(sal) ,min(sal) from emp where job='CLERK' group by deptno;
--3
select deptno,max(sal) ,min(sal) from emp where job='CLERK' and deptno=(select deptno from emp group by deptno having min(sal)<1000) group by deptno;
--4
select ename,deptno,sal from emp order by deptno desc,sal
--5
select ename,deptno from emp where deptno = (select deptno from emp where ename='张三')
--6
select e.ename,e.job,e.deptno,d.dname from emp e left join dept d on d.deptno=e.deptno
--7
select e.ename,e.job,e.deptno,d.dname from emp e left join dept d on d.deptno=e.deptno where e.job='CLERK'
--8
select e.ename,m.mname from emp e left join mgr m on m.mgr=e.mgr
--9
select * from (select ename, job from emp where job='CLERK') a union all select dname, deptno from dept ;
--10
select e.deptno,e.ename,e.sal from emp e left join (select deptno, avg(sal) SV from emp group by deptno) b on
b.deptno=e.deptno where e.sal>b.SV order by e.deptno ;
--11
select count(e.deptno),e.deptno from emp e left join (select deptno, avg(sal) SV from emp group by deptno) b on
b.deptno=e.deptno where e.sal>b.SV group by e.deptno order by e.deptno;
--12
select e.deptno,count(e.deptno) from emp e left join (select deptno, avg(sal) SV from emp group by deptno) b on
b.deptno=e.deptno where e.sal>b.SV group by e.deptno having count(e.deptno)>1 order by e.deptno;
全部回答
- 1楼网友:闲懒诗人
- 2021-03-14 05:25
这个还需要求助我建议您不要去面试了,当务之急先看看书,学好了再去
- 2楼网友:堕落奶泡
- 2021-03-14 03:49
很难吗?如果这里面任何一个问题都不会,还面试什么。这是笔试题吧,难道面对面叙述语句?
- 3楼网友:萝莉姐姐鹿小北
- 2021-03-14 02:18
--把这两个表id查出来组成一个记录集select id from emp1 union all select id from sext ------------------id1234145--上面的记录集取别名为t 根据这个t 的id等于emp1的id的条件查出t中记录数小于2的emp1记录select * from emp1 e where (select count(*)from (select id from emp1 union all select id from sext) twhere t.id = e.id) < 2---------------------------id name2 b3 c 希望对你能有所帮助。
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯