1:查询员工名和对应的部门名(多表联查) SELECT last_name,department_name FROM employees,departments WHERE employees.department_id=departments.department_id;
2:查询有奖金的员工名和工种名(多表联查) SELECT last_name,job_title FROM employees e,jobs j WHERE e.`job_id`=j.`job_id` AND commission_pct IS NOT NULL;
3:查询每个部门的部门名和员工个数(多表联查) SELECT COUNT(*) 个数,department_name FROM departments d,employees e WHERE d.department_id=e.`department_id` GROUP BY e.`department_id`;
4:查询员工个数>5的部门名称(多表联查) SELECT COUNT(*) 个数,department_name FROM departments d,employees e WHERE d.department_id=e.`department_id` GROUP BY e.`department_id` HAVING 个数>5;
5:查询 员工名、部门名和城市名(三表联查) SELECT last_name,department_name,city FROM departments d,employees e,locations l WHERE e.`department_id`=d.`department_id` AND l.`location_id`=d.`location_id`;
6:查询员工表中,部门编号在80-100之间的last_name,employee_id信息,并且按工资降序排列 select last_name,employee_id from employees where department_id BETWEEN 80 and 100 ORDER BY salary desc;
7:查询没有奖金,且工资小于18000的salary,last_name信息 select salary,last_name from employees where commission_pct is NULL and salary<18000;
8:查询员工表中,job_id不为“IT”或者工资为12000的员工信息 select * from employees where not (job_id='IT') or salary=12000;
9:查询各个工种(job_id)的员工工资的最大值,最小值,平均值,总和,并且按job_id升序排列 select max(salary),min(salary),avg(salary),sum(salary),job_id from employees GROUP BY job_id ORDER BY job_id asc;
10:查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不在计算内, select min(salary),manager_id from employees where manager_id is not NULL group by manager_id HAVING min(salary)>=6000
11:查询每个部门的有奖金的员工的最高工资 select department_id,max(salary) from employees where commission_pct is not NULL group by department_id;
12:查询哪个部门有奖金员工最高工资 >10000的部门编号和最高工资 select department_id,max(salary) from employees where commission_pct is not NULL group by department_id having max(salary)>10000;
13:查询各个工种(job_id)的员工工资的最大值,最小值,平均值,总和,并且按job_id升序排列 select max(salary),min(salary),avg(salary),sum(salary),job_id from employees GROUP BY job_id ORDER BY job_id asc;
14:写出查询员工对应的部门名称(用3种方法) 第1种方法: select department_name from departments where department_id in
(select department_id from employees)
第2种方法: select DISTINCT(d.department_name) from departments d,employees e where d.department_id=e.department_id;
第3种方法: select DISTINCT(d.department_name) from departments d inner join employees e on d.department_id=e.department_id