The Great Oracle SQL Quiz – your chance to get grilled at Open World (S290735) – two sample questions

November 7, 2007 Database, Oracle, PL/SQL 5 Comments

Next week, I am doing two presentations at Oracle Open World, both on Thursday. One is on ADF – more on that later. The other one is The Great Oracle SQL Quiz, with 15 brain teasers, mind warpers and soul searching questions on SQL and PL/SQL. It’s the Quiz that Alex (Nuijten) and I have been doing in various locations in Europe and the US for the past three years. The quiz that Steven Feuerstein recently participated in, describing it afterwards as ‘hilarious’. While we focus on fun, and there is a real winner who gets a real prize, the quiz is also a great opportunity to learn about some fairly useful and relatively unknown features. Well, anyway, if you are in the area, come along and play along. It’s session S290735 – Thursday, 8.30-9.30 pm, Hilton Continental Ballroom 5.

So what does a typical question look like? For example a little like this one:

1. The intelligent datebase

In SQL*Plus, the following could be be seen:

The Great Oracle SQL Quiz - your chance to get grilled at Open World (S290735) - two sample questions sqlquiz001

What has been done just prior to this statement? 

A)  alter system set sysdate = NLS_FRIENDLY

B)  declare
sysdate varchar2(10):= ‘Today’;
begin
C)  alter system set NLS_DATE_FORMAT=’RELATIVE’

D)  update dual set sysdate =’TODAY’

 

Or a somewhat more complex question like this one:

2. Historical Aggregation

Here we see the employees and their salaries. That is the current situation. We see nothing about the past. And we would like to answer the question: “Who has received the biggest payraise in history?” (biggest both as a percentage and an absolute number).

The Great Oracle SQL Quiz - your chance to get grilled at Open World (S290735) - two sample questions sqlquiz002

Which database feature do you think we can best employ for answering that question?

A. The supplied package DBMS_LOGMNR

B. Aggregate Change Index

C. Flashback Versions Query

D. Materialized View in combination with After Update Row trigger

 

You will see the answers with the complete code for the second questions in a blog article to appear later today.

Related Posts

About The Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

5 Comments

  1. Laurent Schneider November 8, 2007

    🙂 yes I see you this Sunday and I will not miss your session
    http://laurentschneider.com/wordpress/2007/09/oow-schedule.html

  2. Lucas Jellema November 7, 2007

    Hi Laurent.
    Will you participate in the Quiz? Seems like you are a serious candidate for the finals. However, you will have to stick to answers we provide, no matter how many more you can think of… Will we meet on Sunday? Anyways, hope to run into you next week.
    best regards, Lucas

  3. Laurent Schneider November 7, 2007

    <<dbms_output>> declare procedure put_line(d date) is begin sys.dbms_output.put_line(‘Today’); end; begin dbms_output.put_line(sysdate); end;

  4. Laurent Schneider November 7, 2007

    > declare procedure put_line(d date) is begin sys.dbms_output.put_line(‘Today’); end; begin dbms_output.put_line(sysdate); end;

  5. Laurent Schneider November 7, 2007

    1 : E) alter session set nls_date_format='”Today”‘ 🙂