(Oracle DB) - 24. Conditions

/* Conditions
	#	Condition is combined with one or more expressions and relational operators.
	#	Condition returns TRUE / FALSE / UNKNOWN
*/

/* Comparison Conditions : ANY() / ALL() / SOME() */

	--	ANY(conditions ...) : Same logic with OR
SELECT employee_id, salary
	FROM employees
WHERE salary = ANY(2000, 3000, 4000)
ORDER BY employee_id
;

	--	OR (conditions ...) : Same logic with ANY
SELECT employee_id, salary
	FROM employees
WHERE salary = 2000
	OR salary = 3000
	OR salary = 4000
ORDER BY employee_id
;

	--	ALL(conditions ...) : All the Conditions Must be Satisfied (AND, AND, AND, ...)
SELECT employee_id, salary
	FROM employees
WHERE salary = ALL(2000, 3000, 4000)	-- Logical Error. Kinda context error..
ORDER BY employee_id
;

	--	SOME(conditions ...) : Same logic with ANY
SELECT employee_id, salary
	FROM employees
WHERE salary = SOME(2000, 3000, 4000)
ORDER BY employee_id
;


/* Logical Conditions : AND() / OR() / NOT() */
SELECT employee_id, salary
	FROM employees
WHERE NOT(salary >= 2500)
ORDER BY employee_id
;


/* NULL Condition */
SELECT employee_id, salary
	FROM employees
WHERE salary IS NOT NULL
ORDER BY employee_id
;


/* BETWEEN AND Condition */
SELECT employee_id, salary
	FROM employees
WHERE salary BETWEEN 2000 AND 2500
ORDER BY employee_id
;


/* IN() condition */
SELECT employee_id, salary
	FROM employees
WHERE salary IN(2000, 3000, 4000)
ORDER BY employee_id
;

SELECT employee_id, salary
	FROM employees
WHERE salary NOT IN(2000, 3000, 4000)
ORDER BY employee_id
;


/* EXISTS() Condition
	#	Similar with IN() condition, but !!
		the condition in the round brackets MUST be	sub-query
	#	Join condition MUST be in the sub-query 
*/
SELECT department_id, department_name
	FROM departments dept
WHERE 1 = 1
AND EXISTS
	(
	SELECT *
		FROM employees emp
	WHERE dept.department_id = emp.department_id
	AND(emp.salary > 3000)
	)
ORDER BY dept.department_name
;


/* LIKE Condition
	#	'(string)%' : (string)....
	#	'%(string)' : ....(string)
	#	'_' : One character can be on each underscore(_)
*/
SELECT emp_name
	FROM employees
WHERE 1 = 1
AND emp_name LIKE 'A%'
ORDER BY emp_name
;

SELECT emp_name
	FROM employees
WHERE 1 = 1
AND emp_name LIKE '%el'
ORDER BY emp_name
;

SELECT emp_name, salary
	FROM employees
WHERE salary LIKE 2500
ORDER BY emp_name
;

SELECT emp_name
	FROM employees
WHERE 1 = 1
--AND emp_name LIKE '________el'	-- 'Ellen Abel'
--AND emp_name LIKE '%a__l'	-- 'Joshua Patel'
ORDER BY emp_name
;

	/* About Underscore(_) */
CREATE TABLE ex3_5
(
	name VARCHAR2(30)
)
;

INSERT INTO ex3_5
	VALUES
	('Saul Kripke')
;

INSERT INTO ex3_5
	VALUES
	('Platon')
;

INSERT INTO ex3_5
	VALUES
	('Bertrand Russell')
;

INSERT INTO ex3_5
	VALUES
	('Gottlob Frege')
;

INSERT INTO ex3_5
	VALUES
	('Aristole')
;

INSERT INTO ex3_5
	VALUES
	('Edmund Gettier')
;

SELECT *
	FROM ex3_5
WHERE name LIKE '____nd%'
;