Oracle 子查询,查询人数最多的部门信息。查询工资比100号员工工资高的所有员工信息。询工资高于公司平均工资的所有员工信息。查询在“Sales”部门工作的员工的姓名信息。
**
大写的是列名,或表名
**
1)查询工资比100号员工工资高的所有员工信息。
SELECT * FROM EMPLOYEES
where salary>(SELECT salary FROM EMPLOYEES
where EMPLOYEE_ID=100);
(2)查询工资高于公司平均工资的所有员工信息。
select *from EMPLOYEES
where salary>(select avg(salary) from EMPLOYEES);
(3)查询在“Sales”部门工作的员工的姓名信息。
Select LAST_NAME ,FIRST_NAME
from employees
Where DEPARTMENT_ID=(
Select DEPARTMENT_ID from departments
Where DEPARTMENT_NAME=‘sales’);
(4)查询人数最多的部门信息。
select *from DEPARTMENTS
where DEPARTMENT_ID =(
select DEPARTMENT_ID from EMPLOYEES
group by DEPARTMENT_ID
having count(*) = (
select max(count(*)) from EMPLOYEES
group by DEPARTMENT_ID
));