/************************************
Chapter 6 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 AdventureWorks;
GO
SELECT AVG(ListPrice)
FROM Production.Product;

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

Use AdventureWorks;
GO
SELECT AVG(ListPrice)
FROM Production.Product
WHERE ListPrice > 0;

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

USE AdventureWorks;
GO
SELECT MAX(ListPrice) AS 'Maximum List Price'
FROM Production.Product;

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

SELECT MIN(ListPrice) AS 'Minimum List Price'
FROM Production.Product;

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

USE AdventureWorks;
GO
SELECT Count(*)
FROM HumanResources.Employee; 

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

SELECT SalesPersonID, COUNT(SalesOrderID)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID

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

SELECT COUNT(Color)
FROM Production.Product;

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

SELECT LastName, FirstName,
       COALESCE(CellPhone,HomePhone) as Phone 
FROM Employee
ORDER By LastName;

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

--Return today's date
SELECT GETDATE() AS Today
-- Returns the month (as an integer) for today's date
SELECT MONTH(GETDATE()) As [Month Integer]
-- Returns today's date plus 30 days
SELECT DATEADD(day, 30, GETDATE()) AS [Add 30 Days to today]
-- Returns the month (as an integer) for today's date
SELECT DATEPART(month, GETDATE()) AS [Month as Integer]
-- Returns the month (as a string) for today's date
SELECT DATENAME(month, GETDATE()) AS [Month as Name]
-- Returns the weekday name (as a string) for today's date
SELECT DATENAME(weekday, GETDATE()) AS [WeekDay Name]

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

SELECT FirstName, LastName, Phone
FROM Person.Contact
Where UPPER(FirstName) = UPPER('kim');

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

INSERT INTO Person.Contact
(FirstName, LastName,PasswordHash, PasswordSalt)
VALUES ('   Harry', '   Potter', 'P@ssw0rd','P@$$S@lt');

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

SELECT * from Person.Contact
WHERE FirstName = 'Harry'

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

SELECT * from Person.Contact
WHERE LTRIM(FirstName) = 'Harry';

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

SELECT Max(LEN(Name)) 
FROM Production.Product

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

CREATE Database Chapter6;
GO
USE Chapter6;
GO
CREATE TABLE dbo.AuditAccess
(
	AuditAccessID int IDENTITY(1,1) NOT NULL, 
	UserName varchar(50) NOT NULL, 
	LoginTime datetime NULL,
    	CONSTRAINT [PK_AuditAccess_AuditAccessID] PRIMARY KEY CLUSTERED 
	(
		AuditAccessID
	 )
);

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

SELECT SUSER_SNAME()

/************************************/
INSERT INTO AuditAccess VALUES('Bozo','1-5-2008');
INSERT INTO AuditAccess VALUES('Krusty','1-6-2008');
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),'1-7-2008');
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),GETDATE());
GO
SELECT * FROM AuditAccess;

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

Declare @start datetime, @end datetime
Set @start = GetDate()

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

Set @end = GetDate()
Print 'Code took ' + Str((DATEDIFF(ms ,@start,@end) ), 3,0) + ' milliseconds to run.'

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

USE AdventureWorks;
GO
SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate = '20040701'

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

USE Chapter6;
GO
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),GETDATE());

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

SELECT * FROM AuditAccess
WHERE LoginTime = GetDate()

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

Declare @start datetime, @end datetime
Set @start = GetDate()
SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20040701' AND
OrderDate < '20040702'
Set @end = GetDate()
Print 'Code took ' + Str((DATEDIFF(ms ,@start,@end) ), 3,0) + ' milliseconds to run.'

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

Declare @start datetime, @end datetime
Set @start = GetDate()
SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE CONVERT(CHAR(8), OrderDate, 112)
= '20040701'
Set @end = GetDate()
Print 'Code took ' + Str((DATEDIFF(ms ,@start,@end) ), 3,0) + ' milliseconds to run.'

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

Declare @start datetime, @end datetime
Set @start = GetDate()
SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2004 AND
MONTH(OrderDate) = 07 AND DAY(OrderDate) = 01
Set @end = GetDate()
Print 'Code took ' + Str((DATEDIFF(ms ,@start,@end) ), 3,0) + ' milliseconds to run.'

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

Declare @start datetime, @end datetime
Set @start = GetDate()
SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE DATEDIFF(day, OrderDate, '20040701') = 0
Set @end = GetDate()
Print 'Code took ' + Str((DATEDIFF(ms ,@start,@end) ), 3,0) + ' milliseconds to run.'

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

SELECT YEAR('01/01/2008')

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

--Create the table
USE Chapter6;
GO
CREATE TABLE dbo.ZipState
(
	ZipStateID int IDENTITY(1,1) NOT NULL, 
	ZipCode int NOT NULL,
	State char(2) NOT NULL, 
    	CONSTRAINT [PK_ZipState_ZipStateID] PRIMARY KEY CLUSTERED 
	(
		ZipStateID
	 )
);
--Populate the table
INSERT INTO ZipState (ZipCode, State) VALUES(23462,'VA');
INSERT INTO ZipState (ZipCode, State) VALUES(94562,'CA');
INSERT INTO ZipState (ZipCode, State) VALUES(74562,'OK');
INSERT INTO ZipState (ZipCode, State) VALUES(54562,'WI');

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

SELECT fn_GetState(zip) as State;

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

USE Chapter6;
GO
CREATE FUNCTION dbo.fn_GetState(@zip int)
RETURNS char(2)
AS 
BEGIN
  DECLARE @State char(2)
  SELECT @State = State
  FROM ZipState
  WHERE ZipCode = @zip
  Return @State
END;

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

SELECT dbo.fn_GetState(23462) as State;

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

SELECT dbo.fn_GetState(11111) as State;

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

USE Chapter6;
GO
ALTER FUNCTION dbo.fn_GetState(@zip int)
RETURNS char(2)
AS 
BEGIN
  DECLARE @State char(2)
  SELECT @State = State
  FROM ZipState
  WHERE ZipCode = @zip
  IF (@State IS NULL)
      	SET @State = 'NA'
  Return @State
END;

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

USE Chapter6;
GO
SELECT dbo.fn_GetState(11111);

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

USE Chapter6;
GO
INSERT INTO AuditAccess VALUES('Harry','07-02-2008');
INSERT INTO AuditAccess VALUES('Ron','07-03-2008');
INSERT INTO AuditAccess VALUES('Hermione','07-05-2008');
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),GetDate());
SELECT * FROM AuditAccess;

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

USE Chapter6;
GO
CREATE FUNCTION dbo.fn_CountLogins(@UserName varchar(50))
RETURNS int
AS 
BEGIN
  Declare @Logins int
  SELECT @Logins = COUNT(*) FROM AuditAccess
     WHERE UserName = @UserName 
  Return @Logins
END;

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

DECLARE @UserName varchar(50);
SET @UserName = SUSER_SNAME();
SELECT dbo.fn_CountLogins(@UserName) AS UserName;
SELECT dbo.fn_CountLogins('Harry') as 'Harry' 

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

USE Chapter6;
ALTER TABLE dbo.ZipState 
ADD CreationDate datetime NULL;

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

UPDATE z 
SET CreationDate = GetDate()
FROM ZipState as z INNER JOIN inserted AS i
ON i.ZipStateID = z.ZipStateID;

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

USE Chapter6;
GO
CREATE TRIGGER trgRecordCreationDate
ON ZipState
AFTER INSERT
AS 
UPDATE z 
SET CreationDate = GetDate()
FROM ZipState as z INNER JOIN inserted AS i
ON i.ZipStateID = z.ZipStateID;

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

INSERT INTO ZipState (ZipCode, State) VALUES(23752,'VA');

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

SELECT * FROM ZipState;

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

USE Chapter6;
GO
CREATE TRIGGER trgPreventDeletion
ON AuditAccess
AFTER DELETE
AS 
RAISERROR ('Audit access entries cannot be deleted', 16, 10) WITH LOG
ROLLBACK TRANSACTION;

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

USE Chapter6;
SELECT * FROM AuditAccess;

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

DELETE FROM AuditAccess
WHERE UserName = 'Krusty'

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

USE AdventureWorks;
GO
CREATE TRIGGER Production.trgPriceControl
ON Production.Product
AFTER UPDATE
AS 
IF (SELECT COUNT(*) FROM inserted) > 1
Begin
      RAISERROR ('No more than one product can be updated at a time.', 16, 10) WITH LOG
	ROLLBACK TRAN
    RETURN  -- Exit script
End
DECLARE @NewPrice decimal
DECLARE @OldPrice decimal
SET @NewPrice = (SELECT ListPrice from inserted)
SET @OldPrice = (SELECT ListPrice from deleted)
IF @NewPrice < (@OldPrice * .9)	-- more than 10% reduction
Begin	
	RAISERROR ('Unauthorized price change attempted.', 16, 10) WITH LOG
    ROLLBACK TRAN
End

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

SELECT * FROM Production.Product
WHERE ListPrice > 0

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

BEGIN TRAN
UPDATE Production.Product
SET ListPrice = .01
SELECT * FROM Production.Product
WHERE ListPrice > 0

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

Begin Tran
UPDATE Production.Product
SET ListPrice = .01
WHERE ProductID = 514

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

USE AdventureWorks;
GO
ALTER TABLE HumanResources.JobCandidate
Add Inactive bit NULL

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

USE AdventureWorks;
GO
CREATE TRIGGER trgPreventDeleteJobCandidate
ON HumanResources.JobCandidate
INSTEAD OF DELETE
AS 
UPDATE HumanResources.JobCandidate
 SET Inactive = 1
 FROM HumanResources.JobCandidate as c INNER JOIN deleted AS d
 ON c.JobCandidateID = d.JobCandidateID;
RAISERROR ('Job Candidates cannot be deleted. Candidate marked as inactive instead.', 16, 10) WITH LOG

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

INSERT INTO HumanResources.JobCandidate
           (EmployeeID, ModifiedDate ,Inactive)
VALUES     (1, GetDate(), 1)
GO
SELECT * FROM HumanResources.JobCandidate

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

DELETE FROM HumanResources.JobCandidate 
WHERE JobCandidateID = 14;
GO
SELECT * FROM HumanResources.JobCandidate

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

USE Chapter6;
CREATE TABLE dbo.ArchiveAuditAccess
(
	ArchiveAuditAccessID int NOT NULL, 
	UserName varchar(50) NOT NULL, 
	LoginTime datetime NULL,
	InactiveStatus bit NULL
       CONSTRAINT [DF_Inactive] DEFAULT 1
);

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

ALTER TABLE dbo.AuditAccess
ADD InactiveStatus bit NULL;

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

SELECT * FROM ArchiveAuditAccess
SELECT * FROM AuditAccess

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

CREATE TRIGGER trgArchiveInactiveUsers
ON AuditAccess
AFTER UPDATE
AS 
IF (SELECT COUNT(*) FROM inserted) > 1
Begin
    RAISERROR ('Only one user can be modified at a time.', 16, 10) WITH LOG
    ROLLBACK TRAN
    RETURN
End

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

UPDATE AuditAccess
SET InactiveStatus = 0

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

ALTER TRIGGER trgArchiveInactiveUsers
ON AuditAccess AFTER UPDATE
AS 
IF (SELECT COUNT(*) FROM inserted) > 1
Begin
    RAISERROR ('Only one user can be modified at a time.', 16, 10) WITH LOG
    ROLLBACK TRAN
    RETURN
End
IF (SELECT InactiveStatus FROM inserted) = 1
Begin
    DECLARE @UserName varchar (20)
    SET @UserName = (SELECT UserName FROM inserted)
    -- Add the rows to the Archive table
   	INSERT INTO ArchiveAuditAccess
      SELECT * FROM AuditAccess
      WHERE UserName = @UserName
    -- Delete the rows from the source table
    DELETE FROM AuditAccess
      WHERE UserName = @UserName
    RAISERROR ('User has been archived', 16, 10)
End

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

UPDATE AuditAccess
SET InactiveStatus = 1
WHERE AuditAccessID = 1

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

SELECT * FROM ArchiveAuditAccess
SELECT * FROM AuditAccess

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

CREATE VIEW vwStockQuotes AS
SELECT Symbol, Date, High, Low, ClosingPrice, SharesSold
FROM Stocks

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

CREATE VIEW vwStockQuotes AS
SELECT Symbol, Date, High, Low, ClosingPrice, SharesSold
FROM StocksAL
UNION
SELECT Symbol, Date, High, Low, ClosingPrice, SharesSold
FROM StocksKZ

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

USE Chapter6;
CREATE TABLE dbo.State
(
	StateID char(2) NOT NULL, 
    	CONSTRAINT [PK_State_StateID] PRIMARY KEY CLUSTERED 
	(
		StateID
	 )
);
GO
--Populate the table
INSERT INTO State VALUES('VA');
INSERT INTO State VALUES('CA');
INSERT INTO State VALUES('OK');
INSERT INTO State VALUES('WI');

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

CREATE TABLE dbo.ZipState1To5(
	ZipState1To5ID int IDENTITY(1,1) NOT NULL, 
	ZipCode int NOT NULL,
	State char(2) NOT NULL, 
        CONSTRAINT FK_State_State1To5 FOREIGN KEY(State)
		REFERENCES dbo.State(StateID), 
    	CONSTRAINT [PK_ZipState_ZipState1To5ID] PRIMARY KEY CLUSTERED 
	(
		ZipState1To5ID
	 )
);
GO
CREATE TABLE dbo.ZipState6To9(
	ZipState6To9ID int IDENTITY(1,1) NOT NULL, 
	ZipCode int NOT NULL,
	State char(2) NOT NULL, 
        CONSTRAINT FK_State_State6To9 FOREIGN KEY(State)
		REFERENCES dbo.State(StateID), 
    	CONSTRAINT [PK_ZipState_ZipState6To9ID] PRIMARY KEY CLUSTERED 
	(
		ZipState6To9ID
	 )
);
--Populate the tables
INSERT INTO ZipState1To5 (ZipCode, State) VALUES(23462,'VA'); 
INSERT INTO ZipState1To5 (ZipCode, State) VALUES(44562,'WI');
INSERT INTO ZipState6To9 (ZipCode, State) VALUES(94562,'CA');
INSERT INTO ZipState6To9 (ZipCode, State) VALUES(74562,'OK');

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

CREATE VIEW vwZips AS
   SELECT ZipState1To5ID AS ZipStateID, ZipCode, State
   FROM ZipState1To5
UNION
   SELECT ZipStateAfter5IDZipState6To9ID, ZipCode, State
   FROM ZipStateAfter5ZipState6To9

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

SELECT * FROM vwZips

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

UPDATE vwZips
SET State = 'VA'
WHERE ZipCode = 44562

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

CREATE TRIGGER trgUpdateVwZips
ON vwZips
INSTEAD OF UPDATE
AS 
DECLARE @Zip int
DECLARE @State char(2)
--Get the zip and state from the inserted table
Set @Zip = (SELECT ZipCode FROM inserted)
Set @State = (SELECT State FROM inserted)
If @Zip < 60000 
   --ZipCode is in the ZipState1To5 table
	UPDATE ZipState1To5
		SET State = @State
		WHERE ZipCode = @Zip
ELSE
   --ZipCode is in the ZipState6To9 table
	UPDATE ZipState6To9
		SET State = @State
		WHERE ZipCode = @Zip

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

SELECT * FROM vwZips;
UPDATE vwZips
SET State = 'WI'
WHERE ZipCode = 44562;
SELECT * FROM vwZips

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

USE Chapter6;
CREATE TABLE dbo.State
(
	StateID char(2) NOT NULL, 
    	CONSTRAINT [PK_State_StateID] PRIMARY KEY CLUSTERED 
	(
		StateID
	 )
);
GO
--Populate the table
INSERT INTO State VALUES('VA');
INSERT INTO State VALUES('CA');
INSERT INTO State VALUES('OK');
INSERT INTO State VALUES('WI');

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

DROP TABLE dbo.ZipState 

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

CREATE TABLE dbo.ZipState(
	ZipStateID int NOT NULL,
	ZipCode int NOT NULL,
	State char(2) NOT NULL,
        CONSTRAINT FK_State_State FOREIGN KEY(State)
		REFERENCES dbo.State(StateID),
    	CONSTRAINT [PK_ZipState_ZipStateID] PRIMARY KEY CLUSTERED
	(
		ZipStateID	 )
);
GO
--Populate the table
INSERT INTO ZipState (ZipStateID, ZipCode, State) 
               VALUES(1,23462,'VA');
INSERT INTO ZipState (ZipStateID, ZipCode, State)
               VALUES(2,94562,'CA');
INSERT INTO ZipState (ZipStateID, ZipCode, State) 
               VALUES(3, 74562,'OK');
INSERT INTO ZipState (ZipStateID, ZipCode, State) 
               VALUES(4, 54562,'WI');

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

INSERT INTO ZipState (ZipCode, State) VALUES(54562,'MM');

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

CREATE TABLE dbo.ZipStateError(
	ZipStateErrorID int IDENTITY(1,1) NOT NULL, 
	ZipCode int NOT NULL,
	State char(2) NOT NULL,  
    	CONSTRAINT [PK_ZipState_ZipStateErrorID] PRIMARY KEY CLUSTERED 
	(
		ZipStateErrorID
	 )
);

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

CREATE TRIGGER trgZipStateError
ON ZipState
INSTEAD OF INSERT
AS 
DECLARE @State char(2)
DECLARE @Count int
SET @State = (SELECT State FROM inserted)
SET @Count = (SELECT Count(StateID) FROM State WHERE StateID = @State)
IF @Count = 0
-- Insert the data into the error table
BEGIN
   INSERT INTO ZipStateError
     SELECT ZipCode, State From inserted 
   RAISERROR ('ZipStateError entry added ', 16, 10) WITH LOG
END
ELSE
-- Insert the data into the regular table
	INSERT INTO ZipState SELECT ZipCode,State, GetDate() FROM inserted

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

SELECT * FROM ZipState
SELECT * FROM ZipStateError

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

INSERT INTO ZipState (ZipCode, State) VALUES(54562,'MM');

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

SELECT * FROM ZipState;
SELECT * FROM ZipStateError;

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

USE Chapter6;
GO
CREATE TABLE DatabaseAudit
(
	AuditID int IDENTITY(100,1) NOT NULL, 
	AuditEvent varchar(max) NULL, 
    	CONSTRAINT [PK_DatabaesAudit_AuditID2] PRIMARY KEY CLUSTERED 
	(
		AuditID
	 )
)

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

Use Chapter6;
GO
CREATE TRIGGER trig_All_ddl_database
ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
    PRINT 'Database DDL Trigger Fired.'
    INSERT INTO DatabaseAudit (AuditEvent)
    VALUES (EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))

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

USE Chapter6;
GO
CREATE TABLE TestTable
(
	TestID int IDENTITY(100,1) NOT NULL, 
	TestData varchar(max) NULL, 
);
GO
SELECT * FROM DatabaseAudit;

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

USE Chapter6;
GO
CREATE TRIGGER NoTableChanges
ON DATABASE 
FOR ALTER_TABLE 
AS 
   RAISERROR ('Tables cannot be altered in this database.', 16, 1) 
   ROLLBACK;

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

ALTER TABLE dbo.ZipState 
ADD NewColumn varChar(20) NULL;

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

CREATE TRIGGER ddl_trig_Create_DB 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    DECLARE @Statement nvarchar(max)
    Set @Statement = 'Database created with this statement: '
    Set @Statement = @Statement + (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) 
	SELECT @Statement
	RAISERROR (@Statement, 16, 10) WITH LOG

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

CREATE DATABASE DeleteMe

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

DISABLE TRIGGER trgPreventDeletion on AuditAccess

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

USE AdventureWorks;
GO
ALTER TABLE Production.Product DISABLE TRIGGER trgPriceControl

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

DISABLE TRIGGER ddl_trig_Create_DB
ON ALL SERVER

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

USE Chapter6;
DISABLE TRIGGER NoTableChanges
ON DATABASE

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

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class WeekdayClass
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
        Public Shared Function fn_Happy(ByVal dt As 	_	            System.Data.SqlTypes.SqlDateTime) As String
        If dt.IsNull Then
            Return WeekdayName(Weekday(Today()))
        Else
            Return WeekdayName(Weekday(dt))
        End If
    End Function
End Class

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

cd c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

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

vbc /target:library c:\MCITPSuccess\happy_dll.vb


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

USE Master;
GO
CREATE Database Chapter6

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

USE Chapter6;
GO
CREATE ASSEMBLY assyMCITPSuccess 
FROM 'c:\MCITPSuccess\Happy_dll.dll' 
WITH PERMISSION_SET = SAFE

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

ALTER DATABASE Chapter6
    SET Trustworthy ON

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

USE Chapter6
GO
CREATE FUNCTION udfHappy(@dt as datetime)
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME assyMCITPSuccess.WeekdayClass.fn_Happy

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

USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
EXEC sp_configure 'clr enabled', '1';
RECONFIGURE;

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

USE Chapter6;
GO
SELECT  'Happy ' + dbo.udfHappy(NULL) + '!'
SELECT  'Happy ' + dbo.udfHappy('01/01/2008') + '!'

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