/*******************************/ /* 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 */ /**************************************/