一、原题
二、题目翻译
三、题目解析
View the Exhibit and examine the structure of the PRODUCTS table.

Evaluate the following query:
SQL> SELECT prod_name
FROM products
WHERE prod_id IN
(SELECT prod_id
FROM products
WHERE prod_list_price =
(SELECT MAX(prod_list_price)
FROM products
WHERE prod_list_price <
(SELECT MAX(prod_list_price) FROM products)));
What would be the outcome of executing the above SQL statement?
A. It produces an error.
B. It shows the names of all products in the table.
C. It shows the names of products whose list price is the second highest in the table.
D. It shows the names of all products whose list price is less than the maximum list price.
答案:C
Evaluate the following query:
SQL> SELECT prod_name
FROM products
WHERE prod_id IN
(SELECT prod_id
FROM products
WHERE prod_list_price =
(SELECT MAX(prod_list_price)
FROM products
WHERE prod_list_price <
(SELECT MAX(prod_list_price) FROM products)));
What would be the outcome of executing the above SQL statement?
A. It produces an error.
B. It shows the names of all products in the table.
C. It shows the names of products whose list price is the second highest in the table.
D. It shows the names of all products whose list price is less than the maximum list price.
答案:C
二、题目翻译
看下面PRODUCTS表的结构:
评估下面的语句
上面的SQL语句执行后会出现什么结果?
A.报错。
B.显示所有产品的名称。
C.显示表中价格第二高的产品名称。
D.显示所有小于最高价格的产品名称。
评估下面的语句
上面的SQL语句执行后会出现什么结果?
A.报错。
B.显示所有产品的名称。
C.显示表中价格第二高的产品名称。
D.显示所有小于最高价格的产品名称。
三、题目解析
先求出最里层的子查询SELECT MAX(prod_list_price) FROM products,得出的是最高的产品价格;
紧接着外面的子查询,
SELECT MAX(prod_list_price)
FROM products
WHERE prod_list_price < (SELECT MAX(prod_list_price) FROM products)
求出的是小于最大价格的最大价格,也就是第二高的价格;
然后再求出这个产品的ID,进一步再求出产品的名称。
紧接着外面的子查询,
SELECT MAX(prod_list_price)
FROM products
WHERE prod_list_price < (SELECT MAX(prod_list_price) FROM products)
求出的是小于最大价格的最大价格,也就是第二高的价格;
然后再求出这个产品的ID,进一步再求出产品的名称。