1 找出佣金高于薪金60%的雇员。
SELECT * FROM emp WHERE comm > (sal * 0.6);
2 找出部门10中所有经理和部门20中所有办事员的详细资料。
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job = 'CLERK');
3 找出部门10中所有经理，部门20中所有办事员以及既不是经理又不是办事员但其薪金大于或等2000的所有雇员的详细资料。
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER')
OR (deptno = 20 AND job = 'CLERK'
OR (sal > 2000 AND job NOT IN ('MANAGER','CLERK')));
4 找出收取佣金的雇员的不同工作。
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;
5 找出不收取佣金或收取的佣金低于300的雇员。
SELECT * FROM emp WHERE comm IS NULL OR comm < 300;
6 找出各月最后一天受雇的所有雇员。
SELECT * FROM emp WHERE LAST_DAY(hiredate) = hiredate;
7 找出晚于26年之前受雇的雇员。
SELECT * FROM emp WHERE SYSDATE > ADD_MONTHS(hiredate,26 * 12) ;
8 显示只有首字母大写的的所有雇员的姓名。
SELECT INITCAP(ename) FROM emp;
9 显示正好为5个字符的雇员的姓名。
SELECT * FROM emp WHERE LENGTH(ename) = 5;
10显示不带有“R”的雇员姓名。
SELECT * FROM emp WHERE ename NOT LIKE '%R%';
11显示所有雇员的姓名的前三个字符。
SELECT SUBSTR(ename,0,3) FROM emp;
12显所有雇员的姓名，用a替换所有“A”。
SELECT REPLACE(ename,'A','a') FROM emp;
13显示所有雇员的姓名以及满10年服务年限的日期。
SELECT ename,ADD_MONTHS(hiredate,12 * 10) FROM emp;
14显示雇员的详细资料，按姓名排序。
SELECT * FROM emp ORDER BY ename;
15显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。
SELECT * FROM emp ORDER BY hiredate ASC;
16显示所有雇员的姓名、工作和薪金，按工作的降序排序，而工作按薪金排序。
SELECT ename,job,sal FROM emp ORDER BY job DESC,sal DESC;
17显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，并将最早年份的项目排在最前面。
SELECT ename,TO_CHAR(hiredate,'yyyy-mm') FROM emp ORDER BY TO_CHAR(hiredate,'mm') ASC ,TO_CHAR(hiredate,'yyyy') ASC;
18显示在一个月为30天的情况下所有雇员的日薪金，取整。
SELECT ROUND(sal / 30,0) FROM emp;
19找出在（任何年份的）2月受聘的所有雇员。
SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm') = 2;
20对于每个雇员，显示其加入公司的天数。
SELECT CEIL(sysdate-hiredate) FROM emp;
21.找出所有的普通员工：
select * from emp where empno not in(select mgr from emp where mgr is not null)
22.求部门中哪些人的薪水最高：
Select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno= t.deptno);
23.求部门平均薪水的等级：
Select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);
24.求部门平均的薪水等级
Select deptno,avg(grade) from (select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno;
25.雇员中哪些人是经理人：
Select ename from emp where empno in (select distinct mgr from emp);
26.不准用组函数，求薪水的最高值
Select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));
27．求平均薪水最高的部门的部门编号
Select deptno,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno));
28.求平均薪水最高的部门的部门名字
select dname from dept where
deptno=(Select deptno from (select avg(sal)
avg_sal,deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno)))
29.求比普通员工的最高薪水还要高的经理人名称
Select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal >(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));