ILP Oracle Prelims 2

1. 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?

4

8

10

6

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.

Explanation

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.

2. 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?

250

500

750

1000

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.

Explanation

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.

3. Which SQL statement will not display the output : $1,890.55

SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;

SELECT TO_CHAR(1890.55,'$9,999G99') FROM DUAL;

SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;

SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

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.

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.

4. 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?

Var2 NUMBER := 0

WHERE name = 'JAYA'

Var1 :=var2 + 2000

There are no errors in this PL/SQL block

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.

Explanation

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.

5. What is the output of the below query: select instr('120000 12 0 .125',1,'.') from dual;

13

Compilation error

Syntax error

None of the above

not-available-via-ai

Explanation

not-available-via-ai

6. 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;

No data found

No employees in department!

Compilation Error

Displays the total count of the employees in the department

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!".

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!".

7. Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?

SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth" of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

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;

SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspthfm "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;

SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY), 'fmDdspth "of" Month YYYYfmtHH:HI:SS AM') NEW_DATE FROM dual;

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.

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.

8. SELECT '"' || RPAD(RTRIM(LTRIM('!!ILP!!','!'),'!'),2,'!') || '"' FROM DUAL; What is the output of the above statement?

"IL"

"ILP"

The statement generates an error.

"ILP!!"

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".

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".

Ilp Oracle Prelims 2

1. 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?

2.

What first name or nickname would you like us to use?

2. 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?

3. Which SQL statement will not display the output : $1,890.55

4. 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?

5. What is the output of the below query: select instr('120000 12 0 .125',1,'.') from dual;

7. Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?

8. SELECT '"' || RPAD(RTRIM(LTRIM('!!ILP!!','!'),'!'),2,'!') || '"' FROM DUAL; What is the output of the above statement?