Database Challenges

Here is the data used for the basic testing of TSQL Challenge 22

Boxes

BoxId       BoxName
----------- --------------------
1           Box 1
2           Box 2
3           Box 3
4           Box 4
5           Box 5
6           Box 6

Balls

BallId      BallName
----------- --------------------
1           Ball 1
2           Ball 2
3           Ball 3
4           Ball 4
5           Ball 5
6           Ball 6

Preferences

PreferenceId BoxId       BallId
------------ ----------- -----------
1            1           1
2            2           1
3            2           3
4            3           2
5            3           3
6            4           1
7            4           2
8            4           3
9            4           4
10           4           5
11           4           6
12           5           4
13           5           5
14           6           5

Use the sample script given below to create the source tables and populate them with sample data.

-- --------------------------------------------------
-- Create the 'Boxes' table
-- --------------------------------------------------
IF OBJECT_ID('TC22_Boxes','U') IS NOT NULL 
BEGIN
	DROP TABLE TC22_Boxes
END
GO

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

INSERT INTO TC22_Boxes (BoxName)
SELECT 'Box 1' UNION all
SELECT 'Box 2' UNION all
SELECT 'Box 3' UNION all
SELECT 'Box 4' UNION all
SELECT 'Box 5' UNION all
SELECT 'Box 6' 


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

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

INSERT INTO TC22_Balls (BallName)
SELECT 'Ball 1' UNION all
SELECT 'Ball 2' UNION all
SELECT 'Ball 3' UNION all
SELECT 'Ball 4' UNION all
SELECT 'Ball 5' UNION all
SELECT 'Ball 6' 

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

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

INSERT INTO TC22_Preferences (BoxID, BallID) 
SELECT 1,1 UNION all
SELECT 2,1 UNION all
SELECT 2,3 UNION all
SELECT 3,2 UNION all
SELECT 3,3 UNION all
SELECT 4,1 UNION all
SELECT 4,2 UNION all
SELECT 4,3 UNION all
SELECT 4,4 UNION all
SELECT 4,5 UNION all
SELECT 4,6 UNION all
SELECT 5,4 UNION all
SELECT 5,5 UNION all
SELECT 6,5