Oracle PL/SQL Quiz Questions And Answers!

30 Questions | By Ibecruzin | Updated: Mar 22, 2022 | Attempts: 20482

Questions

Settings

PL/SQL is a procedural language extension to Structured Query Language. You can take this Oracle PL/SQL quiz questions and answers and check your knowledge. The purpose of PL/SQL is to combine database language and procedural programming language. The quiz below has been designed to help you refresh your memory on Oracle PL/SQL. Give it a shot and see your results. Don't forget to share the result with your friends and see who got the better results. All the best!

Questions and Answers

1.

Write a function named date_as_mmyyyy, which will accept a date as a parameter. This date is then converted to characters and formatted as MMYYYY, and then returned. If this date is null, then return the sysdate in this format.
2.

Supply the missing keywordDECLARE 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 keywordDECLARE 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 ) ISp_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 answer.PACKAGE 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 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 keyword.IF foo IS NULL [Blank]__ NULL;END IF;
13.

Enter the missing keyword.IF 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 join.SELECT 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 return.SELECT 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 function.SELECT last_name || ', ' || first_name || ' ' || middle_nameFROM employees;
- A.
  
  Incarnation
- 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 string.SELECT [Blank](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.