非常好的入门学习SQL语句(成年人必看)

1.求部门中哪些人薪水最高：
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);
2.求部门平均薪水的等级：
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);
3.求部门平均的薪水等级：
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;
4.求哪些人是经理人：
select ename from emp where empno in (select distinct mgr from emp);
5.不准用聚集函数，求薪水的最高值：
----Distinct 去重复 distinct
select distinct sal
from emp
where sal not in
(
    select distinct e1.sal
    from emp e1 join emp e2 on (e1.sal < e2.sal) //得出 最大数 以外的数
);
6.求平均薪水最高部门的部门编号：
select deptno, avg_sal
from (
    select deptno, avg(sal) avg_sal
    from emp
    group by deptno
) where avg_sal = (
    select max(avg_sal)
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    )
);
7.求平均薪水最高部门的部门名称：
select deptno,dname
from dept
where deptno = (
    select deptno
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) where avg_sal = (
        select max(avg_sal)
        from (
            select deptno, avg(sal) avg_sal
            from emp
            group by deptno
        )
    )
);
#：聚集函数嵌套（最多只能嵌套两层）：
select deptno,dname
from dept
where deptno = (
    select deptno
    from (
        select deptno, avg(sal) avg_sal
        from emp
        group by deptno
    ) where avg_sal = (
        select max(avg(sal))
        from emp
        group by deptno
    )
);
8.求平均薪水的等级最低部门的部门名称：
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)
    )
);
#：简化办法-创建视图：
1)create view v$_dept_avg_sal_info as
  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);

2)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 t2
  );
3)若权限不足，则：
conn sys/密码 as sysdba;
grant create table, creat view to scott;
9.求比普通员工的最高薪水还要高的经理人名称：
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
        )
    );
10.  求薪水最高的前5名雇员
select ename, sal
from
(
    select ename, sal
    from emp
    order by sal desc
) where rownum <= 5;
11.  求薪水最高的第六到第十名雇员
select ename, sal
from (
    select ename, sal, rownum r
    from (
        select ename, sal
        from emp
        order by sal desc
    )
) where r >=6 and r <= 10;
12.有三个表格S,C,SC
S(SNO, SNAME) 代表（学号，姓名）
C(CNO, CNAM, CTEACHER)代表（课号，课名，教师）
SC(SNO, CNO, SCGRADE)代表（学号，课号，课号成绩）
问题：
⑴.找出没选过“liming”老师课程的所有学生姓名
select sname
from s join sc on (s.sno = sc.sno)
       join c on (c.cno = sc.cno)
where c.cteacher <> 'liming';
⑵.列出两门以上（含两门）不及格学生姓名及平均成绩
select sname
from s
where sno in (
    select sno
    from sc
    where scgrade < 60
    group by sno
    having count(*) >= 2 //having 对统计结果进行筛选
);
⑶.既学过1号课程又学过2号课程所有学生的姓名
select sno from sc where cno = 1 and              ---------此行多余？保留意见！
select sname
from s
where sno in (
    select sno
    from sc
    where cno = 1 and sno in (
        select sno from sc where cno = 2
    )
);


--*********************************连接查询*************************************

select * from salgrade;
--查询所有员工的工资级别，员工名称，工资
select b.grade,a.ename,a.sal
from salgrade b,
    (
        select ename,sal
        from emp
    )a
where a.sal between b.losal and b.hisal
;
--查询员工的上司名字
select a.ename,b.ename
from emp a inner join emp b
on a. mgr=b.empno;

--插叙部门编号为10的员工名称和部门名称
select dname,ename
from dept a inner join emp b
on a.DEPTNO=b.DEPTNO and a.deptno=10;

--用自然连接查询部门编号为10的员工名称和部门名称
select dname,ename
from dept natural join emp
where deptno=10;


--查询scott的同事
select emp.ename
from emp
where emp.deptno=
    (
    select emp.deptno
    from emp
    where lower(emp.ename)='scott'
    );
 
--查询不在部门编号为10的所有职位 
select *
from emp
where job in
(
    select job
    from emp
    where emp.deptno=10
)
and emp.deptno<>10;

--all和any的应用

select *
from emp
where hiredate<all
(
   select hiredate from emp
   where deptno=10
);

select *
from emp
where hiredate<any
(
   select hiredate from emp
   where deptno=10
);


--
select ename,job,sal,deptno
from emp
where(deptno,job)=
(
  select deptno,job
  from emp
  where lower(ename)='smith'
);

--


--*********************************************分组查询********************************
--聚合函数
select max(sal),min(sal)
from emp;

select avg(sal),sum(sal)
from emp;

select  count(*)
from emp;

--不计算空值
select count(comm)
from emp;

select count(distinct deptno) as distinctdeptcount
from emp;

--组合查询
select deptno, avg(sal),max(sal)
from emp
group by deptno;

--round 和trunc区别
select deptno,round( avg(sal),2),trunc(max(sal),2)
from emp
group by deptno;

--组合查询的应用  ，注意HAVING 和ORDER BY 的顺序
--分组列一定要出现在查询表达式中
select deptno,job, avg(sal),max(sal)
from emp
group by deptno,job
having avg(sal)>2000
order by deptno;

--rollup 和cube的含义
--rollup是对所有数据在查询的基础上横向统计
--cube是对所有数据在查询的基础上立体统计
select deptno,job, avg(sal),max(sal)
from emp
group by rollup( deptno,job);

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

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

--group sets函数:分组结果集
select  job,deptno, avg(sal),max(sal)
from emp
group by grouping sets( job,deptno);

--统计每个部门的平均工资和工资等级，要求显示部门名，平均工资，工资等级
select *from emp;
select *from dept;
select *from salgrade;

select d.grade ,c.dname,c.avgsal
from salgrade d,
        (
        select dname,avgsal
        from dept  b,
                (
                    select  avg(sal)as avgsal,deptno
                    from emp
                    group by deptno
                )a
        where b.deptno=a.deptno
        )c
where c.avgsal between d.losal and hisal;
;