从网站栏目看网站功能,一键优化为什么不能100,网站空间建站,微信视频号怎么引流推广33. 查询 employees 表中有多少个部门select count(distinct department_id)from employees 34. 查询全公司奖金基数的平均值(没有奖金的人按 0 计算)select avg(nvl(commission_pct, 0))from employees 35. 查询各个部门的平均工资--错误: avg(salary) 返回公司平均工资, 只…33. 查询 employees 表中有多少个部门select count(distinct department_id)from employees 34. 查询全公司奖金基数的平均值(没有奖金的人按 0 计算)select avg(nvl(commission_pct, 0))from employees 35. 查询各个部门的平均工资--错误: avg(salary) 返回公司平均工资, 只有一个值; 而 department_id 有多个值, 无法匹配返回select department_id, avg(salary)from employees **在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中--正确: 按 department_id 进行分组select department_id, avg(salary)from employeesgroup by department_id36. Toronto 这个城市的员工的平均工资SELECT avg(salary)
FROM employees e JOIN departments d
ON e.department_id d.department_id
JOIN locations l
ON d.location_id l.location_id
WHERE city Toronto 37. (有员工的城市)各个城市的平均工资SELECT city, avg(salary)
FROM employees e JOIN departments d
ON e.department_id d.department_id
JOIN locations l
ON d.location_id l.location_id
GROUP BY city 38. 查询平均工资高于 8000 的部门 id 和它的平均工资. SELECT department_id, avg(salary)
FROM employees e
GROUP BY department_id
HAVING avg(salary) 8000 39. 查询平均工资高于 6000 的 job_title 有哪些SELECT job_title, avg(salary)
FROM employees e join jobs j
ON e.job_id j.job_id
GROUP BY job_title
HAVING avg(salary) 6000
1. 组函数处理多行返回一行吗?
是
2. 组函数不计算空值吗
是
3. where子句可否使用组函数进行过滤?
不可以用having替代
4. 查询公司员工工资的最大值最小值平均值总和
a) select max(salary),min(salary),avg(salary),sum(salary)
b) from employees
5. 查询各job_id的员工工资的最大值最小值平均值总和
a) select job_id,max(salary),min(salary),avg(salary),sum(salary)
b) from employees
c) group by job_id
6. 选择具有各个job_id的员工人数
a) select job_id,count(employee_id)
b) from employees
c) group by job_id
7. 查询员工最高工资和最低工资的差距DIFFERENCE
a) select max(salary),min(salary),max(salary)-min(salary) DIFFERENCE
b) from employees
8. 查询各个管理者手下员工的最低工资其中最低工资不能低于6000没有管理者的员工不计算在内
a) select manager_id,min(salary)
b) from employees
c) where manager_id is not null
d) group by manager_id
e) having min(salary) 6000
9. 查询所有部门的名字location_id员工数量和工资平均值
a) select department_name,location_id,count(employee_id),avg(salary)
b) from employees e right outer join departments d
c) on e.department_id d.department_id
d) group by department_name,location_id
10. 查询公司在1995-1998年之间每年雇用的人数结果类似下面的格式
total 1995 1996 1997 1998
20 3 4 6 7select count(*) total,count(decode(to_char(hire_date,yyyy),1995,1,null)) 1995,count(decode(to_char(hire_date,yyyy),1996,1,null)) 1996,count(decode(to_char(hire_date,yyyy),1997,1,null)) 1997,count(decode(to_char(hire_date,yyyy),1998,1,null)) 1998
from employees
where to_char(hire_date,yyyy) in (1995,1996,1997,1998)
