OCP-1Z0-051 第82题 like模糊查询

一、原题

Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
       FROM customers
    WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
       FROM customers
   WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
        FROM customers
     WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
        FROM customers
     WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

答案：B

二、题目翻译

下面是CUSTOMERS表CUST_NAME列的数据，
要显示第二个名字以"Mc" or "MC"开头的员工第二个名字（第二个名字，也就是空格后的名字）

三、题目解析

A选项不正确，where条件='Mc',没有第二个名字是Mc的人，所以没结果，而且题目要求包含MC或Mc,而不是等于。
B选项正确，INSTR找出空格的位置，然后用SUBSTR从空格后开始找，把找出的结果首字母大写，然后用like模糊查询。
C选项不正确，SUBSTR查找出的结果和INITCAP('MC%')匹配，只能匹配Mc，而无法匹配MC。
D选项不正确，模糊查询，不能用等号。

四、测试
      
      以下是ABCD四个选项的测试结果:

SQL> with  customers as
  2  ( select 'Renske Ladwig' CUST_NAME from dual
  3    union all
  4    select 'Jason Mallin' CUST_NAME from dual
  5    union all
  6    select 'Samuel McCain' CUST_NAME from dual
  7    union all
  8    select 'Allan MCEwen' CUST_NAME from dual
  9    union all
 10    select 'Irene Mikkilineni' CUST_NAME from dual
 11    union all
 12    select 'Julia Nayer' CUST_NAME from dual
 13  )
 14  SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
 15  FROM customers
 16  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';

no rows selected

SQL> with  customers as
  2  ( select 'Renske Ladwig' CUST_NAME from dual
  3    union all
  4    select 'Jason Mallin' CUST_NAME from dual
  5    union all
  6    select 'Samuel McCain' CUST_NAME from dual
  7    union all
  8    select 'Allan MCEwen' CUST_NAME from dual
  9    union all
 10    select 'Irene Mikkilineni' CUST_NAME from dual
 11    union all
 12    select 'Julia Nayer' CUST_NAME from dual
 13  )
 14  SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
 15  FROM customers
 16  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';

SUBSTR(CUST_NAME,INSTR(CUST_NAME,'')+1)
-------------------------------------------------------------------------
McCain
MCEwen

SQL> with  customers as
  2  ( select 'Renske Ladwig' CUST_NAME from dual
  3    union all
  4    select 'Jason Mallin' CUST_NAME from dual
  5    union all
  6    select 'Samuel McCain' CUST_NAME from dual
  7    union all
  8    select 'Allan MCEwen' CUST_NAME from dual
  9    union all
 10    select 'Irene Mikkilineni' CUST_NAME from dual
 11    union all
 12    select 'Julia Nayer' CUST_NAME from dual
 13  )
 14  SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
 15  FROM customers
 16  WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');

SUBSTR(CUST_NAME,INSTR(CUST_NAME,'')+1)
--------------------------------------------------------------------------
McCain

SQL> with  customers as
  2  ( select 'Renske Ladwig' CUST_NAME from dual
  3    union all
  4    select 'Jason Mallin' CUST_NAME from dual
  5    union all
  6    select 'Samuel McCain' CUST_NAME from dual
  7    union all
  8    select 'Allan MCEwen' CUST_NAME from dual
  9    union all
 10    select 'Irene Mikkilineni' CUST_NAME from dual
 11    union all
 12    select 'Julia Nayer' CUST_NAME from dual
 13  )
 14   SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
 15  FROM customers
 16  WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

no rows selected