Oracle PL/SQL Quiz: Hardest Practice Test!

30 Questions | By Grvion | Last updated: Sep 23, 2020 | Total Attempts: 1254

Questions

Settings

Oracle PL/SQL Quiz: Hardest Practice Test!

Are you able to pass this Oracle PL/SQL test? This free procedural language is designed to run alongside the SQL within the same server process, bringing optimal efficiency. The Oracle PL, although not portable and doesn't interface that well with the tons of libraries available for other environments, can process the statements of the system query language with ease. By taking this quiz, you will get to review how to use the languages back to back.

Questions and Answers

1.

Select incorrect variable declarations
- A.
  
  Foo_number varchar2(10);
- B.
  
  Foo_text number(10);
- C.
  
  Foo_char char(1) := 'Y';
- D.
  
  Foo_time date;
- E.
  
  Foo_text varchar2(10) := 'hello world';
2.

Supply the missing keyword DECLARE CURSOR c1 IS SELECT * FROM DUAL; r1 c1%ROWTYPE; BEGIN OPEN c1: ______________ c1 INTO r1; IF c1%NOTFOUND THEN NULL; END IF; CLOSE c1; END;
3.

Select invalid variable types.
- A.
  
  CHAR
- B.
  
  VARCHAR1
- C.
  
  VARCHAR2
- D.
  
  INTEGER
- E.
  
  NUMBER
4.

List the correct sequence of commands to process a set of records when using explicit cursors
- A.
  
  INITIALIZE, GET, CLOSE
- B.
  
  CURSOR, GET, FETCH, CLOSE
- C.
  
  OPEN, FETCH, CLOSE
- D.
  
  CURSOR, FETCH, CLOSE
- E.
  
  GET, SEEK, HIDE
5.

Supply the missing keyword DECLARE CURSOR c1 IS SELECT * FROM DUAL; r1 c1%ROWTYPE; BEGIN OPEN c1: FETCH c1 INTO r1; IF c1%NOTFOUND THEN RAISE; END IF; ________________ c1; END;
6.

Select the best answer PROCEDURE foo ( p_foo_text IN VARCHAR2, p_foo_number IN OUT NUMBER ) IS p_foo_text and p_foo_number are referred to as this procedure's _________
- A.
  
  Variables
- B.
  
  IN OUT Parameters
- C.
  
  Name
- D.
  
  Signature
- E.
  
  None of the above
7.

Select the best answerPACKAGE foo_foo ISPROCEDURE foo( p_foo_text IN VARCHAR2 );PROCEDURE foo(p_foo_number IN NUMBER);END;
- A.
  
  Package specification is invalid. Too many procedures named foo.
- B.
  
  Package specification is invalid. First procedure should be called called foo_1, second procedure should be called foo_2.
- C.
  
  Package specification is valid. We can have an unlimited number of procedures name foo.
- D.
  
  Package specification is valid. This is an example of overloading.
- E.
  
  Package specification is invalid. We can only have one procedure named foo in the package.
8.

Select the best answer to complete this variable declaration for a column value.DECLARE l_foo_column_id SOME_TABLE.SOME_COLUMN_________;BEGIN...
- A.
  
  %ID
- B.
  
  %ROWTYPE
- C.
  
  %COLUMNTYPE
- D.
  
  %TYPE
- E.
  
  None of the above.
9.

Select the best answer to complete this variable declaration for a record.DECLARE l_foo_table SOME_TABLE_________;BEGIN...
- A.
  
  %TABLE
- B.
  
  %ROWTYPE
- C.
  
  %COLUMNTYPE
- D.
  
  %TYPE
- E.
  
  None of the above
10.

Select the best answer. This is an example of what _____ type of cursor?DECLARE l_date DATE;BEGIN SELECT TRUNC(SYSDATE) INTO l_date FROM DUAL;END;
- A.
  
  Explicit
- B.
  
  Implicit
- C.
  
  Select
- D.
  
  PL/SQL
- E.
  
  None of the above
11.

Select the best answer. This is an example of what _____ type of cursor?DECLARE l_date DATE; CURSOR c1 IS SELECT TRUNC(SYSDATE) FROM DUAL;BEGIN OPEN c1; FETCH c1 INTO l_date; CLOSE c1;END;
- A.
  
  Explicit
- B.
  
  Implicit
- C.
  
  Select
- D.
  
  PL/SQL
- E.
  
  None of the above
12.

Supply the missing key wordIF foo IS NULL _________ NULL;END IF;
13.

Enter the missing keywordIF foo = 1 THEN l_text := 'A';______ foo = 2 THEN l_text := 'B';ELSE l_text := 'C';END IF;
- A.
  
  ELSE
- B.
  
  ELSE IF
- C.
  
  ELIF
- D.
  
  ELSIF
- E.
  
  None of the above
14.

Fill in the missing keyword.DECLARE l_date DATE;BEGIN SELECT sysdate ______ l_date FROM dual;END;
15.

Describe the result set that will be obtained from this joinSELECT d.department_name, s.first_name, s.last_name, s.title, s.salary FROM employee s, department dWHERE s.salary > 20000 AND s.title = 'ANALYST' AND ( d.department = 'FINANCE' OR d.department = 'SALES' )
- A.
  
  This SQL will list all employees in the Finance department earning more than 20000
- B.
  
  This SQL will list all employees who are in the Finance or Sales department earning more than 20000
- C.
  
  This SQL will list all employees who are Analysts in the Finance or Sales department earning more than 20000
- D.
  
  This SQL will return a cartesian product
- E.
  
  None of the above
16.

Where do you declare an explicit cursor in the PL/SQL language?
- A.
  
  In the PL/SQL working storage section
- B.
  
  In the PL/SQL declaration section
- C.
  
  In the PL/SQL body section
- D.
  
  In the PL/SQL exception section
- E.
  
  None of the above
17.

Enter the missing keyword to make a new stored procedure.____________ PROCEDURE foo( p_foo_text IN VARCHAR2 ) ASBEGIN NULL;END;
18.

Select the best answer describing the maximum number of times the COMMIT will be executed.ExampleFOR i IN 1..1000 LOOP ... IF MOD(i, 100) = 0 THEN COMMIT; END IF; ...END LOOP;
- A.
  
  The commit is fired 1000 times
- B.
  
  The commit is fired 100 times
- C.
  
  The commit is fired 10 times
- D.
  
  The commit is fired 1 time
- E.
  
  None of the above.
19.

Assuming the date and time is 09/09/2009 09:09:09, what value will the following statement returnSELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS') FROM dual;
- A.
  
  09/09/2009 09:09:09
- B.
  
  09/09/2009 09:09:09AM
- C.
  
  09/09/2009
- D.
  
  09/09/2009 00:00:00
- E.
  
  None of the above
20.

The || is is an example of what functionSELECT last_name || ', ' || first_name || ' ' || middle_nameFROM employees;
- A.
  
  Incantination
- B.
  
  Integration
- C.
  
  Continuation
- D.
  
  Concatenation
- E.
  
  None of the above
21.

Which of the following is not an Oracle DML function?
- A.
  
  DECODE
- B.
  
  TRUNCATE
- C.
  
  TO_CHAR
- D.
  
  NVL
- E.
  
  Trick question, all of these are Oracle DML functions.
22.

Fill in the blank with the name of the function to convert a date to an alphanumeric stringSELECT _______(sysdate, 'MM/DD/YYYY') FROM dual;
23.

Select the invalid PL/SQL looping construct.
- A.
  
  WHILE LOOP ... END LOOP;
- B.
  
  FOR rec IN some_cursor LOOP ... END LOOP;
- C.
  
  LOOP ... UNTIL ; END LOOP;
- D.
  
  LOOP ... EXIT WHEN ; END LOOP;
- E.
  
  None of the above. All are valid.
24.

Select the best answer. Which listed attribute is an invalid attribute of an Explicit cursor.
- A.
  
  %NOTFOUND
- B.
  
  %FOUND
- C.
  
  %ROWCOUNT
- D.
  
  %ISOPEN
- E.
  
  None of the above. All of these are valid.
25.

Which of the following is not a grouping function.
- A.
  
  COUNT
- B.
  
  SUM
- C.
  
  DISTINCT
- D.
  
  MIN
- E.
  
  All of the above.