Database Challenges

The previous sandbox we provided used a few features (such as EXCEPT) available only in SQL Server 2005 and above. One of our readers came up with a SQL Server 2000 version of the sandbox, which will be helpful for this challenge as this challenge targets SQL Server 2000 as well. So if you are working on SQL Server 2000, you can use this sandbox for testing your solutions against the basic testing data for TSQL Challenge 26.

/***********************************************************************
 ---------------------------------------------- 
 BASIC Testing Sandbox for TSQL Challenge 26
 FOR running on SQL Server 2000
 Copyright © beyondrelational.com
 ---------------------------------------------- 

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

 2. Insert your query (solution for TSQL Challenge 26) 
    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 - 24 March 2010 - Khyati Patel	- Initial Release
***********************************************************************/
/*
	Create a wrapper procedure around the solution
*/
SET NOCOUNT ON

IF OBJECT_ID('TC26_TrainingInfo','U') IS NOT NULL BEGIN
	DROP TABLE TC26_TrainingInfo
END

CREATE TABLE TC26_TrainingInfo(
	TrainingID INT IDENTITY,
	Training VARCHAR(20),
	ClassRoom VARCHAR(20),
	StartTime CHAR(5),
	Duration CHAR(5),
	Wk CHAR(2)
)

INSERT INTO TC26_TrainingInfo (
	Training, ClassRoom, StartTime, Duration, Wk )
SELECT 'SQL Server','Silver-Room','10:00','02:00','M' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','W' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','T' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','F' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','F' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','M' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','TH'

/*
	Start the testing phase
*/

DECLARE @x TABLE(
	Training VARCHAR(50),
	ClassRoom VARCHAR(50),
	Timing VARCHAR(50),
	Schedule VARCHAR(50)
)

-- Execute the code and 
INSERT INTO @x 
/* Insert your query below */  

/* Insert your query above */  

DECLARE @z TABLE(
	Training VARCHAR(50),
	ClassRoom VARCHAR(50),
	Timing VARCHAR(50),
	Schedule VARCHAR(50)
)

INSERT INTO @z(Training, ClassRoom, Timing, Schedule)
	SELECT 
		'SQL Server ',
		'Silver-Room',
		'10:00 - 12:00',
		'Mon,Tue,Wed,Fri'
INSERT INTO @z(Training, ClassRoom, Timing, Schedule)
	SELECT 
		'ASP.NET',
		'Cloud-Room',
		'11:00 - 12:45',
		'Mon,Thu,Fri'

-- Match the output with expected result.
IF EXISTS
(
	(
		SELECT 
			Training, ClassRoom, Timing, Schedule
		FROM @x x
		WHERE NOT EXISTS (
			SELECT 
				Training, ClassRoom, Timing, Schedule 
			FROM @z z
			WHERE x.Training = z.Training 
				AND x.ClassRoom = z.ClassRoom 
				AND x.Timing = z.Timing 
				AND x.Schedule = z.Schedule
			)
	)
	UNION ALL
	(
		SELECT 
			Training, ClassRoom, Timing, Schedule
		FROM @z z
		WHERE NOT EXISTS(
			SELECT 
				Training, ClassRoom, Timing, Schedule 
			FROM @x x
			WHERE z.Training = x.Training 
				AND z.ClassRoom = x.ClassRoom 
				AND z.Timing = x.Timing 
				AND z.Schedule = x.Schedule
		)
	)
)
BEGIN
  PRINT 'Invalid Results...Failed!'
END ELSE BEGIN
  PRINT 'Congratulations...Passed!'
END