OCP-1Z0-051 第160题 insert中使用子查询

一、原题

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

There is only one customer with the cust_last_name column having value Roberts.
Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?
A.INSERT INTO orders
  VALUES
    (1,
     '10-mar-2007',
     'direct',
     (SELECT customer_id
        FROM customers
       WHERE cust_last_name = 'Roberts'
         AND credit_limit = 600),
     1000);

B.INSERT INTO orders
  (order_id,
   order_date,
   order_mode,
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   order_total)
VALUES
  (1, '10-mar-2007', 'direct', &&customer_id, 1000);

C.INSERT INTO
  (SELECT o.order_id,
          o.order_date,
          o.order_mode,
          c.customer_id,
          o.order_total
     FROM orders o, customers c
    WHERE o.customer_id = c.customer_id
      AND c.cust_last_name = 'Roberts' ANDc.credit_limit = 600)
VALUES
  (1,
   '10-mar-2007',
   'direct',
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   1000);

D.INSERT INTO orders
  (order_id,
   order_date,
   order_mode,
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   order_total)
VALUES
  (1, '10-mar-2007', 'direct', &customer_id, 1000);

答案：A

二、题目翻译

查看ORDERS and CUSTOMERS 表的结构：
只有一个customer的cust_last_name列的值是Roberts，
哪一个INSERT语句能被用于给ORDERS添加一行，customer的cust_last_name为Roberts，并且CREDIT_LIMIT为600？

三、题目解析

A选项正确，因为题目说明了，只有一个customer的cust_last_name列的值是Roberts，所以，子查询
   SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600
只有一个返回值，这里就能插入成功。
BCD选项语法不正确，没有这样的用法。