Last week, 841 Oracle technologists submitted 2,548 answers to quizzes. Here are the PL/SQL quizzes played that week:
22 August 2011: When you use the FOR UPDATE clause in your query, Oracle will lock all rows identified by the SELECT statement. When you specify columns with FOR UPDATE OF, locks will be placed only on rows in tables whose columns are in the list.
620 Players ♦ Avg. Correct: 79% ♦ Avg. Time: 325 seconds ♦ Rating: 4 stars
23 August 2011: When writing a CASE statement, you should always include an ELSE clause - unless you are absolutely sure that one of the WHEN clauses will always evaluate to TRUE. That's a big assumption to make.
644 Players ♦ Avg. Correct: 54% ♦ Avg. Time: 110 seconds ♦ Rating: 4 stars
24 August 2011: When you initialize an object type instance, you must supply an expression for each attribute. When you initialize a nested table or varray, you do not need to supply any expressions (elements) for the array.
670 Players ♦ Avg. Correct: 75% ♦ Avg. Time: 335 seconds ♦ Rating: 4 stars
25 August 2011: Use TRUNC to remove the specified number of significant digits from a number. Use TRUNC, in particular, when you do not want rounding to be performed on the number.
661 Players ♦ Avg. Correct: 87% ♦ Avg. Time: 271 seconds ♦ Rating: 4 stars
26 August 2011: The value of the LAST_DDL_TIME column of the ALL_OBJECTS and USER_OBJECTS data dictionary views is updated whenever the object is changed (for example, when code is changed and then compiled). But this column value is not changed if you replace a program and the code has not been modified.
627 Players ♦ Avg. Correct: 24% ♦ Avg. Time: 425 seconds ♦ Rating: 4 stars
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
30 August 2011
29 August 2011
Impact of NULLs and Analyzing Choice Correctness (5926)
The SQL quiz that started on 13 August has prompted a number of players to raise questions about the impact of NULL values (sorry about the delay in writing about this!). And, though it might seem odd, their comments also relate to how you should read, interpret and choose correct answers for our quizzes.
The quiz asked the following question of the plch_employees table:
We want to retrieve the total earnings (i.e. salary + commission) of an employee. Which queries give us the correct result?
The rows in the table included some NULL values for commission, while none of the salary values were NULL.
Players questioned our scoring as correct any of the choices, because all the queries assumed that the salary will never be NULL.
I will make a few comments, and then invite both our SQL author/reviewers and players to offer their own perspectives. My comments:
1. It would have been better if we'd put a NOT NULL constraint on the employee_salary column. That would avoid the issue altogether (in fact, I plan to do this after we finish this discussion).
2. It is important to draw a distinction between answering a quiz and developing "bullet-proof" code that handles all possible scenarios.
When you take a quiz, you answer the question in the context of the information provided in that question - and nothing else. In this case, none of the salaries were NULL and so the issue of the impact of NULL on a formula like "salary + commission" only affects the commission "side" of the formula.
So I do not believe that any change in scoring needs to take place. But, as noted earlier, I do think we should add a NOT NULL constraint on the salary column.
Your thoughts?
The quiz asked the following question of the plch_employees table:
We want to retrieve the total earnings (i.e. salary + commission) of an employee. Which queries give us the correct result?
The rows in the table included some NULL values for commission, while none of the salary values were NULL.
Players questioned our scoring as correct any of the choices, because all the queries assumed that the salary will never be NULL.
I will make a few comments, and then invite both our SQL author/reviewers and players to offer their own perspectives. My comments:
1. It would have been better if we'd put a NOT NULL constraint on the employee_salary column. That would avoid the issue altogether (in fact, I plan to do this after we finish this discussion).
2. It is important to draw a distinction between answering a quiz and developing "bullet-proof" code that handles all possible scenarios.
When you take a quiz, you answer the question in the context of the information provided in that question - and nothing else. In this case, none of the salaries were NULL and so the issue of the impact of NULL on a formula like "salary + commission" only affects the commission "side" of the formula.
So I do not believe that any change in scoring needs to take place. But, as noted earlier, I do think we should add a NOT NULL constraint on the salary column.
Your thoughts?
PL/SQL Challenge upgrades to APEX 4.1
We upgraded the website to APEX 4.1 over the weekend. Overall, it went smoothly (thanks, John Scott of Apex Evangelists!). We had to upgrade some plug-ins, but that was easy enough.
Two issues have caught our attention, though:
1. It seems as though processing on the website has gotten noticeably slower in a few spots (I click on the Play Now button and wait 5-10 seconds to move to the quiz page (don't worry - this time is not recorded as part of your answer time!). Does anyone else see this happening?
2. URLs with checksums generated by apex_util.prepare_url no longer seem to work; that is, even though the URLs include a checksum, you see this error:
The behavior on Firefox seems to be inconsistent. On Internet Explorer, though, the URLs are always failing and we just noticed that IE seems to be changingthe URL. That is, I paste in this URL into Firefox and it works fine:
http://plsqlchallenge.com/pls/apex/f?p=10000:PG_PQ_DRILLDOWN:442439591002222::NO:651:P651_COMP_EVENT_ID,P651_QUIZ_ID:5207,5987&cs=1F74C48CFF65FF02A47F87CB1CF821CBD
I then paste it into IE8 and press enter, IE8 changes the url to:
http://www.plsqlchallenge.com/pls/apex/f?p=10000:PG_PQ_DRILLDOWN:3106820171411144::NO:651:P651_COMP_EVENT_ID,P651_QUIZ_ID:5207,5987%26cs%3D1F74C48CFF65FF02A47F87CB1CF821CBD
Notice that & is changed to %26. And then I see the checksum error.
Do you have any experience with this?
Cheers, Steven Feuerstein
Two issues have caught our attention, though:
1. It seems as though processing on the website has gotten noticeably slower in a few spots (I click on the Play Now button and wait 5-10 seconds to move to the quiz page (don't worry - this time is not recorded as part of your answer time!). Does anyone else see this happening?
2. URLs with checksums generated by apex_util.prepare_url no longer seem to work; that is, even though the URLs include a checksum, you see this error:
Session state protection violation: This may be caused by manual alteration of a URL containing a checksum or by using a link with an incorrect or missing checksum.
The behavior on Firefox seems to be inconsistent. On Internet Explorer, though, the URLs are always failing and we just noticed that IE seems to be changingthe URL. That is, I paste in this URL into Firefox and it works fine:
http://plsqlchallenge.com/pls/apex/f?p=10000:PG_PQ_DRILLDOWN:442439591002222::NO:651:P651_COMP_EVENT_ID,P651_QUIZ_ID:5207,5987&cs=1F74C48CFF65FF02A47F87CB1CF821CBD
I then paste it into IE8 and press enter, IE8 changes the url to:
http://www.plsqlchallenge.com/pls/apex/f?p=10000:PG_PQ_DRILLDOWN:3106820171411144::NO:651:P651_COMP_EVENT_ID,P651_QUIZ_ID:5207,5987%26cs%3D1F74C48CFF65FF02A47F87CB1CF821CBD
Notice that & is changed to %26. And then I see the checksum error.
Do you have any experience with this?
Cheers, Steven Feuerstein
Subscribe to:
Posts (Atom)