Hail to the Victors

Blogonaut: Hail to the Victors: Debate re:Exam Question #36

Blogonaut: Hail to the Victors: Debate re:Exam Question #36

Thank you!

October 31, 2004 in Exam | Permalink | Comments (0) | TrackBack (0)

Blogonaut: Hail to the Victors: Debate re:Exam Question #36

Blogonaut: Hail to the Victors: Debate re:Exam Question #36

The union query yields the same output as query II. In oracle, the union operation will remove any duplicate rows automatically, therefore customer #608 appears only once in the results. Shouldn't the answer be "a" - some of the queries are equivalent?

October 28, 2004 in Exam | Permalink | Comments (0) | TrackBack (0)

Debate re:Exam Question #36

Not to hammer the JOIN vs. IN topic to death, but I have a question regarding number 36 on the exam...

Upon typing parts I and II into jedit and running the files in Oracle, I found that the results are similar, but not the same. The following is the output that I received...

Output for part I

CUS CUSTOMER_NAME
--- -----------------------------------
408 The Everything Shop
282 Brookings Direct
608 Johnson's Department Store
148 Al's Appliance and Sport
608 Johnson's Department Store


Output for part II

CUS CUSTOMER_NAME
--- -----------------------------------
148 Al's Appliance and Sport
282 Brookings Direct
408 The Everything Shop
608 Johnson's Department Store

The main difference stems from the fact that customer #608 appears twice in the first set of output results. Here is my theory as to why these results differ…

The first query says to join the tables and then to display all rows where the customer number in the orders table matches a customer number in the customers table, with the exception of those orders that were placed on October 20th. Since customer #608 placed two orders on October 23rd, the results display customer #608 twice. The join query isn’t sophisticated enough to display unique customers only because we are considering both tables with an equal weighting.

On the other hand, the second set of output results yields only unique customers. I would argue that this is due to the structure of the query. If we start off by considering the inner part of the query… and look at the customer numbers in the orders table that have orders outstanding for all days other than October 20th, this limits our results to customer #s 408, 282, 608, 148, and 608. Once we have this list of customer numbers, we then need to trace them back up to the customer table. Since the customer numbers are only listed once in the customer table and the query says to display the customer name and customer number from the customer table, each customer only has the potential to appear once in the results, even though some customers, such as #608, may have more than one order outstanding in the system. Because we have to evaluate this query in different steps it is different than the first query, which seems to have a flatter structure.

Therefore, I would argue that only some of the queries are equivalent... queries II and III are equivalent but I and II are not. If you’ve read this far, you may think that I am completely crazy... am I?

October 27, 2004 in Exam | Permalink | Comments (0) | TrackBack (2)

My Photo

About

Recent Posts

  • Google improves itself yet again...
  • Blogging on blogging…
  • Blogonaut: Hail to the Victors: So, I stand corrected...
  • So, I stand corrected...
  • Oracle Question
  • DocuShare
  • Don’t read XSLT before putting up the Christmas tree...
  • Comply or else...
  • Blogs as the catalyst for promoting bottom-up change
  • Making connections

Categories

  • Class Issues (13)
  • Code Issues (1)
  • Exam (3)
  • Information Business (12)
  • Project Issues (3)
  • Weblogs (1)
See More
Subscribe to this blog's feed
Blog powered by Typepad