Ilp Oracle Quiz Prelims 1

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Srikanth_kanthet
S
Srikanth_kanthet
Community Contributor
Quizzes Created: 5 | Total Attempts: 904
| Attempts: 263
Quiz Flashcard
SettingsSettings
Please wait...
  • 1/16 Questions

    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
Please wait...
Ilp Oracle Quiz Prelims 1 - Quiz
About This Quiz

ILP Oracle Quiz Prelims 1 tests knowledge on SQL syntax, table creation, role management, and datatype selections in Oracle databases. It assesses practical skills crucial for database management and SQL programming, enhancing both academic learning and professional competency.

Quiz Preview

  • 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

    Correct Answer
    A. 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.

    Rate this question:

  • 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

    Correct Answer
    A. Row level trigger
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Flight number + date
    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.

    Rate this question:

  • 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

    Correct Answer
    A. CURSOR name is different in DECLARE as with OPEN
    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.

    Rate this question:

  • 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

    Correct Answer
    A. When (new.job 'President')
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Where current of
    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.

    Rate this question:

  • 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

    Correct Answer
    A. 4,7
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. Implicit cursors are declared implicitly for all the DML and SELECT statements.
    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.

    Rate this question:

  • 10. 

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

    • USER_TRIGGERS

    • USER_OBJECTS

    • USER_TRIGGER_COLS

    • ALL_TRIGGERS

    Correct Answer
    A. USER_TRIGGERS
    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.

    Rate this question:

  • 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

    Correct Answer
    A. 1
    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.

    Rate this question:

  • 12. 

    Which table name is valid?

    • Invoices-1996

    • Catch_#22

    • 1996_invoices

    • .#_667

    Correct Answer
    A. Catch_#22
    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.

    Rate this question:

  • 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

    Correct Answer
    A. USER_CONS_COLUMNS
    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.

    Rate this question:

  • 14. 

    Which is not a valid type of trigger

    • Database trigger

    • Application trigger

    • Transaction trigger

    • System trigger

    Correct Answer
    A. Application trigger
    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.

    Rate this question:

  • 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

    Correct Answer
    A. NUMBER
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Generally we use commit/rollback outside the cursor for loop
    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.

    Rate this question:

Quiz Review Timeline (Updated): Mar 22, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Mar 22, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Sep 12, 2012
    Quiz Created by
    Srikanth_kanthet
Back to Top Back to top
Advertisement