Re: SQL Fun Challenge #2

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: 4 Mar 2004 00:13:07 -0800
Message-ID: <73e20c6c.0403040013.543cb30a@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1078359740.735908_at_yasure>...

Being the anarchist bastard I always have been, I'm gonna throw the solution upside down. Using only a join to filter the result. Follow me folks: the ride is wild...

> Two mathematicians (Boris and Vladimir) met accidently for the first
> time in 20 years.

Ah, so the three kids must ALL have less than 20 years of age. OK, let's create suitable tables (stay with me Daniel, if you please):

SQL> create table youngest as select rownum age from all_tables   2 where rownum < 20;
Table created.
Elapsed: 00:00:01.04
SQL> create table middleone as select * from youngest; Table created.
Elapsed: 00:00:00.00
SQL> create table eldest as select * from youngest; Table created.
Elapsed: 00:00:00.00
SQL> select * from youngest;
Press Return to continue...

       AGE

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19

19 rows selected.
Elapsed: 00:00:00.06

etcetc, you get the gist: each table has all possible ages for each of the kids.

> They greet each other and begin catching up on their respective lives.

No SQL here, please!

> "Do you have any children?" "Yes" replies Vladimir, "I have three." "How
> old are they?", asks Boris.

Ay! Some questions should never be asked...

> "The product of their ages is 36 and the sum of their ages is equal to
> the number of windows on that building across the street."

OK let's join these tables so that we get the required conditions:

First of all some formatting so we can see things properly: COLUMN sum FORMAT 99999
COLUMN Product FORMAT a20

Now the SQL:

1-Better use this:       select
2-Get the product:       a.age||'*'||b.age||'*'||c.age Product,
3-AND the sum:           a.age+b.age+c.age Sum
4-Use all kids:          from youngest a, middleone b, eldest c
5-age product must be:   where a.age * b.age * c.age = 36
6-Ages better be right:  and a.age < b.age and b.age < c.age
7- get some order:       order by 2,a.age,b.age,c.age

or, in SQL-ish:

select
a.age||'*'||b.age||'*'||c.age Product,
a.age+b.age+c.age Sum
from youngest a, middleone b, eldest c
where a.age * b.age * c.age = 36
and a.age < b.age and b.age < c.age
order by 2,a.age,b.age,c.age
SQL> /
Press Return to continue...

PRODUCT                 SUM
-------------------- ------
2*3*6                    11
1*4*9                    14
1*3*12                   16
1*2*18                   21

Elapsed: 00:00:01.00
SQL> Hmmm, still not happy. Let's assume that a kid cannot be born within one year from the other (or else Mum will declare war...):

select
a.age||'*'||b.age||'*'||c.age Product,
a.age+b.age+c.age Sum
from youngest a, middleone b, eldest c
where a.age * b.age * c.age = 36
and a.age < (b.age-1) and b.age < (c.age-1) order by 2,a.age,b.age,c.age
SQL> /
Press Return to continue...

PRODUCT                 SUM
-------------------- ------
1*4*9                    14
1*3*12                   16

AH! MUCH better!
Assuming that Boris can count and there are no anomalies such as twins (Vladi did not specifically say there were twins, so I assume there aren't), he should now know the ages. I've automatically pruned all anomalies with my SQL.

So, what else?

> Boris looks
> at the building, counts the windows then says "Vladi, that still doesn't
> tell me the ages."

Darn you, Daniel! You had to throw a spanner in the works, didn't you? I'll have to assume an exception: twins! Boris couldn't be so stupid that he couldn't count windows! So, there MUST be an anomaly.

OK, <groan> let's introduce the <!@#$%^&!> anomaly:

select
a.age||'*'||b.age||'*'||c.age Product,
a.age+b.age+c.age Sum
from youngest a, middleone b, eldest c

where a.age * b.age * c.age = 36
and ((a.age = b.age and b.age < (c.age-1)) OR
    (a.age < (b.age-1) and b.age = c.age))

order by 2,a.age,b.age,c.age

Ie, we now look for the possibility of two of them being twins (same age).
Results?

SQL> /
Press Return to continue...

PRODUCT                 SUM
-------------------- ------
1*6*6                    13
2*2*9                    13

Elapsed: 00:00:00.04
SQL> That's a LOT better: at least I don't have to suspect Boris' intellect...
So, what else?

> "Ah, says Vladi, then I must tell you that the eldest
> has red hair." "Oh", says Boris, "now I know their ages." What are the
> ages of Boris' children?
>

Hmmm, Vlad doesn't lie or split hairs, so if he's saying "the eldest", it can only be the second combination. There can't be the eldest with twins (I hope...):

There you go:

PRODUCT                 SUM
-------------------- ------
2*2*9                    13

> Create a table, load it with data, and write a single SQL statement to
> produce the data set required to deduce the answer ... then deduce away!

Why stop at one table when you can have much more fun with three and be TOTALLY relational in your train of thought? :D

For a harder one: try to follow the same reasoning and do what Daniel asked but using a SINGLE table like he said.

Hint: you need an extra column,populated with values 'YOUNG','MIDDLE','ELDER'
and a slightly different SQL to load and extract.

But it's easier to explain using three tables.

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Thu Mar 04 2004 - 02:13:07 CST