This challenge will be interesting for PLSQL enthusiasts as well as bird lovers! It involves assigning food packets to birds from two different baskets. Here is a ‘relational’ representation of birds, food items and baskets in the forms tables and rows.

Table Birds lists the birds which are the recipients of food baskets. The food baskets are listed in tables Grains and Fruits. Whenever possible, you must assign the food baskets in pairs and they must be taken from each table in alphabetical order. When one of the tables no longer has a food basket for a bird you must output a null in the corresponding column and continue assigning food baskets from the other table until that one runs out of food baskets as well.

Birds Table

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

1. For a bird with no food basket at all, a single line should be output with the Grain and Fruit columns containing null.
2. The output should be ordered by Code followed by the order in which the Grain/Fruit pairs were extracted from the Grains/Fruits tables.

Use the PLSQL Script given below to generate the source tables and fill them with sample data.

--DROP TABLE PLC1_BIRDS;

CREATE TABLE PLC1_BIRDS(
    Code INT, 
    Name VARCHAR2(10)
);

INSERT INTO PLC1_BIRDS(Code,Name)
SELECT 1,'Pigeon' FROM DUAL UNION ALL
SELECT 2,'Sparrow' FROM DUAL UNION ALL
SELECT 3,'Parrot' FROM DUAL;

--DROP TABLE PLC1_GRAINS;

CREATE TABLE PLC1_GRAINS(
    Code INT, 
    Grain VARCHAR2(10)
);

INSERT INTO PLC1_GRAINS(Code,Grain)
SELECT 1,'Wheat' FROM DUAL UNION ALL
SELECT 1,'Rice' FROM DUAL UNION ALL
SELECT 2,'Corn' FROM DUAL UNION ALL
SELECT 2,'Millet' FROM DUAL;

--DROP TABLE PLC1_FRUITS;

CREATE TABLE PLC1_FRUITS(
    Code INT, 
    Fruit VARCHAR2(10)
);

INSERT INTO PLC1_FRUITS(Code,Fruit)
SELECT 1,'Banana' FROM DUAL UNION ALL
SELECT 1,'Mango' FROM DUAL UNION ALL
SELECT 1,'Guava' FROM DUAL UNION ALL
SELECT 2,'Grapes' FROM DUAL;

1. The solution should be a single query that starts with a "SELECT" or “WITH”.

1. Read the Submission Guidelines given in the FAQ section to ensure that your submission follows all the required guidelines.
2. 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 solution will be executed.
3. The solution should work on Oracle 10g or later versions.