Which SQL statement will return the ENAME, length of th…

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
ENAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of
the letter “a” in the ENAME column, for those employees whose ENAME ends with a the letter “n”?

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
ENAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of
the letter “a” in the ENAME column, for those employees whose ENAME ends with a the letter “n”?

A.
SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ‘a’)
FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = ‘n’;

B.
SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1)
FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = ‘n’;

C.
SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1)
FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = ‘n’;

D.
SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1)
FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = ‘n’;

Explanation:
INSTR is a character function return the numeric position of a named string.
INSTR(NAMED,’a’)
Incorrect answer:
BDid not return a numeric position for `a’.
CDid not return a numeric position for `a’.
DDid not return a numeric position for `a’.



Leave a Reply 2

Your email address will not be published. Required fields are marked *


Deepak

Deepak

what is the difference in option C & D ?

Yana disconzi

Yana disconzi

SUBSTR – Selects a part of the string, whit positions
Position
When position is 0 (zero), then it is treated as 1.
When position is positive, then the function counts from the beginning of string to find the first character. EX: SUBSTR(‘ORACLE’,2,4) -> RACL ORACLE=1 O=0 R=1 A=2 C=3 L=4 E=5
When position is negative, then the function counts backward from the end of string.
EX: SUBSTR(‘ORACLE’,-1,1) -> E ORACLE=1 O=-5 R=-4 A=-3 C=-2 L=-1 E=0

INSTR – function returns the location of a substring in a string.
INSTR(‘Tech on the net’, ‘e’)
Result: 2 (the first occurrence of ‘e’)
INSTR(‘Tech on the net’, ‘e’, 1, 1)
Result: 2 (the first occurrence of ‘e’)
INSTR(‘Tech on the net’, ‘e’, 1, 2)
Result: 11 (the second occurrence of ‘e’)

INSTR(‘Tech on the net’, ‘e’, 1, 3)
Result: 14 (the third occurrence of ‘e’)