Ilp Oracle Quiz Prelims 1

1. How many copies of procedure needs to be loaded for execution by multiple users.

Only one copy

Equal to number of users

Equal to number of times the procedure is called

None of the above

When multiple users execute a procedure, only one copy of the procedure needs to be loaded. This is because the procedure code remains the same regardless of the number of users. Loading multiple copies would be redundant and inefficient. Therefore, to optimize resource utilization, only one copy of the procedure is loaded for execution by multiple users.

Explanation

When multiple users execute a procedure, only one copy of the procedure needs to be loaded. This is because the procedure code remains the same regardless of the number of users. Loading multiple copies would be redundant and inefficient. Therefore, to optimize resource utilization, only one copy of the procedure is loaded for execution by multiple users.

2. If we do not mention as row level then the trigger by default is?

A statement level

A row level

A database level

None of the above

When a trigger is not specified as row level, it means that it is designed to execute once for each triggering statement, regardless of the number of rows affected. In other words, the trigger will only be triggered once for a batch of rows affected by a single statement. Therefore, the correct answer is A statement level.

Explanation

When a trigger is not specified as row level, it means that it is designed to execute once for each triggering statement, regardless of the number of rows affected. In other words, the trigger will only be triggered once for a batch of rows affected by a single statement. Therefore, the correct answer is A statement level.

3. In which of the following trigger, we can use pseudo columns 'old' and 'new'?

Statement level trigger

Row level trigger

Any trigger

Schema level trigger

A row level trigger is the correct answer because it allows us to access the pseudo columns 'old' and 'new'. These pseudo columns are used to reference the old and new values of the row being affected by the trigger. This is useful for performing actions based on the old and new values of a row, such as updating related records or logging changes.

Explanation

A row level trigger is the correct answer because it allows us to access the pseudo columns 'old' and 'new'. These pseudo columns are used to reference the old and new values of the row being affected by the trigger. This is useful for performing actions based on the old and new values of a row, such as updating related records or logging changes.

4. In airline reservation system, the entities are date,flight number,place of departure,destination , type of plane and seats available.The primary key is

Flight number

Flight number + place of departure

Flight number + date

Flight number + destination

The primary key in an airline reservation system uniquely identifies each record in the system. In this case, the combination of flight number and date would be the most appropriate choice for the primary key. This is because each flight can have multiple occurrences on different dates, and each occurrence would have a unique combination of flight number and date. Using flight number alone may not be sufficient as it would not differentiate between different occurrences of the same flight. Therefore, flight number + date ensures that each record in the system is uniquely identified.

Explanation

The primary key in an airline reservation system uniquely identifies each record in the system. In this case, the combination of flight number and date would be the most appropriate choice for the primary key. This is because each flight can have multiple occurrences on different dates, and each occurrence would have a unique combination of flight number and date. Using flight number alone may not be sufficient as it would not differentiate between different occurrences of the same flight. Therefore, flight number + date ensures that each record in the system is uniquely identified.

5. When do we get the exception INVALID_CURSOR?

CURSOR is already open

CURSOR name is different in DECLARE as with OPEN

CURSOR name is duplicated

Referring the already closed CURSOR

The exception INVALID_CURSOR is thrown when the name of the cursor used in the DECLARE statement is different from the name used in the OPEN statement. This means that the cursor being referenced in the OPEN statement does not exist or is not declared properly, causing the exception to be raised.

Explanation

The exception INVALID_CURSOR is thrown when the name of the cursor used in the DECLARE statement is different from the name used in the OPEN statement. This means that the cursor being referenced in the OPEN statement does not exist or is not declared properly, causing the exception to be raised.

6. Which of the following is a valid When clause in a trigger?

When (:new.job 'President')

When (.new.job 'President')

When (new.job 'President')

None of the above

The correct answer is "When (new.job 'President')". In a trigger, the When clause is used to specify the condition under which the trigger should be executed. In this case, the When clause is checking if the value of the "job" attribute in the newly inserted or updated row is equal to "President". This means that the trigger will only be triggered when a row with the job "President" is inserted or updated. The other options are incorrect because they have syntax errors or incorrect usage of the ":new" keyword.

Explanation

The correct answer is "When (new.job 'President')". In a trigger, the When clause is used to specify the condition under which the trigger should be executed. In this case, the When clause is checking if the value of the "job" attribute in the newly inserted or updated row is equal to "President". This means that the trigger will only be triggered when a row with the job "President" is inserted or updated. The other options are incorrect because they have syntax errors or incorrect usage of the ":new" keyword.

7. Which of the following clauses is valid with cursor for update?

Where current of

Where cursor of

Where update of

None of the above

The "Where current of" clause is valid with cursor for update. This clause is used in SQL to specify that the current row being pointed to by a cursor should be updated. It allows for updating the row in a cursor without needing to specify any additional conditions or criteria.

Explanation

The "Where current of" clause is valid with cursor for update. This clause is used in SQL to specify that the current row being pointed to by a cursor should be updated. It allows for updating the row in a cursor without needing to specify any additional conditions or criteria.

8. You attempt to create the salary table with this command: 1. CREATE TABLE salary 2. (employee_id NUMBER(9) 3. CONSTRAINT salary_pk PRIMARY KEY, 4. 1995_salary NUMBER(8,2), 5. manager_nameVARCHAR2(25) 6. CONSTRAINT mgr_name_nn NOT NULL, 7. $salary_96NUMBER(8,2)); Which two lines of this statement will return errors?

4,5

1,7

4,7

3,7

Lines 4 and 7 will return errors. Line 4 will return an error because the column name "1995_salary" starts with a number, which is not allowed. Line 7 will return an error because the column name "$salary_96" contains special characters, specifically the dollar sign, which is not allowed.

Explanation

Lines 4 and 7 will return errors. Line 4 will return an error because the column name "1995_salary" starts with a number, which is not allowed. Line 7 will return an error because the column name "$salary_96" contains special characters, specifically the dollar sign, which is not allowed.

9. Which statement about implicit cursors is true?

Programmers need to close all the implicit cursors before the end of the PL/SQL program.

Programmers can declare implicit cursors by using the CURSOR type in the declaration section.

Implicit cursors are declared implicitly only for DML statements.

Implicit cursors are declared implicitly for all the DML and SELECT statements.

Implicit cursors are automatically created by the Oracle database for all DML (Data Manipulation Language) and SELECT statements. Programmers do not need to explicitly declare them using the CURSOR type in the declaration section. Additionally, there is no requirement for programmers to close implicit cursors before the end of the PL/SQL program.

Explanation

Implicit cursors are automatically created by the Oracle database for all DML (Data Manipulation Language) and SELECT statements. Programmers do not need to explicitly declare them using the CURSOR type in the declaration section. Additionally, there is no requirement for programmers to close implicit cursors before the end of the PL/SQL program.

10. To find information about trigger status, which of the following views are appropriate?

USER_TRIGGERS

USER_OBJECTS

USER_TRIGGER_COLS

ALL_TRIGGERS

The appropriate view to find information about trigger status is USER_TRIGGERS. This view contains information about all triggers owned by the current user, including their names, types, status, and the tables they are associated with. USER_OBJECTS provides information about all objects owned by the current user, but it does not specifically focus on triggers. USER_TRIGGER_COLS provides information about the columns used in triggers, but it does not provide status information. ALL_TRIGGERS provides information about all triggers accessible to the current user, not just those owned by the user.

Explanation

The appropriate view to find information about trigger status is USER_TRIGGERS. This view contains information about all triggers owned by the current user, including their names, types, status, and the tables they are associated with. USER_OBJECTS provides information about all objects owned by the current user, but it does not specifically focus on triggers. USER_TRIGGER_COLS provides information about the columns used in triggers, but it does not provide status information. ALL_TRIGGERS provides information about all triggers accessible to the current user, not just those owned by the user.

11. Evaluate this SQL script: CREATE ROLE manager; " CREATE ROLE clerk; CREATE ROLE inventory; CREATE USER scott IDENTIFIED BY tiger; GRANT inventory TO clerk; GRANT clerk TO manager; GRANT inventory TO scott / How many roles will user SCOTT have access to?

1

2

0

3

The user SCOTT will have access to 1 role. This can be determined by looking at the GRANT statements in the script. SCOTT is granted the role inventory, but no other roles are granted to SCOTT. Therefore, SCOTT will only have access to the inventory role.

Explanation

The user SCOTT will have access to 1 role. This can be determined by looking at the GRANT statements in the script. SCOTT is granted the role inventory, but no other roles are granted to SCOTT. Therefore, SCOTT will only have access to the inventory role.

12. Which table name is valid?

Invoices-1996

Catch_#22

1996_invoices

.#_667

The table name "catch_#22" is valid because it follows the rules for table names. Table names can contain letters, numbers, and underscores, but they cannot start with a number or contain special characters like hyphens or periods. "catch_#22" meets these criteria and is a valid table name.

Explanation

The table name "catch_#22" is valid because it follows the rules for table names. Table names can contain letters, numbers, and underscores, but they cannot start with a number or contain special characters like hyphens or periods. "catch_#22" meets these criteria and is a valid table name.

13. Which view should a user query to display the columns associated with the constraints on a table owned bythe user?

USER_CONSTRAINTS

ALL_CONSTRAINTS

USER_CONS_COLUMNS

USER_COLUMNS

The USER_CONS_COLUMNS view should be queried to display the columns associated with the constraints on a table owned by the user. This view provides information about the columns that are part of the constraints defined on tables owned by the user. It includes details such as the table name, constraint name, column name, and position of the column within the constraint. By querying this view, the user can easily retrieve the necessary information about the columns associated with the constraints on their table.

Explanation

The USER_CONS_COLUMNS view should be queried to display the columns associated with the constraints on a table owned by the user. This view provides information about the columns that are part of the constraints defined on tables owned by the user. It includes details such as the table name, constraint name, column name, and position of the column within the constraint. By querying this view, the user can easily retrieve the necessary information about the columns associated with the constraints on their table.

14. Which is not a valid type of trigger

Database trigger

Application trigger

Transaction trigger

System trigger

An application trigger is not a valid type of trigger. Database triggers are used to automatically perform actions in response to specific events or changes in a database. Transaction triggers are used to initiate actions when a specific transaction occurs. System triggers are used to perform actions when certain system events occur. However, an application trigger is not a recognized type of trigger in the context of database management systems.

Explanation

An application trigger is not a valid type of trigger. Database triggers are used to automatically perform actions in response to specific events or changes in a database. Transaction triggers are used to initiate actions when a specific transaction occurs. System triggers are used to perform actions when certain system events occur. However, an application trigger is not a recognized type of trigger in the context of database management systems.

15. Which datatype should you use for interest rates with varying and unpredictable decimal places, such as 1.234, 3.4, 5, and 1.23?

NUMBER(p,s)

LONG

NUMBER

Float

The correct answer is NUMBER because it is a datatype in most programming languages that can handle decimal numbers with varying and unpredictable decimal places. The NUMBER datatype allows for precision and scale to be specified, which means it can accommodate any number of decimal places. This makes it suitable for storing interest rates with varying decimal places such as 1.234, 3.4, 5, and 1.23.

Explanation

The correct answer is NUMBER because it is a datatype in most programming languages that can handle decimal numbers with varying and unpredictable decimal places. The NUMBER datatype allows for precision and scale to be specified, which means it can accommodate any number of decimal places. This makes it suitable for storing interest rates with varying decimal places such as 1.234, 3.4, 5, and 1.23.

16. Which of the following is false?

Generally we use commit/rollback inside the cursor for loop

Generally we use commit/rollback outside the cursor for loop

Generally we do not use commit/rollback with the cursor for loop

None of the above

In general, we use commit/rollback inside the cursor for loop to ensure that each iteration of the loop is treated as a separate transaction. This allows us to control the commit or rollback behavior for each individual iteration. On the other hand, using commit/rollback outside the cursor for loop would treat the entire loop as a single transaction, which may not be desired in certain scenarios. Therefore, the statement "Generally we use commit/rollback outside the cursor for loop" is false.

Explanation

In general, we use commit/rollback inside the cursor for loop to ensure that each iteration of the loop is treated as a separate transaction. This allows us to control the commit or rollback behavior for each individual iteration. On the other hand, using commit/rollback outside the cursor for loop would treat the entire loop as a single transaction, which may not be desired in certain scenarios. Therefore, the statement "Generally we use commit/rollback outside the cursor for loop" is false.