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

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

use AdventureWorks
go
select * from Sales.SalesOrderHeader

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

USE AdventureWorks;
GO
SELECT *
FROM Sales.SalesOrderHeader

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

SELECT Title, FName, LName, EmailAddress
FROM table

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

SELECT Title, FName AS [First Name],
 LName AS [Last Name], EmailAddress [Email Address]
FROM table

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

USE AdventureWorks;
GO
SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact;
                             
/************************************/

USE AdventureWorks;
GO
SELECT *
FROM Person.Contact;

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

SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact;

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

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

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

SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2003/07/04' AND
OrderDate < '2004/07/04'

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

SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate = '2004/07/27'	
-- would not work consistently

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

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 Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName = 'Smith' AND 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                                                                                    
/************************************/

USE AdventureWorks;
GO
SELECT AVG(ListPrice)
FROM Production.Product

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

SELECT * 
INTO dbo.NotRealProducts
FROM Production.Product

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

ALTER TABLE dbo.NotReaLproducts
	ALTER COLUMN [ListPrice] [money] NULL

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

UPDATE NotRealProducts
Set ListPrice = NULL 
WHERE ListPrice = 0

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

SELECT AVG(ListPrice)
FROM NotRealProducts

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

SELECT AVG(ISNULL(ListPrice, 0.00))
FROM NotRealProducts

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

DROP Table NotRealProducts

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

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

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

USE AdventureWorks;
GO
SELECT * FROM Production.Product

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

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

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

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 Name, ProductNumber, StandardCost, ListPrice
FROM Production.Product
ORDER BY ListPrice DESC

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

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;
GO
SELECT LastName, FirstName, Phone
FROM Person.Contact
ORDER BY LastName

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

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

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

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

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

USE AdventureWorks;
GO
SELECT SalesPersonID, COUNT(*) AS 'Total Number of Sales'
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;

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

USE AdventureWorks;
GO
SELECT SalesPersonID, COUNT(SalesPersonID) AS 'Total Number of Sales'
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;

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

USE AdventureWorks;
GO
SELECT SalesPersonID, COUNT(SalesPersonID) AS 'Total Number of Sales'
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
HAVING COUNT(SalesPersonID) > 100;

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

USE Master;
GO
CREATE DATABASE Chapter4;
GO
USE Chapter4;
GO
CREATE TABLE Inventory (
Item varchar(53) NOT NULL,
Color varchar(53) NULL,
Quantity int NULL
);

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

INSERT INVENTORY VALUES ('Table', 'Red', 223);
INSERT INVENTORY VALUES ('Table', 'White', 54);
INSERT INVENTORY VALUES ('Table', 'Blue', 124);
INSERT INVENTORY VALUES ('Chair', 'Red', 210);
INSERT INVENTORY VALUES ('Chair', 'Blue', 101);
INSERT INVENTORY VALUES ('Chair', 'White', 23);
INSERT INVENTORY VALUES ('Stand', 'Red', 213);
INSERT INVENTORY VALUES ('Stand', 'Blue', 141);
INSERT INVENTORY VALUES ('Stand', 'White', 28);  

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

SELECT * FROM INVENTORY

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

SELECT Item, Color, SUM(Quantity) AS Quantity
FROM Inventory
GROUP BY Item, Color WITH CUBE;

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

SELECT Item, Color, SUM(Quantity) AS Quantity
FROM Inventory
GROUP BY Item, Color WITH Rollup

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

USE AdventureWorks;
GO
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 p.Name, pr.ProductReviewID
FROM Production.Product p,
Production.ProductReview pr
WHERE p.ProductID *= pr.ProductID

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

USE AdventureWorks;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID;

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

sp_dbcmptlevel AdventureWorks, 80;
GO
SELECT E.EmployeeID, E.HireDate, J.Resume
FROM HumanResources.Employee AS E,
HumanResources.JobCandidate AS J
WHERE E.EmployeeID *= J.EmployeeID;
GO
sp_dbcmptlevel AdventureWorks, 90;

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

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

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

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

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

USE AdventureWorks;
GO
SELECT VendorID, Name 
FROM Purchasing.Vendor

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

USE AdventureWorks;
GO
SELECT ContactID, LastName 
FROM Person.Contact;

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

USE AdventureWorks;
GO
SELECT VendorID AS ID, Name 
FROM Purchasing.Vendor
UNION
SELECT ContactID, LastName 
FROM Person.Contact

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

SELECT VendorID AS ID, Name 
FROM Purchasing.Vendor
UNION ALL
SELECT ContactID, LastName 
FROM Person.Contact

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

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 ContactID = 10

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

DELETE FROM Person.Contact
WHERE ContactID = 10

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

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

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

USE AdventureWorks
GO
WITH cteEmployees( FirstName, Lastname) AS
(
  SELECT FirstName, LastName
  FROM Person.Contact c JOIN HumanResources.Employee e
  On c.ContactID = e.ContactID
)
SELECT *
FROM cteEmployees

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

USE Chapter4;
GO
CREATE TABLE dbo.LincolnCabinet
(
	LincolnCabinetID int IDENTITY (100,1) NOT NULL, 
	LastName varchar(50) NULL,
	FirstName varchar(50) NULL,
	MgrId int NULL references dbo.LincolnCabinet,
	Title varchar(50) NULL
    CONSTRAINT PK_LincolnCabinetID PRIMARY KEY (LincolnCabinetID)
);

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

INSERT INTO LincolnCabinet (LastName, FirstName, MgrID, Title) 
VALUES('Lincoln', 'Abraham', Null, 'President')
INSERT INTO LincolnCabinet (LastName, FirstName, MgrID, Title)
VALUES('Stanton', 'Edwin', 100, 'Secretary of War')
INSERT INTO LincolnCabinet (LastName, FirstName, MgrID, Title)
VALUES('Seward', 'William', 100, 'Secretary of State')
INSERT INTO LincolnCabinet (LastName, FirstName, MgrID, Title)
VALUES('Welles', 'Gideon', 100, 'Secretary of the Navy')
INSERT INTO LincolnCabinet (LastName, FirstName, MgrID, Title)
VALUES('Welles', 'Orson', 103, 'Grandson')

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

USE Chapter4;
GO
WITH cteReport (HierarchyOrder, MgrID, LincolnCabinetID, LastName, Title)
AS
(-- Anchor member definition
SELECT 0 AS HierarchyOrder, lc.MgrID, lc.LincolnCabinetID, lc.LastName, 
lc.Title
FROM LincolnCabinet AS lc 
WHERE MgrID IS NULL
UNION ALL 
-- Recursive member definition
SELECT d.HierarchyOrder + 1, lc.MgrID, lc.LincolnCabinetID, lc.LastName, lc.Title
FROM LincolnCabinet AS lc
INNER JOIN cteReport AS d 
ON lc.MgrID = d.LincolnCabinetID 
)
--Statement that executes the CTE
SELECT HierarchyOrder, LastName, Title, LincolnCabinetID, MgrID
FROM cteReport

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

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
( -- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID )
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;

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

SELECT LastName FROM Customer1
INTERSECT
SELECT LastName FROM Customer2

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

SELECT LastName FROM Customer1
EXCEPT
SELECT LastName FROM Customer2

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

SELECT LastName FROM Customer2
EXCEPT
SELECT LastName FROM Customer1

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

SELECT VendorID, PurchaseOrderID, EmployeeID
FROM Purchasing.PurchaseOrderHeader
WHERE EmployeeID IN ( 238, 241, 244, 261 )
ORDER BY VendorID

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

USE AdventureWorks;
GO
SELECT VendorID, [238] AS Pellow, [241] AS Kurjan, [244] AS Hagens, [261] AS Miller
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
( COUNT (PurchaseOrderID)
FOR EmployeeID IN ( [238], [241], [244], [261] )
) AS pvt
ORDER BY VendorID

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

SELECT CustomerID, SalesOrderID, SubTotal, 
       RANK() OVER(ORDER BY SubTotal DESC) AS Rank
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (599, 514, 638, 72)
ORDER BY SubTotal Desc

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

SELECT CustomerID, SalesOrderID, SubTotal, 
DENSE_RANK() OVER(ORDER BY SubTotal DESC) AS Dense_Rank, 
ROW_NUMBER() OVER(ORDER BY SubTotal DESC) AS Row_Number, 
NTILE(10) OVER(ORDER BY SubTotal DESC) AS NTile, 
RANK() OVER(ORDER BY SubTotal DESC) AS Rank 
FROM Sales.SalesOrderHeader 
WHERE CustomerID IN (599, 514, 638, 72) 
ORDER BY SubTotal Desc

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

SELECT CustomerID, SalesOrderID, SubTotal,
RANK() OVER(PARTITION BY CustomerID 
ORDER BY SubTotal DESC) AS Rank
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (599, 514, 638, 72)
ORDER BY CustomerID

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