1.简单查询使用
1)查询工资高于500或岗位为MANAGER的雇员,同时还要满足他们姓名的首字母为大写的J
select ename,sal,job from emp where ename like 'J%' and (sal>500 or job = 'MANAGER');
2)显示工资最高的员工姓名和他的岗位
select ename,job from emp where sal=(select max(sal) from emp);
3)按照部门号升序而员工工资降序排序输出所有信息
select * from emp order by deptno,sal desc;
2.group by 分组使用
1)显示每个部门的平均和最高工资
select deptno,avg(sal) as avg_sal,max(sal) as max_sal from emp group by deptno;
2)显示平均工资低于2000的部门和它的平均工资
select deptno,avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;
3)显示每种岗位的雇员总数和平均工资
select job,count(*),avg(sal) as avg_sal from emp group by job;
4.多表查询
子查询
1)显示部门号为10的部门名、员工名和工资
select dname,ename,sal from emp,dept where emp.deptno = dept.deptno and emp.deptno=10;
2)显示员工FORD的上级领导的编号与姓名
select empno,ename from emp where empno = (select mgr from emp where ename like 'FORD');
3)显示和SMITH同一部门的员工信息
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
4)查询和10号部门工作相同的雇员名称、岗位、工资与部门号,但是不包含10号部门自己的
select ename,job,sal,deptno from emp
where job in (select job from emp where deptno=10)
and deptno != 10;
5)显示工资比部门编号为30的所有员工工资高的员工姓名、工资与部门号
select ename,sal,deptno from emp
where sal >
(select max(sal) from emp group by deptno having deptno = 30);
select ename,sal,deptno from emp where sal > all (select sal from emp where deptno = 30);
派生查询
1)显示高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename,deptno,sal,avg_sal from emp,
(select avg(sal) avg_sal,deptno dt from emp group by deptno) as avg_emp
where emp.deptno = avg_emp.dt and emp.sal > avg_emp.avg_sal;
2)查找每个部门工资最高的人的姓名、工资、部门、最高工资
select ename,sal,deptno,max_sal from emp,
(select deptno dt ,max(sal) max_sal from emp group by deptno)
as max_emp
where emp.deptno=max_emp.dt and emp.sal = max_emp.max_sal;
3)显示每个部门的信息(部门号、编号、地址)和人员数量
select dname,emp.deptno,loc,count(*)
from emp,dept
where emp.deptno=dept.deptno group by emp.deptno;