Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This challenge involves assigning food packets to birds from two different baskets. Your job is to read the input table and produce the required result set using a single PLSQL query.

PLSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists

PLSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists

Oct 18 2011 6:29AM by Jacob Sebastian   

Introduction

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.

Sample Data

Birds Table

Code Name
---- -------
1    Pigeon
2    Sparrow
3    Parrot
Grains Table
Code Grain
---- ------
1    Wheat
1    Rice
2    Corn
2    Millet
Fruits Table
Code Fruit
---- ------
1    Banana
1    Mango
1    Guava
2    Grapes

Expected Results

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

Rules

  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.

Sample Script

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;

Restrictions

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

Notes

  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.

Tags:3 table joins

Jacob Sebastian
1 · 100% · 32235
PLSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists , 5.0 out of 5 based on 0 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]