(Oracle DB) - 25. Self Check 3

/* Self_Check_3_1 */
CREATE TABLE ex3_6
(
	employee_id NUMBER NOT NULL,
	emp_name VARCHAR2(60) NOT NULL,
	salary NUMBER,
	manager_id NUMBER
)
;

SELECT employee_id, emp_name, salary, manager_id
	FROM employees
WHERE 1 = 1
AND manager_id = 124
AND salary BETWEEN 2000 AND 3000
;

INSERT INTO ex3_6 ex
	(
	SELECT emp.employee_id, emp.emp_name, emp.salary, emp.manager_id
		FROM employees emp
	WHERE emp.manager_id = 124
	AND emp.salary BETWEEN 2000 AND 3000
	)
;

SELECT * FROM ex3_6;
COMMIT;

/* Self_Check_3_2 */

--######	Execute Queries Below	######
DELETE FROM ex3_3;
SELECT * FROM ex3_3;

INSERT INTO ex3_3
	(employee_id)
	SELECT e.employee_id
		FROM employees e, sales s
	WHERE e.employee_id = s.employee_id
		AND s.sales_month BETWEEN '200010' AND '200012'
	GROUP BY e.employee_id
;

COMMIT;
SELECT * FROM ex3_3;

--########################################

SELECT employee_id, salary, manager_id
	FROM employees
WHERE manager_id = 145
;	-- 6 employees

MERGE INTO ex3_3 ex
	USING
		(
		SELECT
			emp.employee_id, emp.manager_id, emp.salary
			FROM employees emp
		WHERE emp.manager_id = 145
		) used
	ON (ex.employee_id = used.employee_id)
WHEN MATCHED THEN
	UPDATE
		SET ex.additional_bonus = used.salary * 0.01
WHEN NOT MATCHED THEN
	INSERT
		VALUES
		(used.employee_id, used.salary * 0.05)
;

SELECT *
	FROM ex3_3
ORDER BY employee_id
;


/* Self_Check_3_3 */
SELECT employee_id, emp_name
	FROM employees
WHERE 1 = 1
AND commission_pct IS NULL
;


/* Self_Check_3_4 */
SELECT employee_id, salary
	FROM employees
WHERE 1 = 1
AND salary >= 2000
AND salary <= 2500
--AND salary BETWEEN 2000 and 2500
ORDER BY employee_id
;


/* Self_Check_3_4 */
SELECT employee_id, salary
	FROM employees
WHERE salary = ANY(2000, 3000, 4000)
ORDER BY employee_id
;

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