/*******************************/
/* PODCAST 04 - CREATING VIEWS */
/*******************************/
USE [HUMAN_RESOURCES]
GO

/***************************************/
/* TOPIC 01 - CREATING VIEWS WITH SSMS */
/***************************************/

DROP VIEW IF EXISTS [dbo].[EMP_SALARY_VIEW]
GO

DROP VIEW IF EXISTS [HR].[EMP_DEPT_VIEW]
GO

/**********************************************************************/
/* VIEW NAME: EMP_DEPT_VIEW                                           */
/**********************************************************************/

/**********************************************************************/
/* Column Logical Name       Physical Column Name Physical Table Name */
/*                                                                    */
/* Employee First Name       [EMP_FNAME]          [HR].[EMPLOYEE]     */
/* Employee Last Name        [EMP_LNAME]          [HR].[EMPLOYEE]     */
/* Hire Date                 [HIRE_DATE]          [HR].[EMPLOYEE]     */
/* Department Name           [DEPT_NAME]          [HR].[DEPARTMENT]   */
/*                                                                    */
/**********************************************************************/

/**********************************************************************/
/* JOIN BETWEEN [HR].[DEPARTMENT] & [HR].[EMPLOYEE]                   */
/* JOIN COLUMN:              [DEPT_ID]                                */
/**********************************************************************/

/**********************************************************************/
/* VIEW NAME: EMP_SALARY_VIEW                                         */
/**********************************************************************/

/**********************************************************************/
/* Column Logical Name       Physical Column Name Physical Table Name */
/*                                                                    */
/* Department ID             [DEPT_ID]            [HR].[DEPARTMENT]   */
/* Department Name           [DEPT_NAME]          [HR].[DEPARTMENT]   */
/* Manager ID                [MGR_ID]             [HR].[DEPARTMENT]   */
/* Employee First Name       [EMP_FNAME]          [HR].[EMPLOYEE]     */
/* Employee Last Name        [EMP_LNAME]          [HR].[EMPLOYEE]     */
/* Hire Date                 [HIRE_DATE]          [HR].[EMPLOYEE]     */
/* Employee Salary           [Salary]             [PAYROLL].[SALARY]  */
/**********************************************************************/

/**********************************************************************/
/* JOIN BETWEEN [HR].[DEPARTMENT] & [HR].[EMPLOYEE]                   */
/* JOIN COLUMN:              [DEPT_ID]                                */
/**********************************************************************/

/**********************************************************************/
/* JOIN BETWEEN [HR].[EMPLOYEE] & [PAYROLL].[SALARY]                  */
/* JOIN COLUMN:              [EMP_ID]                                 */
/**********************************************************************/


/*******************************************/
/* END TOPIC 01 - CREATING VIEWS WITH SSMS */
/*******************************************/

/***************************************/
/* TOPIC 02 - CREATING VIEWS WITH TSQL */
/***************************************/

USE [HUMAN_RESOURCES]
GO

DROP VIEW IF EXISTS [HR].[EMPLOYEE_DEPT_VIEW]
GO

CREATE VIEW [HR].[EMPLOYEE_DEPT_VIEW]
AS
SELECT 
	D.DEPT_ID		AS [Department Id],
	D.DEPT_NAME		AS [Department Name],
	D.MGR_ID		AS [Manager Id], 
	E.EMP_ID		AS [Employee Id], 
	E.EMP_FNAME		AS [Employee First Name],
	E.EMP_LNAME		AS [Employee Last Name]
FROM HR.DEPARTMENT D
	INNER JOIN 
		HR.EMPLOYEE E
			ON D.DEPT_ID = E.DEPT_ID
--ORDER BY D.DEPT_ID
GO

DROP VIEW IF EXISTS [HR].[EMPLOYEE_SALARY_VIEW]
GO

CREATE VIEW [HR].[EMPLOYEE_SALARY_VIEW]
AS
SELECT 
	D.DEPT_ID			AS [Department Id],
	D.[DEPT_NAME]		AS [Department Name],
	E.EMP_ID			AS [Employee Id] ,
	E.EMP_LONG_NAME		AS [Employee Full Name], 
	PS.SALARY			AS [Employee Salary], 
	PS.FROM_DATE		AS [Start Date],
	PS.TO_DATE			AS [End Date] 
FROM [HR].[DEPARTMENT] D
	INNER JOIN
		HR.EMPLOYEE E
			ON D.DEPT_ID = E.DEPT_ID
	INNER JOIN
		[PAYROLL].[SALARY] PS
			ON E.EMP_ID = PS.EMP_ID
GO

SELECT
	[Department Id], [Department Name], [Employee Id], [Employee Full Name], [Employee Salary], [Start Date], [End Date]
FROM [HR].[EMPLOYEE_SALARY_VIEW]
ORDER BY
	[Department Id],[Employee Id]
GO

/*******************************************/
/* END TOPIC 02 - CREATING VIEWS WITH TSQL */
/*******************************************/

/***************************************/
/* TOPIC 03 - DROPPING VIEWS WITH SSMS */
/***************************************/

sp_depends '[HR].[EMPLOYEE_SALARY_VIEW]'
GO

sp_depends '[HR].[EMPLOYEE]'
GO

/*******************************************/
/* END TOPIC 03 - DROPPING VIEWS WITH SSMS */
/*******************************************/

CREATE VIEW [HR].[EMPLOYEE_SALARY_VIEW2]
AS
SELECT * FROM [HR].[EMPLOYEE_SALARY_VIEW]
GO

sp_depends '[HR].[EMPLOYEE_SALARY_VIEW2]'
GO


/***********************************/
/* TOPIC 04 - DROP VIEWS WITH TSQL */
/***********************************/

USE [HUMAN_RESOURCES]
GO

sp_depends '[HR].[EMPLOYEE_DEPT_VIEW]'
GO

DROP VIEW IF EXISTS [HR].[EMPLOYEE_DEPT_VIEW]
GO

-- alternate syntax

DROP VIEW [HR].[EMP_SALARY_VIEW]
GO

sp_depends '[HR].[EMP_DEPT_VIEW]'
GO

DROP VIEW IF EXISTS [HR].[EMP_DEPT_VIEW]
GO

DROP VIEW [HR].[EMPLOYEE_SALARY_VIEW]
GO

/**************************************/
/* END TOPIC 04 - DROP VIEW WITH TSQL */
/**************************************/