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.

View

Database trigger

Cursor

Application trigger

Variable

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) You cannot search for a NULL value in a column using the WHERE clause.

B) If a NULL value is returned in the subquery or if NULL is included in the list when using a NOT IN operator, no rows will be returned

C) Only = and != operators can be used to search for NULL values in a column.

D) In an ascending order sort, NULL values appear at the bottom of the result set.

E) Concatenating a NULL value to a non- NULL string results in a NULL

True

False

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;

Constraints enforce rules at the view level

Constraints provide data independence

Constraint make complex queries easy

Constraints enforce rules at the table level

Constraints prevent the deletion of an index if there are dependencies

Constraints prevent the deletion of a table if there are dependencies

Invalid objects cannot be described.

An object with status of invalid cannot be a referenced object.

All schema objects have a status that is recorded in the data dictionary.

You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.

The Oracle server automatically records dependencies among objects.

You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view

It persists across transactions within a session.

It persists from session to session for the same user.

It does not persist across transaction within a session.

It persists from user to user when the package is invoked.

It does not persist from session to session for the same user.

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;

If errors occur during the compilation of a trigger, the trigger is still created.

If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors.

If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors

If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.

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

DEPTREE

IDEPTREE

DEPENDENCIES

USER_OBJECTS

USER_DEPENDENCIES

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.

A function called from a DML statement cannot read or modify the particular table being modified by the DML statement.

A function called from a query statement or from a parallelized DML statement cannot execute a DML statement or modify the database.

A function called from a query or DML statement cannot end the current transaction, create or roll back a savepoint, or alter the system or session.

A function called from a DML statement can read or modify the particular table being modified by the DML statement.

A function called from a query statement or from a parallelized DML statement can execute a DML statement or modify the database.

A function called from a query or DML statement can end the current transaction, create or roll back a savepoint, or alter the system or session.

The subprograms must be local.

The subprograms can be local or remote.

It allows you to exceed the maximum number of subprograms

Two subprograms with the same name must differ only in return type.

Two subprograms with the same name and number of formal parameters must have at least one parameter defined with a different data type.

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

Querying with the SELECT * notification.

Declaring variables with the %TYPE attribute.

Specifying schema names when referencing objects.

Declaring records by using the %ROWTYPE attribute.

Specifying package.procedure notation while executing procedures.

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

True

False

Data encryption

User authentication

Transparent data conversion

Configuration and administration mechanisms

Support for multiple network transport protocols