p where sal<2000);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-02-81 1600 160 30
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-09-81 1500 0 30
如果是not in 需要对子查询结果中的空值做处理，否则会全部返回空
147. View the Exhibitand examine the structure of the PROMOTIONS table.
eva luate the followingSQL statement:
SQL>SELECTpromo_name,CASE
WHEN promo_cost>=(SELECT AVG(promo_cost)
FROM promotions
WHEREpromo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
FROM promotions;
Which statement is trueregarding the outcome of the above query
A. It shows COST_REMARKfor all the promos in the table.
B. It produces an errorbecause the subquery gives an error.
C. It shows COST_REMARKfor all the promos in the promo category 'TV'.
D. It produces an errorbecause subqueries cannot be used with the CASE expression.
Answer: A
解析：
CASE
WHEN promo_cost>=(SELECT AVG(promo_cost)
FROM promotions
WHEREpromo_category='TV')
then 'HIGH'
else 'LOW'
END COST_REMARK
如果大于显示为 HIGH 否则显示为LOW 所以所有的promos将会显示出来
148. View the Exhibitand examine the structure of the PRODUCTS tables.
You want to generate areport that displays the average list price of product categories where theaverage
list price is less thanhalf the maximum in each category.
Which query would give thecorrect output
A. SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) < ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
B. SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) > ANY
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
C. SELECTprod_category,avg(prod_list_price)
FROM products
HAVINGavg(prod_list_price) < ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
D. SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) > ANY
(SELECTmax(prod_list_price)/2
FROM products);
Answer: A
解析：
题意：generate a reportthat displays the average list price of product categorieswhere the average list price is less than half the maximum in each category.
average listprice is less than half the maximum in each category.
avg(prod_list_price)< ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
e average listprice of product categories
需要以类别分组
所以：
SELECTprod_category,avg(prod_list_price)
FROM products
GROUP BY prod_category
HAVINGavg(prod_list_price) < ALL
(SELECTmax(prod_list_price)/2
FROM products
GROUP BY prod_category);
149. View the Exhibitsand examine the structures of the COSTS and PROMOTIONS tables.
eva luate the followingSQL statement:
SQL> SELECT prod_idFROM costs
WHERE promo_id IN(SELECT promo_id FROM promotions
WHERE promo_cost
(SELECT MAX(promo_cost)FROM promotions
GROUP BY(promo_end_datepromo_
begin_date)));
What would be theoutcome of the above SQL statement
A. It displays prod IDsin the promo with the lowest cost.
B. It displays prod IDsin the promos with the lowest cost in the same time interval.
C. It displays prod IDsin the promos with the highest cost in the same time interval.
D. It displays prod IDsin the promos with cost less than the highest cost in the sa