Ilp Oracle Prelims 2
Oracle Prelims 2
- 1.
What is the output of the below query: select instr('120000 12 0 .125',1,'.') from dual;
- A.
13
- B.
Compilation error
- C.
Syntax error
- D.
None of the above
Correct Answer
C. Syntax error -
- 2.
Which SQL statement will not display the output : $1,890.55
- A.
SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
- B.
SELECT TO_CHAR(1890.55,'$9,999G99') FROM DUAL;
- C.
SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
- D.
SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
Correct Answer
B. SELECT TO_CHAR(1890.55,'$9,999G99') FROM DUAL;Explanation
The correct answer is SELECT TO_CHAR(1890.55,'$9,999G99') FROM DUAL; because the format mask '$9,999G99' does not match the value 1890.55. The 'G' format specifier is used to group digits, but in this case, there is no grouping specified in the format mask. Therefore, the output will not display the value as $1,890.55.Rate this question:
-
- 3.
Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?
- A.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth" of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
- B.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth"of" Month YYYY fmHH:MI:SS AM') NEW_DATE HH:MI:SS AM') NEW _ DATE FROM dual;
- C.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspthfm "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;
- D.
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY), 'fmDdspth "of" Month YYYYfmtHH:HI:SS AM') NEW_DATE FROM dual;
Correct Answer
A. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth" of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;Explanation
The correct answer is the first option: SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth" of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual. This statement uses the TO_CHAR function to convert the date '19-Mar-2001' into the desired format. The 'fmDdspth" of" Month YYYY fmHH:MI:SS AM' format model specifies that the day should be displayed as the ordinal number (e.g., Nineteenth), followed by "of" and the month name (e.g., March), followed by the four-digit year, and finally the time in 12-hour format with AM/PM indicator.Rate this question:
-
- 4.
SELECT '"' || RPAD(RTRIM(LTRIM('!!ILP!!','!'),'!'),2,'!') || '"' FROM DUAL; What is the output of the above statement?
- A.
"IL"
- B.
"ILP"
- C.
The statement generates an error.
- D.
"ILP!!"
Correct Answer
A. "IL"Explanation
The given SQL statement concatenates the string '!!ILP!!' with two exclamation marks on each side, trims any leading and trailing exclamation marks, and then pads the resulting string with exclamation marks on the right side to make it a length of 2. Finally, the resulting string is enclosed in double quotes. The output of the statement will be "IL" because the resulting string after trimming and padding will be "IL".Rate this question:
-
- 5.
Evaluate this IF statement: IF v_value > 100 THEN v_new_value := 2 * v_value; ELSIF v_value > 200 THEN v_new_value := 3 * v_value; ELSIF v_value < 300 THEN v_new_value := 4 * v_value; ELSE v_new_value := 5 * v_value; END IF; What would be assigned to V_NEW_VALUE if V_VALUE is 250?
- A.
250
- B.
500
- C.
750
- D.
1000
Correct Answer
B. 500Explanation
If the value of v_value is 250, the condition v_value > 100 is true, but v_value > 200 is false. Therefore, the code will execute the block under the ELSIF v_value > 200 THEN condition. This block assigns v_new_value as 3 times the value of v_value, which is 3 * 250 = 750. So, the value assigned to v_new_value if v_value is 250 would be 750.Rate this question:
-
- 6.
Evaluate this PL/SQL block: BEGIN FOR i IN 1..10 LOOP IF i = 4 OR i = 6 THEN null; ELSE INSERT INTO test(results) VALUES (I); END IF; COMMIT; END LOOP; ROLLBACK; END; How many values will be inserted into the TEST table?
- A.
4
- B.
8
- C.
10
- D.
6
Correct Answer
B. 8Explanation
The PL/SQL block contains a loop that iterates from 1 to 10. However, when the loop variable "i" is equal to 4 or 6, the block executes a null statement, which means nothing is inserted into the TEST table. For all other values of "i", an INSERT statement is executed, inserting the value of "i" into the TEST table. Since "i" can take values from 1 to 10, but skips 4 and 6, a total of 8 values will be inserted into the TEST table.Rate this question:
-
- 7.
DECLARE var1 CONSTANT NUMBER := 50; var2 NUMBER := 0; BEGIN SELECT eno INTO var2 FROM employee WHERE name = 'JAYA'; var1 :=var2 + 2000; END; Which of the following lines in this block of PL/SQL code will produce an error?
- A.
Var2 NUMBER := 0
- B.
WHERE name = 'JAYA'
- C.
Var1 :=var2 + 2000
- D.
There are no errors in this PL/SQL block
Correct Answer
C. Var1 :=var2 + 2000Explanation
The line "var1 := var2 + 2000" will produce an error because the variable "var1" is declared as a constant and cannot be reassigned a new value.Rate this question:
-
- 8.
What is the output of the following code snippet if there are no employees in department number 15: declare total_count INTEGER := 0; BEGIN SELECT COUNT(*) INTO total_count FROM employee WHERE deptno = 15; IF total_count = 0 THEN DBMS_OUTPUT.PUT_LINE ('No employees in department!'); ELSE DBMS_OUTPUT.PUT_LINE ('Count of employees in dept 15 = ' || TO_CHAR (total_count)); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('No data found'); END;
- A.
No data found
- B.
No employees in department!
- C.
Compilation Error
- D.
Displays the total count of the employees in the department
Correct Answer
B. No employees in department!Explanation
The code snippet first declares a variable total_count and initializes it to 0. It then executes a SELECT statement to count the number of rows in the employee table where deptno is 15 and stores the result in total_count. If total_count is equal to 0, it prints "No employees in department!" using the DBMS_OUTPUT.PUT_LINE function. Otherwise, it prints "Count of employees in dept 15 = " followed by the value of total_count. Since there are no employees in department number 15, the condition total_count = 0 is true and the code will output "No employees in department!".Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 20, 2023Quiz Edited by
ProProfs Editorial Team -
Sep 12, 2012Quiz Created by
Srikanth_kanthet
Back to top