View the Exhibit and examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables. 
You need to generate a report showing the promo name along with the customer name for all
products that were sold during their promo campaign and before 30th October 2007.
You issue the following query:
Which statement is true regarding the above query?
A.
It executes successfully and gives the required result.
B.
It executes successfully but does not give the required result.
C.
It produces an error because the join order of the tables is incorrect.
D.
It produces an error because equijoin and nonequijoin conditions cannot be used in the same
SELECT statement.
Can somebody please explain why this query executes successfully but does not give the required result?
On where, the filter needs TO_DATE function on ’30-oct-2007′ to apply correctly.
plz explain
Tested and ORA-00918: column ambigously defined…so not B
Check http://blog.csdn.net/rlhua/article/details/12925007
Please, can anybody explain the reason for not giving required result?
B IS CORRECT BECAUSE
JOIN MISSING ON (P.PROMO_ID=S.PROMO_ID)
Hi,
I have tried to run this query with JOIN on (p.promo_id=s.promo_id ) – it worked.
but it worked without adding join condition as well. Can anyone give proper explanation of this answer..
i think A is correct
i think the trick is that the primary key is promotion id but not the promotion date. on a particular date, may have more than one effective promotions. Need to use promotion id to join sales and promotion tables to correctly locate the exact promotion used by the sales.
Well my way looking at it,
If a join does not involve a primary and foreign key then it might lead to cartesian join.
The first join is just a join between a given promo campaign and ALL(!) the products that were sold during that promo period.
Would this query work and give the required result?
SELECT promo_name, cust_name
FROM sales s JOIN customer c
ON (s.cust_id = c.cust_id) AND s.time_id < TO_DATE('30-oct-2007')
JOIN promotions p
ON (s.promo_id = p.promo_id)
Thank you
FDJGJSDL
ithink that b is correct.Explanation for those, who wonder.
Just consider first join condition. Promotions and sales are joined only on time_id. If we have two rows sales for different promos but with same time_id and two rows in promos with identical BEGIN_DATE and END_DATE. So the outcome would give us 4 rows. 2 of them wold be as required and other two woulb be incorrect
Can somebody give the explanation of the answer in simple and correct terminology..??