子查询

#查询平均年龄在25岁以上的部门名
#select d.name from department as d right join employee as e on d.id = dep_id group by age having avg(age > 25);

# select dep_id from employee group by dep_id having avg(age) > 25;
# select name from department where id in(
#     select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名

# select id from department where name = '技术';
# select name from employee where dep_id = (
# select id from department where name = '技术'
# );

#查看不足1人的部门名

# select dep_id from employee group by dep_id having count(dep_id) >= 1;

# select name from department where id not in(
# select dep_id from employee group by dep_id having count(dep_id) >= 1
# );

#查询大于所有人平均年龄的员工与年龄
# select name,age from employee where age > (select avg(age) from employee);

#查询大于部门内平均年龄的员工名,年龄
# select dep_id,avg(age) from employee group by dep_id;

# select name,age from employee as t1 inner join (
# select dep_id ,avg(age) avg_age from employee group by dep_id
# ) as t2 on t1.dep_id = t2.dep_id where age > avg_age;

# select dep_id,name,age from employee group by dep_id having age > (
#     select avg(age) from employee where dep_id = dep_id)