SELECT
dept_manager.emp_no,
employees.last_name,
employees.first_name,
departments.dept_name
FROM
dept_manager
JOIN employees ON employees.emp_no = dept_manager.emp_no
JOIN departments ON dept_manager.dept_no = departments.dept_no;
计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
dept_emp.dept_no,
departments.dept_name,
AVG(salary) avg_salary
FROM
salaries
JOIN dept_emp ON salaries.emp_no = dept_emp.emp_no
JOIN departments ON departments.dept_no = dept_emp.dept_no
GROUP BY
dept_emp.dept_no
ORDER BY avg_salary;
SELECT
employees.emp_no,
first_name,
last_name,
gender,
birth_date,
dept_name
FROM
dept_manager
join
employees ON dept_manager.emp_no = employees.emp_no
join
departments ON dept_manager.dept_no = departments.dept_no order by dept_manager.emp_no;
2
SELECT
dept_name, AVG(salary) as salary_avg
FROM
salaries
LEFT JOIN
dept_emp ON dept_emp.emp_no = salaries.emp_no
LEFT JOIN
departments ON departments.dept_no = dept_emp.dept_no
GROUP BY departments.dept_no
ORDER BY AVG(salary);
SELECT
a.first_name,
a.last_name,
b.from_date,
b.to_date,
c.dept_name
FROM
employees a
JOIN
dept_manager b ON a.emp_no = b.emp_no
JOIN
departments c ON b.dept_no = c.dept_no
部分结果如下图:
2、 计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
b.dept_no, c.dept_name, AVG(a.salary)
FROM
salaries a
JOIN
dept_emp b ON a.emp_no = b.emp_no
JOIN
departments c ON b.dept_no = c.dept_no
GROUP BY b.dept_no
ORDER BY AVG(a.salary);
SELECT
c.first_name,
c.last_name,
a.from_date,
a.to_date,
b.dept_name
FROM
dept_manager a
LEFT JOIN departments b ON
a.dept_no = b.dept_no
LEFT JOIN employees c ON
a.emp_no = c.emp_no
2.计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
c.dept_name,
AVG(a.salary)
FROM
salaries a
RIGHT JOIN dept_emp b ON
a.emp_no = b.emp_no
INNER JOIN departments c ON
b.dept_no = c.dept_no
GROUP BY
b.dept_no
ORDER BY
AVG(a.salary)
计算各部门的平均工资并排序,给出平均工资、部门名称
‘’’
SELECT
AVG( sa.salary ),
dp.dept_name
FROM
employees em
LEFT JOIN ( salaries sa, dept_emp de ) ON em.emp_no = sa.emp_no
AND em.emp_no = de.emp_no
LEFT JOIN departments dp ON dp.dept_no = de.dept_no
GROUP BY
de.dept_no
ORDER BY
AVG( sa.salary );
‘’’
select d.dept_no,d.dept_name,dm.emp_no,CONCAT(e.last_name,e.first_name) as name,dm.from_date,dm.to_date from employees_XCT.dept_manager dm
LEFT JOIN employees_XCT.departments d
on dm.dept_no=d.dept_no
LEFT JOIN employees_XCT.employees e
on dm.emp_no=e.emp_no
ORDER BY d.dept_no
②计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT d.dept_name as 部门名称 ,AVG(salary) as 平均工资 from employees_XCT.salaries s
join employees_XCT.employees e
on s.emp_no= e.emp_no
join employees_XCT.dept_emp de
on de.emp_no=e.emp_no
join employees_XCT.departments d
on de.dept_no=d.dept_no
where s.to_date='9999-01-01'
GROUP BY de.dept_no
ORDER BY AVG(salary) DESC
select
d.dept_name,
m.from_date,
m.to_date,
e.*
from dept_manager m
left join employees e on m.emp_no = e.emp_no
left join departments d on m.dept_no = d.dept_no
order by d.dept_name, m.from_date
计算各个部门的平均工资并排序,给出平均工资、部门名称
select
d.dept_name,
avg(s.salary) avg_salary,
count(*) count1
from dept_emp de
left join salaries s on de.emp_no = s.emp_no
left join departments d on d.dept_no = de.dept_no
where s.to_date = '9999-01-01' and de.to_date = '9999-01-01' -- 当前在职
group by d.dept_no
order by avg_salary
select a.emp_no,birth_date,first_name,last_name,gender,a.dept_no,dept_name,hire_date,from_date,to_date from dept_manager a join departments b on a.dept_no=b.dept_no join employees c on a.emp_no=c.emp_no;
select c.dept_name, avg(salary) from salaries a join dept_emp b on a.emp_no=b.emp_no join departments c on b.dept_no=c.dept_no group by c.dept_no order by avg(salary) desc;
1.select employees.first_name, employees.last_name, departments.dept_name from dept_manager left join employees on employees.emp_no = dept_manager.emp_no left join departments on dept_manager.dept_no = departments.dept_no
2.select avg(salaries.salary) a, departments.dept_name from salaries left join dept_emp on dept_emp.emp_no=salaries.emp_no left join departments on dept_emp.dept_no=departments.dept_no group by departments.dept_name order by a;
1、 给出各部门历任领导的记录并附上部门名字
select c.first_name,c.last_name,b.from_date,b.to_date,a.dept_name from departments a,dept_manager b,employees c WHERE a.dept_no=b.dept_no AND b.emp_no=c.emp_no;
2、计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT AVG(salary) 平均工资,dept_name 部门名称 FROM departments a,dept_emp b,salaries c WHERE a.dept_no=b.dept_no AND b.emp_no=c.emp_no GROUP BY b.dept_no ORDER BY 平均工资;
SELECT AVG(salary) 平均工资 ,dept_name 部门名称 from salaries, dept_emp,departments where salaries.emp_no=dept_emp.emp_no and dept_emp.dept_no = departments.dept_no GROUP BY dept_emp.dept_no order by 平均工资;
# 计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
d_e.dept_no 部门编号,
ANY_VALUE(d.dept_name) 部门名称,
AVG(s.salary) 平均工资
FROM
(dept_emp d_e, departments d)
JOIN
salaries s ON s.emp_no = d_e.emp_no
AND d_e.dept_no = d.dept_no
GROUP BY d_e.dept_no
ORDER BY AVG(s.salary);
作业1:给出各部门历任领导的记录并附上部门名字
SELECT employees.emp_no,first_name,last_name,gender,birth_date,dept_name
FROM dept_manager
JOIN employees ON dept_manager.emp_no = employees.emp_no
JOIN departments ON dept_manager.dept_no = departments.dept_no
ORDER BY dept_manager.emp_no;
作业2:计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT dept_emp.dept_no,departments.dept_name,AVG(salary) avg_salary
FROM salaries
JOIN dept_emp ON salaries.emp_no = dept_emp.emp_no
JOIN departments ON departments.dept_no = dept_emp.dept_no
GROUP BY dept_emp.dept_no
ORDER BY avg_salary;