连接测试题

#1.显示所有员工的姓名、部门号和部门名称

SELECT last_name,d.department_id,department_name

FROM employees e,departments d

WHERE e.`department_id`=d.`department_id`;

#2.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id

FROM employees e,departments d

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 employees e,departments d,locations l

WHERE e.`department_id`=d.`department_id`

AND d.`location_id`=l.`location_id`

AND e.`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

FROM employees e,departments d,locations l

WHERE e.`department_id`=d.`department_id`

AND d.`location_id`=l.`location_id`

AND city = 'Toronto';

#5.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name,job_title,MIN(salary)

FROM employees e,departments d,jobs j

WHERE e.`department_id`=d.`department_id`

AND e.`job_id`=j.`job_id`

GROUP BY department_name,job_title;

#6.查询每个国家下的部门个数大于2的国家编号

SELECT COUNT(*) 部门个数,country_id

FROM departments d,locations l

WHERE d.`location_id`=l.`location_id`

GROUP BY country_id

HAVING COUNT(*)>2;

#7.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp# manager Mgr#

kochhar 101 king 100

SELECT e.last_name emplyees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"

FROM employees e,employees m

WHERE e.manager_id=m.employee_id

AND e.last_name='kochhar';