SELECT e.last_name,e.department_id,dep.department_name
FROM employees e LEFTJOIN departments dep
ON e.department_id =dep.department_id;
2.查询90号部门员工的job_id和90号部门的location_id
DESC locations;#查看表的组成DESC departments;SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id=90;
SELECT e.last_name,e.commission_pct,d.department_name,d.location_id,l.city
FROM employees e LEFTJOIN departments d
ON e.department_id = d.department_id
LEFTJOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct ISNOTNULL;SELECT*FROM employees WHERE commission_pct ISNOTNULL;
SELECT e.last_name,e.job_id ,d.department_id,d.department_name
FROM employees e JOIN departments d
ON e.department_id =d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE l.city ='Toronto';
sql92语法
SELECT e.last_name,e.job_id ,d.department_id,d.department_name
FROM employees e ,departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city ='Toronto';
SELECT e.last_name,e.job_id,e.salary,d.department_name,l.street_address
FROM departments d LEFTJOIN employees e
ON d.department_id = e.department_id
LEFTJOIN locations l
ON d.location_id = l.location_id
WHERE d.department_name ='Executive';
employees Emp# manager Mgr#
kochhar 101 king 100SELECT e.last_name "enployees",e.employee_id "Emp#",mgr.last_name "manager",mgr.employee_id "Mgr#"FROM employees e LEFTJOIN employees mgr
ON e.manager_id =mgr.employee_id;
7查询那些部门没有员工
SELECT d.department_id
FROM departments d LEFTJOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id ISNULL;
8 查询那个城市没有部门
SELECT l.location_id,l.city
FROM locations l LEFTJOIN departments d
ON l.location_id = d.location_id
WHERE d.location_id ISNULL;SELECT department_id
FROM departments
WHERE department_id IN(1000,1100,1200,1300,1600);
9查询部门名为sales或IT的员工信息
SELECT E.employee_id ,E.last_name,E.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN('sales','IT');