/************************************
Chapter 2 Scripts
For MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

*************************************/

Use Master
Create Database Employee

/************************************/ 

-- Note: Zip Code data type created 
-- manually within this chapter

USE [Employee]
GO
CREATE TABLE [dbo].[EmployeesTSQL](
	[EmpID] [int] NOT NULL,
	[LName] [varchar](50) NOT NULL,
	[FName] [varchar](50) NULL,
	[HireDate] [smalldatetime] NULL,
	[BDay] [smalldatetime] NULL,
	[SSN] [varchar](11) NULL,
	[Street] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[State] [char](2) NULL,
	[Zip] [dbo].[ZipCode] NULL,
	[Phone] [char](13) NULL,
	[Department] [varchar](50) NULL,
	[Salary] [money] NULL,
	[Picture] [varchar](max) NULL,
	[Resume] [varchar](max) NULL,
	[ProjCompleted] [xml] NULL
) 


/*************************************/

-- Note: Employees table created manually 
-- within this chapter

Use Employee
Go
INSERT into Employees (EmpID,LName,FName,HireDate,Phone,Salary)
VALUES(1,'Gibson','Darril','Sep 1, 1968', '(757)555-5555',100000)
INSERT into Employees (EmpID,LName,FName,HireDate,Phone,Salary)
VALUES (2,'Grzena','Dawn','Sep 1, 1978', '(757)555-4444',100000)


/*************************************/ 

-- Note: vw_EmpPhone View created manually 
-- within this chapter

USE Employee
Select * FROM vw_EmpPhone

/*************************************/

-- Note: Employees table created manually 
-- within this chapter

USE Employee
GO
CREATE VIEW dbo.vw_EmpSalary
AS
SELECT     TOP (100) PERCENT EmpID, LName, FName, Salary
FROM         dbo.Employees
ORDER BY LName

/*************************************/

Select * FROM vw_EmpSalary

/*************************************/

USE Employee;
GO
EXEC sp_helptext 'dbo.vw_EmpSalary'
GO

/*************************************/

-- Note: Employees table created manually 
-- within this chapter

CREATE VIEW dbo.vw_ITEmpSalary
AS
SELECT     LName, FName, Salary
FROM         dbo.Employees
WHERE Department = 'IT'


/*************************************/

USE Employee;
GO
Select * FROM dbo.vw_EmpPhone

/*************************************/