/************************************
Chapter 3 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
CREATE DATABASE Chapter3; 
GO
USE Chapter3;
GO
CREATE TABLE dbo.Employee
(
	EmployeeID int IDENTITY(100,1) NOT NULL, 
	LastName varchar(35) NULL, 
	FirstName varchar(35) NULL, 
    	CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED 
	(
		EmployeeID
	 )
)

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

USE Chapter3;
GO
CREATE TABLE dbo.Address
(
	AddressID int IDENTITY(1,1) NOT NULL, 
	AddressLine1 varchar(35) NULL, 
	AddressLine2 varchar(35) NULL, 
	City varchar(35) NULL, 
	State char(2) NULL, 
	Zip varchar(10) NULL,
    	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
	(
		AddressID
	 )
) 

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

USE Chapter3;
GO
Alter TABLE dbo.Employee
	ADD AddressID int NULL
	CONSTRAINT FK_Employee_Address FOREIGN KEY(AddressID)
		REFERENCES dbo.Address(AddressID)

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

USE Chapter3;
Go
Alter TABLE dbo.Employee
	Drop Constraint FK_Employee_Address;
GO
Alter TABLE dbo.Employee
	ADD CONSTRAINT FK_Employee_Address FOREIGN KEY(AddressID)
		REFERENCES dbo.Address(AddressID)
		ON DELETE CASCADE

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

USE Master
CREATE DATABASE Chapter3;

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

CREATE TABLE dbo.Employee
(
	EmployeeID int IDENTITY(100,1) NOT NULL, 
	LastName varchar(35) NULL, 
	FirstName varchar(35) NULL, 
    	CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED 
	(
		EmployeeID
	 )
);

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

CREATE TABLE dbo.Address
(
	AddressID int IDENTITY(1,1) NOT NULL, 
	AddressLine1 varchar(35) NULL, 
	AddressLine2 varchar(35) NULL, 
	City varchar(35) NULL, 
	State char(2) NULL, 
	Zip varchar(10) NULL,
    	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
	(
		AddressID
	 )
);

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

Alter TABLE dbo.Employee
	ADD AddressID int NULL
	CONSTRAINT FK_Employee_Address FOREIGN KEY(AddressID)
		REFERENCES dbo.Address(AddressID);

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

CREATE TABLE dbo.State
(
	State char(2) NOT NULL, 
    CONSTRAINT [PK_State_State] PRIMARY KEY CLUSTERED 
	(
		State
	 )
);

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

INSERT INTO dbo.State VALUES('VA')
INSERT INTO dbo.State VALUES('NY')
INSERT INTO dbo.State VALUES('MA')
INSERT INTO dbo.State VALUES('NV')
INSERT INTO dbo.State VALUES('HI')
INSERT INTO dbo.State VALUES('CA')

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

Alter TABLE dbo.Address
	ADD CONSTRAINT FK_Address_State FOREIGN KEY(State)
		REFERENCES dbo.State(State);

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

INSERT INTO dbo.Address(AddressLine1, City, State, Zip)
VALUES ('565 Untraveled Path','Mossey','VA','23462')

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

INSERT INTO dbo.Address(AddressLine1, City, State, Zip)
VALUES ('30 Degree Dr.','Frostproof','FF','73462')

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

SELECT * FROM Address

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

USE Chapter3;
GO
ALTER TABLE dbo.Employee
	ADD Phone char(13) NULL

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

USE Chapter3;
GO
ALTER TABLE Employee
	ADD CONSTRAINT ckPhone
	CHECK
	(Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

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

INSERT into Employee (LastName,FirstName,Phone)
VALUES('Marx','Hi','(757)123-4567')

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

SELECT * FROM Employee

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

INSERT into Employee (LastName,FirstName,Phone)
VALUES('Wright','Eilene','934.123.4567')

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

SELECT * FROM Employee

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

USE Chapter3;
GO
ALTER TABLE Employee 
    	ADD HireDate smalldatetime NULL,
	BirthDate datetime NULL;

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

USE Chapter3;
GO
ALTER TABLE Employee WITH NOCHECK
	ADD CONSTRAINT Ck_HireDate CHECK 
   (HireDate  < GETDATE() OR HireDate = GetDate() );

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

USE Chapter3;
GO
ALTER TABLE Employee 
	ADD CONSTRAINT Ck_BirthDate CHECK
	 (BirthDate > DATEADD(yyyy, -120, GETDATE() ) )

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

USE Chapter3;
GO
ALTER TABLE Employee
	Add Salary money NULL 

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

USE Chapter3;
GO
ALTER TABLE Employee WITH NOCHECK
	ADD CONSTRAINT Ck_Salary CHECK 
	 (Salary >= 20000 AND Salary <= 200000)

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

ALTER TABLE Driver 
ADD DriverLicenceNumber VARCHAR(20) NULL 
   CONSTRAINT DLN_unique UNIQUE;

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

USE master;
GO
ALTER DATABASE AdventureWorks ADD FILEGROUP Ch3FG1;
ALTER DATABASE AdventureWorks ADD FILEGROUP Ch3FG2;
ALTER DATABASE AdventureWorks ADD FILEGROUP Ch3FG3;

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

USE master;
GO
ALTER DATABASE AdventureWorks
ADD FILE (NAME = 'Ch3F1',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Ch3F1.ndf',
	SIZE = 1024KB, FILEGROWTH = 1024KB) 
		TO FILEGROUP Ch3FG1
ALTER DATABASE AdventureWorks
ADD FILE (NAME = 'Ch3F2',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Ch3F2.ndf',
	SIZE = 1024KB, FILEGROWTH = 1024KB) 
		TO FILEGROUP Ch3FG2
ALTER DATABASE AdventureWorks
ADD FILE (NAME = 'Ch3F3',
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Ch3F3.ndf',
	SIZE = 1024KB, FILEGROWTH = 1024KB) 
		TO FILEGROUP Ch3FG3

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

USE AdventureWorks;
GO
CREATE PARTITION FUNCTION pf_OrdersByDate (DateTime)
As RANGE RIGHT
For VALUES ('1/1/2001', '1/1/2002')

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

USE AdventureWorks;
GO
CREATE PARTITION SCHEME ps_OrdersByDate
AS PARTITION pf_OrdersByDate
TO (Ch3FG1, Ch3FG2,Ch3FG3)

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

USE AdventureWorks;
GO
CREATE TABLE dbo.SalesOrderHeaderPart(
	SalesOrderID int NOT NULL,
	OrderDate datetime not NULL, 
	DueDate datetime not NULL
) ON ps_OrdersByDate(OrderDate)

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

USE AdventureWorks;
GO
Insert dbo.SalesOrderHeaderPart
Select SalesOrderID,OrderDate,DueDate
FROM Sales.SalesOrderHeader

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

Select * from dbo.SalesOrderHeaderPart

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

SELECT * FROM dbo.SalesOrderHeaderPart
WHERE $partition.pf_OrdersByDate (OrderDate) = 1 

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

SELECT *FROM dbo.SalesOrderHeaderPart
WHERE $partition.pf_OrdersByDate (OrderDate) = 2

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

SELECT *FROM dbo.SalesOrderHeaderPart
WHERE $partition.pf_OrdersByDate (OrderDate) = 3

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

--Insert a row into partition 1
Insert dbo.SalesOrderHeaderPart (SalesOrderID, OrderDate, DueDate)
VALUES (99000, '1/1/1995', '1/1/1995')
--Insert a row into partition 2
Insert dbo.SalesOrderHeaderPart (SalesOrderID, OrderDate, DueDate)
VALUES (99000, '1/1/2001', '1/1/2001')
--Insert a row into partition 3
Insert dbo.SalesOrderHeaderPart (SalesOrderID, OrderDate, DueDate)
VALUES (99000, '1/1/2006', '1/1/2006')

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

--Will now show 1 row
SELECT *FROM dbo.SalesOrderHeaderPart
WHERE $partition.pf_OrdersByDate (OrderDate) = 1
--Will now show 1380 rows
SELECT *FROM dbo.SalesOrderHeaderPart
WHERE $partition.pf_OrdersByDate (OrderDate) = 2
--Will now show 30,087 rows
SELECT *FROM dbo.SalesOrderHeaderPart
WHERE $partition.pf_OrdersByDate (OrderDate) = 3

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

USE Master;
GO
CREATE DATABASE Tester;
GO
USE Tester
GO
CREATE TABLE dbo.Roles
(
	RoleID int NOT NULL, 
	RoleName varchar(15) NULL, 
    CONSTRAINT PK_RoleID PRIMARY KEY (RoleID)
)
GO
CREATE TABLE dbo.Person
(
	PersonID int NOT NULL, 
	LastName varchar(15) NULL, 
	FirstName varchar(15) NULL, 
	RoleID int NULL 
    CONSTRAINT PK_PersonID PRIMARY KEY (PersonID)
)
GO
CREATE TABLE dbo.PersonRoles
(
	PersonID int NOT NULL references dbo.Person, 
	RoleID int NOT NULL references dbo.Roles, 
    CONSTRAINT PK_PersonIDRoleID PRIMARY KEY (PersonID, RoleID)
)
GO
CREATE TABLE dbo.Category
(
	CategoryID int NOT NULL, 
	Category varchar(30) NULL, 
    CONSTRAINT PK_CategoryID PRIMARY KEY (CategoryID)
)
GO
CREATE TABLE dbo.Tests
(
	TestID int NOT NULL, 
	TestName varchar(30) NULL, 
	CategoryID int NULL references dbo.Category 
    CONSTRAINT PK_TestID PRIMARY KEY (TestID)
)
GO
CREATE TABLE dbo.Question
(
	QuestionID int NOT NULL, 
	QuestionText varchar(200) NULL, 
	Explanation varchar(300) NULL, 
	Reference varchar(200) NULL, 
	TestID int NULL references dbo.Tests
    CONSTRAINT PK_QuestionID PRIMARY KEY (QuestionID)
)
GO
CREATE TABLE dbo.Answer
(
	AnswerID int NOT NULL, 
	AnswerText varchar(100) NULL, 
	CorrectAnswer bit NULL,  
	QuestionID int NOT NULL references dbo.Question
    CONSTRAINT PK_AnswerID PRIMARY KEY (AnswerID)
)
GO
CREATE TABLE dbo.TestHistory
(
	TestHistoryID int NOT NULL, 
	PersonID int NULL references dbo.Person,
	TestID int NULL references dbo.Tests,
	DateTaken datetime NULL, 
	Score decimal(4,2) NULL
    CONSTRAINT PK_TestHistoryID PRIMARY KEY (TestHistoryID)
)
GO

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