Ilp Oracle Prelims 2

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Srikanth_kanthet
S
Srikanth_kanthet
Community Contributor
Quizzes Created: 5 | Total Attempts: 905
| Attempts: 79
SettingsSettings
Please wait...
  • 1/8 Questions

    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
Please wait...
About This Quiz

ILP Oracle Prelims 2 is a quiz designed to assess knowledge in SQL and Oracle database management. It covers SQL queries, PL\/SQL blocks, and format functions, testing practical skills essential for database professionals.

Ilp Oracle Prelims 2 - Quiz

Quiz Preview

  • 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

    Correct Answer
    A. 500
    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.

    Rate this question:

  • 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;

    Correct Answer
    A. 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:

  • 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

    Correct Answer
    A. Var1 :=var2 + 2000
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Syntax error
  • 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

    Correct Answer
    A. 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:

  • 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;

    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:

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

    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:

Quiz Review Timeline (Updated): Jun 25, 2024 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Jun 25, 2024
    Quiz Edited by
    ProProfs Editorial Team
  • Sep 12, 2012
    Quiz Created by
    Srikanth_kanthet
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.