Ilp Oracle Prelims 2

8 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Oracle Prelims 2


Questions and Answers
  • 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

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

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

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

  • 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

  • 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

  • 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

  • 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