/************************************
Chapter 2 Scripts
For MCITP SQL Server 2005 Database Developer 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.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

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

USE Master;
GO
CREATE DATABASE Chapter2;

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

CREATE TYPE Zip
FROM varchar(10) NOT NULL ;

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

CREATE TABLE Employees(
	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
)

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

USE [Chapter2]
GO
CREATE TABLE [dbo].[EmployeesTSQL](
	[EmpID] [int] NOT NULL,
	[LName] [nvarchar](50) NOT NULL,
	[FName] [nvarchar](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
) 


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

Use Chapter2
Go
INSERT into Employees (EmpId,LName,FName,HireDate,Phone,Salary)
VALUES(1,'Gibson','Duane','Sep 9, 1964', '(757)555-5555',100000)
INSERT into Employees (EmpId,LName,FName,HireDate,Phone,Salary)
VALUES (2,'Villegas','Nimfa','Mar 31, 1962', '(757)555-4444',100000)
INSERT into EmployeesTSQL (EmpId,LName,FName,HireDate,Phone,Salary)
VALUES(1,'Gibson','Duane','Sep 9, 1964', '(757)555-5555',100000)
INSERT into Employees (EmpId,LName,FName,HireDate,Phone,Salary)
VALUES (2,'Villegas','Nimfa','Mar 31, 1962', '(757)555-4444',100000)

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

USE Chapter2;
GO
CREATE TABLE dbo.Address
(
	AddressLine1 varchar(35) NULL, 
	AddressLine2 varchar(35) NULL, 
	City varchar(35) NULL, 
	State char(2) NULL
		Constraint State_CA_def
		DEFAULT 'CA', 
	Zip varchar(10) NULL,
) 

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

USE Chapter2;
GO
ALTER TABLE Address 
    Add Country VARCHAR(50) NOT NULL;


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

USE Chapter2;
GO
ALTER TABLE Address 
    Add Country VARCHAR(50) NULL;

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

USE Chapter2;
GO
Alter TABLE Address
Drop Column Country;

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

USE Chapter2;
GO
ALTER TABLE Address 
    Add Country VARCHAR(50) NOT NULL
	CONSTRAINT Country_USA_def
	DEFAULT 'USA';

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

USE Chapter2;
GO
CREATE TABLE ComputedColumnTest
(
      QuantityOnHand int NULL,
	Cost money NULL,
	InventoryCost AS (QuantityOnHand * Cost)
);

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

USE Chapter2;
GO
CREATE TABLE ComputedColumnTestPersisted
(
	ComputedColumnTestID int NOT NULL,
      QuantityOnHand int NULL,
	Cost money NULL,
	InventoryCost AS (QuantityOnHand * Cost) PERSISTED,
CONSTRAINT PK_ComputedColumnTest_ComputedColumnTestID
PRIMARY KEY CLUSTERED (ComputedColumnTestID)
);

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

Use EmployeesTSQL;
Go
ALTER TABLE EmployeesTSQL
ADD FNameSpanish AS (FName)
COLLATE Modern_Spanish_CI_AS;
ALTER TABLE EmployeesTSQL
ADD LNameSpanish AS (LName)
COLLATE Modern_Spanish_CI_A;

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

USE EmployeesTSQL;
Go
INSERT into EmployeesTSQL (EmpId,LName,FName,HireDate,Phone,Salary)
VALUES(1,'Gibson','Duane','Sep 9, 1964', '(757)555-5555',100000);
INSERT into EmployeesTSQL (EmpId,LName,FName,HireDate,Phone,Salary)
VALUES (2,'Villegas','Nimfa','Mar 31, 1962', '(757)555-4444',100000);
GO
SELECT FNameSpanish, LNameSpanish
FROM EmployeesTSQL;

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

USE Chapter2;
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 Chapter2;
GO
EXEC sp_helptext 'dbo.vw_EmpSalary ';

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

USE AdventureWorks;
GO
CREATE VIEW dbo.vw_Employee_details 
AS 
SELECT 
    e.EmployeeID
    ,c.LastName
    ,c.MiddleName
    ,c.FirstName
    ,e.Title AS [Job Title] 
    ,c.Phone
    ,c.EmailAddress
    ,a.AddressLine1
    ,a.AddressLine2
    ,a.City
    ,sp.Name AS [State or Province]
    ,a.PostalCode
    ,cr.[Name] AS [CountryRegionName] 
FROM HumanResources.Employee AS e
    INNER JOIN Person.Contact AS c 
    ON c.ContactID = e.ContactID
    INNER JOIN HumanResources.EmployeeAddress AS ea 
    ON e.EmployeeID = ea.EmployeeID 
    INNER JOIN Person.Address AS a 
    ON ea.AddressID = a.AddressID
    INNER JOIN Person.StateProvince AS sp 
    ON sp.StateProvinceID = a.StateProvinceID
    INNER JOIN Person.CountryRegion AS cr 
    ON cr.CountryRegionCode = sp.CountryRegionCode;

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

SELECT * 
FROM vw_EmpSalary;

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

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

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

Select * from Chapter2.dbo.vw_EmpPhone

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

USE Chapter2;
GO
Select * from dbo.vw_EmpPhone

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