#1992sql
#查询员工名，部门名
SELECT
last_name,department_name
FROM
employees e
JOIN
departments d
ON
e.`department_id`=d.`department_id`

#查询名字中包含e的员工和工种名
SELECT
last_name,job_title
FROM
employees e
INNER JOIN
jobs j
ON
e.`job_id`=j.job_id
WHERE
last_name LIKE '%e%' ;

#查询部门的个数>3的城市名和部门个数
SELECT
COUNT(*),city
FROM
departments d
INNER JOIN
locations l
ON
d.`location_id`=l.`location_id`

GROUP BY
city
HAVING
COUNT(*)>3;

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

#非等值连接
#查询员工的工资级别
SELECT
last_name,grade_level
FROM
employees e
JOIN
job_grades j
ON
e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY
last_name;

#哪个部门没有员工
SELECT
d.*,e.`employee_id`
FROM
departments d
LEFT OUTER JOIN
employees e
ON
d.`department_id`=e.`department_id`
WHERE
e.employee_id IS NULL
GROUP BY d.`department_name`;

#全外连接
USE girls；
SELECT
b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id =bo.id;

 

#交叉连接就是笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;