Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004

    Unanswered: Answers needed for these multiple choice questions

    Question 1) Can you use the functions nvl and decode as well as the CASE statement interchangeably?

    A Only NVL with DECODE and vice versa
    B Only Decode with CASE statement and vice versa.
    C NVL with Decode or CASE statement as well as Decode and Case Statement interchangeably
    D All three can be used interchangeably

    Question 2) An inner join

    A Is preferred if it meets business requirements
    B Requires a (+) sign in the sql syntax for Oracle
    C Should never be used on dimensional metrics
    D A and B
    E A and C only

    Question 3) You can use a Sub-select in a query after

    A The FROM clause
    B The IN clause
    C The '=' operator
    D All of the above
    E All of the above and more

    Question 4) Many to Many relationships:

    A Result in double-counting
    B May be resolved in different ways but using a relationship table is always preferred.
    C Should be avoided as far as possible for simplicity
    D All of the above
    E A and C only

    Question 5) Views:

    A Often minimize re-work during the development or integration phase
    B Can be used to implement security
    C Can be materialized in recent versions of Oracle
    D None of the above
    E All of the above


  2. #2
    Join Date
    Sep 2003
    Virginia, USA
    1. No, they are not interchangable. Consider they accept different parameters, so you cannot simply replace NVL with DECODE. CASE has no parameters but has statement syntax.
    2. When it is correct for the given problem.
    3. Read the book. See inline views and nested queries.
    4. M2M are normal. Only causes problems when used incorrectly.
    5. ...
    A is subjective.
    B is true, but not really a good solution if you have strong security requirements - any DBA can still see the confidential data.
    C is not completely true because a materialized view is actually a table and not a view.
    As far as tests go they probably want you to believe the answer is "all of the above."
    Author, Oracle Database 10g: From Nuts to Soup

  3. #3
    Join Date
    Feb 2004
    Thank you.
    And yes, I looked at the sub selects and inline views as suggested for the Question 3)

    What I found out is that the answer could be E

    Because apart from FROM, IN and = operator, a select can appear even in a SELECT clause. I mean in a scalar subquery a select can be like --

    SELECT last_name, department_id, (SELECT MAX(salary) from employees sq
    where sq.department_id = e.department_id) HSAL from employees e
    where last_name like 'R%';

    Hence the belief that E could be true for Question 3)
    Please correct me if I am wrong. Thanks again

  4. #4
    Join Date
    Jun 2004
    Liverpool, NY USA
    This looks a lot like homework!!
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2004
    yeah, it looks like homework, but at least he finally read a manual and came up with a correct answer on his own. Yes, subqueries can be used in "all of the above, and more": select, from, where, having, start with, connect by
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts