Re: SQL Fun Challenge #2

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Thu, 4 Mar 2004 18:27:36 GMT
Message-ID: <40477518.6A941F80@remove_spam.peasland.com>

> Remember this is a SQL challenge ... just looking up the answer in a
> book or working it out on paper doesn't count. Your insert statements
> would not have gotten you a passing grade in class last night in that
> you manually produced the possible ages for your insert statement by
> hand.

No partial credit in your class??? I guess this is where the student argues with the teacher over the "finer" points in his answer compared to the finer points in the problem definition.

See....I had already seen this problem many years ago. Didn't have to look it up anywhere. Your assignment said to "Create a table, load it with data". I created a table, and I loaded it with data. I only loaded it with the data that I knew to be valid. All other data would be extraneous data. After all, in a table of zip codes, do you load it with all numbers in the range of 00000-99999. You don't because many values in that range are not valid zip codes. Similarly, other values would not be valid ages for the problem, simply because I had an a priori knowledge of the question.

As I'm not in your class but rahter in this forum with, I assume, intelligent people that would be able to easily modify my solution, I assume that it would be easy to come up with something more like:

CREATE TABLE kids (

   age1 NUMBER,
   age2 NUMBER,
   age3 NUMBER);

INSERT INTO kids VALUES (1,1,1);
INSERT INTO kids VALUES (1,1,2);
....
INSERT INTO kids VALUES (1,1,36);
INSERT INTO kids VALUES (2,1,1);
...
INSERT INTO kids VALUES (36,36,36);

Now from that, one could easily write the following to determine which ages multiply to 36.

SELECT age1,age2,age3 FROM kids
WHERE age1*age2*age3=36;

And it shouldn't take too long for an intelligent person to take the above query and make is an inline-view to the original query as opposed to querying KIDS directly, i.e....

SELECT age1,age2,age3,age1+age2+age3 AS total FROM ( SELECT z.age1+z.age2+z.age3 AS total,count(*)

       FROM (SELECT age1,age2,age3 FROM kids WHERE age1*age2*age3=36) z
       GROUP BY age1+age2+age3
       HAVING count(*) > 1) x,
      kids k

WHERE k.age1+k.age2+k.age3 = x.total
  AND k.age3 > k.age2;

And my solution is not "a single SQL statement to produce the data set required to deduce the answer", but rather I go that step further where I actually produce the answer in this single SQL statement! No further deduction is required since the SQL statement uses intelligence to eliminate the wrong answers from the equation.

Cheers,
Brian Received on Thu Mar 04 2004 - 12:27:36 CST