选择部门30中的所有员工?
select * from emp where deptno=30;
列出所有办事员(CLERK)的姓名,编号?
select ename,empno from emp where job='CLERK';
找出佣金高于薪金的员工?
select ename,sal,comm from scott.emp where comm>sal;
找出佣金高于薪金的60%的员工?
select ename,sal,comm,sal*0.6 from scott.emp where nvl(comm,0)>sal*0.6;
找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料
select * from scott.emp where (deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK');
不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料?
select * from scott.emp where job not in('CLERK','MANAGER') and sal>=2000;
找出收取佣金的员工的不同工作?
select distinct job from scott.emp where comm is not null;
找出不收取佣金或收取的佣金低于100的员工?
select * from scott.emp where comm is null or comm<100;
找出早于12年前受雇的员工?
select ename,hiredate,months_between(sysdate,hiredate)/12 from scott.emp where months_between(sysdate,hiredate)/12>12;
查询表DEPT中的部门号、部门名称两个字段的所有信息。
select deptno,dname from scott.dept;
请从表EMP中查询10号部门工作的雇员姓名和工资。
select ename,sal from scott.emp where deptno=10;
请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。
select ename,sal from scott.emp where job='CLERK' or job='MANAGER';
请在EMP表中查找部门号在10-30之间的雇员的姓名、部门号、工资、工作
SELECT ename,deptno,sal,job from scott.emp where deptno between 10 and 30;