Database Challenges

The following sandbox can be used to test your solutions for TSQL Challenge 26 against the 'tricky' data.

/***********************************************************************
 ---------------------------------------------- 
 LOGIC Testing Sandbox for TSQL Challenge 26
 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. 
    (There may be some basic info as to what is missing/extra as well)

 Revision History:
 Rev 00 - 20 July 2010 - Khyati - Initial Release
***********************************************************************/
IF OBJECT_ID('tempdb..#TestSolution') IS NOT NULL BEGIN
	DROP PROCEDURE #TestSolution
END
GO

CREATE PROCEDURE #TestSolution AS
BEGIN

   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' 
   UNION ALL
   SELECT 'ORACLE','Gold-Room','11:00','03:20','TH'  
   UNION ALL
   SELECT 'ORACLE','Gold-Room','11:00','03:20','M'  
   UNION ALL
   SELECT 'ORACLE','Gold-Room','11:00','03:20','T'  
   UNION ALL
   SELECT 'ORACLE','Gold-Room','11:00','03:20','W'  
   UNION ALL
   SELECT 'ORACLE','Gold-Room','11:00','03:20','F'  
   UNION ALL
   SELECT 'ASP.NET','Gold-Room','16:02','01:47','F' 
   UNION ALL
   SELECT 'ASP.NET','Gold-Room','16:02','01:47','M' 
   UNION ALL
   SELECT 'ASP.NET','Gold-Room','16:02','01:47','TH'  
 ;
   /* Insert your query below */
  
   /* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/

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

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

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'
INSERT INTO @z(Training, ClassRoom, Timing, Schedule)
	SELECT 
		'ORACLE',
		'Gold-Room',
		'11:00 - 14:20',
		'Mon,Tue,Wed,Thu,Fri'
INSERT INTO @z(Training, ClassRoom, Timing, Schedule)
	SELECT 
		'ASP.NET',
		'Gold-Room',
		'16:02 - 17:49',
		'Mon,Thu,Fri'
		
-- 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