1.给出各部门历任领导的记录并附上部门名字
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;
2.计算各个部门的平均工资并排序，给出平均工资、部门名称
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 平均工资;
1.给出各部门历任领导的记录并附上部门名字
‘’’
SELECT employees.* ,departments.dept_name 职位 from employees, dept_manager,departments where employees.emp_no=dept_manager.emp_no and dept_manager.dept_no = departments.dept_no ;
‘’’
‘’’
2.计算各个部门的平均工资并排序，给出平均工资、部门名称
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
avg(b.salary) as cc,
c.dept_name
FROM
dept_emp a ,
salaries b ,
departments c
where a.emp_no = b.salary and c.dept_no = a.dept_no
GROUP BY
a.dept_no
ORDER BY cc desc
这样为啥查询出来的数据是不正确的
作业
# 给出各部门历任领导的记录并附上部门名字
SELECT
d_m.emp_no 员工编号,
e.first_name 姓,
e.last_name 名,
e.birth_date 生日,
d_m.dept_no 部门编号,
d.dept_name 部门名称,
d_m.from_date 任职时间,
d_m.to_date 任期结束时间
FROM
dept_manager d_m
JOIN
departments d ON d_m.dept_no = d.dept_no
join employees e on e.emp_no = d_m.emp_no;
# 计算各个部门的平均工资并排序，给出平均工资、部门名称
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;
SELECT
employees.emp_no,
employees.first_name,
last_name,
dept_manager.dept_no,
dept_name,
dept_manager.from_date,
dept_manager.to_date
FROM
employees
JOIN
dept_manager ON employees.emp_no = dept_manager.emp_no
JOIN
departments ON dept_manager.dept_no = departments.dept_no
order by dept_manager.dept_no;
SELECT
AVG(salary), dept_emp.dept_no, dept_name
FROM
salaries
JOIN
dept_emp ON salaries.emp_no = dept_emp.emp_no
join departments on
dept_emp.dept_no = departments.dept_no
GROUP BY dept_no ORDER BY AVG(salary) DESC;
1、给出各部门历任领导的记录并附上部门名字
谢谢
select dept_name,first_name,last_name
from departments join dept_manager on dept_manager.dept_no=departments.dept_no
join employees on dept_manager.emp_no=employees.emp_no
order by dept_name
2、计算各个部门的平均工资并排序，给出平均工资、部门名称
select dept_name,avg(salary) as 平均工资
from salaries join employees on salaries.emp_no=employees.emp_no
join dept_emp on employees.emp_no=dept_emp.emp_no
JOIN departments on departments.dept_no=dept_emp.dept_no
GROUP BY dept_name
ORDER BY 平均工资
SELECT
d.dept_name,
dm.from_date,
dm.to_date,
dm.emp_no,
e.first_name,
e.last_name
FROM
dept_manager AS dm
JOIN employees AS e ON e.emp_no = dm.emp_no
JOIN departments AS d ON d.dept_no = dm.dept_no;
# 部门平均工资
SELECT
AVG(s.salary),d.dept_name
FROM
salaries AS s
JOIN dept_emp AS de ON de.emp_no = s.emp_no
JOIN departments AS d ON d.dept_no = de.dept_no
WHERE s.to_date = "9999-01-01"
GROUP BY d.dept_no ORDER BY AVG(s.salary);
历任领导：
SELECT
a.dept_name,
c.first_name,
c.last_name,
b.from_date,
b.to_date
FROM
departments AS a,
dept_manager AS b,
employees as c
WHERE
a.dept_no = b.dept_no and b.emp_no = c.emp_no
计算各个部门的平均工资并按照平均工资的倒序排列
SELECT
AVG( b.salary ) AS ‘avg_sa’,
c.dept_name
FROM
dept_emp AS a,
salaries AS b,
departments AS c
WHERE
a.emp_no = b.emp_no
AND a.dept_no = c.dept_no
GROUP BY
a.dept_no
ORDER BY
AVG(
b.salary) DESC
给出各部门历任领导的记录并附上部门名字：
SELECT
first_name,dept_name
FROM
employees,dept_manager,departments
WHERE
employees.emp_no = dept_manager.emp_no AND dept_manager.dept_no = departments.dept_no
计算各个部门的平均工资并排序，给出平均工资、部门名
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 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;
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;