Oracle Database 12c: SQL Fundamentals — Question 36
Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME -
---------------------
Lex De Haan -
Renske Ladwig -
Jose Manuel Urman -
Jason Mallin -
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
CUST NAME -
---------------------
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)
Answer options
- A. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ', 1, 2)<>0;
- B. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name), '*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ', -1, 2)<>0;
- C. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ''), '*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ', -1, -2)<>0;
- D. SELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')), LENGTH(cust_name)- INSTR(cust_name, ' '), '*') "CUST NAME" FROM customers WHERE INSTR(cust_name, ' ', 1, 2)<>0 ;
Correct answer: A, B
Explanation
Options A and B correctly identify customer names with three names by checking for the presence of a second space in the CUST_NAME. Option A uses the correct approach for identifying the position of the second space, while option B incorrectly checks for a space in a negative position, which would not yield the correct results. Options C and D do not properly determine the conditions needed to filter the names correctly.