OCP-1Z0-051 第141题 子查询的应用

一、原题

View the Exhibit and examine the structure of CUSTOMERS and GRADES tables.

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A.SELECT custname, grade
      FROM customers, grades
   WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval;

B.SELECT custname, grade
      FROM customers, grades
   WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval
         AND cust_credit_limit BETWEEN startval AND endval;

C.SELECT custname, grade
       FROM customers, grades
    WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers)
         AND cust_credit_limit BETWEEN startval AND endval;

D. SELECT custname, grade
        FROM customers, grades
     WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers)
           AND MAX(cust_credit_limit) BETWEEN startval AND endval;

答案：BC

二、题目翻译

看下面CUSTOMERS and GRADES表的结构
现在要显示有最高credit limit的用户的名称和等级
哪两个SQL语句能得出想要的结果?（选择两项）

三、题目解析

要判断等级，就要判断是否在表中的最高值和最低值之间，就要使用的非等值连接，这里是BETWEEN...AND
A选项不正确，不符合题目要求。
D选项不正确，有语法错误，WHERE子句里不能使用聚合函数。