• databases Online Quiz - 233
Back
0

databases Online Quiz - 233

Description: databases Online Quiz - 233
Number of Questions: 20
Created by:
Tags: databases
Start the Quiz
Attempted 0/20 Correct 0 Score 0
‹
›

The following statement will raise an exception on which line? select dept_name, avg(all salary) ,count() “number of employees” from emp , dept where deptno = dept_no and count() > 5 group by dept_name order by 2 desc;

technology databases

  1. select dept_name, avg(all salary), count(*) “number of employees"

  2. where deptno = dept_no

  3. and count(*) > 5

  4. group by dept_name

  5. order by 2 desc;

Show answer
Correct Option: C

Which two program declarations are correct for a stored program unit? (Choose two)

technology databases

  1. a) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER

  2. b) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER

  3. c) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)

  4. d) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2)

  5. e) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))

Show answer
Correct Option: A,C

Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

technology databases

  1. a) EXECUTE UPD_BAT_STAT;

  2. b) EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  3. c) EXECUTE UPD_BAT_STAT(31, 'FOUR', 'TWO');

  4. d) UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  5. e) RUN UPD_BAT_STAT;

Show answer
Correct Option: A,B
  1. Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?
technology databases

  1. a) g_comm has a value of 15 at 9:06am for Smith.

  2. b) g_comm has a value of 15 at 9:06am for Jones.

  3. c) g_comm has a value of 20 at 9:06am for both Jones and Smith.

  4. d) g_comm has a value of 15 at 9:03 am for both Jones and Smith.

  5. e) g_comm has a value of 10 at 9:06am for both Jones and Smith.

Show answer
Correct Option: B

Which two describe a stored procedure? (Choose two)

technology databases

  1. a) A stored procedure is typically written in SQL.

  2. b) A stored procedure is a named PL/SQL block that can accept parameters.

  3. c) A stored procedure is a type of PL/SQL subprogram that performs an action.

  4. d) A stored procedure has three parts: the specification, the body, and the exception handler part.

  5. e) The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.

Show answer
Correct Option: B,C

Which type of argument passes a value from a procedure to the calling environment?

technology databases

  1. a) VARCHAR2

  2. b) BOOLEAN

  3. c) OUT

  4. d) IN

Show answer
Correct Option: C

When creating a function in SQL *Plus, you receive this message: .Warning: Function created with compilation errors.. Which command can you issue to see the actual error message?

technology databases

  1. a) SHOW FUNCTION_ERROR

  2. b) SHOW USER_ERRORS

  3. c) SHOW ERRORS

  4. d) SHOW ALL_ERRORS

Show answer
Correct Option: C

Given a function CALCTAX : CREATE OR REPLACE FUNCTION calc tax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL *Plus prompt, which statement is true?

technology databases

  1. a) You need to execute the command CALCTAX(1000); .

  2. b) You need to execute the command EXECUTE FUNCTION calc tax; .

  3. c) You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000); .

  4. d) You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;

  5. e) You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

Show answer
Correct Option: E

Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which three are valid calls to the add_dep procedure ? (Choose three)

technology databases

  1. a) add_dept;

  2. b) add_dept( .Accounting .);

  3. c) add_dept(, .New York .);

  4. d) add_dept(p_location=> .New York .);

Show answer
Correct Option: A,B,D

When creating store procedures and functions which construct allows you to transfer values to and from the calling environment?

technology databases

  1. a) Local variables.

  2. b) Arguments.

  3. c) Boolean variables.

  4. d) Substitution variables.

Show answer
Correct Option: B

Under which situation do you create a server side procedure?

technology databases

  1. a) When the procedure contains no SQL statements.

  2. b) When the procedure contains no PL/SQL commands.

  3. c) When the procedure needs to be used by many client applications accessing several remote databases.

  4. d) When the procedure needs to be used by many users accessing the same schema objects on a local database.

Show answer
Correct Option: D

The PROCEDURE_ADD_PRODUCT is defined within a package specifications as follows: PROCEDURE_ADD_PRODUCT (P_PRODNO NUMBER,P_PRODNAME VARCHER2); Which procedure declaration can’t be added to package specifications?

technology databases

  1. a) PROCEDURE add_product (p_order_date DATE);

  2. b) PROCEDURE add_product (p_name VARCHER2, P_ORDERED DATE);

  3. c) PROCEDURE add_product (p_prodname VARCHER2, P_PRISE NUMBER);

  4. d) PROCEDURE add_product (p_prize NUMBER, P_DESCRIPTION VARCHER2);

Show answer
Correct Option: D

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2(30)) IS BEGIN INSERT INTO PLAYER(ID, LAST_NAME) VALUES(V_ID, V_LAST_NAME); COMMIT; END; Why does this command fail when executed?
technology databases

  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.

Show answer
Correct Option: A

The ADD_PLAYER, UPD_PLAYER_STAT and UPD_PITCHER_STAT procedures are grouped together in a package. A variable must be shared among only these procedures. Where should you declare this variable?

technology databases

  1. a) In the package body.

  2. b) In the data base triggers.

  3. c) In the package specification.

  4. d) In the procedures declare section using the exact name in each.

Show answer
Correct Option: A

When using a packaged function in a query, what is true?

technology databases

  1. a) The COMMIT and ROLLBACK commands are allowed in the packaged function.

  2. b) You can not use packaged functions in a query statement.

  3. c) The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

  4. d) The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.

  5. e) The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.

Show answer
Correct Option: C

All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)
technology databases

  1. a) GRANT SELECT ON ADD_PLAYER TO PUBLIC;

  2. b) GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

  3. c) GRANT INSERT ON PLAYER TO PUBLIC;

  4. d) GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

  5. e) REVOKE INSERT ON PLAYER FROM PUBLIC;

Show answer
Correct Option: B,E
  1. Examine this package body: CREATE OR REPLACE PACKAGE BODY forward_pack IS V_sum NUMBER; - 44 - PROCEDURE calc_ord(. . . ); PROCEDURE generate_summary(. . . ) IS BEGIN Calc_ord(. . . ); . . . END calc_ord; END forward_pack; / Which construct has a forward declaration?
technology databases

  1. a) V_SUM

  2. b) CALC_ORD.

  3. c) FORWARD_PACK

  4. d) GENERATE_SUMMARY.

Show answer
Correct Option: B

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2(30)) IS BEGIN INSERT INTO PLAYER(ID, LAST_NAME) VALUES(V_ID, V_LAST_NAME); COMMIT; END; Why does this command fail when executed?
technology databases

  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.

Show answer
Correct Option: A

How do you handle an Exception?
technology databases

  1. a) Trap it with a Handler

  2. b) Propagate it to the Calling Environment

  3. c) a & then b

  4. d) b & then a

Show answer
Correct Option: C

This statement fails when executed: CREATE OR REPLACE TRI GGER CALC_TEAM_AVG AFTER I NSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT ( PLAYER_I D, SEASON_YEAR, AT_BATS, HI TS) VALUES ( : NEW. I D, 1 997, 0, 0) ; END; To which type must you convert the trigger to correct the error?

technology databases

  1. a) Row

  2. b) Statement

  3. c) ORACLE FORM trigger

  4. d) Before

Show answer
Correct Option: A
- Hide questions
ADVERTISEMENT

Find more quizzes from top tags

general knowledge
3600 Quizzes
119725 Questions
 technology
307 Quizzes
36705 Questions
 sports
961 Quizzes
19148 Questions
 history
1088 Quizzes
13125 Questions
 biology
1026 Quizzes
11462 Questions
 physics
537 Quizzes
11445 Questions
 science & technology
551 Quizzes
11015 Questions
 chemistry
432 Quizzes
8610 Questions
 maths
429 Quizzes
7860 Questions
 softskills
0 Quizzes
7131 Questions