/******************************************************
 *
 * Name:         exploring-views-part1.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     01-06-2013
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  Create a series of articles on
 *               database developer (70-433) topics.
 *
 *     Topic:    Talk about views - part 1.
 *
 ******************************************************/

-- Use the correct database
USE [AdventureWorks2012]
GO

-- Delete existing view
IF  EXISTS (
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[HumanResources].[vHireDate]') AND [type] in (N'V')
)
DROP VIEW [HumanResources].[vHireDate]
GO


-- Create stub view (abstracting columns)
CREATE VIEW [HumanResources].[vHireDate] (Eenie, Meenie, Miney, Mo)
AS
    SELECT 1 AS Fee, 2 AS Fi, '3' AS Fo, '4' AS Fumb
GO

-- Show the funny data
SELECT * FROM [HumanResources].[vHireDate]
GO

-- Alter view to msdn example
ALTER VIEW [HumanResources].[vHireDate] 
AS 
    SELECT 
        p.FirstName
      , p.LastName
      , e.BusinessEntityID
      , e.HireDate
    FROM 
        HumanResources.Employee e 
        JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID;
GO

-- Example use of the view
SELECT * 
FROM [HumanResources].[vHireDate] 
WHERE FirstName like 'J%'
ORDER BY FirstName
GO