SQL面试用题

5.查询员工中工资大于本部门平均工资的员工的last_name,

salary和其department_id

SELECT last_name, salary, department_id

FROM   employees outer

WHERE  salary >

(SELECT AVG(salary)

 FROM   employees

 WHERE  department_id =  

        outer.department_id) ;

 

6. 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id

FROM   employees e

WHERE  2 <= (SELECT COUNT(*)

             FROM   job_history

             WHERE  employee_id = e.employee_id);

 

--EXISTS 操作符

• EXISTS 操作符检查在子查询中是否存在满足条件的行
• 如果在子查询中存在满足条件的行: <