子查问练习
db40
列出薪资比'王海涛'的薪资高的所有员工，显示姓名、薪资select name,sal from emp where sal>(select sal from emp where name="王海涛");列出与'刘沛霞'从事雷同职位的所有员工，显示姓名、职位。select name,job from emp where job=(select job from emp where name="刘沛霞");列出薪资比'大数据部'部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资和部门名称。select e.name,e.sal,d.name from emp e join dept d on sal>(select MAX(sal) from emp where dept_id=30);加上没有部门的员工select e.name,e.sal,d.name from emp e left join dept d on e.dept_id=d.id where sal>(select MAX(sal) from emp where dept_id=30);多表查问练习
.列出在'培优部'任职的员工，假设不晓得'培优部'的部门编号，显示部门名称，员工名称。select d.name,e.name from dept d join emp e on d.name="培优部" where d.id=e.dept_id;(自查问)列出所有员工及其间接下级，显示员工姓名、下级编号，下级姓名 select c.name,c.id,c.topid,p.name,p.id from emp c join emp p on c.topid=p.id; 列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资select job,min(sal) from emp group by job having min(sal)>1500; // 补充where和having的区别 // 1.都能过滤2.where是在分组之前对数据过滤；having是在分组之后对数据过滤3.where不能和聚合函数一起应用，不能和order\group应用列出在每个部门就任的员工数量、平均工资。显示部门编号、员工数量，均匀薪资。select count(*),avg(sal),dept_id from emp group by dept_id;查出至多有一个员工的部门，显示部门编号、部门名称、部门地位、部门人数。select e.dept_id,d.name,d.loc,count(*) from emp e join dept d on e.dept_id=d.id group by dept_id having count(*)>0;列出受雇日期早于间接下级的所有员工，显示员工编号、员工姓名、部门名称。select c.id,c.name,c.dept_id,d.name dept_name from emp c,emp p, dept d where c.topid=p.id and c.dept_id=d.id and c.hdate<p.hdate;查问员工表中薪资最高的员工信息 select * from emp order by sal desc limit 0,1;