Oracle PL/SQL Quiz Questions And Answers!
(31).jpg "Oracle PL/SQL Quiz Questions And Answers!")
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!
- 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.
Questions: 40 | Attempts: 3165 | Last updated: Feb 4, 2013
-
Sample QuestionYou attempt to query the data base with this command: SELECT name,salary FROM employee WHERE salary= (SELECT salary FROM employee WHERE last_name= ‘Wagner’ OR dept_no=233) Why could this statement cause an error?
Questions: 45 | Attempts: 3082 | Last updated: Sep 23, 2020
-
Sample QuestionSelect incorrect variable declarations
Questions: 10 | Attempts: 2694 | Last updated: Jul 30, 2011
-
Sample QuestionExamine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table?
Back to top