Database Challenges

Note that this sandbox is a draft and still being modified.

/***********************************************************************
 ---------------------------------------------- 
 LOGIC Testing Sandbox for TSQL Challenge 22
 Copyright © beyondrelational.com
 ---------------------------------------------- 

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

 2. Insert your query (solution for TSQL Challenge 22) 
    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 - 21 March 2010 - Ramireddy			- 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
	-- --------------------------------------------------
	-- Create the 'Boxes' table
	-- --------------------------------------------------
	IF OBJECT_ID('TC22_Boxes','U') IS NOT NULL 
	BEGIN
		DROP TABLE TC22_Boxes
	END;

	CREATE TABLE TC22_Boxes
	(
		BoxId INT IDENTITY PRIMARY KEY,
		BoxName VARCHAR(20)
	)

	SET IDENTITY_INSERT TC22_Boxes ON
	INSERT INTO TC22_Boxes (BoxID, BoxName)
	SELECT 2, 'Box 2' UNION ALL 
	SELECT 3, 'Box 3' UNION ALL 
	SELECT 4, 'Box 4' UNION ALL 
	SELECT 5, 'Box 5' UNION ALL 
	SELECT 6, 'Box 6' UNION ALL 
	SELECT 7, 'Box 7' UNION ALL
	SELECT 8, 'Box 8' UNION ALL 
	SELECT 9, 'Box 9' UNION ALL
	SELECT 10, 'Box 10'
	SET IDENTITY_INSERT TC22_Boxes OFF

	-- --------------------------------------------------
	-- Create the 'Balls' table
	-- --------------------------------------------------
	IF OBJECT_ID('TC22_Balls','U') IS NOT NULL 
	BEGIN
		DROP TABLE TC22_Balls
	END;

	CREATE TABLE TC22_Balls(
		BallId INT IDENTITY PRIMARY KEY,
		BallName VARCHAR(20)
	);

	SET IDENTITY_INSERT TC22_Balls ON
	INSERT INTO TC22_Balls (BallID, BallName)
	SELECT 1,'Ball 1' UNION all
	SELECT 2,'Ball 2' UNION all
	SELECT 3,'Ball 3' UNION all
	SELECT 5,'Ball 5' UNION all
	SELECT 6,'Ball 6' UNION all
	SELECT 7,'Ball 7' UNION all
	SELECT 8,'Ball 8' UNION all
	SELECT 9,'Ball 9' UNION all
	SELECT 10,'Ball 10'
	SET IDENTITY_INSERT TC22_Balls OFF

	-- --------------------------------------------------
	-- Create the 'Preferences' table
	-- --------------------------------------------------
	IF OBJECT_ID('TC22_Preferences','U') IS NOT NULL 
	BEGIN
		DROP TABLE TC22_Preferences
	END;

	CREATE TABLE TC22_Preferences(
		PreferenceId INT IDENTITY PRIMARY KEY,
		BoxId INT,
		BallId INT
	);

	INSERT INTO TC22_Preferences (BoxID, BallID) 
	SELECT 3,2   UNION ALL
	SELECT 2,3   UNION ALL
	SELECT 3,3   UNION ALL
	SELECT 4,5   UNION ALL
	SELECT 5,5   UNION ALL
	SELECT 5,6   UNION ALL
	SELECT 9,6   UNION ALL
	SELECT 10,7  UNION ALL
	SELECT 5,7   UNION ALL
	SELECT 6,9   UNION ALL
	SELECT 8,8   UNION ALL
	SELECT 10,10 UNION ALL
	SELECT 7,2   UNION ALL
	SELECT 7,1 

	;
	/* Insert your query below */

	/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

DECLARE @x TABLE(
	AutoID INT IDENTITY,
	box VARCHAR(50),
	ball VARCHAR(50)
)

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

DECLARE @z TABLE(
	AutoID INT IDENTITY,
	box VARCHAR(50),
	ball VARCHAR(50)
)

INSERT INTO @z(box, ball) SELECT 'Box 2','Ball 3'
INSERT INTO @z(box, ball) SELECT 'Box 3','Ball 2'
INSERT INTO @z(box, ball) SELECT 'Box 4','Ball 5'
INSERT INTO @z(box, ball) SELECT 'Box 5','Ball 7'
INSERT INTO @z(box, ball) SELECT 'Box 6','Ball 9'
INSERT INTO @z(box, ball) SELECT 'Box 7','Ball 1'
INSERT INTO @z(box, ball) SELECT 'Box 8','Ball 8'
INSERT INTO @z(box, ball) SELECT 'Box 9','Ball 6'
INSERT INTO @z(box, ball) SELECT 'Box 10','Ball 10'


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