118. View the Exhibit and examine the structure of the PROMOTIONS table.
Examine the following two SQL statements:
Statement 1
SQL>SELECT promo_category,SUM(promo_cost)
FROM promotions
WHERE promo_end_date-promo_begin_date > 30
GROUP BY promo_category;
Statement 2
SQL>SELECT promo_category,sum(promo_cost)
FROM promotions
GROUP BY promo_category
HAVING MIN(promo_end_date-promo_begin_date)>30;
Which statement is true regarding the above two SQL statements?
A. statement 1 gives an error, statement 2 executes successfully
B. statement 2 gives an error, statement 1 executes successfully
C. statement 1 and statement 2 execute successfully and give the same output
D. statement 1 and statement 2 execute successfully and give a different output
答案解析：
sh@TESTDB> select promo_category,SUM(promo_cost)
2 from promotions
3 where promo_end_date-promo_begin_date>30
4 group by promo_category;
PROMO_CATEGORY SUM(PROMO_COST)
------------------------------ ---------------
ad news 0
internet 3703800
magazine 2889600
flyer 2079800
newspaper 5113600
post 3031500
TV 4850500
radio 1095000
8 rows selected.
sh@TESTDB> select promo_category,SUM(promo_cost)
2 from promotions
3 group by promo_category
4 having min(promo_end_date-promo_begin_date)>30;
PROMO_CATEGORY SUM(PROMO_COST)
------------------------------ ---------------
ad news 0
radio 1095000
两个语句都能执行成功。
找所有PROMO_CATEGORY 中的promo_end_date-promo_begin_date大于30的所有的promo_cost的按promo_category分组求和
sh@TEST0910> select promo_category,SUM(promo_cost),promo_end_date-promo_begin_date
2 from promotions
3 where promo_end_date-promo_begin_date>30
4 group by promo_category,promo_end_date-promo_begin_date
5 order by promo_category;
PROMO_CATEGORY SUM(PROMO_COST) PROMO_END_DATE-PROMO_BEGIN_DATE
------------------------------ --------------- -------------------------------
TV 1267200 31
TV 1333500 61
TV 254700 62
TV 904200 90
TV 308000 91
TV 782900 92
ad news 0 214
flyer 680000 31
flyer 450300 61
flyer 214500 62
flyer 260700 90
flyer 240200 91
flyer 234100 92
internet 589800 31
internet 1195200 61
internet 249000 62
internet 568500 90
internet 398200 91
internet 703100 92
magazine 514900 31
magazine 781300 61
magazine 144000 62
magazine 404700 91
magazine 1044700 92
newspaper 1361500 31
newspaper 1325800 61
newspaper 664700 62
newspaper 473800 90
newspaper 707600 91
newspaper 580200 92
post 838800 31
post 587900 61
post 277300 62
post 156300 90
post 296100 91
post 875100 92
radio 465700 31
radio 145900 61
radio 275800 62
radio 137200 91
radio 70400 92
41 rows selected.
min(promo_end_date-promo_begin_date)>30，找所有PROMO_CATEGORY 中最小的promo_end_date-promo_begin_date，大于30。最小值不大于30，丢掉。
从下面看，TV,flyer ,internet ,magazine ,newspaper,post最小值均为29，不满足条件。
radio 最小值是31满足大于31的条件，ad news 最小值是214，满足大于31的条件，故只有两个值。
sh@TEST0910> select promo_category,SUM(promo_cost),promo_end_date-promo_begin_date
2 from promotions
3 group by promo_category,promo_end_date-promo_begin_date
4 order by promo_category;
PROMO_CATEGORY SUM(PROMO_COST) PROMO_END_DATE-PROMO_BEGIN_DATE
------------------------------ --------------- -------------------------------
NO PROMOTION 0 0
TV 430600 29
TV 38900 30
TV 1267200 31
TV 1333500 61
TV 254700 62
TV 904200 90
TV 308000 91
TV 782900 92
ad news 0 214
flyer 83600 29
flyer 680000 31
flyer 450300 61
flyer 214500 62
flyer 260700 90
flyer 240200 91
flyer 234100 92
internet 526700 29
internet 25700 30
internet 589800 31
internet 1195200 61
internet 249000 62
internet 568500 90
internet 398200 91
internet 703100 92
magazine 408500 29
magazine 104600 30
magazine 514900 31
magazine 781300 61
magazine 144000 62
magazine 404700 91
magazine 1044700 92
newspaper 406900 29
newspaper 160000 30
newspaper 1361500 31
newspaper 1325800 61
newspaper 664700 62
newspaper 473800 90
newspaper 707600 91
newspaper 580200 92
post 196700 29
post 84900 30
post 838800 31
post 587900 61
post 277300 62
post 156300 90
post 296100 91
post 875100 92
radio 465700 31
radio 145900 61
radio 275800 62
radio 137200 91
radio 70400 92
53 rows selected.