SQL数据库基础培训资料.ppt

2.6 阶段训练 【训练1】 显示人数最多的部门名称。 输入并执行以下查询： SELECT DECODE(dname,SALES,销售部,ACCOUNTING,财务部,RESEARCH,研发部,未知) 部门名 FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM emp GROUP BY deptno); 执行结果： 部门名 --------- 销售部 ? 说明：本训练使用了分组统计、相等连接和子查询，使用了DECODE函数进行部门名称转换。 【训练2】 显示各部门的平均工资、最高工资、最低工资和总工资列表，并按平均工资高低顺序排序。 输入并执行以下查询： SELECT dname 部门,AVG(sal) 平均工资,MAX(sal) 最高工资,MIN(sal) 最低工资,SUM(sal) 总工资 FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY dname ORDER BY AVG(sal) DESC; 说明：结果中排除了部门30，因部门30的总工资小于3000。 注意：HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行。 【练习3】统计人数小于4的部门的平均工资。 5．分组统计结果排序 可以使用ORDER BY从句对统计的结果进行排序，ORDER BY从句要出现在语句的最后。 【训练9】 按职务统计工资总和并排序。 执行以下查询： SELECT job 职务, SUM(sal) 工资总和 FROM emp GROUP BY job ORDER BY SUM(sal); 执行结果为： 职务 工资总和 ---------------- ------------------ CLERK 4150 PRESIDENT 5000 SALESMAN 5600 ANALYST 6000 MANAGER 8275 注意：排序使用的是计算列SUM(sal)，也可以使用别名，写成： SELECT job 职务, SUM(sal) 工资总和 FROM emp GROUP BY job ORDER BY 工资总和; 【练习4】统计各部门的人数，按平均工资排序。 6．组函数的嵌套使用 在如下训练中，使用了组函数的嵌套。 【训练10】 求各部门平均工资的最高值。 执行以下查询： SELECT max(avg(sal)) FROM emp GROUP BY deptno; 执行结果为： MAX(AVG(SAL)) ----------------------- 2916.66667 说明：该查询先统计各部门的平均工资，然后求得其中的最大值。 注意：虽然在查询中有分组列，但在查询字段中不能出现分组列。如下的查询是错误的： SELECT deptno,max(avg(sal)) FROM emp GROUP BY deptno; 因为各部门平均工资的最高值不应该属于某个部门。 【练习5】求每种职务总工资的最低值。 2.5.3 子查询 我们可能会提出这样的问题，在雇员中谁的工资最高，或者谁的工资比SCOTT高。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。具体的讲：要查询工资高于SCOTT的雇员的名字和工资，必须通过两个步骤来完成，第一步查询雇员SCOTT的工资，第二步查询工资高于SCOTT的雇员。第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。 子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。 子查询按照返回数据的类型可以分为单行子查询、多行子查询和多列子查询。 1．单行子查询 【训练1】 查询比SCOTT工资高的雇员名字和工资。