This puzzle is part of the TSQL Challenge contest.
Author: Jacob Sebastion
This challenge will be interesting for TSQL enthusiasts as well as bird lovers! It involves assigning food items to birds from two different baskets. Here is a ‘relational’ representation of birds, food items and baskets in the form of tables and rows.
Table Birds lists the birds which are the recipients of food items. The baskets containing the food items are the tables Grains and Fruits. Whenever possible, you must assign the food items in pairs and they must be taken from each table in alphabetical order. When one of the tables no longer has a food item for a bird you must output a null in the corresponding column and continue assigning food items from the other table until that one runs out of food baskets as well.
Code Name ---- ------- 1 Pigeon 2 Sparrow 3 Parrot
Code Grain ---- ------ 1 Wheat 1 Rice 2 Corn 2 Millet
Code Fruit ---- ------ 1 Banana 1 Mango 1 Guava 2 Grapes
Code Bird Grain Fruit ---- ------- ------ ------ 1 Pigeon Rice Banana 1 Pigeon Wheat Guava 1 Pigeon NULL Mango 2 Sparrow Corn Grapes 2 Sparrow Millet NULL 3 Parrot NULL NULL
- For a bird with no food basket at all, a single line should be output with the Grain and Fruit columns containing null.
- The output should be ordered by Code followed by the order in which the Grain/Fruit pairs were extracted from the Grains/Fruits tables.
IF OBJECT_ID('TC1_BIRDS','U') IS NOT NULL BEGIN DROP TABLE TC1_BIRDS END GO CREATE TABLE TC1_BIRDS( Code INT, Name VARCHAR(10) ) GO INSERT INTO TC1_BIRDS(Code,Name) SELECT 1,'Pigeon' UNION ALL SELECT 2,'Sparrow' UNION ALL SELECT 3,'Parrot' GO IF OBJECT_ID('TC1_GRAINS','U') IS NOT NULL BEGIN DROP TABLE TC1_GRAINS END GO CREATE TABLE TC1_GRAINS( Code INT, Grain VARCHAR(10) ) GO INSERT INTO TC1_GRAINS(Code,Grain) SELECT 1,'Wheat' UNION ALL SELECT 1,'Rice' UNION ALL SELECT 2,'Corn' UNION ALL SELECT 2,'Millet' IF OBJECT_ID('TC1_FRUITS','U') IS NOT NULL BEGIN DROP TABLE TC1_FRUITS END GO CREATE TABLE TC1_FRUITS( Code INT, Fruit VARCHAR(10) ) GO INSERT INTO TC1_FRUITS(Code,Fruit) SELECT 1,'Banana' UNION ALL SELECT 1,'Mango' UNION ALL SELECT 1,'Guava' UNION ALL SELECT 2,'Grapes'
- The solution should be a single query that starts with a “SELECT” or “;WITH”
- Read the Submission Guidelines and make sure that your solution follows them.
- If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.