1.显示所有员工的姓名，部门号和部门名称
SELECt e.last_name,e.department_id,dep.department_name
FROM employees e LEFT JOIN 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;
3.选择所有有奖金的员工的last_name,department_name,location_id,city
SELECt e.last_name,e.commission_pct,d.department_name,d.location_id,l.city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERe e.commission_pct IS NOT NULL;
SELECt * FROM employees WHERe commission_pct IS NOT NULL;
4 选择city 在Toronto工作的员工的last_name,job_id ,department_id,department_name
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';
5查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECt e.last_name,e.job_id,e.salary,d.department_name,l.street_address
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERe d.department_name ='Executive';
DESC departments #查询表的结构
DESC employees
DESC locations
6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
SELECt e.last_name "enployees",e.employee_id "Emp#" ,mgr.last_name "manager" ,mgr.employee_id "Mgr#"
FROM employees e LEFT JOIN employees mgr
ON e.manager_id =mgr.employee_id;
7查询那些部门没有员工
SELECt d.department_id
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERe e.department_id IS NULL;
8 查询那个城市没有部门
SELECt l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERe d.location_id IS NULL;
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');
