msyql-多表查询

#1，查询员工名和对应的部门名
SELECT `last_name` AS 员工名,
`department_name` AS 部门名
FROM `departments`,`employees`
WHERE `employees`.`department_id`=`departments`.`department_id`;

#2，查询员工名，工种号，工种名
SELECT    e.`last_name` AS 员工名,
e.`job_id` AS 工种号,
j.`job_title` AS 工种名
FROM `employees` e,
`jobs` j
WHERE j.`job_id`=e.`job_id`;

#3，查询有奖金的员工名，部门名
SELECT e.`last_name` AS 员工名,
d.`department_name` AS 部门名称,
e.`commission_pct` AS 奖金率
FROM `employees` e,`departments` d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

#4，查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name` AS 部门名,
`city` AS 城市名
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

#5，查询每个城市的部门个数
SELECT
COUNT(*) AS 部门个数,
`city` AS 城市
FROM `departments` d,`locations` l
WHERE d.`location_id` = l.`location_id`
GROUP BY `city`;