Oracle PL/SQL Test! Hardest Trivia Quiz

30 Questions | By Ibecruzin | Last updated: Apr 30, 2020 | Total Attempts: 5400

Questions

Settings

PL/SQL is a procedural language extension to Structured Query Language. The purpose of PL/SQL is to combine database language and procedural programming language, as we discussed in class these past few days. Being that the midterms are almost here, the quiz below has been designed to help you refresh your memory on Oracle PL/SQL quiz. Give it a shot and be well prepared!

More Oracle Quizzes

Oracle Basic Test! Trivia Questions Quiz Oracle Basic Test! Trivia Questions Quiz

Oracle Test2 Oracle Test2

Oracle_test6 Oracle_test6

Featured Quizzes

Which Naruto Character Are You Most Like? Which Naruto Character Are You Most Like?

Quiz: The Best Pokemon Picker! Quiz: The Best Pokemon Picker!

Quiz: The Impossible Test! Quiz: The Impossible Test!

Related Topics

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 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 answerPROCEDURE 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 answerPACKAGE foo_foo ISPROCEDURE foo( p_foo_text IN VARCHAR2 );PROCEDURE foo(p_foo_number IN NUMBER);END;

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

Discuss
- 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' )

Discuss
- 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.
26.

All the blanks can be filled with one word. Fill in the blank Starting in Oracle 9i, you can use the _________ statement within a SQL statement. It has the functionality of an IF-THEN-ELSE statement. The syntax for the ________ statement is: __________ [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE resultEND
27.

Which of the following is not a valid Oracle PL/SQL exception.

Discuss
- A.
  
  NO_DATA_FOUND ORA-01403
- B.
  
  TWO_MANY_ROWS ORA-01422
- C.
  
  DUP_VAL_ON_INDEX ORA-00001
- D.
  
  OTHERS
- E.
  
  None of the above. These are all valid.
28.

What is the value of l_child_number?DECLARE l_parent_number NUMBER := 1;BEGIN DECLARE l_child_number NUMBER := 2; BEGIN l_child_number := l_parent_number + l_child_number; END; DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number));EXCEPTION WHEN OTHERS THEN l_child_number := 0; DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number);END;

Discuss
- A.
  
  1
- B.
  
  2
- C.
  
  3
- D.
  
  0
- E.
  
  None of the above.
29.

Select the best answer below. What are the components of a package?
- A.
  
  Box, wrapping and binding
- B.
  
  Header and body
- C.
  
  Specification and content
- D.
  
  Specification and body
- E.
  
  None of the above
30.

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 sysdate in this format.

Back to top