SQL

When invoking this procedure, you encounter the error:

ORA-000: Unique constraint(SCOTT.THEATER_NAME_UK) violated.

How should you modify the function to handle this error?

1. An user defined exception must be declared and associated with the error code and handled in the EXCEPTION section.

2. Handle the error in EXCEPTION section by referencing the error code directly.

3. Handle the error in the EXCEPTION section by referencing the UNIQUE_ERROR predefined exception.

4. Check for success by checking the value of SQL%FOUND immediately after the UPDATE statement.

INTERSECT - returns all distinct rows selected by both queries. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates.

Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

- To modify the datatype of a column the column must be empty.

- To add a column with NOT NULL constrain, the table must be empty.

10. CREATE OR REPLACE PROCEDURE find_cpt

11. (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)

12. IS

13. BEGIN

14. IF v_cost_per_ticket > 8.5 THEN

15. SELECT cost_per_ticket

16. INTO v_cost_per_ticket

17. FROM gross_receipt

18. WHERE movie_id = v_movie_id;

19. END IF;

20. END;

Which mode should be used for V_COST_PER_TICKET?

1. IN

2. OUT

3. RETURN

4. IN OUT

A developer would like to use referential datatype declaration on a variable. The variable name is EMPLOYEE_LASTNAME, and the corresponding table and column is EMPLOYEE, and LNAME, respectively. How would the developer define this variable using referential datatypes?

1. Use employee.lname%type.

2. Use employee.lname%rowtype.

3. Look up datatype for EMPLOYEE column on LASTNAME table and use that.

4. Declare it to be type LONG.

The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?

1. ALTER TRIGGER check_theater ENABLE;

2. ENABLE TRIGGER check_theater;

3. ALTER TABLE check_theater ENABLE check_theater;

4. ENABLE check_theater;

Correlated sub-query is a sub-query, which has reference to the main query.

1. Implicit cursors are used for SQL statements that are not named.

2. Developers should use implicit cursors with great care.

3. Implicit cursors are used in cursor for loops to handle data processing.

4. Implicit cursors are no longer a feature in Oracle.

Using ROWID.

CONSTRAINTS

1. open

2. fetch

3. parse

4. None, cursor for loops handle cursor opening implicitly.

Sub-query is a query whose return values are used in filtering conditions of the main query.

1. LOOP

2. END LOOP

3. IF-THEN

4. EXIT

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

OLTP stands for On Line Transaction Processing

Applications that have outgrown their environment are re-engineered to run in a larger environment. This is upsizing.

When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.

An event handler is a routine that is written to respond to a particular event.

Because it is easier to define an integrity constraint than a database trigger.

The client deals with the user interface part of the system.