Database Challenges

You can use the following TSQL code template to test your solutions against the basic testing data of TSQL Challenge 19. Insert your code in the location specified in the code and execute it. If your solution matches 100% with the expected results, you will see a 'success' message. If there is any difference in the output, you will see a message indicating 'failure'.

/*
	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
	DECLARE @emp TABLE (
		EmployeeID INT,
		FirstName VARCHAR(15),
		LastName VARCHAR(15),
		ReportsTo INT
	)

	DECLARE @ord TABLE (
		OrderID INT,
		EmployeeID INT
	)

	INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo)
	SELECT 2,'Andrew','Fuller',NULL UNION ALL
	SELECT 1,'Nancy','Davolio',2 UNION ALL
	SELECT 3,'Janet','Leverling',2 UNION ALL
	SELECT 4,'Margaret','Peacock',2 UNION ALL
	SELECT 5,'Steven','Buchanan',2 UNION ALL
	SELECT 8,'Laura','Callahan',2 UNION ALL
	SELECT 6,'Michael','Suyama',5 UNION ALL
	SELECT 7,'Robert','King',5 UNION ALL
	SELECT 9,'Anne','Dodsworth',5

	INSERT INTO @ord (OrderID, EmployeeID) 
	SELECT 10258,1 UNION ALL
	SELECT 10270,1 UNION ALL
	SELECT 10275,1 UNION ALL
	SELECT 10265,2 UNION ALL
	SELECT 10277,2 UNION ALL
	SELECT 10251,3 UNION ALL
	SELECT 10253,3 UNION ALL
	SELECT 10256,3 UNION ALL
	SELECT 10250,4 UNION ALL
	SELECT 10252,4 UNION ALL
	SELECT 10248,5 UNION ALL
	SELECT 10254,5 UNION ALL
	SELECT 10249,6 UNION ALL
	SELECT 10289,7 UNION ALL
	SELECT 10303,7 UNION ALL
	SELECT 10308,7 UNION ALL
	SELECT 10262,8 UNION ALL
	SELECT 10268,8 UNION ALL
	SELECT 10276,8 UNION ALL
	SELECT 10278,8 UNION ALL
	SELECT 10255,9 UNION ALL
	SELECT 10263,9 
	;
	
	/* Insert your query below */


	/* Insert your query above */
END
GO

SET NOCOUNT ON
/*
	Start the testing phase
*/
DECLARE @x TABLE(
	AutoID INT IDENTITY,
	name VARCHAR(500),
	level INT,
	by_self INT,
	by_sub INT,
	total INT
)

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

DECLARE @z TABLE(
	AutoID INT IDENTITY,
	name VARCHAR(500),
	level INT,
	by_self INT,
	by_sub INT,
	total INT
)

INSERT INTO @z SELECT 'Fuller, Andrew',0,2,20,22
INSERT INTO @z SELECT '    Buchanan, Steven',1,2,6,8
INSERT INTO @z SELECT '        Dodsworth, Anne',2,2,0,2
INSERT INTO @z SELECT '        King, Robert',2,3,0,3
INSERT INTO @z SELECT '        Suyama, Michael',2,1,0,1
INSERT INTO @z SELECT '    Callahan, Laura',1,4,0,4
INSERT INTO @z SELECT '    Davolio, Nancy',1,3,0,3
INSERT INTO @z SELECT '    Leverling, Janet',1,3,0,3
INSERT INTO @z SELECT '    Peacock, Margaret',1,2,0,2

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