Explanation
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2012.htm#SQLRF00817
https://docs.oracle.com/cd/A84870_01/doc/server.816/a76989/ch26.htm

INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.
The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
References:
http://oraclexpert.com/using-the-set-operators/

To find customers whose last names start with "Mc" or "MC", we need to ensure our SQL query correctly identifies and compares these prefixes regardless of case variations. Let's analyze the given options:
Option B: WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC%') This clause uses UPPER to convert both the extracted substring (starting just after the first space, assuming it indicates the start of the last name) and the comparison string 'MC%' to uppercase. This ensures case-insensitive comparison. The LIKE operator is used to match any last names starting with "MC", which will correctly capture both "Mc" and "MC". This option is correct.
Option C: WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%' This clause applies INITCAP to the substring, which capitalizes the first letter of each word and makes other letters lowercase. The result is compared to 'Mc%', assuming only the last name follows the space. This approach will match last names starting with "Mc" (like "McEwen"), but not "MC". However, considering we're looking for "Mc" specifically, this clause works under the assumption that "Mc" is treated as proper capitalization for these last names. Thus, it can also be considered correct, albeit less inclusive than option B.
The other options either use incorrect syntax or apply case-sensitive matches without ensuring that both "Mc" and "MC" are captured:
Option A: Contains syntax errors (unmatched quotes and wrong use of IN).
Option D: Uses case-sensitive match without combining both "Mc" and "MC".
Option E: Only matches "Mc", which is too specific.