oracle数据库面试题及答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1.-- 查找出部门10中的工种与部门30中任何工种都不相同的职工的姓名与工种。
2.select ename,job from emp
3.where deptno=10 and job not in (select distinct job from emp where deptno=30)
4.--30号部门工种 select distinct job from emp where deptno=30
5.
6.-- 查找出部门20中的工种相同的职工的姓名与工种。
7.select ename,job from emp
8.where deptno=20 and job in
9.(select job from emp
10.where deptno =20
11.group by job
12.having count(*)>1);
13.
14.--先查找出，20号部门中，每个工种的人数超过1人的工种
15.select job from emp
16.where deptno =20
17.group by job
18.having count(*)>1;
19.
20.-- 查找出工资在1000到3500元之间的职工所在部门的所有人员的有关信息。
21.select * from emp
22.where deptno in (select deptno from emp where sal between 1000 and 3500);
23.
24.-- 查找出工资高于20号部门任意一个员工工资的信息。
25.select * from emp
26.where sal > (select max(sal) from emp where deptno=20)
27.
28.-- 查找出工种在部门10中没有的其他部门职工的姓名、工种和工资信息。
29.select ename,job,sal from emp
30.where job not in (select distinct job from emp where deptno=10) and deptno != 10;
31.
32.-- 查找出10号部门中与销售部门中任何职工工种相同的职工的信息。
33.select * from emp
34.where deptno=10 and
35.job in (select job from emp where deptno=(select deptno from dept where dname='SALES')
);
36.
37.--销售部门的工种
38.select job from emp where deptno=(销售部门的部门号)
39.
40.--销售部门的部门号
41.select deptno from dept where d
1.update score set sc_sco=sc_sco+10 where sc_no=345;
2.select st_name,st_no from student where
st_no in(select st_no from scroe where sc_no = 123 and sc_sco>80);
3.select sum(sc.sc_sco),sc.sc_stno,st.st_name,st.st_class from student st,score sc where st.st_no = sc.sc_stno
group by sc.sc_stno,st.st_name,st.st_class;
1.select sname from s where sno not in (select sno from sc where cno in (select cno from c where cteacher ='李明'))
--选修了李明老师课程的学生学号
2.select sno from sc where cno in (select cno from c where cteacher='李明')
--李明老师教过的课程
select cno from c where cteacher='李明'
3.select sname from s where sno in(
select sno from sc where cno = 1 or cno = 2
group by sno
having count(*) > 1);
1.select s#,grade from sc where c#=('C2');
2.select sname from s where sname like 'D%';
3.select s#,sname from s where s# in(select s# from sc where c# = (select c# from c where cname='MATHS'));
4.select s# from sc where c# in('C2','C4') GROUP BY s# having count(*)>1;
select min(业绩) from A
group by 部门
having min(业绩) > 7 and count(*)>1;