OCP-1Z0-051 第145题 子查询嵌套

一、原题

View the Exhibit and examine the structure of the CUSTOMERS table.

You issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'KING' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'KING':
SQL> SELECT cust_id, cust_last_name
       FROM customers
      WHERE country_id IN
            (SELECT country_id FROM customers WHERE cust_last_name = 'King')
        AND cust_credit_limit <
            (SELECT MAX(cust_credit_limit)
               FROM customers
              WHERE country_id IN (SELECT country_id
                                     FROM customers
                                    WHERE cust_last_name = 'King'));
Which statement is true regarding the outcome of the above query?
A. It executes and shows the required result.
B. It produces an error and the < operator should be replaced by < ALL to get the required output.
C. It produces an error and the < operator should be replaced by < ANY to get the required output.
D. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output.

答案：A

二、题目翻译

看下面的CUSTOMERS表的结构:
执行下面的SQL语句，显示用户信息：与last name为KING的客户有相同的country，并且用户的credit limit小于KING所在countries的最大的credit limit.
关于查询哪句话正确:
A.执行并显示所需结果。
B.报错，因为<操作符应该被<ALL替换才能得到所需结果。
C.报错，因为因为<操作符应该被<ANY替换才能得到所需结果。
D.报错，因为主查询中的WHERE子句中的IN操作符应该被=替换才能得到所需结果。

三、题目解析

先用子查询
SELECT country_id
   FROM customers
WHERE cust_last_name = 'King'
求出king所在的国家ID.
然后，再求出这个国家最大的credit_limit，最后求出和king国家相同，并且credit_limit小于king所在的国家的最大credit_limit的客户的相关信息。