第18节-练习

#1.查询工资最低的员工信息：last_name,salary

select last_name,salary from employees where salary is not null order by salary limit 1;
或 
select last_name, salary from employees where salary=(select min(salary) from employees);

#2. 查询平均工资最低的部门信息

SELECT d.* FROM departments d  INNER JOIN 
    (SELECT 
      AVG(salary) avg_sal,
      department_id 
    FROM
      employees 
    GROUP BY department_id 
    ORDER BY avg_sal 
    LIMIT 1) a 
    ON d.department_id = a.department_id ;
或 
    SELECT * FROM departments
    WHERE department_id=(
    SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1);

 

#3. 查询平均工资最低的部门信息和该部门的平均工资

步骤：① select avg(salary) A, department_id from employees
group by department_id
order by A
limit 1；
     ② SELECT d.*, A FROM departments d
INNER JOIN (SELECT AVG(salary) A,department_id FROM employees GROUP BY department_id 
ORDER BY A LIMIT 1) tab2 
ON d.department_id=tab2.department_id;

#4.查询平均工资最高的job信息

① 查询最高工资：
select avg(salary) A,job_id  from employees group by job_id order by A desc limit 1;

② SELECT j.* 
FROM jobs j 
  INNER JOIN 
    (SELECT AVG(salary) A,job_id 
    FROM employees 
    GROUP BY job_id 
    ORDER BY A DESC 
    LIMIT 1) h_sal 
    ON j.job_id = h_sal.job_id ;

 

__EOF__

本文作者：Jasmine
本文链接：https://www.cnblogs.com/Jasmine6-Lee/p/12674898.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！