[打卡]WJY-多表查询练习题+函数

-- 1显示所有员工的姓名，部门号和部门名称
SELECT last_name,e.department_id,department_name
from employees e
LEFT JOIN departments d
on e.department_id=d.department_id;

-- 或

select last_name,e.department_id,department_name
from departments d
right join employees e on e.department_id = d.department_id;

-- 2查询90号部门员工的job_id和90号部门的location_id
select job_id,location_id
from employees e join departments d
where e.department_id = d.department_id
and d.department_id = 90;

-- 或

select job_id,location_id
from employees e join departments d
on e.department_id = d.department_id
and d.department_id = 90;

-- 3选择city在Toronto工作的员工的last_name，job_id,department_id,department_name
SELECT last_name,job_id,e.department_id,department_name
from employees e join departments d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
and city='Toronto';

-- 或

select last_name,job_id,e.department_id,department_name
from employees e ,locations l, departments d
where e.department_id = d.department_id
and d.location_id = l.location_id
and city = 'Toronto';

-- 4查询员工所在的部门名称，部门地址，姓名，工作，工资，其中员工所在部门的部门名称为'Executive'
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
and department_name = 'Executive';

-- 5查询部门名为Sales 或 IT的员工信息

select employee_id,last_name,department_name
from employees e, departments d
where e.department_id = d.department_id
and d.department_name in ('Sales','IT');

-- 第七章 聚合函数
-- AVG SUM MAX MIN COUNT |GROUP BY|
select avg(salary),sum(salary)
from employees;

select avg(last_name),sum(last_name),avg(hire_date),sum(hire_date)
from employees;

select max(salary) from employees;
select min(salary) from employees;

select max(last_name),min(last_name),max(hire_date),min(hire_date)
from employees;

select count(employee_id),count(salary),count(salary*12)
from employees;