1. select * from emp;
2. select empno, ename, job from emp;
3. select empno 编号, ename 姓名, job 工作 from emp;
4. select job from emp;
5. select distinct job from emp;
6. select distinct empno, job from emp;
说明:因为雇员编号不重复, 所以此时证明所有的列没有重复,所以不能消除掉重复的列.
7. 查询出雇员的编号, 姓名, 工作, 但是显示的格式:编号是: 7369 的雇员, 姓名是: smith, 工作是: clear
select '编号是: ' || empno || '的雇员, 姓名是: ' || ename || ', 工作是: ' || job from emp;
8. 求出每个雇员的姓名及年薪
select ename, sal * 12 income from emp;
9. 求出工资大于 1500 的所有雇员信息
select * from emp where sal > 1500;
10. 查询每月可以得到奖金的雇员信息
select * from emp where comm is not null;
11. 查询没有奖金的雇员信息
select * from emp where comm is null;
12. 查询出基本工资大于 1500 同时可以领取奖金的雇员信息
select * from emp where sal > 1500 and comm is not null;
13. 查询出基本工资大于 1500 或者可以领取奖金的雇员信息
select * from emp where sal > 1500 or comm is not null;
14. 查询出基本工资不大于 1500 或者不可以领取奖金的雇员信息
select * from emp where not(sal > 1500 and comm is not null);
15. 查询基本工资大于 1500, 但是小于 3000 的全部雇员信息
select * from emp where sal > 1500 and sal < 3000;
16. 查询基本工资大于等于 1500, 但是小于等于 3000 的全部雇员信息
select * from emp where sal >= 1500 and sal 2000;
84. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于 5000, 输出结果按月工资的合计升序排序.
select job, sum(sal) su from emp where job 'SALESMAN' group by job having sum(sal) > 5000 order by su;
select temp.job, sum(temp.sal) s
from (select job, sal from emp e where job 'SALESMAN') temp
group by temp.job
having sum(temp.sal) > 5000
order by s;
85. 求出平均工资最高的部门工资
select max(avg(sal)) from emp group by deptno;
86. 要求查询出比雇员编号为 7654 工资高的所有雇员信息
select * from emp where sal >(select sal from emp where empno = 7654);
87. 要求查询出工资比 7654 高, 同时与 7788 从事相同工作的全部雇员信息
select * from emp
where sal >(select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);
88. 要求查询出工资最低的雇员姓名, 工作, 工资
select ename, job, sal from emp where sal = (select min(sal) from emp);
89. 要求查询出: 部门名称，部门的员工数，部门的平均工资，部门的最低收入雇员的姓名
select d.dname, temp.c, temp.a, e.ename
from dept d,
(select deptno, count(empno) c, avg(sal) a, min(sal) m from emp group by deptno) temp,
emp e
where d.deptno = temp.deptno and e.sal = temp.m;
select d.deptno, temp.dname, temp.c, temp.a, e.ename, e.sal
from
(select d.dname , count(e.empno) c, avg(e.sal) a, min(e.sal) m
from emp e, dept d
where e.deptno = d.deptno
group by d.dname) temp,
emp e,
dept d
where temp.m = e.sal
and temp.dname = d.dname;
90. 求出每个部门的最低工资的雇员的信息
select * from emp where sal in(select min(sal) from emp group by deptno);
select * from emp where sal =any(select min(sal) from emp group by deptno);
select * from
(select min(sal) m from emp group by deptno) temp,
emp e
where e.sal = temp.m;
91. 范例 90 中, 比子查询条件中最低(小)的工资要大的雇员信息
select * from emp where sal >any(select min(sal) from emp group by deptno);
select * from emp where sal > (select min(min(sal)) from emp group by deptno);
92. 范例 90 中, 比子查询条件中最高(大)的工资要小的雇员信息
select * from emp where sal all(select min(sal) from emp group by deptno);
select * from emp where sal > (select max(min(sal)) from emp group by deptno);
94. 范例 90 中, 比子查询条件中最低(小)的工资要小的雇员信息
select * from emp where sal