连接查询练习题

#1.显示所有员工的姓名，部门号和部门名称
SELECT  last_name,d.department_id,department_name
FROM  departments  d, employees e
WHERE d.`department_id`=e.`department_id`;
#2.查询90号部门员工的job_id和90号部门的location_id
SELECT j.`job_id`,l.`location_id`
FROM `employees` e,`jobs` j,`locations` l,`departments` d
WHERE e.`job_id`=j.`job_id`&&e.`department_id`=d.`department_id`&&
d.`location_id`=l.`location_id` &&d.`department_id`=90;

#简洁性
SELECT  job_id,location_id
FROM `departments` d,`employees` e
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;
#3.选择所有有奖金的员工的last_name,department_name,location_id,city
SELECT `last_name`,`department_name`,l.`location_id`,`city`
FROM `departments` d,`employees` e,`locations` l
WHERE  d.`department_id`=e.`department_id` AND d.`location_id`=l.`location_id`
AND `commission_pct` IS NOT NULL;

#4.选择city在Toronto的员工的last_name,job_id,department_id,department_name
SELECT `last_name`,`job_id`,d.`department_id`,`department_name`,city
FROM `departments` d,`employees` e,`locations` l
WHERE d.`department_id`=e.`department_id` AND d.`location_id`=l.`location_id`
AND city='Toronto';
#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT  `department_name`,`job_title`,MIN(`salary`) 最低工资
FROM `employees` e,`jobs` j,`departments` d
WHERE e.`department_id`=d.`department_id` AND j.`job_id`=e.`job_id`
GROUP BY   department_name ,job_title;
#6.查询每个国家下的部门个数大于2的国家编号
SELECT  `country_id` ,COUNT(*)
FROM `locations` l,`departments` d
WHERE l.`location_id`=d.`location_id`
GROUP BY country_id
HAVING  COUNT(*)>2;
#7.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
/*
employees     Emp%     manager    Ngr%
kochhar        101     king      100

*/

SELECT e.`last_name` employees ,e.`employee_id` "Emp% ",
m.`last_name` manager,m.`employee_id` "Ngr%"
FROM `employees` e,`employees` m
WHERE e.`manager_id`=m.`employee_id`
AND e.`last_name`='kochhar';