PL/SQL Challenge Authorship

The PL/SQL Challenge site by Steven Feuerstein is great for learning various SQL and PL/SQL techniques. I am one of the quiz authors - I write most of the SQL quizzes (and one or two PL/SQL quizzes now and then.)

That means there is now accumulated quite a bit of my work as quizzes - each quiz demonstrating some knowledge of SQL. I could replicate this work as blog posts as well, but instead it is now possible for you to search all my quizzes on PL/SQL Challenge.

I have added that link to the right-hand menu of the (more...)

ROWS versus default RANGE in analytic window clause

I have talked at KScope about the difference between ROWS and RANGE in the analytic window clause, but haven't yet blogged about it. Recently while working out a quiz for the PL/SQL Challenge I discovered yet another reason for remembering to primarily use ROWS and only use RANGE when the actual problem requires it.



From my KScope presentation examples here is a simple case of a rolling sum of salaries:

select deptno
, ename
, sal
, sum(sal) over (
partition by deptno
order by sal
) sum_sal
from scott.emp
order by deptno
, sal
/

    DEPTNO ENAME             SAL     (more...)

The KScope Charitable Dinner Raffle

Do you want a chance for a dinner with me at KScope13 in New Orleans chatting about SQL? And at the same time get a warm charitable feeling inside helping the volunteers rebuilding New Orleans?

If yes, then read on :-) ...



In January ODTUG started a little competition where you could win a dinner for two at KScope13 in New Orleans by telling about your favorite experience from KScope. I entered a little story from KScope12 where I presented on analytic functions.

Surprise, surprise - I won \o/ ... But as I haven't spotted any other entries in the competition, I (more...)

Recursive subquery graph traversing

In December a user Silpa asked a question on AskTom on "Bi-directional hierarchical query," which inspired me to fool around with recursive subquery factoring (available from version 11.2) giving Silpa a solution which he seemed to find useful. Since then I've fooled around a little more with it, particularly concerning cycles in the graph data.

Silpa gave a table like this for testing:

create table network_table (
origin number
, destination number
)
/

And some data as well:

insert into network_table values (11, 12)
/
insert into network_table values (12, 13)
/
insert into network_table values (14, 11)
(more...)

I’m evaluated…

UKOUG 2012 evaluations have arrived - I think I did OK :-).


On a scale from 1 to 6 my scores were:

  • Topic: 5.5
  • Content: 5.5
  • Presentation skills: 4.83
  • Quality of slides: 5
  • Value of presentation: 5.67
I'm quite happy with those scores - particularly that the 6 people that filled out evaluation schemas thought they got a lot of value from the presentation. The skills score is fair, I had expected a bit less as I know I am not world class presenter - but I hope practice makes better :-)

The comments also makes (more...)