文档库 最新最全的文档下载
当前位置:文档库 › oracle基础练习题

oracle基础练习题

对emp,dept进行以下查询(comm列为员工的提成)

思考:select first_name , name from emp , dept;产看其产生结果是否有现实意义。

1. 查看员工的姓名和员工部门号
select ename,deptno from emp;
2.每个员工所在的部门和部门所在的地区
select ename,emp.deptno,loc from emp,dept where emp.deptno=dept.deptno;
3.查出所有员工的部门经理的名称(*)(自连接,子查询)
select a.empno,a.ename,a.deptno,b.ename from emp a,emp b
where a.deptno=b.deptno and b.job='MANAGER';

4.求各个部门的平均工资。
select dname,avg(sal)
from emp,dept
where emp.deptno=dept.deptno
group by dname;

5.查出高于平均工资的员工的工资。
select empno,ename,sal
from emp
where sal>(
select avg(sal)
from emp);

6.查询员工有多少人有提成;员工的提成总额。
select count(comm) from emp;
select sum(comm) from emp;


7.员工分部在多少个不同的部门.
select count(distinct deptno) from emp;


8.求各个部门不同工种的平均工资。
select deptno,job,avg(sal)
from emp
group by deptno,job;

9.1981年雇用的职员信息(*)
select * from emp where to_char(hiredate,'yyyy')='1981'




10.找出没有员工的部门(考虑两种方法实现)(*)
select deptno,dname from dept
where deptno not in(
select distinct(deptno) from emp);

select dname
from (select * from emp,dept where emp.deptno(+)=dept.deptno)
where ename is null;

11.查询哪些部门的平均工资比2000高.
select dname,avg(sal) from emp,dept
where emp.deptno=dept.deptno
group by dname
having avg(sal)>2000;


12.除了30部门以外的部门的平均工资. (两种方法:where/having)(*)
select deptno,avg(sal)
from emp
where deptno<>30
group by deptno;

select deptno,avg(sal)
from emp
group by deptno
having deptno<>30;

13.找出所有员工中,工资最低的那个员工。
select ename,sal
from emp
where sal=(select min(sal) from emp);

14.查询谁跟SMITH的工种一样.(*)

select ename from emp
where job=(select job from emp where ename='SMITH');

15.查出哪些员工的工资比平均工资低.
select ename from emp
where sal<(select avg(sal) from emp);

16.哪些部门的平均工资比30部门的平均工资要低.
select deptno avg(sal)
from emp
group by deptno
having avg(sal)<(select avg(sal) from emp where deptno=30 );

17.学生选课问题:没有选修'B2'课程的学生信息。(*)
select * from s
where sno not in(select distinct sno from c,sc where cname='B2' and https://www.wendangku.net/doc/1916779164.html,o=https://www.wendangku.net/doc/1916779164.html,o);
18.选修了全部课程的学生(*)

select * from s
where sno in
(select sno from sc
group by sno
having count(cno)=(select count(cno) from c));



s(sno,sname)
c(cno,cname)
sc(sno,cno,grade)

create table s(sno char(10),sname char(10));
create table c(cno char(10),cname char(10));
create table sc(sno char(10),cno char(10),grade number(3),primary key(sno,cno))
insert int

o s values('98001','s1');
insert into s values('98002','s2');
insert into c values('001','c1');
insert into c values('002','B2');

insert into sc values('98001','001',60);
insert into sc values('98001','002',60);
insert into sc values('98002','001',70);




相关文档