(Oracle DB) - 10. View

/* View */
	/*
	#	Database Object for selecting data from one or more tables, or other view.
	#	Actual data is stored in the referenced table which is selected by view,
		but view can be used as the referenced table.
	#	Can be used as a new view with referencing other view(s)
	#	'Selecting data' is meaningful function of view,
		so the definition of view is made of SELECT statements.
	*/

/* CREATE View */
	
	-- Why View Is Needed?
SELECT
	a.employee_id,
	a.emp_name,
	a.department_id,
	b.department_name
	FROM employees a, departments b
WHERE a.department_id = b.department_id
;	/*
	Table 'employee' doesn't have the information about department_name,
	'departments' have that information. So if we try to check the department
	name of any employees, we need to refer to the table 'departments'
		
	However, it's not a good way to write this long query several times if some people
	try this query. It's NOT efficient!
	*/

CREATE OR REPLACE VIEW emp_dept_v1 AS	-- Creating a view
SELECT
	a.employee_id,
	a.emp_name,
	a.department_id,
	b.department_name
	FROM employees a, departments b
WHERE a.department_id = b.department_id
;

SELECT *
	FROM emp_dept_v1;	-- Use this view!
	
/*
#	For processing and using needed-data from some tables, view is the best way. 
#	View selects only column and data, and hide the source table.
	It's good for security of data.	
*/

/* DROP View */
DROP VIEW emp_dept_v1;