几条常见的查询题

employee表                                                   department表

emp_id     int     (表id)                           dpt_id                    int        (表id)
emp_name   varchar (员工姓名)            dpt_name             varchar    (部门名称)
dpt_id     int     (部门ID)                        dpt_manage_id      int        (部门负责人id)
emp_salary int     (工资)
emp_age    int     (年龄) 

其中employee表的emp_id与department表的dpt_manage_id的关系为相等.

请分别写出SQL
1,查询研发部的所有工资高于5000元所有员工的姓名和工资
2,查询部门工资总额高于5万元的部门名称
3,查询张三属下并且年龄高于40岁的所有员工的姓名,年龄,所在部门和工资

不懂写SQL,请各位指教!谢谢

 

1. 

select emp_name, emp_salaryfrom employee ewhere emp_salary> 5000 and exists(select 1 from departmentwhere dpt_id= e.dpt_idand dpt_name= '研发部' )

select e.emp_name,e.emp_salary from employee e,depatrment d where d.dpt_id=e.dpt_id and d.dpt_name='研发部' and e.emp_salary >3000

2. 

select dpt_namefrom department d where (select sum(emp_salary)from employeewhere dpt_id= d.dpt_id )> 50000

select d.dpt_name from depatrment d inner join employee e on d.dpt_id=e.dpt_id group by d.dpt_name   having sum(e.emp_salary)>50000 

3.
select e.emp_name, e.emp_age, d.dpt_name, e.emp_salaryfrom employee einner join department don e.dpt_id= d.dpt_idwhere e.emp_age> 40 and exists(select 1 from employeewhere emp_id= d.dpt_manage_idand emp_name= '张三'

select e.emp_name,e.emp_age,d.dpt_name,e.emp_salary from employee e inner join depatrment d on e.dpt_id=d.dpt_id where e.emp_age>18and e.emp_id = d.dpt_manage_id ande.emp_id in (select emp_id from employee where emp_name='张三')