SQL子查询

创建员工表：

create table if not exists emp(

emp_id int primary key auto_increment,emp_name varchar,salary double,dept_id int comment '部门',manager_id int comment '上级部门'

);

插入数据：

insert into emp(emp_name,salary,dept_id,manger_id)values("tom",15000.0,1,null),

("孙岩",5000.0,5,null),

("曹广司",7000.0,3,null),

("王强",6000.0,2,null),

("周杰",15000.0,2,null),

("李华",65000.0,1,null),

("张建安",8000.0,1,null)

1、查询部门与张建国的部门相同，工资大于孙岩工资的员工信息。

select * from emp where depe_id = (

select dept_id from emp where emp_name ="张建国"

)

and salary > (

select salary from emp where emp_name = "孙岩"

）;

2、查询孙姓员工所在部门的员工信息

select * from emp

where dept_id in(

select dept_id from emp where emp_name like '孙%'

);