147. View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is true regarding the outcome of the above query?
A. It shows COST_REMARK for all the promos in the table.
B. It produces an error because the subquery gives an error.
C. It shows COST_REMARK for all the promos in the promo category 'TV'.
D. It produces an error because subqueries cannot be used with the CASE expression.
该子查询是在select后 from 前,这种叫标量子查询。
Answer: A
Evaluate the following SQL statement:
SQL>SELECT promo_name,CASE
WHEN promo_cost >=(SELECT AVG(promo_cost)
FROM promotions
WHERE promo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is true regarding the outcome of the above query?
A. It shows COST_REMARK for all the promos in the table.
B. It produces an error because the subquery gives an error.
C. It shows COST_REMARK for all the promos in the promo category 'TV'.
D. It produces an error because subqueries cannot be used with the CASE expression.
该子查询是在select后 from 前,这种叫标量子查询。
有匹配数据时显示一个值,没有数据时显示null
不影响主查询的行数
SQL> SELECT promo_name,
2 CASE
3 WHEN promo_cost >= (SELECT AVG(promo_cost)
4 FROM sh.promotions
5 WHERE promo_category = 'TV') THEN
6 'HIGH'
7 ELSE
8 'LOW'
9 END cost_remark
10 FROM sh.promotions
11 WHERE rownum <= 5;
PROMO_NAME COST_REMARK
------------------------------ -----------
NO PROMOTION # LOW
newspaper promotion #16-108 LOW
post promotion #20-232 LOW
newspaper promotion #16-349 LOW
internet promotion #14-471 LOW
5 rows selected
SQL> SELECT COUNT(*)
2 FROM (SELECT promo_name,
3 CASE
4 WHEN promo_cost >=
5 (SELECT AVG(promo_cost)
6 FROM sh.promotions
7 WHERE promo_category = 'TV') THEN
8 'HIGH'
9 ELSE
10 'LOW'
11 END cost_remark
12 FROM sh.promotions);
COUNT(*)
----------
503
1 row selected
SQL> SELECT COUNT(*) FROM sh.promotions;
COUNT(*)
----------
503
1 row selected
Answer: A