Oracle Preparatory Exam-1

EXECUTE find_seats_sold (500,11);

Theater_pck.find_seats_sold (500,11);

EXECUTE theater_pck.find_seats_sold (500,11);

SELECT find_seats_sold(movie_id, theater_id) FROM gross_receipt;

BEGIN current_avg_cost_per_ticket := 6.75; END;

BEGIN theater_pck.current_avg_cost_per_ticket := 6.75; END;

BEGIN SELECT AVG(cost_per_ticket) INTO current_avg_cost_per_ticket FROM gross_receipt; END;

This variable is private to the package and cannot be directly assigned a value within SQL*Plus.

ON

OFF

AFTER

BEFORE

The DML event that invoked the database trigger is committed

The DML is successful. Each processed row is marked invalid.

The DML is successful. The modified table is marked invalid

The DML event that invoked the database trigger is rolled back.

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

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

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

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

4

3

Error in code

No output

8

0

4

6

10

SUM(start_date)

AVG(start_date)

COUNT(start_date)

AVG(start_date, end_date)

MAXIMUM(start_date)

SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL;

TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL;

SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

Date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;

SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;

CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;

CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;

BEFORE (gross_receipt) DELETE, INSERT, UPDATE

BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt FOR EACH ROW

AFTER DELETE OR INSERT OR UPDATE ON gross_receipt

BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt

VARIABLE g_yearly_budget NUMBER :g_yearly_budget := GET_BUDGET(11);

VARIABLE g_yearly_budget NUMBER EXECUTE g_yearly_budget := GET_BUDGET(11);

VARIABLE g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

VARIABLE :g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

By limiting changes to logic to one location

By eliminating the need for an executable section

By eliminating the need for the storage of object code

By ignoring side effects when invoked from within a SQL statement

Remove the first RETURN statement.

Change "RETURN number" to "OUTPUT number".

Add "(v_studio_id IN NUMBER)" right after the IS keyword.

Add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header.

Displays all the direct dependencies on the EMPLOYEE table

Displays only the direct dependencies you created on the EMPLOYEE table

Displays the name of the indirect dependencies on the EMPLOYEE table

Displays the OWNER column to indicate the schema of any referenced objects

PRAGMA PURITY_CHECK

PRAGMA EXCEPTION_INIT

PRAGMA FUNCTION_PURITY

PRAGMA RESTRICT_REFERENCES

Side effects are changes to database tables or public packaged variables declared in the package specification.

Side effects do not delay the execution of a query.

All side effects are allowed when a function is called from a SQL query or DML statement

Side effects are the yield order-dependent results of a function called from a SQL statement.

Each procedure must have a unique name.

The names of the arguments must be identical.

Each argument name must be unique because there are the same number of arguments.

At least one argument must be in a different data type family because there are the same number of arguments.

Create a view

Creates a new table

Add new column

Create a query by adding 1000 to sal column

Lgwr moves data from logbuffer to logfiles

Lgwr moves data from logfiles to logbuffer

Dbwr moves data from logbuffer to logfiles

Lgwr moves data from logbuffer to archivefiles

Archive files

Redo

Undo

Temp

You use an application trigger to fire when a DELETE statement occurs.

You use a database trigger to fire when an INSERT statement occurs.

You use a system event trigger to fire when an UPDATE statement occurs.

You use INSTEAD OF trigger to fire when a SELECT statement occurs

SHOW FUNCTION_ERROR

SHOW USER_ERRORS

SHOW ALL_ERRORS

SHOW ERRORS

1

10

None

A value equal to the number of rows in the EMP table.

SYS privileges

Your privileges

Public privileges

User A's privileges

User A cannot execute your procedure that has dynamic SQL.

The procedure is created, stored in the data dictionary, and given a status of INVALID. The function remains in the data dictionary.

The procedure is created, stored in the data dictionary, and given a status of VALID.

The ORA-00955 message "name is already used by an existing object" appears.

The procedure is created, stored in the data dictionary, and given a status of INVALID. The function no longer exists in the data dictionary