Northern California Oracle Users Group

Home » NoCOUG » Fourth International NoCOUG SQL Challenge

Fourth International NoCOUG SQL Challenge

Special announcement: Invite your friends and co-workers to the spring conference on May 21 and register them as your guests. If your guests attend the conference, you and your guests will each receive a complimentary copy of How to Win Friends and Influence People, the timeless book by Dale Carnegie which has sold more than 15 million copies.

The Fourth International NoCOUG SQL Challenge has been published in the May issue of the NoCOUG Journal available at http://www.nocoug.org/Journal/NoCOUG_Journal_201505.pdf. Shakespeare’s story of Romeo and Juliet plays out in an American setting. In the NoCOUG version, Romeo sings American songs to Juliet and gives her gourmet dipped swizzled strawberries for her birthday. The bard of Avon must be rolling in his grave laughing.

Entries should be attached as a comment to this announcement. Please use the code tag to delimit SQL code. Judging criteria will include correctness, originality, efficiency, portability, readability, and order of receipt. The winner will receive an Apple Watch Sport or gift card of equal value. The August Order of the Wooden Pretzel will also be conferred on the winner, in keeping with the celebrated comment by Steven Feuerstein in the August 2006 issue of the NoCOUG Journal: “Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.” The current knights of the August Order of the Wooden Pretzel—Alberto Dell’Era (Italy), Andre Araujo (Australia), Rob van Wijk (Netherlands), Ilya Chuhnakov (Russia), and Lukasz Pluta (Poland)—are not eligible to participate. Additional prizes may be awarded at the discretion of NoCOUG. The contest will close at a time of NoCOUG’s choosing. NoCOUG and the judges reserve the right to publish and comment on any of the submissions with due credit to the originators. NoCOUG reserves the complete right to clarify, interpret, or modify the contest rules at any time. NoCOUG’s decisions are final.

NoCOUG Journal Vol. 29 #2

About the NoCOUG SQL challenges

In an interview for the NoCOUG Journal, Steven Feuerstein was asked: “SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”

Steven replied:  “Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic. PL/SQL remains the fastest and easiest way to access and manipulate data in an Oracle RDBMS, and I am certain it is going to stay that way for decades.”

To prove Steven correct, NoCOUG held three international SQL challenges. The winning entries were mind-boggling indeed.

First International NoCOUG SQL Challenge

An ancient 20-sided die was discovered in the secret chamber of mystery at Hogwash School of Es-Cue-El. A mysterious symbol was inscribed on each face of the die. The great Wizard of Odds discovered that each symbol represents a number. The great wizard discovered that the die was biased and invited all practitioners of the ancient arts of Es-Cue-El to create an Es-Cue-El spell to display the probabilities of obtaining various sums when the die was thrown “N” times in succession in a game of chance. Nine solutions were found by participants in seven countries and three continents but the winner, Alberto Dell’Era from Italy, rose above the competition by implementing Discrete Fourier Transforms and becoming the first knight of the August Order of the Wooden Pretzel. You can read an explanation of his wonderful solution here. Alberto also implemented Fast Fourier Transforms but we won’t even go there.

Second International NoCOUG SQL Challenge

An ancient manuscript titled “Love Your Data” was discovered in the secret chamber of mystery at Hogwash School of Es-Cue-El. The manuscript was covered with mysterious words and the great Wizard of Odds implored contestants to create an Es-Cue-El spell that revealed the secret message. Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia) submitted solutions and became the second, third, and fourth knights of the August Order of the Wooden Pretzel. Ilya submitted two solutions: one using the MODEL clause and one using recursive common table expressions. You can read their wonderful solutions in the 100th issue of the NoCOUG Journal.

Third International NoCOUG SQL Challenge

The Wicked Witch of the West had invited six friends to the Third Annual Witching & Wizarding Ball at Pythian Academy of Es-Cue-El & No-Es-Cue-El. Here friends made their attendance contingent upon the attendance ofothers. For examle, Burdock Muldoon, Elfrida Clagg, and Falco Aesalon all said they would come if Carlotta Pinkstone and Daisy Dodderidge both came. The Wicked Witch of the West needed an Es-Cue-El or No-Es-Cue-El spell to determine whom she needed to persuade to attend the wizarding ball in order to ensure that all her invitees attend. Master sorcerer Lukasz Plata of Poland not only solved the problem with a single SQL statement but provided a proof that his solution was correct. He became the fifth knight of the August Order of the Wooden Pretzel. You can read his wonderful solution here.

Advertisements

273 Comments

  1. nocoug says:

    Dataset IV should read as follows:

    /* Dataset IV (12 rows) */
    /* The expected answer is July 31 */
     
    /* the 10 values from Dataset II */
    /* similar to the values in Cheryl's Birthday except that July 31 is used instead of July 16 */
    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    
    /* two additional values */
    insert into Dates VALUES (DATE'2015-05-13');
    insert into Dates VALUES (DATE'2015-09-13');
    

    The reason for the two additional values is to trigger Rule 1a below:

    Rule 1a: Romeo says “I don’t know when Juliet’s birthday is” (September is eliminated)
    Rule 1b: Romeo says “Count Paris doesn’t know either” (May and June are eliminated)
    Rule 2: Count Paris says “Now I do!” (Only July 31, August 15, and August 17 are left in contention)
    Rule 3: Romeo says “Now I do too!” (Only July 31 is left in contention)

    Like

  2. Chris says:

    What if DATE’2015-05-19′ was not in that list of Dataset IV?

    Like

    • nocoug says:

      What if DATE’2015-05-19′ was not in that list of Dataset IV?

      All four rules are still needed. Consider the original dataset in http://en.wikipedia.org/wiki/Cheryl%27s_Birthday (similar to Dataset II except that July 16 is used instead of July 31).

      Stage 1:

      • Romeo knows the month but not the day (Rule 1a).
      • Count Paris knows the day but not the month.
      • We (the public) know neither the month nor the day.
      • Romeo additionally knows that Count Paris cannot determine the month at this stage (Rule 1b).

      Stage 2:

      All days in May and June are eliminated through the application of Rule 1b.

      • Romeo knows the month but not the day.
      • Count Paris determines the month (Rule 2). He now knows both the month and the day.
      • We (the public) know neither the month nor the day

      Stage 3:

      July 14 and August 14 are eliminated through the use of Rule 2. Only July 16, August 15, and August 17 are left in contention.

      • Romeo determines the day (Rule 3). He now knows both the month and the day.
      • We (the public) know neither the month nor the day.

      Stage 4

      August 15 and August 17 are eliminated through the use of Rule 3. Only July 16 is left in contention.

      • We (the public) determine both the month and the day.

      Now remove May 19 from the original dataset in http://en.wikipedia.org/wiki/Cheryl%27s_Birthday.

      Stage 1:

      • Romeo knows the month but not the day (Rule 1a).
      • Count Paris knows the day but not the month.
      • We (the public) know neither the month nor the day.
      • Romeo additionally knows that Count Paris cannot determine the month at this stage (Rule 1b).

      Stage 2:

      All days in June are eliminated through the application of Rule 1b.

      • Romeo knows the month but not the day.
      • Count Paris determines the month (Rule 2). He now knows both the month and the day.
      • We (the public) know neither the month nor the day

      Stage 3:

      All dates except August 17 are eliminated through the use of Rule 2. Only August 17 is left in contention.

      • Romeo determines the day (Rule 3). He now knows both the month and the day.
      • We (the public) determine both the month and the day.

      Like

      • Chris says:

        One question on the 2nd scenario stag 2. How exactly Paris determines the month in a way that public could be aware. Since he couldnt rule out May but only June there are now two candidates: 13/05 (as Paris knows from 1a that it couldnt be September) and 17/08. Personally he knows which one to take and Romeo therefore too. But the public? IMHO there are two solutions possible in this case:

        select
        dateofbirth
        from dates where id= 17
        
        DATEOFBIRTH
        05/15/2015
        05/31/2015
        06/17/2015
        06/18/2015
        07/14/2015
        07/31/2015
        08/14/2015
        08/15/2015
        08/17/2015
        05/13/2015
        09/13/2015
        
        with d as(
        select unique
         extract(month from dateofbirth)m
        ,extract(day from dateofbirth)d
        from dates where id=17)
        select m,d from(
        select * from d
        model
        dimension by(m,d)
        measures(0s,0t)rules(
         s[m,d]=sum(1)[m,cv()]
        ,t[m,d]=case when min(s)[cv(),d]>1 and sum(1)[cv(),d]>1 then 1 end
        ,s[m,d]=case when t[cv(),cv()]=1 then sum(t)[m,cv()] end
        ,t[m,d]=sum(case when s=1 then 1 end)[cv(),d]
        ))where s=1 and t=1
        
        M	D
        8	17
        5	13
        

        Like

        • Chris says:

          Explanation of the foregoing solution as requested here https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge/comment-page-2/#comment-407

          with d as(
          select unique
           extract(month from dateofbirth)m
          ,extract(day from dateofbirth)d
          from dates where id=7)
          select m,d from(
          select * from d
          model
          dimension by(m,d)
          measures(0s,0t)rules(
           -- sum(1) is just a shortcut for count(*)
           -- the aggregates work like this for example:
           -- agg(measure)[current_value,dimension]
           -- aggregation of measure for all rows with first dimension of current_value
           -- irrespective of the other dimension
           -- s := sum of months for the current day (remember we are distinct already)
           s[m,d]=sum(1)[m,cv()]
           -- t := all month with unique days and all unique months are "nulled"
           --      all others marked with 1 => possible months
          ,t[m,d]=case when min(s)[cv(),d]>1 and sum(1)[cv(),d]>1 then 1 end
           -- s := the count of each day over the months, marked in the last step, is calculated
           --      if s=1 then this is a unique day over the remaining months from the last step
           --      => possible days (from this Paris will know)
          ,s[m,d]=case when t[cv(),cv()]=1 then sum(t)[m,cv()] end
           -- t := for the current month the number of unique days is calculated from the last step
           --      Romeo could only find a solution if his month has only one possible day
          ,t[m,d]=sum(case when s=1 then 1 end)[cv(),d]
          ))
          -- rows being a solution must confirm
          where s=1 and t=1
          

          Like

        • Chris says:

          Ilustration

          
          starting position
          
          M	13	14	15	17	18	19	31
          4	0|0	0|0	- 	- 	- 	- 	-
          5	- 	- 	0|0	- 	- 	0|0	0|0
          6	- 	- 	- 	0|0	0|0	- 	-
          7	- 	0|0	- 	- 	- 	- 	0|0
          8	- 	0|0	0|0	0|0	- 	- 	0|0
          9	0|0	- 	- 	- 	- 	- 	- 
          
           -- s := sum of months for the current day (remember we are distinct already)
           s[m,d]=sum(1)[m,cv()]
           
          M	13	14	15	17	18	19	31
          4	2|0	3|0	- 	- 	- 	- 	-
          5	- 	- 	2|0	- 	- 	1|0	3|0
          6	- 	- 	- 	2|0	1|0	- 	-
          7	- 	3|0	- 	- 	- 	- 	3|0
          8	- 	3|0	2|0	2|0	- 	- 	3|0
          9	2|0	- 	- 	- 	- 	- 	- 
          
           -- t := all month with unique days and all unique months are "nulled"
           --      all others marked with 1 => possible months
          ,t[m,d]=case when min(s)[cv(),d]>1 and sum(1)[cv(),d]>1 then 1 end
          
          M	13	14	15	17	18	19	31
          4	2|1	3|1	- 	- 	- 	- 	-
          5	- 	- 	2|	- 	- 	1|	3|
          6	- 	- 	- 	2|	1|	- 	-
          7	- 	3|1	- 	- 	- 	- 	3|1
          8	- 	3|1	2|1	2|1	- 	- 	3|1
          9	2|	- 	- 	- 	- 	- 	- 
          
           -- s := the count of each day over the months, marked in the last step, is calculated
           --      if s=1 then this is a unique day over the remaining months from the last step
           --      => possible days (from this Paris will know)
          ,s[m,d]=case when t[cv(),cv()]=1 then sum(t)[m,cv()] end
          
          M	13	14	15	17	18	19	31
          4	1|1	3|1	- 	- 	- 	- 	-
          5	- 	- 	|	- 	- 	|	|
          6	- 	- 	- 	|	|	- 	-
          7	- 	3|1	- 	- 	- 	- 	2|1
          8	- 	3|1	1|1	1|1	- 	- 	2|1
          9	|	- 	- 	- 	- 	- 	- 
          
           -- t := for the current month the number of unique days is calculated from the last step
           --      Romeo could only find a solution if his month has only one possible day
          ,t[m,d]=sum(case when s=1 then 1 end)[cv(),d]
          
          M	13	14	15	17	18	19	31
          4	1|1	3|1	- 	- 	- 	- 	-
          5	- 	- 	|	- 	- 	|	|
          6	- 	- 	- 	|	|	- 	-
          7	- 	3|	- 	- 	- 	- 	2|
          8	- 	3|2	1|2	1|2	- 	- 	2|2
          9	|	- 	- 	- 	- 	- 	- 
          

          Like

  3. svenweller says:

    I think we could explore some new possibilities. All solutions so far delivered a SELECT statement. I feel that the phrase “SQL QUERY” could also be interpreted as any kind /series of SQL. See also: http://simple.wikipedia.org/wiki/Structured_Query_Language for support of this interpretation.

    So here is my try using some dml/ddl statements.

    create table d as select unique to_char(DateOfBirth,’MM’) m, to_char(DateOfBirth,’DD’) t from dates;
    delete from d where m in (select min(m) from d group by t having sum(1)=1);
    delete from d where t in (select t from d group by t having sum(1)>1);
    select * from d where m not in (select m from d group by m having sum(1)>1);

    It small but is still a considerable amount larger that the best SELECT solutions yet, But I feel there is a little room for improvement left.

    Like

  4. nocoug says:

    So what’s the official opinion on this:
    1a and 1b are both based on the original dataset (I understand it as: neither of us knows the birthday)

    or:
    1b should be done based on the result of 1a(some data may has been eliminated by 1a)

    @James Thank you for your contributions to the competition. This is another teachable moment which demonstrates that, in situations like this, only the implementation (SQL, relational calculus, or relational algebra) is unambiguous but the plain-language functional specification is subject to interpretation. The ideal specification would be a relational calculus or relational algebra specification but there would be no further need for implementation in that case.

    When Romeo says “Count Paris doesn’t know either,” he is putting himself in Count Paris’s shoes. Depending on the data set, Count Paris may or may not know whether Romeo knows Juliet’s birthday. If he knows that Romeo does not know Juliet’s birthday, we may apply Rule 1b on top or Rule 1a because then Rule 1a is redundant 🙂 But, if Count Paris does not know (just by looking at the choices) whether Romeo knows Juliet’s birthday, we must apply Rule 1a and Rule 1b separately.

    In the case of the original data set in the Cheryl’s Birthday problem, it is obvious that Romeo does not know Juliet’s birthday just by looking at the choices. But in the following data set, it is uncertain whether Romeo knows Juliet’s birthday (just by looking at the choices).

    -- Dataset VII (14 rows)
    -- The expected answer is April 13
    
    truncate table dates;
    -- the 10 values from Dataset II
    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    -- four additional values
    insert into Dates VALUES (DATE'2015-04-13');
    insert into Dates VALUES (DATE'2015-04-14');
    insert into Dates VALUES (DATE'2015-08-31');
    insert into Dates VALUES (DATE'2015-09-13');
    commit work;
    

    The expected answer in this case is April 13.

    Romeo knows that month is April but there are two choices in April so he does not know the date.
    Romeo further knows that every choice in April is duplicate in another month, so Count Paris does not know the date.
    Count Paris knows that the day is 13 and that it occurs in April and September. Since September is eliminated (Rule 1a), Juliet’s birthday must be on April 13. We on the other hand, do not yet know when Cheryl’s birthday is. All we can tell at this point, is that it is one of April 13, August 15, and August 17 (July 31 is not in contention because the value 31 occurs both in July as well as in August).
    Romeo knows that the month is April but he does not know whether the day is 13 or 14. Since Romeo has figured out Juliet’s birthday, he realizes that Juliet’s birthday is on April 13.
    Once Romeo tells us that he has figured out Juliet’s birthday, we can figure out Juliet’s birthday.

    The following solution applies Rule 1b on top of Rule 1a and therefore returns an incorrect result (no rows).

      select m, d, count(*) over (partition by m) as m_count from (
        select m, d, count(*) over (partition by d) as d_count from (
          select m, d, min(d_count) over (partition by m) as min_d_count from (
            select m, d, count(*) over (partition by d) as d_count from (
              select m, d, count(*) over (partition by m) as m_count, count(*) over (partition by d) as d_count from (
                select distinct extract(month from dateofbirth) as m, extract(day from dateofbirth) as d from dates
              )
            )
            where m_count > 1 -- Rule 1a
          )
        )
        where  min_d_count > 1 -- Rule 1b
      )
      where d_count = 1 -- Rule 2
    )
    where m_count = 1 -- Rule 3
    ;
    

    The following solution applies Rule 1a and Rule 1b simultaneously and therefore returns the correct solution (April 13).

    select m, d from (
      select m, d, count(*) over (partition by m) as m_count from (
        select m, d, count(*) over (partition by d) as d_count from (
          select m, d, m_count, min(d_count) over (partition by m) as min_d_count from (
            select m, d, count(*) over (partition by m) as m_count, count(*) over (partition by d) as d_count from (
              select distinct extract(month from dateofbirth) as m, extract(day from dateofbirth) as d from dates
            )
          )
        )
        where m_count > 1 and min_d_count > 1 -- Rule 1a and Rule 1b
      )
      where d_count = 1 -- Rule 2
    )
    where m_count = 1 -- Rule 3
    ;
    

    Chris Goerg’s original solution returns an incorrect result (no rows)

    with d as (
    select unique
    extract(month from dateofbirth) m
    , extract(day from dateofbirth) d
    from dates
    )
    select m, d from (
    select
     *
    from d
    model
    dimension by (m,d)
    measures (0 cd, 0 cm)
    rules (
    -- mark the unique months
     cd[m,d]=decode(count(*)over(partition by cv(m)),1,1)
    -- calculate the occurences of the day values
    -- since we have the partition also over the unique months it "disappears" 
    -- in the step after next because there are no other days in this month to mark
    ,cm[m,d]=count(*)over(partition by cv(d), cd[cv(),cv()])
    -- mark all other days in the month of the unique day and unmark the unique day
    -- those days are the possible days whilst their months are the excluded months
    ,cd[m,d]=decode(decode(cm[cv(),cv()],1,null,min(cm)[cv(m),any]),1,1)
    -- mark the possible days is the non excluded months and unmark those in the excluded ones
    ,cm[m,d]=decode(min(cd)[any,cv()],cd[cv(),cv()],null,1)
    -- mark the unique possible dates and unmark the others
    ,cm[m,d]=decode(decode(cm[cv(),cv()],1,count(cm)over(partition by cv(d))),1,1)
    -- count months in which the marked day value occurs
    ,cd[m,d]=decode(cm[cv(),cv()],1,count(cm)[cv(),any])
    ))
    where cd=1 and cm = 1;
    

    The MODEL-clause solution posted by Chris at https://community.oracle.com/message/13090606#13090606 returns the correct result. It’s quite cryptic so perhaps Chris will explain for the benefit of those who are not familiar with the MODEL clause.

    with d as(
    select unique
    extract(month from dateofbirth)m
    ,extract(day from dateofbirth)d
    from dates)
    select m,d from(
    select * from d
    model
    dimension by(m,d)
    measures(0s,0t)rules(
    s[m,d]=sum(1)[m,cv()]
    ,t[m,d]=case when min(s)[cv(),d]>1 and sum(1)[cv(),d]>1 then 1 end
    ,s[m,d]=case when t[cv(),cv()]=1 then sum(t)[m,cv()] end
    ,t[m,d]=sum(case when s=1 then 1 end)[cv(),d]
    ))where s=1 and t=1;
    

    Like

    • James Su says:

      Can I draw a conclusion that 1a and 1b should always be applied simultaneously? The only exception is when 1a becomes “completely redundant”, but in this case the “simultaneous” solution still works.

      Do you still consider this test case (which I posted on May25) an invalid one:
      truncate table juliet_dates;
      INSERT INTO juliet_dates VALUES (DATE ‘2000-6-17’);
      INSERT INTO juliet_dates VALUES (DATE ‘2000-7-14’);
      INSERT INTO juliet_dates VALUES (DATE ‘2000-7-15’);
      INSERT INTO juliet_dates VALUES (DATE ‘2000-8-14’);
      INSERT INTO juliet_dates VALUES (DATE ‘2000-8-15’);
      INSERT INTO juliet_dates VALUES (DATE ‘2000-8-17’);

      I think the answer is Aug 17 and that’s what my SQL returns.

      BTW if a test case does not match the story(no answer or more than one answers), it’s still a valid one in which case a correct SQL solution should return no row or more than one rows.

      Like

      • nocoug says:

        @James The six values you chose fit the following abbreviated scenario:

        Romeo declared dejectedly: “I don’t know when Juliet’s birthday is but, thank heavens, Count Paris doesn’t know either.” Count Paris exclaimed excitedly: “Now I do!” What is Juliet’s birthday?

        Like

  5. nocoug says:

    The following seven data sets can be used for testing:

    Data Set I (11 rows)
    The expected answer is July 31

    insert into Dates VALUES (DATE'1582-05-15');
    insert into Dates VALUES (DATE'1582-05-19');
    insert into Dates VALUES (DATE'1582-06-18');
    insert into Dates VALUES (DATE'1582-07-31');
    insert into Dates VALUES (DATE'1582-08-15');
    insert into Dates VALUES (DATE'1583-05-19');
    insert into Dates VALUES (DATE'1583-05-31');
    insert into Dates VALUES (DATE'1583-06-17');
    insert into Dates VALUES (DATE'1583-07-14');
    insert into Dates VALUES (DATE'1583-08-14');
    insert into Dates VALUES (DATE'1583-08-17');
    

    Data Set II (10 rows)
    The expected answer is July 31

    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    

    Data Set III (15 rows)
    The expected answer is July 31

    -- The 10 values from Data Set II
    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    -- Five additional values
    insert into dates values (date'2015-05-20');
    insert into dates values (date'2015-05-21');
    insert into dates values (date'2015-09-14');
    insert into dates values (date'2015-09-20');
    insert into dates values (date'2015-09-21');
    

    Data Set IV (12 rows)
    The expected answer is July 31

    -- The 10 values from Data Set II
    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    -- Two additional values
    insert into Dates VALUES (DATE'2015-05-13');
    insert into Dates VALUES (DATE'2015-09-13');
    

    Data Set V (13 rows)
    The expected answers are July 31 and September 20 (ambiguous scenario)

    -- The 10 values from Data Set II
    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    -- Three additional values
    insert into Dates VALUES (DATE'2015-05-20');
    insert into Dates VALUES (DATE'2015-09-14');
    insert into Dates VALUES (DATE'2015-09-20');
    

    Data Set VI (12 rows)
    The expected answer is July 31

    -- The 10 values from Data Set II
    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    -- Two additional values
    insert into Dates VALUES (DATE'2015-05-14');
    insert into Dates VALUES (DATE'2015-05-17');
    

    Data Set VII (14 rows)
    The expected answer is April 13

    -- The 10 values from Data Set II
    insert into Dates VALUES (DATE'2015-05-15');
    insert into Dates VALUES (DATE'2015-05-31');
    insert into Dates VALUES (DATE'2015-05-19');
    insert into Dates VALUES (DATE'2015-06-17');
    insert into Dates VALUES (DATE'2015-06-18');
    insert into Dates VALUES (DATE'2015-07-14');
    insert into Dates VALUES (DATE'2015-07-31');
    insert into Dates VALUES (DATE'2015-08-14');
    insert into Dates VALUES (DATE'2015-08-15');
    insert into Dates VALUES (DATE'2015-08-17');
    -- Four additional values
    insert into Dates VALUES (DATE'2015-04-13');
    insert into Dates VALUES (DATE'2015-04-14');
    insert into Dates VALUES (DATE'2015-08-31');
    insert into Dates VALUES (DATE'2015-09-13');
    

    Like

  6. nocoug says:

    The contest is now closed. The winner will be announced in the August issue of the NoCOUG Journal tentatively scheduled for release on Monday, August 10.

    Like

  7. nocoug says:

    The August issue is available at http://www.nocoug.org/Journal/NoCOUG_Journal_201508.pdf. Comments are welcome.

    Like

  8. […] What’s that got to do with problem solving? I think that the first rule of problem solving is “If at first you don’t succeed, try, try, again.” Chris Goerg demonstrated this rule very well in the fourth International NoCOUG SQL challenge. His attempts to solve the challenge are on display for all to see at https://nocoug.wordpress.com/2015/05/11/fourth-international-nocoug-sql-challenge/. […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Next NoCOUG meeting
August 20 at Chevron, San Ramon

Follow me on Twitter

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 280 other followers

%d bloggers like this: