MySQL99语法，子查询

#SQL99语法
#内连接——等值连接  连接条件放在on后面，筛选条件放在where后面
#INNER可以省略

#查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序
SELECT department_name,COUNT(employees.employee_id)
FROM departments
INNER JOIN employees
ON departments.department_id=employees.department_id
GROUP BY department_name
HAVING COUNT(employees.employee_id)>3 
ORDER BY COUNT(employees.employee_id) DESC;

#查询员工名，部门名，工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employees
INNER JOIN departments
ON employees.department_id=departments.department_id
INNER JOIN jobs
ON jobs.job_id=employees.job_id
ORDER BY department_name DESC;

#非等值连接

#查询每个工资级别的个数>20的个数，并且按工资级别降序
SELECT  grade_level,COUNT(grade_level)
FROM employees
INNER JOIN job_grades
WHERE salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal
GROUP BY grade_level
HAVING COUNT(grade_level)>20
ORDER BY job_grades.grade_level DESC

#外连接
/* 外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
		如果从表中没有和它匹配的，则显示null
		外连接查询结果=内连接结果+主表中有而从表没有的记录
左外连接  left join左边的主表
右外连接  right join右边的主表

*/
#查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.employee_id
WHERE e.employee_id is NULL

#查询哪些城市没有部门
SELECT city 
FROM departments
RIGHT JOIN locations
ON departments.location_id=locations.location_id
WHERE departments.department_id is null;

#查询部门名为SAL 或IT的员工信息
SELECT employees.*,department_name
FROM departments
LEFT JOIN employees
ON departments.department_id=employees.department_id
WHERE department_name='SAL' OR department_name='IT';



#子查询
#查询谁的工资比Abel高？
SELECT last_name,salary
FROM employees
WHERE salary>(
			SELECT salary
			FROM employees
			WHERE last_name='Abel'
			);
			
#查询job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id和工资
SELECT 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 last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees);

#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50);