Oracle 練習 1

Q1:

Write a query that returns those employees that don't make any commision and have a salary greater than 1100 but less than 5000. Exclude those employees that have a salary equal to 3000

A:

SELECT * 

FROM emp

WHERE ( comm IS NULL OR comm = 0)

AND sal > 1100 

AND sal < 5000

AND sal NOT IN 3000

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7698	BLAKE	MANAGER	7839	1981/05/01	2850	-	30
7782	CLARK	MANAGER	7839	1981/06/09	2450	-	10
7566	JONES	MANAGER	7839	1981/04/02	2975	-	20
7844	TURNER	SALESMAN	7698	1981/09/08	1500	0	30
7934	MILLER	CLERK	7782	1982/01/23	1300	-	10

另一種寫法:

SELECT * FROM EMP WHERE ( COMM IS NULL

AND SAL > 1100 AND SAL < 5000

AND SAL <> 3000 )

OR COMM = 0

Q2:

Returns those employees that are salesman and that make either 300 dollars in commission or greater than 1100 dollars in commission

A2:

SELECT * 

FROM emp

WHERE job = 'SALESMAN'

AND comm = 300 

OR comm > 1100

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7499	ALLEN	SALESMAN	7698	1981/02/20	1600	300	30
7654	MARTIN	SALESMAN	7698	1981/09/28	1250	1400	30