常用SQL查询语句练习

#1、求10号部门工资最高和最低的员工的工资
SELECT MAX(salary),MIN(salary)
FROM EMPLOYEES
WHERE department_id = 30;

#2、查询员工最早的入职时间和最晚入职时间
SELECT MIN(hire_date),MAX(hire_date)
FROM EMPLOYEES;

#3、查询20号部门的平均工资
SELECT AVG(salary)
FROM EMPLOYEES
WHERE department_id = 20;

#4、查询20号部门所有员工每个月的工资总和
SELECT SUM(salary)
FROM EMPLOYEES
WHERE department_id = 20;

#5、查询总人数
SELECT COUNT(employee_id)
FROM EMPLOYEES;

#6、查询有奖金的总人数
SELECT COUNT(employee_id)
FROM EMPLOYEES
WHERE commission_pct is not null;

#空值问题
#count(*)不忽略空值，其他情况忽略空值
SELECT COUNT(commission_pct)
FROM EMPLOYEES;

SELECT COUNT(*)
FROM EMPLOYEES;

#7、查询部门20的员工，每个月的工资总和及平均工资。
SELECT SUM(salary),AVG(salary)
FROM EMPLOYEES
WHERE department_id = 20;

#8 查询工作在Toronto的员工人数，最高工资及最低工资。
SELECT COUNT(e.employee_id),MAX(e.salary),MIN(e.salary)
FROM EMPLOYEES e,DEPARTMENTS d,LOCATIONS l
WHERE e.department_id = d.department_id and d.location_id = l.location_id and l.city = 'Toronto';

#9查询员工表中一共有几种岗位类型。
SELECT COUNT(DISTINCT job_id)
FROM EMPLOYEES;

#查询部门名称，部门编号，各部门平均工资，按照部门进行分组
SELECT e.department_id,d.department_name,AVG(e.salary)
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.department_id = d.department_id
GROUP BY e.department_id,d.department_name;

#having子句
#查询部门名称，部门编号，各部门平均工资，按照部门进行分组,并且平均工资大于2000
SELECT e.department_id,d.department_name,AVG(e.salary)
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.department_id = d.department_id
GROUP BY e.department_id,d.department_name
HAVING AVG(e.salary)>2000;

#书写顺序 select--from --where--group by--having-- order BY
#执行顺序： from--where-group by -having-select-order by

#按多列进行分组
#根据多列分组时，group by 子句中各列之间用逗号分隔

#查询每个部门每个岗位的工资总和
SELECT e.department_id 部门编号,j.job_title 岗位,SUM(e.salary)
FROM EMPLOYEES e,JOBS j
WHERE e.job_id = j.job_id
GROUP BY e.department_id,j.job_title;