第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 ;