Database Challenges

/***********************************************************************
 ---------------------------------------------- 
 BASIC Testing Sandbox for TSQL Challenge 28
 Copyright © beyondrelational.com
 ---------------------------------------------- 

 Notes:
 1. Copy this template and paste in your SSMS Query editor

 2. Insert your query (solution for TSQL Challenge 28) 
    in the Section between "Insert your query below" 
    and "Insert your query above"

 3. Run the whole batch. If you see 
    "Invalid Results...Failed!" getting printed in output 
    window, there is something wrong in the query. 

 Revision History:
 Rev 00 - 19 March 2010 - Jacob Sebastian	- Initial Release
***********************************************************************/
/*
	Create a wrapper procedure around the solution
*/
IF OBJECT_ID('tempdb..#TestSolution') IS NOT NULL BEGIN
	DROP PROCEDURE #TestSolution
END
GO

CREATE PROCEDURE #TestSolution AS
BEGIN
	IF OBJECT_ID('TC28_Articles') IS NOT NULL
		DROP TABLE TC28_Articles

	CREATE TABLE TC28_Articles (
		ArticleID INT,
		Title VARCHAR(50)
	)

	IF OBJECT_ID('TC28_Categories') IS NOT NULL 
		DROP TABLE TC28_Categories

	CREATE TABLE TC28_Categories (
		CategoryID INT,
		CategoryName VARCHAR(20),
		HomePageCount SMALLINT
	)

	IF OBJECT_ID('TC28_ArticleCategories') IS NOT NULL 
		DROP TABLE TC28_ArticleCategories

	CREATE TABLE TC28_ArticleCategories (
		CategoryID INT,
		ArticleID INT
	)

	INSERT INTO TC28_Categories(CategoryID, CategoryName, HomePageCount)
	SELECT 1, 'TSQL', 2 UNION ALL
	SELECT 2, 'SSIS', 3 UNION ALL
	SELECT 3, 'Replication', 2 UNION ALL
	SELECT 4, 'SQL Server General', 2

	INSERT INTO TC28_Articles(
		ArticleID,
		Title )
	SELECT 1, 'Understanding TSQL Functions' UNION ALL
	SELECT 2, 'Summarizing data with rollup and cube' UNION ALL
	SELECT 3, 'Undestanding TRANSACTIONS' UNION ALL
	SELECT 4, 'What is SSIS?' UNION ALL
	SELECT 5, 'Getting started with SSIS' UNION ALL
	SELECT 6, 'Deploying SSIS packages' UNION ALL
	SELECT 7, 'Troubleshooting SSIS' UNION ALL
	SELECT 8, 'What is replication?' UNION ALL
	SELECT 9, 'Getting started with replication' UNION ALL
	SELECT 10, 'Comparing different replication types' UNION ALL
	SELECT 11, 'Troubleshooting replication'

	INSERT INTO TC28_ArticleCategories (ArticleID, CategoryID)
	SELECT 1, 1 UNION ALL
	SELECT 2, 1 UNION ALL
	SELECT 3, 1 UNION ALL
	SELECT 4, 2 UNION ALL
	SELECT 5, 2 UNION ALL
	SELECT 6, 2 UNION ALL
	SELECT 7, 2 UNION ALL
	SELECT 8, 3 UNION ALL
	SELECT 9, 3 UNION ALL
	SELECT 10, 3 UNION ALL
	SELECT 11, 3 UNION ALL
	SELECT 1, 4 UNION ALL
	SELECT 5, 4 UNION ALL
	SELECT 8, 4 UNION ALL
	SELECT 9, 4 UNION ALL
	SELECT 3, 4 

	;
	/* Insert your query below */

	/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

DECLARE @x TABLE(
	AutoID INT IDENTITY,
	CategoryName VARCHAR(20),
	Title VARCHAR(50)
)

-- Execute the code and 
INSERT @x EXEC #TestSolution

DECLARE @z TABLE(
	AutoID INT IDENTITY,
	CategoryName VARCHAR(20),
	Title VARCHAR(50)
)

INSERT INTO @z(CategoryName, Title)
SELECT 'TSQL','Undestanding TRANSACTIONS' UNION ALL
SELECT 'TSQL','Summarizing data with rollup and cube' UNION ALL
SELECT 'SSIS','Troubleshooting SSIS' UNION ALL
SELECT 'SSIS','Deploying SSIS packages' UNION ALL
SELECT 'SSIS','Getting started with SSIS' UNION ALL
SELECT 'Replication','Troubleshooting replication' UNION ALL
SELECT 'Replication','Comparing different replication types' UNION ALL
SELECT 'Replication','Getting started with replication' UNION ALL
SELECT 'SQL Server General','etting started with replication' UNION ALL
SELECT 'SQL Server General','What is replication?'

-- Match the output with expected result.
IF EXISTS(
  (SELECT * FROM @x EXCEPT SELECT * FROM @z)
  UNION ALL
  (SELECT * FROM @z EXCEPT SELECT * FROM @x))
BEGIN
  PRINT 'Invalid Results...Failed!'
END ELSE BEGIN
  PRINT 'Congratulations...Passed!'
END