Home › Quizzes › Database Stored Procedures, Triggers, and Job Scheduling

Database Stored Procedures, Triggers, and Job Scheduling

Quiz covering stored procedures, triggers, indexes, and job scheduling in Oracle and SQL Server databases.

20 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

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
Question 2 Multiple Choice (Single Answer)

In which oracle verion scheduling concept is introduced ?

  1. Oracle 8
  2. Oracle 7
  3. Oracle 10g
  4. Oracle 9i
  5. None
Question 3 Multiple Choice (Single Answer)

what is the view that provides the job history ?

  1. all_scheduler_job_log_
  2. dba_scheduler_job_logs
  3. users_scheduler_job_log
  4. all the above
  5. None
Question 4 Multiple Choice (Single Answer)

Which package in plsql provides scheduling ?

  1. DBMS_SCHEDULER package
  2. DBMS_SCHEDULE package
  3. DBMS_JOB package
  4. Both A & C
  5. None
Question 5 Multiple Choice (Single Answer)

An existing sales catalog database structure exists on a system in your company. The company sells inventory from a single warehouse location that is across town from where the computer systems are located. The product table has been created with a nonclustered index, based on the product ID, which is also the primary key. Nonclustered indexes exist on the product category column and also the storage location column. Most of the reporting done is ordered by storage location. How would you change the existing index structure?

  1. Change the definition of the primary key so that it is a clustered index.
  2. Create a new clustered index, based on the combination of storage location and product category.
  3. Change the definition of the product category so that it is a clustered index.
  4. Change the definition of the storage location so that it is a clustered index.
Question 6 Multiple Choice (Multiple Answers)

You are designing an application that will provide data entry clerks the capability of updating the data in several tables. You would like to ease entry and provide common input so the clerks do not need to enter data into all fields or enter redundant values. What types of technologies could you use to minimize the amount of input needed? (Select all that apply.)

  1. Foreign key
  2. Cascading update
  3. Identity column
  4. Default
  5. NULL
  6. Unique index
Question 7 Multiple Choice (Single Answer)

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
Question 8 Multiple Choice (Single Answer)

You are preparing a new index on a table that has 1,500 rows. 10 rows are added to this table every day. The table already has a primary key, and the new index does not represent the order in which data in the table is to be stored. Updates to the table occur periodically but are infrequent. Which type of index would you create under this situation?

  1. Use a clustered index with a high FILLFACTOR setting
  2. Use a clustered index with a low FILLFACTOR setting
  3. Use a nonclustered index with a high FILLFACTOR setting
  4. Use a nonclustered index with a low FILLFACTOR setting
Question 9 Multiple Choice (Single Answer)

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.
Question 10 Multiple Choice (Single Answer)

You have an accounting SQL Server database application that is accessed by 50 users on your company network. When a user inserts or updates a record, you want to make sure that all the required columns have appropriate values. Which of the following would be best for this situation?

  1. A stored procedure and a trigger
  2. A batch and a trigger
  3. An UPDATE TRigger and an INSERT trigger
  4. One trigger by itself
Question 11 Multiple Choice (Single Answer)

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.
Question 12 Multiple Choice (Single Answer)

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
Question 13 Multiple Choice (Single Answer)

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);
Question 14 Multiple Choice (Single Answer)

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

  1. VARCHAR2
  2. BOOLEAN
  3. OUT
  4. IN
Question 15 Multiple Choice (Multiple Answers)

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’);
Question 16 Multiple Choice (Multiple Answers)

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.
Question 17 Multiple Choice (Single Answer)

What is true about stored procedures?

  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
Question 18 Multiple Choice (Single Answer)

Examine this code: CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id NUMBER(4); BEGIN INSERT INTO departments VALUES (5, ‘Education’, 150, p_location_id); SELECT department_id INTO v_dept_id FROM employees WHERE employee_id=99999; END insert_dept; / CREATE OR REPLACE PROCEDURE insert_location ( p_location_id NUMBER, p_city VARCHAR2) IS BEGIN INSERT INTO locations (location_id, city) VALUES (p_location_id, p_city); insert_dept(p_location_id); END insert_location; / You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You now invoke the insert_location procedure using the following command: EXECUTE insert_location (19, ‘San Francisco’) What is the result in this EXECUTE command?

  1. The locations, departments, and employees tables are empty
  2. The departments table has one row. The locations and the employees tables are empty
  3. The location table has one row. The departments and the employees tables are empty.
  4. The locations table and the departments table both have one row. The employees table is empty.
Question 19 Multiple Choice (Single Answer)

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
Question 20 Multiple Choice (Single Answer)

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.
← Back to Quizzes