[打卡]第七章——聚合函数——sql

#练习

SELECT e.last_name,d.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;

SELECT job_id,location_id
FROM employees e,departments d
WHERE e.department_id=d.department_id;

SELECT e.last_name,e.job_id,d.department_id,d.department_name
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city='Toronto';

#查询员工表在左边
SELECT last_name,d.department_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id;

#查询员工表在右边
SELECT last_name,d.department_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id;

#查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e,departments d
WHERE e.department_id=d.department_id
and e.department_id=90;

#选择city在Toronto工作的员工的 last_那么，job_id,
#department_id,department_name
SELECT last_name,job_id,e.department_id,department_name
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city='Toronto';

#4
SELECT department_name,street_address,last_name,job_id,salary
FROM departments d,locations l,employees e
WHERE d.location_id=l.location_id
AND e.department_id=d.department_id
AND department_name='Exeutive';

SELECT department_name,street_address,last_name,job_id,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 department_name='Executive';

#5
SELECT department_name,employee_id,last_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND department_name IN('Sales','IT');

#第七章 聚合函数

#1.1 AVG(平均数) / SUM（求和）
SELECT AVG(salary),SUM(salary)
FROM employees;

#字符串求和没有任何意义
SELECT AVG(last_name),SUM(last_name),AVG(hire_date),SUM(hire_date)
FROM employees;

#1.2 MAX （最大值）/ MIN（最小值）
#(适用于数值类型、字符串类型，日期时间类型的字段)
SELECT MAX(salary),MIN(salary)
FROM employees;

SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;

#1.3 COUNT
#作用：计算指定字段在查询结果中出现的个数
SELECT COUNT(employee_id)
FROM employees;