oracle查询练习

–创建部门表
create table dept(
deptno number(8) primary key, --部门编号
dname varchar2(20)not null, --部门名字
loc varchar2(20) --地方
)
–插入数据
insert into dept values(10,‘会计部’,‘纽约’);
insert into dept values(20,‘研究部’,‘达拉斯’);
insert into dept values(30,‘销售部’,‘芝加哥’);
insert into dept values(40,‘运营部’,‘波士顿’);

–创建员工表
create table emp(
empno number(8) primary key, --员工编号
ename varchar2(20), --员工名字
job varchar2(20), --职位
mgr number(8), --上级
hiredate date, --入职时间
sal number(6), --薪水
comm number(4), --佣金
deptno number(8), --部门
CONSTRAINT emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno)
)

–插入员工信息
insert into emp values(7369,‘SMITH’,‘办事员’,7902,to_date(‘1980/12/17’,‘yyyy/MM/dd’),800,null,20);
insert into emp values(7499,‘ALLEN’,‘推销员’,7698,to_date(‘1981/2/20’,‘yyyy/MM/dd’),1600,300,30);
insert into emp values(7521,‘WARD’,‘推销员’,7698,to_date(‘1981/2/22’,‘yyyy/MM/dd’),1250,500,30);
insert into emp values(7566,‘JONES’,‘经理’,7839,to_date(‘1981/4/2’,‘yyyy/MM/dd’),2975,null,20);
insert into emp values(7654,‘MARTIN’,‘推销员’,7698,to_date(‘1981/9/28’,‘yyyy/MM/dd’),1250,1400,30);
insert into emp values(7698,‘BLAKE’,‘经理’,7839,to_date(‘1981/5/1’,‘yyyy/MM/dd’),2850,null,30);
insert into emp values(7782,‘CLARK’,‘经理’,7839,to_date(‘1981/6/9’,‘yyyy/MM/dd’),2450,null,10);
insert into emp values(7788,‘SCOTT’,‘分析师’,7566,to_date(‘1987/4/19’,‘yyyy/MM/dd’),3000,null,20);
insert into emp values(7839,‘KING’,‘董事长’,null,to_date(‘1981/11/17’,‘yyyy/MM/dd’),5000,null,10);
insert into emp values(7844,‘TURNER’,‘推销员’,7698,to_date(‘1981/9/8’,‘yyyy/MM/dd’),1500,0,30);
insert into emp values(7876,‘ADAMS’,‘办事员’,7788,to_date(‘1987/5/23’,‘yyyy/MM/dd’),1100,null,20);
insert into emp values(7900,‘JAMES’,‘办事员’,7698,to_date(‘1981/12/3’,‘yyyy/MM/dd’),950,null,30);
insert into emp values(7902,‘FORD’,‘分析师’,7566,to_date(‘1981/12/3’,‘yyyy/MM/dd’),3000,null,20);
insert into emp values(7934,‘MILLER’,‘办事员’,7782,to_date(‘1982/1/31’,‘yyyy/MM/dd’),1300,null,10);

完成以下操作

–1、找出佣金高于薪金60%的雇员

–2、找出部门10中所有经理和部门20中所有办事员的详细资料

–3、找出不收取佣金的雇员的不同职位

–4、找出各月最后一天受雇的所有雇员

–5、找出早于35年之前受雇的雇员姓名和入职时间

–6、显示所有雇员的姓名及上级的姓名（包括没有上级的）

–7、列出薪金高于在部门30工作的所有雇员的薪金的雇员

–8、列出各个部门的经理的最低薪金

–9、找到处于第四高的薪水金额的雇员

–10、列出在纽约的最早雇员的姓名、入职时间

–11、列出在各个地方工作的人数和地名、平均工资(四舍五入达到小数点后两位)

–12、查询姓名中包含字母“N”的员工在相同部门的其他雇员的姓名和部门名（不包含自己）

–13、不准用函数，求薪水的最高值

–14、求平均薪水最高的部门的部门名字

–15、求普通员工（有上级且没有下级的雇员）中比管理人员（有下级的雇员）的最低薪水要高的雇员姓名

–1、找出佣金高于薪金60%的雇员
select * from emp where comm>(sal*0.6)

–2、找出部门10中所有经理和部门20中所有办事员的详细资料
select * from emp where (deptno=10 and job=‘经理’) or (deptno=20 and job=‘办事员’)

–3、找出不收取佣金的雇员的不同职位
select distinct job from emp where comm is null

–4、找出各月最后一天受雇的所有雇员
select ename from emp where last_day(hiredate)=hiredate

–5、找出早于35年之前受雇的雇员姓名和入职时间
select ename,hiredate from emp where months_between(sysdate,hiredate)/12>35

–6、显示所有雇员的姓名及上级的姓名（包括没有上级的）
select e1.ename,e2.ename 上级 from emp e1,emp e2 where e1.mgr=e2.empno(+)

–7、列出薪金高于在部门30工作的所有雇员的薪金的雇员
select * from emp where sal> all (select sal from emp where deptno=30) select * from emp where sal>(select max(sal) from(select * from emp where deptno=30))

–8、列出各个部门的经理的最低薪金
select min(sal) from(select ename,sal,deptno from emp where job=‘经理’) group by deptno

–9、找到处于第四高的薪水金额的雇员
select * from emp where sal= (select sal from (select e.*,rownum num from emp e order by sal desc) where num=4)

–10、列出在纽约的最早雇员的姓名、入职时间
select e.ename,e.hiredate from emp e,dept d where e.deptno=d.deptno and d.loc=‘纽约’ and hiredate= (select min(hiredate) from emp e,dept d where e.deptno=d.deptno and d.loc=‘纽约’)

–11、列出在各个地方工作的人数和地名、平均工资(四舍五入达到小数点后两位)
select d.loc,count(*),round(avg(e.sal),2) 平均工资 from emp e,dept d where e.deptno=d.deptno group by loc

–12、查询姓名中包含字母“N”的员工在相同部门的其他雇员的姓名和部门名（不包含自己） select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.deptno in (select distinct deptno from emp where ename like’%H%’) and e.ename not in (select ename from emp where ename like’%H%’)

–13、不准用函数，求薪水的最高值
Select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));

–14、求平均薪水最高的部门的部门名字
select dname from dept where deptno= (select deptno from (select deptno,avg(sal) sal from emp group by deptno) b where sal= (select max(sal) from(select deptno,avg(sal) sal from emp group by deptno)))

–15、求普通员工（有上级且没有下级的雇员）中比管理人员（有下级的雇员）的最低薪水要高的雇员姓名
select ename from (select ename,sal from emp where ename not in (select distinct e2.ename from emp e1,emp e2 where e1.mgr=e2.empno)) where sal> (select min(sal) from (select distinct e2.ename,e2.sal from emp e1,emp e2 where e1.mgr=e2.empno))

内连接参考：图解MySQL 内连接、外连接、左连接、右连接、全连接……太多了