0

databases Online Quiz - 75

Description: databases Online Quiz - 75
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

What code modificaiton should be made to run the below code ? begin dbms_scheduler.create_job ( job_name => 'RUN_SHELL1', schedule_name => 'DEMO_SCHEDULE', job_type => 'EXECUTABLES', job_action => '/home/test/run_script.sh', enabled => true, comments => 'Run shell-script' ); end; /

  1. No change is required,job can be scheduled.

  2. Enabled option should be set to False.

  3. Job type should be changed to Executable

  4. Job type should be changed to Executable Block

  5. None


Correct Option: C
  1. Oracle 8

  2. Oracle 7

  3. Oracle 10g

  4. Oracle 9i

  5. None


Correct Option: A
  1. all_scheduler_job_log_

  2. dba_scheduler_job_logs

  3. users_scheduler_job_log

  4. all the above

  5. None


Correct Option: E
  1. DBMS_SCHEDULER package

  2. DBMS_SCHEDULE package

  3. DBMS_JOB package

  4. Both A & C

  5. None


Correct Option: D

You are the database developer for a leasing company. Your database includes a table that is defined as shown here: CREATE TABLE Lease (Id Int IDENTITY NOT NULL CONSTRAINT pk_lease_id PRIMARY KEY NONCLUSTERED, Lastname varchar(50) NOT NULL, FirstName varchar(50) NOT NULL, SSNo char(9) NOT NULL, Rating char(10) NULL, Limit money NULL) Each SSNo must be unique. You want the data to be physically stored in SSNo sequence. Which constraint should you add to the SSNo column on the Lease table?

  1. The UNIQUE CLUSTERED constraint

  2. The UNIQUE UNCLUSTERED constraint

  3. The PRIMARY KEY CLUSTERED constraint

  4. The PRIMARY KEY UNCLUSTERED constraint


Correct Option: A

You have a database that contains several FOREIGN KEY and CHECK constraints. Users are having problems with data entry on the database because the data they are adding is constantly in violation of the CHECK constraints. Corporate policy regarding database design prevents you from modifying the current constraints, so you decide to implement your changes via a trigger. Which types of triggers would be best suited for this task?

  1. UPDATE, DELETE, and INSERT TRiggers

  2. Just UPDATE and INSERT triggers

  3. INSTEAD OF TRiggers

  4. Triggers cannot be used in this circumstance.


Correct Option: C

Which statement is valid when removing procedures?

  1. Use a drop procedure statement to drop a standalone procedure

  2. Use a drop procedure statement to drop a procedure that is part of a package.Then recompile the package specification

  3. Use a drop procedure statement to drop a procedure that is part of a package.Then recompile the package body

  4. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.


Correct Option: A

Which table should you query to determine when your procedure was last compiled?

  1. USER_PROCEDURES

  2. USER_PROCS

  3. USER_OBJECTS

  4. USER_PLSQL_UNITS


Correct Option: C

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID, LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the UPD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

  1. EXECUTE UPD_BAT_STAT(V_ID);

  2. UPD_BAT_STAT(V_ID);

  3. RUN UPD_BAT_STAT(V_ID);

  4. START UPD_BAT_STAT(V_ID);


Correct Option: B

AI Explanation

To successfully invoke the UPD_BAT_STAT procedure and pass a parameter, you need to use the correct syntax. Let's go through each option to determine which statement should be added to the procedure:

Option A) EXECUTE UPD_BAT_STAT(V_ID) - This option is incorrect because EXECUTE is not a valid keyword to invoke a procedure in PL/SQL.

Option B) UPD_BAT_STAT(V_ID) - This option is correct because it uses the correct syntax to invoke the UPD_BAT_STAT procedure and pass the V_ID parameter.

Option C) RUN UPD_BAT_STAT(V_ID) - This option is incorrect because RUN is not a valid keyword to invoke a procedure in PL/SQL.

Option D) START UPD_BAT_STAT(V_ID) - This option is incorrect because START is not a valid keyword to invoke a procedure in PL/SQL.

The correct answer is Option B) UPD_BAT_STAT(V_ID). This option is correct because it uses the correct syntax to invoke the UPD_BAT_STAT procedure and pass the V_ID parameter.

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

  1. VARCHAR2

  2. BOOLEAN

  3. OUT

  4. IN


Correct Option: C

Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT ‘AUSTIN’, V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END Which two statements will successfully invoke this procedure in SQL *Plus?

  1. EXECUTE INSERT_TEAM;

  2. EXECUTE INSERT_TEAM(3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);

  3. EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);

  4. EXECUTE INSERT_TEAM (V_ID := V_NAME := ‘LONGHORNS’, V_CITY :=‘AUSTIN’);


Correct Option: B,C

Which two describe a stored procedure?

  1. A stored procedure is typically written in SQL.

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

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

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

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


Correct Option: B,C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A stored procedure is typically written in SQL - This option is incorrect. While stored procedures can be written in SQL, they can also be written in other programming languages such as PL/SQL.

Option B) A stored procedure is a named PL/SQL block that can accept parameters - This option is correct. A stored procedure is a named PL/SQL block that can accept parameters. It allows you to encapsulate and execute a set of SQL statements and PL/SQL code.

Option C) A stored procedure is a type of PL/SQL subprogram that performs an action - This option is correct. A stored procedure is a type of PL/SQL subprogram that performs a specific action or set of actions.

Option D) A stored procedure has three parts: the specification, the body, and the exception handler part - This option is incorrect. While it is true that a stored procedure can have three parts (specification, body, and exception handler), it is not a mandatory requirement. A stored procedure can have only a body or a body with an exception handler.

Option E) The executable section of a stored procedure contains statements that assign values, control execution, and return values to the calling environment - This option is incorrect. While the executable section of a stored procedure can contain statements that assign values, control execution, and return values, it is not the only section of a stored procedure. A stored procedure can also have a declaration section where variables are declared and initialized.

The correct answer is B, C. These options correctly describe a stored procedure as a named PL/SQL block that can accept parameters and performs a specific action or set of actions.

  1. A stored procedure uses the DECLARE keyword in the procedure specification to declare formal parameters

  2. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification

  3. A stored procedure must have at least one executable statement in the procedure body.

  4. A stored procedure uses the DECLARE keyword in the procedure body to declare formal


Correct Option: C

Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TIMESTAMP. What happens when procedure A is invoked at 1 P.M?

  1. There is no affect on procedure A and it runs successfully

  2. Procedure B is invalidated and recompiles when invoked.

  3. Procedure A is invalidated and recompiles for the first time it is invoked.

  4. Procedure A is invalidated and recompiles for the second time it is invoked


Correct Option: D

Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID; EXCEPTION WHEN STATS_EXITS_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE (‘cannot delete this player, child records exist in PLAYER_BAT_STAT table’); END; What prevents this procedure from being created successfully?

  1. A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. The STATS_EXIST_EXCEPTION has not been declared as a number

  3. The STATS_EXIST_EXCEPTION has not been declared as an exception

  4. Only predefined exceptions are allowed in the EXCEPTION section.


Correct Option: C
- Hide questions