VF=>user  ID=>     Login Feedback FAQ Blog
1,076,617 quizzes played | 3,158 active players

The PL/SQL Challenge offers an ever-growing library of information to help you become more expert in the Oracle technology stack. The Quizzes tab gives you access to all quizzes taken in the past. The Resources tab offers searchable access to many topics in Oracle documentation, to popular Ask Tom threads, and to fascinating blog posts from around the world. Utilities gives you quick access to SQL and PL/SQL utilities, i.e., reusable code. Visit Commentary to search across all quiz discussions.


All of the quizzes already taken by players are available on this page. You can search for a specific string in the topic for that question; filter for a particular type of quiz (you might, for example, want to check out the quizzes given in the last playoff); check out all the advanced quizzes, etc.

Filter Quizzes


View
Quiz
Played InFeature / Summary / AuthorEnded
OnDescending
CommentaryPct Correct
SQL Deja Vu

Analytic Functions: Difference between RANGE/ROWS Windows

Retrieving yesterdays data in analytic functions can use RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING. Depending on the data, sometimes ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING can be an alternative or the shortcut using LAG function.

Author: Kim Berg Hansen [10002-2328656]
2017-05-19 FridayNo Comments
Last: No Comments
-
SQL Deja Vu

Single Row Functions: NUMTOYMINTERVAL

NUMTOYMINTERVAL and NUMTODSINTERVAL provide easy way to make interval datatypes from data rather than literals. Just beware that INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND is not convertible.

Author: Kim Berg Hansen [9321-2308601]
2017-05-12 FridayNo Comments
Last: No Comments
-
Select from SQL

Functions: Analytic Functions

Multiple methods can be used for "blanking" repeat values in the output.

Author: Kim Berg Hansen [24266-2308561]
2017-05-12 FridayNo Comments
Last: No Comments
-
Select from SQL

TRIM

TRIM function can be used as an alternative to RTRIM and/or LTRIM - it has its own pros and cons. Pro: It can trim both leading and trailing characters in one operation. Con: It can only trim one character, not a set of characters.

Author: Kim Berg Hansen [24146-2291342]
2017-05-05 FridayNo Comments
Last: No Comments
-
SQL Deja Vu

Single Row Functions: RAWTOHEX

To get the decimal values of bytes in a RAW, convert to a HEX string either implicitly or with RAWTOHEX. Then use to_number with the number format XX to get the desired result.

Author: Kim Berg Hansen [8012-2269795]
2017-05-05 FridayNo Comments
Last: No Comments
-
SQL Deja Vu

PIVOT and UNPIVOT: Unpivoting Operations

UNPIVOT can unpivot single or multiple columns as single or multiple combinations. The different combinations can used to unpivot to various different levels of detail.

Author: Kim Berg Hansen [10687-2263463]
2017-04-28 FridayNo New Comments

Last: 2015-04-01 17:19:41
-
Select from SQL

Windowing clause: RANGE

For analytic calculations on a window of rows between some absolute values (instead of relative to the current row), using the RANGE clause is not the way to go. RANGE cannot directly use "absolute positioning" and if you try to offset it to indirectly getting the desired window, you risk exceptions that may or may not occur depending on data.

Author: Kim Berg Hansen [24006-2263462]
2017-04-28 FridayNo New Comments

Last: 2017-05-01 14:07:31
-
SQL Deja Vu

Format Models: Datetime Format Models

TO_TIMESTAMP throws exception when format model does not match, so you may need a way to distinguish between what format model to use before you call TO_TIMESTAMP.

Author: Kim Berg Hansen [9162-2206561]
2017-04-21 FridayNo New Comments

Last: 2014-04-10 16:55:46
-
Select from SQL

Analytic Functions: COUNT

Values in the RANGE window clause of analytics functions are measured relative to the current value, it is not like absolute positioning.

Author: Kim Berg Hansen [23906-2206538]
2017-04-21 FridayNo Comments
Last: No Comments
-
Select from SQL

Analytic Functions: SUM

Analytic functions do not aggregate, they show the output value on every row. When nesting analytic calls with inline views, you have to be careful that calculations not inadvertently include duplicated values.

Author: Kim Berg Hansen [23706-2192932]
2017-04-14 FridayNo New Comments

Last: 2017-04-09 18:23:04
-
SQL Deja Vu

Functions: Analytic Functions

Different analytic functions might be used in different ways to solve the same requirement. Different developers might think differently about how to solve a problem. Training yourself to use more analytics in more ways will help you both solving requirements in more optimal ways and understanding other developers code.

Author: Kim Berg Hansen [10003-2192933]
2017-04-14 FridayNo New Comments

Last: 2014-09-30 10:26:40
-
Select from SQL

Analytic Functions: RATIO_TO_REPORT

An analytic function like RATIO_TO_REPORT can be used on the results of regular aggregate functions. If the aggregation involves ROLLUP (or grouping sets or CUBE), you can use GROUPING functions in the analytic PARTITION clause to calculate the analytic function on the different grouping levels of the ROLLUP.

Author: Kim Berg Hansen [23486-2177101]
2017-04-07 FridayNo Comments
Last: No Comments
-
SQL Deja Vu

Hierarchical Query Operators: CONNECT_BY_ROOT

In hierarchical queries, CONNECT_BY_ROOT operator can fetch values from the top level row in each branch in the tree.

Author: Kim Berg Hansen [10763-2177102]
2017-04-07 FridayNo Comments
Last: No Comments
-
SQL Deja Vu

Aggregate Functions: COUNT

DISTINCT keyword can be added to many aggregate functions, including count, making the aggregate function work on the DISTINCT set of values rather than all values.

Author: Kim Berg Hansen [9526-2161742]
2017-03-31 FridayNo New Comments

Last: 2015-11-11 23:26:00
-
Select from SQL

Operators: Set Operators

The various SET operators (UNION, UNION ALL, MINUS, INTERSECT) can be combined to achieve disjunctive union (also known as symmetric difference.) Alternatives methods also exist.

Author: Kim Berg Hansen [23226-2161741]
2017-03-31 FridayNo Comments
Last: No Comments
-
  • 1 - 15
 
About Oracle | | Terms of Use | Your Privacy Rights | Copyright 2010-2017, Oracle Corporation