Oracle Quiz
- 1.
To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write
- A.
A.any outer join
- B.
B.a left outer join
- C.
C.a cross join
- D.
D.a right outer join
- E.
E.an inner join
Correct Answer
D. D.a right outer join -
- 2.
Top N analysis requires _____ and _____. (Choose two.)
- A.
A.the use of rowed
- B.
B.a GROUP BY clause
- C.
C. an ORDER BY clause
- D.
D. only an inline view
- E.
E. an inline view and an outer query
Correct Answer(s)
C. C. an ORDER BY clause
E. E. an inline view and an outer query -
- 3.
Which are DML statements? (Choose all that apply.)
- A.
A. COMMIT
- B.
B. MERGE
- C.
C. UPDATE
- D.
D. DELETE
- E.
E. CREATE
- F.
F. DROP
Correct Answer(s)
B. B. MERGE
C. C. UPDATE
D. D. DELETE -
- 4.
Which of the following statements creates a valid procedure? (Choose all that apply.)
- A.
A.CREATE OR REPLACE PROCEDURE PROC_TEST IS NULL; END;
- B.
B.CREATE OR REPLACE PROCEDURE PROC_TEST AS BEGIN NULL; END;
- C.
C.CREATE OR REPLACE PROCEDURE TEST IS NULL; END;
- D.
D.CREATE OR REPLACE PROCEDURE TEST IS DECLARE BEGIN NULL; END;
Correct Answer
B. B.CREATE OR REPLACE PROCEDURE PROC_TEST AS BEGIN NULL; END; -
- 5.
You have stored a function called GET_STATUS in the database with the following header: FUNCTION GET_STATUS (p_order_id NUMBER, p_option VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS Now you want to create a new procedure and store it in the database. Assuming you've declared all referenced variables, which of the following statements are syntactically acceptable for your new procedure? (Choose all that apply.)
- A.
A.v_status := v_priority ∥ get_status(15, NULL);
- B.
B.v_status := get_status(15);
- C.
C.v_status := get_status(NULL);
- D.
D.v_status := get_status(ABS(v_order_id), ‘FLAG’);
Correct Answer(s)
A. A.v_status := v_priority ∥ get_status(15, NULL);
B. B.v_status := get_status(15);
C. C.v_status := get_status(NULL);
D. D.v_status := get_status(ABS(v_order_id), ‘FLAG’); -
- 6.
Consider the following valid procedure header: PROCEDURE ASSIGN_PURCHASE_ORDER (purchase_order_id NUMBER DEFAULT 0, order_date DATE := SYSDATE, supplier_id SUPPLIERS.SUPPLIER_ID%TYPE DEFAULT 0) Which of the following PL/SQL statements is not a valid call to this procedure?
- A.
A.ASSIGN_PURCHASE_ORDER;
- B.
B.ASSIGN_PURCHASE_ORDER(supplier_id => 700);
- C.
C.ASSIGN_PURCHASE_ORDER(‘01-JAN-2004’);
- D.
D.ASSIGN_PURCHASE_ORDER(SEQ_PURCHASE_ORDER_ID.NEXTVAL);
Correct Answer
C. C.ASSIGN_PURCHASE_ORDER(‘01-JAN-2004’); -
- 7.
Consider the following function: FUNCTION ACTIVE_SESSION_COUNT RETURN NUMBER This function does not write to the database or package constructs. You can invoke this function from which of the following? (Choose all that apply.)
- A.
A.The WHERE clause of an UPDATE statement
- B.
B.The left side of an assignment statement
- C.
C.The COLUMN list of an INSERT statement
- D.
D.The default value assignment of the parameter of a procedure
Correct Answer(s)
A. A.The WHERE clause of an UPDATE statement
D. D.The default value assignment of the parameter of a procedure -
- 8.
I)Procedural Language is an extension to SQL ii)Structured Query Language is an extension to SQL
- A.
A)Only i is true
- B.
B)Only ii is true
- C.
C)Both i & ii
- D.
D)None
Correct Answer
C. C)Both i & ii -
- 9.
Which data type is used to hold single value?
- A.
A) LOB
- B.
B)Reference data types
- C.
C)Composite data types
- D.
D)Scalar data types
Correct Answer
D. D)Scalar data types -
- 10.
Which part contains cursors and user defined exceptions that are referenced in the executable and declarative sections.
- A.
A) Declarative
- B.
B) Executable
- C.
C) Exception handling
- D.
D)Both a & c
Correct Answer
A. A) Declarative -
- 11.
PUT_LINE combines the action of
- A.
A) PUT and LINE
- B.
B) PUT and NEW_LINE
- C.
C) PUT_LINE
- D.
D) none
Correct Answer
B. B) PUT and NEW_LINE -
- 12.
Consider the following code sample: CREATE OR REPLACE FUNCTION BALANCE_LEDGER(account_id NUMBER) RETURN NUMBER IS v_result BOOLEAN; BEGIN RECONCILE(account_id, v_result); IF (v_result) THEN RETURN '1'; ELSE RETURN '2'; END IF; END BALANCE_LEDGER; Which of the following statements is true about this code sample? (Choose all that apply.) . .
- A.
A.It will not compile due to RETURN datatype conflicts.
- B.
B.It will compile, but it will not execute due to RETURN datatype conflicts
- C.
C.It will compile, but it will not execute within SQL statements.
- D.
D.It will compile and execute
Correct Answer
D. D.It will compile and execute -
- 13.
Benefits of Subprograms
- A.
A)Easy maintenance
- B.
B)Improved data security and integrity
- C.
C) Improved code clarity
- D.
D) All of the above
Correct Answer
D. D) All of the above -
- 14.
You submit a statement to the database that starts with the following: CREATE PROCEDURE PROC_AUDIT (AUDIT_ID AUDITS.AUDIT%TYPE) IS Which of the following statements is guaranteed to be true?
- A.
A. The code for this program unit will definitely be stored in the data dictionary.
- B.
B.This procedure will definitely have a status of VALID.
- C.
C.This procedure will be callable from a SQL statement
- D.
D.None of the above
Correct Answer
D. D.None of the above -
- 15.
The ALTER FUNCTION statement can be used to do which of the following? (Choose all that apply.)
- A.
A.Modify the list of parameters for a function.
- B.
B.Change the program unit's processing section.
- C.
C.Recompile the program unit's code.
- D.
D.Create an overloaded function.
Correct Answer
C. C.Recompile the program unit's code. -
- 16.
You submit the following statement to the database: CREATE OR REPLACE FUNCTION CREDIT_CHECK (p_card_id NUMBER) RETURN VARCHAR2 IS CURSOR cur_card_services IS SELECT APPROVAL_CODE FROM CARD_SERVICE WHERE CARD_ID = p_card_id; v_approval_code CARD_SERVICE.APPROVAL_CODE%TYPE; BEGIN OPEN cur_card_services; FETCH cur_card_services INTO v_approval_code; CLOSE cur_card_services; RETURN v_approval_code; END CREDIT_CHECK; Which of the following statements will successfully execute this function from SQL*Plus? (Choose all that apply.)
- A.
A.EXEC CREDIT_CHECK(4020);
- B.
B.SELECT CREDIT_CHECK(p_card_id => 101) FROM DUAL;
- C.
C.BEGIN DBMS_OUTPUT.PUT_LINE(CREDIT_CHECK(101)); END;
- D.
D.CREDIT_CHECK(4020);
Correct Answer
C. C.BEGIN DBMS_OUTPUT.PUT_LINE(CREDIT_CHECK(101)); END; -
- 17.
Which of the following statements describes a difference between a stored database procedure and a stored database function?
- A.
A.Procedures are easier to code and invoke than functions.
- B.
B.Functions execute more quickly than procedures.
- C.
C.Functions can be invoked from within a SQL statement; procedures cannot.
- D.
D.Procedures can handle more parameters.
Correct Answer
C. C.Functions can be invoked from within a SQL statement; procedures cannot. -
- 18.
Which of the following is an advantage of a stored database procedure?
- A.
A.It runs faster than a stored database function.
- B.
B.It can be invoked by any privileged schema from anywhere on the network.
- C.
C.It can be automatically invoked by any privileged client from anywhere on the operating system network configuration protocol adapter.
- D.
D. When a stored database procedure is executed, all other stored database procedures are automatically loaded so future calls to those procedures will be faster.
Correct Answer
B. B.It can be invoked by any privileged schema from anywhere on the network. -
- 19.
Consider the following procedure: PROCEDURE ADD_PORT(new_port_name VARCHAR2) IS BEGIN INSERT INTO PORTS (PORT_ID, PORT_NAME) VALUES (SEQ_PORT_ID.NEXTVAL, new_port_name); COMMIT; END ; Which of the following is a valid call to this procedure from SQL*Plus?
- A.
A.ADD_PORT(‘Miami’);
- B.
B.EXECUTE ADD_PORT(‘Miami’);
- C.
C.SELECT ADD_PORT(‘Miami’) FROM DUAL;
- D.
D.EXECUTE PROCEDURE ADD_PORT(‘Miami’);
Correct Answer
B. B.EXECUTE ADD_PORT(‘Miami’); -
- 20.
You have already successfully compiled and stored a procedure called ADD_LINE_ITEM in the database. Now you want to make a change to the procedure's exception handling section. Which of the following statements will you use to make this change?
- A.
A.ALTER PROCEDURE
- B.
B.DROP PROCEDURE
- C.
C.CREATE PROCEDURE
- D.
D.CREATE or REPLACE PROCEDURE
Correct Answer
D. D.CREATE or REPLACE PROCEDURE -
- 21.
Which of the following data dictionary views contains the source code for a stored packaged function? USER_TEXT USER_FUNCTIONS USER_SOURCE USER_PACKAGES
- A.
A.USER_TEXT
- B.
B.USER_FUNCTIONS
- C.
C.USER_SOURCE
- D.
D.USER_PACKAGES
Correct Answer
C. C.USER_SOURCE -
- 22.
Where in the data dictionary can you find the status of a stored database procedure? (Choose all that apply.)
- A.
A.USER_OBJECTS
- B.
B.USER_PROCEDURES
- C.
C.USER_STATUS
- D.
D.USER_SOURCE
Correct Answer
A. A.USER_OBJECTS -
- 23.
Which of the following queries will display the source code of a function called CANCEL_ORDER?
- A.
A.SELECT TEXT FROM USER_SOURCE WHERE NAME = ‘CANCEL_ORDER’ ORDER BY TEXT;
- B.
B.SELECT TEXT FROM USER_FUNCTIONS WHERE NAME = ‘CANCEL_ORDER’ ORDER BY LINE_NUMBER;
- C.
C.SELECT TEXT FROM USER_SOURCE WHERE TYPE = ‘FUNCTION’ AND NAME = ‘CANCEL_ORDER’ ORDER BY LINE;
- D.
D.SELECT TEXT FROM USER_SOURCE WHERE OBJECT_TYPE = ‘FUNCTION’
Correct Answer
C. C.SELECT TEXT FROM USER_SOURCE WHERE TYPE = ‘FUNCTION’ AND NAME = ‘CANCEL_ORDER’ ORDER BY LINE; -
- 24.
You have stored the following procedure in your schema: PROCEDURE INITIALIZE_RATIOS IS BEGIN UPDATE RATIO_LIST SET A = 100, B = 200 WHERE RATIO_LIST_ID = 1; END INITIALIZE_RATIOS; With this procedure stored in the database with a status of VALID, you issue the following DDL statement: ALTER TABLE RATIO_LIST ADD C NUMBER(3); What is the status of the procedure after this DDL is executed?
- A.
A.VALID
- B.
B.INVALID
- C.
C.CHANGED
- D.
D.NEW
Correct Answer
B. B.INVALID -
- 25.
What is the result of the following SELECT statement? SELECT ROUND (631.1250, 2), ROUND (631.1250, 0), ROUND (631.1250, -2), ROUND (631.1250, 3) FROM DUAL;
- A.
A.600 0 631.00 631
- B.
B.631.13 631 600 631.125
- C.
C.630 0 631.12 631
- D.
D.2 0 -2 3
Correct Answer
B. B.631.13 631 600 631.125 -
- 26.
Declare j number; begin for i in 1..6 loop j:=i; goto outer; end loop; <<outer>> j:=j+1; end; What is the value j
- A.
A.2
- B.
B.7
- C.
C.6
- D.
D.0
Correct Answer
A. A.2 -
- 27.
Declare j number; begin for i in 1..6 loop j:=i; goto outer; <<outer>> j:=j+1; end loop; end; What is the value j
- A.
A.2
- B.
B.6
- C.
C.7
- D.
D.0
Correct Answer
C. C.7 -
- 28.
You need to unite the data from the STUDENTS and FACULTY tables for a new report. Which SQL SELECT capability are you using to accomplish this?
- A.
A.SELECTION
- B.
B.JOINING
- C.
C.PROJECTION
- D.
D.MANAGEMENT
Correct Answer
B. B.JOINING -
- 29.
What values does the SYSDATE return when used in a SELECT statement? (Select all that apply.)
- A.
A.The date the database was created
- B.
B.The current database server time
- C.
C.The time the database was created
- D.
D.The current database server date
Correct Answer(s)
B. B.The current database server time
D. D.The current database server date -
- 30.
Which of the following is true if you use the alter tablespace statement and specify the TEMPORARY clause (Choose all that apply)?
- A.
A.Oracle no longer perform any checkpoint for the online datafiles in the tablespace
- B.
B.Oracle does not ensure that all files are written
- C.
C.Oracle performs a checkpoint for all online datafiles in the tablespace
- D.
D.The offline files may require media recovery before you bring the tablespace online
- E.
E.The offline files may require media recovery after you bring the tablespace online
Correct Answer(s)
B. B.Oracle does not ensure that all files are written
C. C.Oracle performs a checkpoint for all online datafiles in the tablespace
D. D.The offline files may require media recovery before you bring the tablespace online -
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Feb 13, 2013Quiz Edited by
ProProfs Editorial Team -
Mar 04, 2012Quiz Created by
Steffy_gray
Back to top