oracle

select * from emp;
select deptno,ename,avg(sal) from emp  group by deptno,ename,job;
--在select里的列，如果不在组函数中，则一定要在group by里出现；

---一个部门的工资平均值
select deptno,max(sal) from emp group by deptno,job;
---每个部门中不同职位的最大值
select max(sal) from emp;
select ename from emp where sal=(select max(sal) from emp);
select * from emp where empno in  (select empno from emp where empno > 7000);
---求薪水最高的那些人
select deptno,max(sal) from emp group by deptno;
---求每个部门的最高薪水
select avg(sal),deptno from emp group by deptno;
select avg(sal),deptno from emp   group by deptno having avg(sal)>2000 order by deptno;
select * from emp where deptno is null;
select distinct(deptno) from emp where deptno <> 10 or deptno is null;
select distinct(deptno) from emp;
---求部门平均值大于2000的部门
selecet * from emp;
2 where sal > 1000
3 group by deptno
4 having
5 order by

select ename ,sal from emp where sal= (select max(sal) from emp);
--求薪水最高的那些人
select ename ,sal from emp
       where sal >(select avg(sal) from emp);
--求薪水大于平均值的人
select max(sal),deptno from emp group by deptno;

 

select * from emp;
select * from dept;

'SMITH'  -->部门名
select deptno from emp where ename = 'SMITH';
select dname from dept where deptno = (select deptno from emp where ename = 'SMITH');

select  e.*,d.* from emp e left join dept d  on e.deptno = d.deptno where e.ename = 'SMITH';

select emp.*,dept.* from emp left join dept on emp.deptno = dept.deptno  where emp.ename = 'SMITH';
-- select ...  from  tab1  left join tab2  on tab1.col1 = tab2.col1  where tab1.col2 = ...and ...

select ename from emp where empno = (select mgr from emp where ename = 'SMITH');

select e1.ename  员工名称,e2.ename 经理名称 from emp e1,emp e2 where e1.mgr = e2.empno;
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;

select emp.*,dept.* from emp,dept where emp.deptno = dept.deptno

--左连接  left join

--右连接  right join

--内连接  inner join

--外连接  outter join

 

 

select enmae ,sal from emp
2 join(select max(sal)max_sal,deptno from emp group by deptno)t
3 on(emp.sal=t.max_sal and emp.deptno=t.deptno>;

select empno ,ename ,mgr from emp;

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
---求员工的经理名
select deptno, avg_sal, grade
  from (select deptno, avg(sal) avg_sal from emp group by deptno) t
  join salgrade s on (t.avg_sal between s.losal and s.hisal)
---求部门平均薪水的等级
select ename,sal from emp
join (select max(sal)max_sal,deptno from emp group by deptno)t
on(emp.sal=t.max_sal and emp.deptno=t.deptno)
---求部门中哪些人薪水最高
select deptno ,ename ,grade from emp
 join salgrade s on(emp.sal between s.losal and s.hisal)
 ----部门平均的薪水等级
 select deptno ,avg(grade) from
 (select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal  and
  s.hisal))t
  group by deptno
 ---求部门平均的薪水等级
 select ename from emp where empno in(select mgr from emp)
 ---求哪些人是经理
select ename from emp where empno in(select distinct mgr from emp)
select e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal);
select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal);

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

select sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal))

select avg(sal),deptno from emp group by deptno;

---select max(avg(sal)) from
---(select avg(sal),deptno from emp group by deptno)

select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal) from
          (select avg(sal) avg_sal ,deptno from emp group by deptno))
          ---求平均薪水最高的部门的部门编号
         
---select deptno,avg_sal from
--(select avg(sal) avg_sal,deptno from emp group by deptno)
--where avg_sal=(select max(sal) from emp group by deptno)

select dname ,t1.deptno,grade,avg_sal from
(
select deptno,grade,avg_sal from
（select deptno,avg(sal) avg_sal from emp group by deptno)t
 join salgrade s on (t.avg_sal between s.losal and s.hisal)
 )t1
 join dept on (t1.deptno = dept.deptno)
 where t1.grade=
 (
   select min(grade) from
(
  select deptno , grade,avg_sal from
  (select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
)
  );
 

select dname ,t1.deptno,grade,avg_sal from
v$_dept_avg_sal_info t1--视图
join dept on (t1.deptno=dept.deptno)
where t1.grade=
(
select min(grade) from v$_dept_avg_sal_info
)
---求平均薪水等级最低的部门名称

---求部门经理人中平均工资最低的部门名称（思考题）

select max(sal) from emp
where empno not in (select distinct mgr from emp);

select ename from emp
where empno in(select distinct mgr from emp where mgr is not null)
and
sal>
(
select max(sal) from emp where empno not in
 (select distinct mgr from emp where mgr is not null)
 )
---求比普通员工的最高薪水还要高的经理人名称

select ename ,hiredate from
(select ename ,hiredate from emp order by hiredate desc)
where rownum <=5;

---conn sys/bjsxt as sysdba;

----- insert into dept value (50，'game','bj');

select * from emp
---create table emp2 as select * from emp;

select * from dept;
---select table emp2 as select * from emp;