/************************************
Chapter 4 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 adventureworks
go
select * from sales.salesorderheader

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

use adventureworks
go
select * from Sales.SalesOrderHeader

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

USE AdventureWorks;
GO
SELECT * 
FROM Sales.SalesOrderHeader

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

USE AdventureWorks
SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact

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

USE AdventureWorks
SELECT * 
FROM Person.Contact

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

USE AdventureWorks
SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact

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

USE AdventureWorks
SELECT Title, FirstName AS [First Name], LastName [Last Name], EmailAddress
FROM Person.Contact

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

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName = 'Stone'

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

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE ModifiedDate >= '6/29/2004'

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

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'St%'

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

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE '%st_'

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

USE AdventureWorks
SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE UPPER(LastName) LIKE 'MC%'

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

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName = 'Smith' OR FirstName ='John'

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

SELECT ContactID, Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE ContactID BETWEEN 5 and 10
SELECT ContactID, Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE ContactID >=5 AND ContactID <=10

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

SELECT ContactID, Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE FirstName = 'Sandy' OR FirstName = 'Jay' OR 
      FirstName = 'Carla' OR FirstName = 'Ronald'  
/************************************/

SELECT ContactID, Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE FirstName IN ('Sandy', 'Jay', 'Carla','Ronald')

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

SELECT ContactID, Title, FirstName, MiddleName, LastName, EmailAddress
FROM Person.Contact
WHERE MiddleName IS NULL

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

SELECT FirstName, LastName
FROM Person.Contact
WHERE EXISTS
	(SELECT *
	 FROM HumanResources.Employee
	  WHERE Person.Contact.ContactID =
            HumanResources.Employee.ContactID)

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

USE AdventureWorks
SELECT * FROM Production.Product

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

SELECT Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product

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

USE AdventureWorks
SELECT Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
WHERE StandardCost > 0

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

SELECT Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
WHERE ProductNumber Like 'CN%'

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

SELECT Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
WHERE StandardCost > 0 AND StandardCost < 100

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

SELECT Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
WHERE StandardCost BETWEEN 0.00 AND 100

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

SELECT Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
WHERE EXISTS
	(SELECT * FROM Production.Product 
	 WHERE ProductNumber LIKE '[A-C]%')

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

SELECT TOP 10 Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC

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

SELECT TOP 10 PERCENT Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC

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

SELECT TOP 10 Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
ORDER BY Name 
COLLATE SQL_Lithuanian_Cp1257_CS_AS

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

USE AdventureWorks
SELECT LastName, FirstName, Phone
FROM Person.Contact
ORDER BY LastName

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

USE AdventureWorks
SELECT LastName, FirstName, Phone
FROM Person.Contact
ORDER BY LastName DESC

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

USE AdventureWorks
SELECT TOP 5 CustomerID, SalesPersonID, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY TotalDue DESC

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

USE AdventureWorks;
GO
SELECT SalesPersonID, 
SUM(TotalDue) AS 'Total Sales'
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID

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

USE AdventureWorks;
GO
SELECT SalesPersonID, 
SUM(TotalDue) AS 'Total Sales'
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
HAVING SUM(TotalDue) > 1000000

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

USE AdventureWorks;
GO
SELECT SalesPersonID, 
SUM(TotalDue) AS 'Total Sales'
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
HAVING SUM(TotalDue) > 10000000
ORDER BY SalesPersonID

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

SELECT Name, ProductNumber, ProductModelID
FROM Production.Product

SELECT ProductModelID,Name
FROM Production.ProductModel

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

SELECT Production.Product.Name, Production.Product.ProductNumber,
       Production.ProductModel.Name AS 'Model Name'
FROM Production.ProductModel 
INNER JOIN Production.Product 
ON Production.ProductModel.ProductModelID =   
   Production.Product.ProductModelID

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

SELECT TOP 5 CustomerID, SalesPersonID, TotalDue
FROM Sales.SalesOrderHeader
ORDER BY TotalDue DESC

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

USE AdventureWorks

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

SELECT SalesOrderID AS Invoice,
TotalDue [TotalDue], 
OrderDate [Invoice Date]
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

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

SELECT GetDate()

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

SELECT CONVERT(varchar,GetDate(),101) 

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

SELECT CONVERT(varchar,GetDate(),102)

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

SELECT SalesOrderID AS Invoice,
       TotalDue [Invoice Total], 
CONVERT(varchar,OrderDate, 101) 
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

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

SELECT SalesOrderID AS Invoice,
       TotalDue [Invoice Total], 
CONVERT(varchar,OrderDate, 101) As OrderDate
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

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

SELECT SalesOrderID AS Invoice,
       TotalDue [Invoice Total], 
CONVERT(varchar,OrderDate, 101) As [Order Date]
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

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

SELECT Person.Contact.LastName AS [Last Name], 
       Person.Contact.FirstName AS [First Name],
       Sales.SalesOrderHeader.CustomerID [Customer Number], 
       Sales.SalesOrderHeader.TotalDue [Order Total]
FROM Person.Contact INNER JOIN
     Sales.SalesOrderHeader 
ON Person.Contact.ContactID = Sales.SalesOrderHeader.ContactID INNER JOIN
     Sales.SalesPerson 
ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID

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

SELECT pc.LastName AS [Last Name], 
       pc.FirstName AS [First Name], 
	 soh.CustomerID [Customer Number], 
       soh.TotalDue [Order Total]
FROM Person.Contact AS pc INNER JOIN
       Sales.SalesOrderHeader soh
ON pc.ContactID = soh.ContactID 
   INNER JOIN Sales.SalesPerson sp 
ON soh.SalesPersonID = sp.SalesPersonID

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

USE AdventureWorks;
SELECT * 
FROM Person.Contact
WHERE FirstName ='JoLynn' AND LastName = 'Dobney'

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

UPDATE Person.Contact
SET LastName = 'Adams'
WHERE ContactID = 1070

SELECT * 
FROM Person.Contact
WHERE ContactID = 1070

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

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 FirstName = 'Darril'

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

DELETE FROM Person.Contact
WHERE FirstName = 'Darril'

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

SELECT * 
FROM Purchasing.Vendor

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

USE AdventureWorks
INSERT INTO Purchasing.Vendor
 (AccountNumber, Name,CreditRating)
VALUES('AcmeSupp101','AcmeSupplies',1)

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

USE AdventureWorks
SELECT * 
FROM Purchasing.Vendor
WHERE AccountNumber = 'AcmeSupp101' 

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

USE AdventureWorks
UPDATE  Purchasing.Vendor
Set CreditRating = 3
WHERE AccountNumber = 'AcmeSupp101'

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

USE AdventureWorks
DELETE 
FROM Purchasing.Vendor
WHERE AccountNumber = 'AcmeSupp101'

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

USE AdventureWorks
Go
DISABLE TRIGGER dVendor ON Purchasing.Vendor
GO
DELETE 
FROM Purchasing.Vendor
WHERE AccountNumber = 'AcmeSupp101' 
GO
ALTER TABLE Purchasing.Vendor ENABLE TRIGGER dVendor

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

BEGIN TRANSACTION
UPDATE Person.Contact
SET LastName = 'Dobney'
WHERE ContactID = 1070
COMMIT TRANSACTION

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

SELECT * FROM 
Production.ProductInventory
WHERE ProductID = 1

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

USE AdventureWorks
BEGIN TRANSACTION
	UPDATE Production.ProductInventory
		SET Quantity = Quantity - 10
		WHERE ProductID = 1 AND LocationID=1

	UPDATE Production.ProductInventory
		SET Quantity = Quantity + 10
		WHERE ProductID = 1 AND LocationID=6
COMMIT TRANSACTION

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

SELECT * FROM 
Production.ProductInventory
WHERE ProductID = 1

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

SELECT @@TRANCOUNT

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

BEGIN TRANSACTION
	UPDATE Production.ProductInventory
		SET Quantity = Quantity - 1000
	WHERE ProductID = 1 AND LocationID=1
	IF (SELECT Quantity 
		FROM Production.ProductInventory
		WHERE ProductID =1 and LocationID=1) < 0
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END
	UPDATE Production.ProductInventory
		SET Quantity = Quantity + 1000
		WHERE ProductID = 1 AND LocationID=6
COMMIT TRANSACTION

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

USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

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

BEGIN TRAN

USE AdventureWorks
UPDATE Production.Product
SET ListPrice = ListPrice * 1.1
Where ProductID=514

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

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

--Window 2
USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

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

ROLLBACK TRAN

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

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

BEGIN TRAN

UPDATE Production.Product
SET ListPrice = ListPrice * 1.1
Where ProductID=514

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

COMMIT TRAN

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

--Window 2
SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

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

UPDATE Production.Product
SET ListPrice = 133.24
Where ProductID=514

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ProductID = 514

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

ALTER TABLE
Production.ProductInventory
ADD CONSTRAINT CK_Quantity
CHECK (Quantity >= 0)

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

UPDATE Production.ProductInventory
SET Quantity = -1000
WHERE ProductID = 1 AND LocationID=1

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

BEGIN TRY
  BEGIN TRANSACTION
	UPDATE Production.ProductInventory
		SET Quantity = Quantity - 1000
	WHERE ProductID = 1 AND LocationID=1
	UPDATE Production.ProductInventory
		SET Quantity = Quantity + 1000
		WHERE ProductID = 1 AND LocationID=6
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
	SELECT 'Transaction rolled back'
END CATCH

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

SELECT  * FROM Production.ProductInventory
WHERE ProductID = 1

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

DECLARE @VendorID int, @CreditRating int
SET @VendorID = 1
SET @CreditRating=50

USE AdventureWorks
UPDATE Purchasing.Vendor
SET CreditRating = @CreditRating
WHERE VendorID=@VendorID

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

DECLARE @VendorID int, @CreditRating int
SET @VendorID = 1
SET @CreditRating=50

BEGIN TRY
  BEGIN TRANSACTION

	USE AdventureWorks
	UPDATE Purchasing.Vendor
	SET CreditRating = @CreditRating
	WHERE VendorID=@VendorID

  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  ROLLBACK TRANSACTION
	IF @CreditRating > 5 OR @CreditRating < 1
		SELECT 'Credit Rating must be between 1 and 5.'
END CATCH

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

SELECT *
FROM Purchasing.Vendor

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

SET @CreditRating=3

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

CREATE DATABASE XMLTest

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

USE XMLTest
CREATE TABLE Project
(
	ProjectID int IDENTITY(1,1) NOT NULL,
	ProjectData xml NOT NULL
   	CONSTRAINT [PK_Project_ProjectID] PRIMARY KEY CLUSTERED 
		(ProjectID)
 )

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

--Insert untyped data
USE XMLTest
DECLARE @xmlInsert XML
SET @xmlInsert = 
'<project>
  <projectname>Employee Database</projectname>
  <startdate>1994-06-10abc</startdate>
  <enddate>2008-05-05def</enddate>
  <manager>Darril Gibson</manager>
  <scope>Create database for employees</scope>
</project>'
INSERT INTO Project 
VALUES (@xmlInsert)

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

USE XMLTest
DELETE FROM Project

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

USE XMLTest
CREATE XML SCHEMA COLLECTION dbo.xmlProjectSchemaCollection AS
'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="project">
    <xs:complexType>
      <xs:sequence>
	   <xs:element name="projectnumber" type="xs:int"/>
	   <xs:element name="projectname" type="xs:string"/>
	   <xs:element name="startdate" type="xs:date"/>
	   <xs:element name="enddate" type="xs:date"/>
	   <xs:element name="manager" type="xs:string"/>
	   <xs:element name="scope" type="xs:string"/>
      </xs:sequence>
    </xs:complexType>
</xs:element>
</xs:schema> '

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

USE XMLTest
SELECT xml_schema_namespace('dbo','xmlProjectSchemaCollection')

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

USE XMLTest
ALTER TABLE Project
ALTER COLUMN ProjectData XML (dbo.xmlProjectSchemaCollection)

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

USE XMLTest
DECLARE @xmlInsert XML
SET @xmlInsert = 
'<project>
  <projectnumber>101</projectnumber>
  <projectname>Employee Database</projectname>
  <startdate>2007-06-30Z</startdate>
  <enddate>2008-05-29Z</enddate>
  <manager>Darril Gibson</manager>
  <scope>Create database for employees</scope>
</project>'
INSERT INTO Project 
VALUES (@xmlInsert)

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

SELECT * FROM Project 

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

USE XMLTest
SELECT ProjectData.query('project/projectname')
FROM Project
Where ProjectID=1

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

DECLARE @myXML xml
SET @myXML = 
'<Root>
<Project ProjectNumber="101" ProjectName = "Employee Database" 
  StartDate ="2007-06-30" EndDate = "2007-07-29"
  Manager = "Darril Gibson">
</Project>
<Project ProjectNumber="102" ProjectName = "Build Parts Database" 
  StartDate ="2007-08-30" EndDate = "2007-09-29"
  Manager = "Justin Case">
</Project>
</Root>'
DECLARE @ProjName varchar(50)
SET @ProjName =  @myXML.value('(/Root/Project/@ProjectName)[2]', 'nvarchar(50)' )
SELECT @ProjName

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

SELECT Author, Title, Price, PublishDate
FROM Books WITH (INDEX(1))
WHERE PublishDate > '2001'