Oracle Sequences, Indexes, and Synonyms: Concepts u0026 Usage Quiz Quiz

Explore essential concepts and practical uses of sequences, indexes, and synonyms in PL/SQL with this quiz tailored for those seeking to strengthen their foundational understanding. Covered topics include creation, benefits, and syntax details related to database object management for efficient development and querying.

1. Purpose of Sequences

   Which best describes the purpose of a sequence in PL/SQL for generating values in a table?

   1. Creates duplicate rows for testing.
   2. Sorts table records based on a column.
   3. Encrypts data in a column.
   4. Automatically generates unique numbers for use as primary keys.

   Explanation: Sequences are used to automatically create a unique set of numbers, often for primary keys or other unique identifiers in tables. Sorting table records is handled by queries, not sequences. Creating duplicate rows does not involve sequences. Encryption concerns data security, not value generation.

2. Syntax for Creating a Sequence

   Which statement correctly creates a sequence named EMP_SEQ that starts at 1 and increments by 1?

   1. CREATE INDEX EMP_SEQ START WITH 1 INCREMENT BY 1;
   2. CREATE SEQUENCE EMP_SEQ INCREMENT 1 FROM 1;
   3. CREATE SEQUENCE EMP_SEQ START WITH 1 INCREMENT BY 1;
   4. CREATE SYNONYM EMP_SEQ FOR EMPLOYEES;

   Explanation: The correct syntax uses CREATE SEQUENCE followed by the sequence name with START WITH and INCREMENT BY clauses. The option using CREATE INDEX incorrectly refers to indexes, not sequences. The SYNONYM statement defines aliases, and the fourth option uses incorrect keywords for sequence creation.

3. Index Functionality

   What is the primary function of an index in a table in PL/SQL databases?

   1. To generate unique row identifiers.
   2. To assign default values to columns.
   3. To quickly locate specific rows within a table.
   4. To back up database objects.

   Explanation: Indexes are used to speed up the retrieval of rows by providing fast access paths to the data. Assigning default values involves table or column definitions. Backups concern data safety, not search performance. Unique identifiers are managed by constraints or sequences.

4. Using NEXTVAL

   When inserting data, how can you use a sequence named ORD_SEQ to get the next available value?

   1. ORD_SEQ.LASTVAL
   2. ORD_SEQ.NEXTNUMBER
   3. ORD_SEQ.NEXTVAL
   4. ORD_SEQ.CURRVAL

   Explanation: NEXTVAL is the method used to retrieve the next available sequence value, making it suitable for inserting new rows. CURRVAL returns the current value, but only after NEXTVAL is called in the session. LASTVAL and NEXTNUMBER are not valid sequence keywords in PL/SQL.

5. Creating a Synonym

   Which command creates a synonym named EMP for the table EMPLOYEES?

   1. CREATE SEQUENCE EMP FOR EMPLOYEES;
   2. CREATE SYNONYM EMPLOYEES FOR EMP;
   3. CREATE SYNONYM EMP FOR EMPLOYEES;
   4. CREATE INDEX EMP FOR EMPLOYEES;

   Explanation: CREATE SYNONYM EMP FOR EMPLOYEES creates a public or private synonym EMP for easier referencing of the EMPLOYEES table. SEQUENCE and INDEX keywords do not create synonyms. The last option reverses the table and synonym names, which is incorrect.

6. Dropping an Index

   Which command correctly removes an index named IDX_DEPT?

   1. DELETE INDEX IDX_DEPT;
   2. DROP INDEX IDX_DEPT;
   3. REMOVE INDEX IDX_DEPT;
   4. DROP SEQUENCE IDX_DEPT;

   Explanation: The DROP INDEX statement is used to remove an index. DROP SEQUENCE is used for sequences, not indexes. DELETE and REMOVE are not valid commands for dropping indexes in PL/SQL.

7. CURRVAL Usage

   In which situation can CURRVAL of a sequence be successfully referenced within a session?

   1. Without ever calling NEXTVAL in any session.
   2. After NEXTVAL for the same sequence has been used in the session.
   3. Even if the sequence doesn't exist.
   4. Before any NEXTVAL for the sequence has been called in the session.

   Explanation: CURRVAL returns the current value only after NEXTVAL has been used in the session to generate a value. Using CURRVAL before any NEXTVAL results in an error. If the sequence does not exist or has never been used, CURRVAL cannot be referenced.

8. Index Type Identification

   Which type of index automatically enforces uniqueness on the indexed column(s)?

   1. Bitmap index
   2. Non-unique index
   3. Unique index
   4. Clustered index

   Explanation: A unique index ensures that no duplicate values exist in the indexed columns. Bitmap and non-unique indexes do not enforce uniqueness, and 'clustered index' is not a standard term in PL/SQL.

9. Public Synonym Usage

   What is a key benefit of creating a public synonym for a database object?

   1. It allows all users to reference the object without specifying the schema name.
   2. It restricts access to the object to only one user.
   3. It makes the object read-only.
   4. It improves query performance automatically.

   Explanation: Public synonyms make it possible for all users to access the object without specifying its owner or schema. They don't restrict access or inherently improve performance. Making an object read-only requires different permissions or constraints.

10. Modifying Sequence Values

    Which statement about altering an existing sequence’s increment value is correct?

    1. You must drop and recreate the sequence to modify increments.
    2. The INCREMENT BY value cannot be changed once set.
    3. You should use ALTER INDEX to change increments.
    4. You can use ALTER SEQUENCE to change the INCREMENT BY value.

    Explanation: ALTER SEQUENCE allows changes to properties like INCREMENT BY for a sequence. Dropping and recreating is unnecessary unless more drastic changes are needed. The value is not permanently fixed. ALTER INDEX has no effect on sequences.