Oracle数据库——查询练习-09

练习

1.列出至少有一个员工的所有部门==子查询
至少有一个员工指的是员工数量大于1
第一步：按照部门编号分组，而后统计出部门人数大于1的部门编号
SELECT deptno FROM emp
GROUP BY deptno 
HAVING COUNT(empno)>0;
第二步：以上查询返回多行单列，按照之前给出的原则来讲，此时只能够在WHERE中出现
SELECT *
FROM dept
WHERE deptno IN(
    SELECT deptno FROM emp
    GROUP BY deptno 
    HAVING COUNT(empno)>0);

2.列出薪金比"SMITH"多的所有员工==子查询
第一步：找到SMITH的薪金
SELECT sal FROM emp WHERE ename='SMITH';
第二步：根据以上的查询结果找到高于此数值的雇员信息
SELECT *
FROM emp
WHERE sal>(
    SELECT sal FROM emp WHERE ename='SMITH');

3.列出所有员工的姓名及其直接上级的姓名==多表查询，自身关联
SELECT e.ename,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+);

4.列出受雇日期早于其直接上级的所有员工==多表查询，自身关联
SELECT e.*
FROM emp e,emp m
WHERE e.mgr=m.empno(+) AND e.hiredate<m.hiredate;

5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门==子查询
员工信息主要指的是员工的统计信息
SELECT  d.deptno,d.dname,d.loc,temp.count,temp.avg
FROM dept d,(
    SELECT deptno dno,COUNT(sal) count,AVG(sal) avg
    FROM emp
    GROUP BY deptno)temp
WHERE d.deptno=temp.dno(+);
当发现所要显示的数据不完整的时候都使用外连接控制

6.列出所有“CLERK”(办事员)的姓名及其部门名称 ==多表查询
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno AND e.job='CLERK';

7.列出最低薪金大于1500的各种工作 ==分组统计，HAVING过滤
首先按照工作分组，而后统计每种工作的最低工资，对分组后的数据过滤
SELECT job,MIN(sal)
FROM emp
GROUP BY job
HAVING MIN(sal)>1500;

8.列在部门‘SALES’（销售部）工作的员工的姓名，假定不知道销售部的部门编号==多表查询 子查询
第一步：找到销售部的部门编号，返回单行单列
SELECT deptno
FROM dept
WHERE dname='SALES';
第二步：找到再次部门工作的雇员姓名
SELECT ename
FROM emp
WHERE deptno=(
    SELECT deptno
    FROM dept
    WHERE dname='SALES');

9.列出薪金高于公司平均薪金的所有员工==子查询
第一步：找到公司的平均薪金
SELECT AVG（sal） FROM emp;
第二步：高于以上数值的雇员
SELECT *
FROM emp
WHERE sal>(SELECT AVG（sal） FROM emp);

10.列出与“SCOTT”从事相同工作的所有员工==子查询
第一步：找到“SCOTT”的工作
SELECT job FROM emp WHERE ename='SCOTT';
第二步：找到与以上的工作相同的雇员信息
SELECT  *
FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT')
    AND ename<>'SCOTT';

11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金==子查询
第一步：找到部门30中的员工的薪金
SELECT sal FROM emp WHERE deptno=30;
第二步：使用IN进行判断
SELECT * FROM emp 
WHERE sal IN(SELECT sal FROM emp WHERE deptno=30);

12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金==子查询
第一步：找到部门30中的员工的薪金
SELECT sal FROM emp WHERE deptno=30;
第二步：大于以上数据
SELECT * FROM emp
WHERE sal>ALL(
SELECT sal FROM emp WHERE deptno=30);

13.列出在每个部门工作的员工数量，平均工资和平均服务期限==子查询
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg,temp.avg_year
FROM dept d,(
SELECT  deptno dno,COUNT(empno) count,AVG(sal) avg,AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/
12) avg_year
 FROM emp
 GROUP BY deptno)  temp
WHERE d.deptno=temp.dno(+);

14.列出所有员工的姓名，部门名称和工资==多表查询
SELECT e.ename,d.dname,e.sal
FROM emp e,dept d
WHERE e.deptno=d.deptno;

15.列出所有部门的详细信息和部门人数==子查询
SELECT d.deptno,d.dname,d.loc,temp.count
FROM dept d,(
SELECT  deptno dno,COUNT(empno) count
 FROM emp
 GROUP BY deptno)  temp
WHERE d.deptno=temp.dno(+);

16.列出各种工作的最低工资==分组统计
SELECT job, MIN(sal)
FROM emp
GROUP BY job;

17.列出各个部门的MANAGER（经理）的最低薪金==假设每个部门有多个经理
SELECT deptno,MIN(sal)
FROM emp
WHERE job='MANAGER'
GROUP BY deptno;

18.列出所有员工的工资，按年薪从低到高排序。==函数处理，ORDER BY
SELECT ename,(sal+NVL(comm,0))*12 income
FROM emp
ORDER BY income;

19.查出某个员工的所有上级主管，并要求出这些主管中的薪水超过3000（用一条sql语句解决）==自身关联
SELECT DISTINCT m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno AND m.sal>3000;

20.求出部门名称中，带‘S’字符的部门员工的，工资合计，部门人数
要求，部门工资合计要 大于5000，并且按照部门的人数排序
第一步：先按照部门名称分组
SELECT d.dname,SUM(e.sal),COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.dname;
第二步：找到部门名称带‘S’
SELECT d.dname,SUM(e.sal),COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno AND d.dname LIKE '%S%'
GROUP BY d.dname
HAVING SUM(e.sal)>5000;

21.列出员工任职的年，月，日==单行函数
SELECT ename,TO_CHAR(hiredate,'yyyy'),TO_CHAR(hiredate,'mm'),TO_CHAR(hiredate,'dd')
FROM emp;
22.列出员工中薪水最低，任职日期最长的员工。 ==子查询,单行多列子查询
SELECT *
FROM emp
WHERE(sal,hiredate)=(
SELECT MIN(sal),MIN(hiredate) FROM emp);
23.求出职工的周薪，保留两位小数 ==单行函数
SELECT ename,ROUND((sal+NVL(comm,0))/30*7,2)
FROM emp;
以上的习题是最基本的习题，必须可以独立写出来

总结
1.SQL语法结构执行顺序
FROM子句==确定数据来源，而数据来源可能是实体版，也可能是一个临时表
WHERE子句==针对要操作的数据执行过滤，不可以使用统计函数
GROUP BY子句：使用一个列上存在重复的列进行分组，分组后可以统计，而且使用GROUP BY之后SELECT子句
里面只能够出现分组字段，和统计函数
HAVING子句：针对于分组后的数据进行过滤，可以使用统计函数
SELECT子句：确定要显示的数据列，可以设置别名
OREDR BY子句：数据的排序，可以使用SELECT别名；
2.复杂查询：限定查询+多表查询+分组统计查询+子查询；
复杂查询之中重要的是问题的解决思路