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:
SQL> SELECT promo_name,cust_name
FROM promotions p JOIN sales s
ON(time_id BETWEEN promo_begin_date AND promo_end_date)
JOIN customer c
ON (s.cust_id = c.cust_id)
AND time_id < '30-oct-2007';
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.
答案：B
二、题目翻译
查看PROMOTIONS、SALES和 CUSTOMER表的结构:
要生成一个报表，显示所有产品的promo name和customer name，销售的产品是在促销活动期间，并且在2007年10月30日以前。
执行下面的查询
关于上面的查询哪句话是正确的？
A.执行成功，并给出正确结果。
B.执行成功，但不能给出正确结果。
C.报错，因为表的连接顺序不正确。
D.报错，因为等值连接与非等值连接不能用在同一个SELECT语句中。
三、题目解析
这个sql虽然执行成功，但是结果不正确， promotions和sales表之间应该还有一个关联条件是promo_id相等。
四、测试
SQL> create table promotions(
2 PROMO_ID NUMBER(2) NOT NULL,
3 promo_name varchar2(10),
4 promo_cat varchar2(10),
5 promo_cost number(8,2),
6 promo_begin_date date,
7 promo_end_date date
8 );
Table created.
SQL> create table sales(
2 prod_id number(3) not null,
3 promo_id number(3) not null,
4 time_id date,
5 qty_sold number(6,2),
6 cust_id number(2) not null
7 );
Table created.
SQL> create table customer(
2 cust_id number(3) not null,
3 cust_name varchar2(20),
4 cust_address varchar(30)
5 );
Table created.
SQL> insert into promotions values(1,'First','action1',500,to_date('2007-1-5','yyyy-mm-dd'),to_date('2007-1-30','yyyy-mm-dd'));
1 row created.
SQL> insert into promotions values(2,'Second','action2',800,to_date('2007-10-25','yyyy-mm-dd'),to_date('2007-11-5','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
SQL> insert into sales values(101,1,to_date('2007-1-10','yyyy-mm-dd'),2,21);
1 row created.
SQL> insert into sales values(102,2,to_date('2007-10-28','yyyy-mm-dd'),5,22);
1 row created.
SQL> insert into sales values(103,2,to_date('2007-11-2','yyyy-mm-dd'),3,23);
1 row created.
SQL> insert into sales values(104,3,to_date('2007-11-8','yyyy-mm-dd'),6,23);
1 row created.
SQL> insert into sales values(105,3,to_date('2007-10-28','yyyy-mm-dd'),4,21);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into customer values(21,'cust1','loc1');
1 row created.
SQL> insert into customer values(22,'cust2','loc2');
1 row created.
SQL> insert into customer values(23,'cust3','loc3');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from promotions;
PROMO_ID PROMO_NAME PROMO_CAT PROMO_COST PROMO_BEGIN_ PROMO_END_DA
---------- -------------------- -------------------- ---------- ------------ ------------
1 First action1 500 05-JAN-07 30-JAN-07
2 Second action2 800 25-OCT-07 05-NOV-07
3 Third action3 800 10-MAY-07 25-MAY-07
SQL> select * FROM SALES;
PROD_ID PROMO_ID TIME_ID QTY_SOLD CUST_ID
---------- ---------- ------------ ---------- ----------
101 1 10-JAN-07 2 21
102 2 28-OCT-07 5 22
103 2 02-NOV-07 3 23
104 3 08-NOV-07 6 23
105 3 28-OCT-07 4 21
SQL> select * from customer;
CUST_ID CUST_NAME CUST_ADDRESS
---------- ---------------------------------------- ------------------------------------------
21 cust1 loc1
22 cust2 loc2
23 cust3 loc3
SQL> SELECT promo_name,cust_name
2 FROM promotions p JOIN sales s
3 ON(time_id BETWEEN promo_begin_date AND promo_end_date)
4 JOIN customer c
5 ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';
PROMO_NAME CUST_NAME
-------------------- ----------------------------------------
Second cust1
First cust1
Second cust2
很明显，这里Second cust1不是在活动期间的，这条，其实就是sales表中的是最后一条记录，它的promo_id是3，但日期却不在这个区间之间，而是在promo_id=2的区间内。
105 3 28-OCT-07 4 21
改成下面这样，才正确了：
SQL> SELECT promo_name,cust_name
2 FROM promotions p JOIN sales s
3 ON((time_id BETWEEN promo_begin_date AND promo_end_date) and s.promo_id=p.promo_id)
4 JOIN customer c
5 ON (s.cust_id = c.cust_id) AND time_id < '30-oct-2007';
PROMO_NAME CUST_NAME
-------------------- ----------------------------------------
First cust1
Second cust2
表连接的用法，详见：
http://blog.csdn.net/holly2008/article/details/25704471