#标量子查询 ##where having面
SELECT salary from employees where last_name=“abel” SELECT * from employees where salary>( SELECT salary from employees where last_name=‘abel’);
#返回job_id于 141号员工相同的，salary 比143号员工多的 员工姓名 id salary job_id SELECT employee_id ,last_name,job_id,salary from employees where job_id=(SELECT job_id from employees where employee_id=141) and salary>(SELECT salary from employees where employee_id=143)
#返回工资最少的员工的last_name，job_id，salary SELECT * from employees where salary=(SELECT min(salary ) from employees )
SELECT top 1 from employees ORDER BY salary desc
SELECT * from employees ORDER BY salary asc LIMIT 0,1
##列子查询 ##返回location_id是1400 或者 1700 的所有员工的姓名 SELECT DISTINCT department_id from departments where location_id in (1400,1700)
SELECT DISTINCT last_name from employees inner join departments on employees.department_id=departments.department_id where location_id in (1400,1700) SELECT last_name from employees where department_id in (SELECT DISTINCT department_id from departments where location_id in (1400,1700))
##最小的员工编号 并且工资最高的员工信息 SELECT * from employees where employee_id=(SELECT min(employee_id) from employees ) and salary=(SELECT max(salary) from employees)
##查询每个部门的平均工资和等级 SELECT ag_dept.*,job_grades.grade_level from ( SELECT AVG(salary) avgs ,department_id from employees GROUP BY department_id HAVING department_id is not null ) ag_dept inner join job_grades on ag_dept.avgs BETWEEN job_grades.lowest_sal and job_grades.highest_sal
##EXISTS ##查询有员工的部门名 SELECT department_name from departments d WHERE EXISTS (SELECT * from employees e where d.department_id=e.department_id)
SELECT department_name ,count() from departments d inner join employees e on e.department_id=d.department_id GROUP BY department_name having count()>0
SELECT department_name from departments d where department_id in (SELECT department_id from employees )
SELECT last_name,salary from employees where department_id =(SELECT department_id from employees where last_name=‘Zlotkey’)
#查询工资比本平均工资高的员工号，姓名工资 SELECT employee_id ,last_name ,salary from employees inner join ( SELECT avg(salary ) avs,department_id from employees GROUP BY department_id ) ag_dep on employees.department_id=ag_dep.department_id where salary>ag_dep.avs
#查询名字中包含字母u的员工在相同部门的员工的员工号和姓名 SELECT department_id,last_name from employees e where department_id in (SELECT DISTINCT department_id from employees where last_name like ‘%u%’)
#查询部门的location_id 为 1700 的员工的员工号 SELECT employee_id from employees e inner join departments d on e.department_id=d.department_id where d.location_id=1700
#查询管理者是king的员工姓名和工资 SELECT last_name,salary from employees where manager_id in(SELECT employee_id from employees where last_name=‘K_ing’)
#分页查询 ##查询右奖金的员工信息，且显示前10名 SELECT * from employees where commission_pct is not null ORDER BY salary desc LIMIT 0 ,10
#查询最低工资的员工信息：last_name salary select last_name,salary from employees where salary in (SELECT min(salary) from employees) #查询平均工资最低的部门 SELECT * from departments d right join employees e on e.department_id=d,d.department_id where
SELECT d.*,avg(salary) from employees e inner join departments d on e.department_id=d.department_id GROUP BY department_id ORDER BY avg(salary) asc LIMIT 0,1 #查询给个job最高的平均工资 SELECT AVG(salary) ,job_id from employees GROUP BY job_id ORDER BY AVG(salary) desc LIMIT 0,1
#查询平均工资高于公司的平均工资的部门有那些 SELECT department_id ,avg(salary) from employees GROUP BY department_id HAVING avg(salary) > (SELECT avg(salary) from employees) #查询最高平均工资中，最低工资是多少 SELECT min(salary) from employees where department_id = (SELECT department_id from employees GROUP BY department_id ORDER BY AVG(salary) desc LIMIT 1) #查询各个部门中最高工资中最低的工资 SELECT max(salary) from employees where department_id=(SELECT department_id from employees GROUP BY department_id ORDER BY max(salary) asc LIMIT 1) SELECT * from employees where department_id=10
##查询平均工资最高的部门的manager的详细信息 SELECT * from employees where employee_id =(SELECT manager_id from employees GROUP BY department_id ORDER BY avg(salary) LIMIT 1)
SELECT department_id from employees GROUP BY department_id ORDER BY AVG(salary) desc LIMIT 1
select DISTINCT manager_id from employees where department_id=(SELECT department_id from employees GROUP BY department_id ORDER BY AVG(salary) desc LIMIT 1)
SELECT * from employees where employee_id in (select DISTINCT manager_id from employees where department_id=(SELECT department_id from employees GROUP BY department_id ORDER BY AVG(salary) desc LIMIT 1))
