SQL Challenges

14062012

June 14, 2012

Dominic Delmolino put together a very interesting challenge. The challenge is to produce something called a Pascal matrix using Oracle Database… more specifically, just SQL. I had a vague recollection of Pascal matrixes when I read Dominic’s challenge. Basically, the goal is to create a matrix similar to the following:

The rule for generating the matrix is simply that a cell’s value is the sum of the value in the cell that is immediately to the left plus the value in the cell that is immediately above. Sounds easy, right?

If we were just working in Microsoft Excel (or some other spreadsheet package), we could do something like this to quickly create the matrix:

Dominic’s challenge probably would not be much of a challenge if we could just type in formulas like the above into a SQL statement. Give his challenge a try to see if you are able to derive a unique solution to the problem. I probably spent a couple of minutes (maybe 60 seconds with the help of copy and paste) creating the above example using Microsoft Excel, but spent a couple of hours trying to produce a solution that worked using SQL.

——

Part 2 of the challenge.

Take a look at the bullet point items in this blog article about NULL values in table’s columns, in particular the first three large bullet point items. Do you agree or disagree with the statements, and why?

Thanks for posting, but it appears that a portion of your SQL statement disappeared. Less than and greater than signs in comments can cause problems, because those characters are used to denote formatting keywords on web pages. I am interested in seeing the full SQL statement, please repost. See the note at the very bottom of the blue section at the right regarding what substitutions needs to be performed to include less than and greater than signs in comments. Thanks.

I have not worked with the model clause, but had hoped that someone would post an example using that clause. It appears that the model clause is quite powerful, allowing the specification of formulas that are similar to the formulas that I used in Microsoft Excel at the start of this article.

There are clearly some very creative solutions to this problem, as seen on the other blog.

Formatting of code sections is a bit of a problem, so I thought that I would share my two solutions here.

The first solution that I created:
I have a degree in mathematics, but that degree is very, very rusty. Based on the provided link, we are trying to produce an array like the following, where the value in a cell in the array is the sum of the value immediately above and immediately to the left:

Now we can just… cheat and look at the formula from the article that you provided:

(i + j - 2)! / ((i - 1)! * (j - 1)!)

! is short for factorial

4! = 4 * 3 * 2 * 1
3! = 3 * 2 * 1
2! = 2 * 1
1! = 1

Calculating the factorial is a bit of a challenge. The SUM analytic function can be used to generate running sums of a column, but there is no equivalent for running products of a column.
If we calculate the natural log of a column value, generate a running sum of those natural log values, and then generate the anti-log, we can essentially generate a running product (factorial) for a column:

[…] with the solutions to my little problem of generating a symmetric Pascal matrix using SQL. Charles Hooper in particular has provided some very nice commentary on the problem, complete with diagrams and 2 alternative […]

An interesting looking SQL statement. Unfortunately, it appears that part of your SQL statement disappeared:

ERROR at line 5:
ORA-00920: invalid relational operator

Unfortunately, less than and greater than signs are HTML formatting keywords. As such, it is necessary to replace less than (<) and greater than (>) signs in comments with the appropriate HTML equivalent – see the bottom of the blue section at the right.

[…] with the solutions to my little problem of generating a symmetric Pascal matrix using SQL. Charles Hooper in particular has provided some very nice commentary on the problem, complete with diagrams and 2 alternative […]

Sorry for the delay in responding. Very nice solution with the MODEL clause. I suspected that there was an elegant solution to the problem using the MODEL clause, but I have not worked with it often enough to put the solution together from the beginning to the end.

Thank you for offering a solution to this SQL challenge – there are a couple of other SQL challenges on this blog.

Postgres certainly seems to offer features/funtions that make this problem seem easy to solve, based on the solution that you offered.

If I recall correctly, I thought that one of Postgres greatest asset was its ability to nearly behave as a drop-in replacement for Oracle Database. Postgres ability to select rows without specifying a datasource is interesting, as is its function to calculate factorials.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: