/************************************
Chapter 5 Scripts
For MCITP:Database Administrator 
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 AdventureWorks;
SELECT AVG(ListPrice)
FROM Production.Product;

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

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

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

SELECT Count(*)
FROM Production.Product;

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

SELECT Count(ListPrice)
FROM Production.Product
WHERE ListPrice > 0;

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

SELECT COUNT(Color)
FROM Production.Product;

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

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

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

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

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

SELECT * FROM Person.Contact
WHERE FirstName = 'Darril';

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

SELECT * FROM Person.Contact
WHERE LTRIM(FirstName) = 'Darril';

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

CREATE Database Chapter5;
GO
USE Chapter5;
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(SUSER_SNAME(),'1-5-2007');
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),'1-6-2007');
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),'1-7-2007');
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),GETDATE());

SELECT * FROM AuditAccess;

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

--Create the table
USE Chapter5;
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');

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

USE Chapter5;
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 Chapter5;
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 Chapter5
SELECT dbo.fn_GetState(11111);

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

USE Chapter5;
INSERT INTO AuditAccess VALUES('Hoot','07-02-2007');
INSERT INTO AuditAccess VALUES('Bozo','07-03-2007');
INSERT INTO AuditAccess VALUES('Squeaky','07-05-2007');
INSERT INTO AuditAccess VALUES(SUSER_SNAME(),GetDate());
SELECT * FROM AuditAccess;

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

USE Chapter5;
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 'Self';
SELECT dbo.fn_CountLogins('Hoot') AS 'Hoot' 

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

SELECT * FROM sp_helpdb --  will result in error
SELECT sp_helpdb AS 'HelpDb'	-- will result in error

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

EXEC sp_helpdb;

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

USE Chapter5;
sp_server_info;

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

USE Chapter5;
EXEC sp_server_info;

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

EXEC sp_helpdb;

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

EXEC sp_helpdb Chapter5;

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

SELECT @@SPID;

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

EXEC sp_who @@SPID;

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

USE AdventureWorks;
GO
SELECT DATEADD(day, 21, OrderDate) AS TimeFrame 
FROM Sales.SalesOrderHeader;

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

SELECT OrderDate, DATEADD(day, 21, OrderDate) AS TimeFrame 
FROM Sales.SalesOrderHeader;

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

SELECT DATEADD(day, 21, '11/03/2007');

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

USE AdventureWorks;
GO
CREATE Proc dbo.usp_AddDaysExample1
AS
SELECT OrderDate, DATEADD(day, 21, OrderDate) AS TimeFrame 
FROM Sales.SalesOrderHeader;
GO
CREATE Proc dbo.usp_AddDaysExample2
AS
SELECT DATEADD(day, 21, '11/03/2007');

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

EXEC usp_AddDaysExample1;
EXEC usp_AddDaysExample2;

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

USE AdventureWorks;
GO
--This will add a variable number of days to the OrderDate
ALTER Proc dbo.usp_AddDaysExample1
	@InputDays integer
AS
SELECT OrderDate, DATEADD(day, @InputDays, OrderDate)AS TimeFrame 
FROM Sales.SalesOrderHeader;
GO
--This will add 21 days to a given date
ALTER Proc dbo.usp_AddDaysExample2
	@InputDate datetime
AS
SELECT DATEADD(day, 21, @InputDate);

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

EXEC usp_AddDaysExample1 12;
EXEC usp_AddDaysExample2 '12/03/2007';

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

USE AdventureWorks;
GO
--This will add a variable number of days to a given date
ALTER Proc dbo.usp_AddDaysExample2
@InputNumberofDays int,
	@InputDate datetime
AS
SELECT DATEADD(day, @InputNumberofDays, @InputDate);

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

EXEC usp_AddDaysExample2 30,'11/03/2007';

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

EXEC usp_ AddDaysExample2 '11/03/2006', 30;

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

EXEC usp_AddDaysExample2 
@InputDate = '11/03/2007',
@InputNumberofDays = 30;

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

USE AdventureWorks;
GO
DBCC CHECKDB;

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

Use Master;
GO
CREATE DATABASE Dba;

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

SELECT MAX(dbid) FROM master.dbo.sysdatabases;

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

--Declare variables
DECLARE @dbid integer;			--Current database
DECLARE @DBName nvarchar(50);		--Database name
DECLARE @mySQL nvarchar(200);		--SQL Statement
--Start with first database
SET @dbid = 1;
--Loop through all databases
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN
 	SELECT @DBName = name
 	  FROM master.dbo.sysdatabases
 	  WHERE dbid = @dbid;
 	--Dynamically build statement to execute for each database
 	SET @mySQL = 'DBCC CHECKDB(' + @DBName + ')'; 
 	--Show dynamically built SQL statement
 	SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';
      --Increment database to do the same for the next database
      Set @dbid = @dbid + 1;
END;

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

SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';
      EXEC sp_executesql @statement = @mySQL

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

--SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';

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

USE Dba;
GO
CREATE PROC usp_DBCC_CheckAllDB
AS
--Declare variables
DECLARE @dbid integer;
DECLARE @DBName nvarchar(50);
DECLARE @mySQL nvarchar(200);
--Start with first database
SET @dbid = 1;
--Loop through all databases
WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN
 	SELECT @DBName = name
 	  FROM master.dbo.sysdatabases
 	  WHERE dbid = @dbid;
 	--Dynamically build statement to execute for each database
 	SET @mySQL = 'DBCC CHECKDB(' + @DBName + ')';
 --SELECT 'Statement = ' + @mySQL AS 'Dynamically Built T-SQL Statement';
      EXEC sp_executesql @statement = @mySQL
      --Increment database to do the same for the next database
      Set @dbid = @dbid + 1;
END;

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

USE dba;
GO
EXEC usp_DBCC_CheckAllDB

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

USE AdventureWorks;
GO
CREATE Proc dbo.usp_SQLInjection
      @login nvarchar(20),
	@password nvarchar(20)
AS
SELECT * 
FROM Users
WHERE login = @login AND password = @password;

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

SELECT * 
FROM Users
WHERE login = [Darril OR 1=1'; --]
AND password = [P@ssw0rd]

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

CREATE Proc dbo.usp_SQLInjection
      @login nvarchar(20),
	@password nvarchar(20)
AS
DECLARE @mySQL nvarchar(1000)
SET @mySQL = 'SELECT * FROM Users '
SET @mySQL = @mySQL + 'WHERE login = ' + @login 
SET @mySQL = @mySQL + 'AND password = ' + @password + ';'
EXEC sp_executesql @statement = @mySQL

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

USE Chapter5;
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 Chapter5;
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 Chapter5;
GO
CREATE TRIGGER trgPreventDeletion
ON AuditAccess
AFTER DELETE
AS 
RAISERROR ('Audit access entries cannot be deleted', 16, 10) WITH LOG
ROLLBACK TRANSACTION;

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

USE Chapter5;
SELECT * FROM AuditAccess;

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

DELETE FROM AuditAccess
WHERE UserName = 'Bozo'

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

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 Chapter5;
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

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

USE Chapter5;
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.ZipState(
	ZipStateID int IDENTITY(1,1) 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 (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');

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

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 Chapter5;
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 Chapter5;
DISABLE TRIGGER NoTableChanges
ON DATABASE