Test Your Oracle AUTO INCREMENT Knowledge

Progress

True / False

In Oracle, the AUTO_INCREMENT keyword is used to automatically generate unique values for a column.

True False

Oracle does not use the AUTO_INCREMENT keyword. Instead, it uses sequences and triggers or the IDENTITY column feature to generate unique values automatically.

True / False

Oracle sequences are used to generate unique numbers for auto-increment functionality.

True False

Sequences in Oracle are database objects used to generate a series of unique numbers, which can be used for auto-increment functionality.

True / False

A sequence in Oracle can be used to populate a primary key column with unique values.

True False

Sequences can be used to populate primary key columns with unique values to ensure each row has a unique identifier.

True / False

In Oracle, you can create a sequence using the CREATE SEQUENCE statement.

True False

The CREATE SEQUENCE statement is used to create a new sequence in Oracle.

True / False

The NEXTVAL and CURRVAL pseudocolumns are used in Oracle to retrieve the next and current values from a sequence.

True False

NEXTVAL retrieves the next value in the sequence, while CURRVAL retrieves the current value of the sequence.

True / False

In Oracle, the IDENTITY column feature provides an alternative to using sequences for auto-increment functionality.

True False

Oracle introduced the IDENTITY column feature as an alternative to sequences, simplifying the auto-increment functionality directly in the table definition.

True / False

In Oracle, a sequence can be used in combination with a trigger to automatically insert a unique value into a column.

True False

A trigger can be defined to automatically insert the next value from a sequence into a column whenever a new row is inserted into a table.

True / False

The CACHE option in the CREATE SEQUENCE statement improves performance by preallocating sequence values.

True False

The CACHE option preallocates a set of sequence values in memory, reducing the number of disk I/O operations and improving performance.

True / False

The IDENTITY column feature in Oracle does not allow customization of the start value or increment value.

True False

The IDENTITY column feature allows customization of the start value and increment value, providing flexibility in how the values are generated.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on implementing auto-increment functionality using sequences, triggers, and the IDENTITY column feature.

True False

The OCP certification covers advanced topics, including how to implement auto-increment functionality using sequences, triggers, and the IDENTITY column feature, reflecting the candidate's comprehensive understanding of Oracle database management.

Q11

Single Choice

What is the primary purpose of the Auto Increment feature in Oracle?

To automatically increase the value of a sequence every time a row is inserted. To automatically assign a default value to a column. To ensure that all values in a column are unique.To reset the value of a column to zero after each insertion.

The Auto Increment feature in Oracle is typically implemented using sequences to automatically generate unique values that increment with each new row insertion.

Q12

Single Choice

In Oracle, which of the following SQL commands creates a sequence that starts at 1 and increments by 1?

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE emp_seq START AT 1 INCREASE BY 1; CREATE SEQUENCE emp_seq BEGIN WITH 1 ADD 1;CREATE SEQUENCE emp_seq INITIATE WITH 1 INCREASE BY 1;

The correct syntax in Oracle to create a sequence that starts at 1 and increments by 1 is CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1.

Q13

Single Choice

Which of the following is true about using a sequence in Oracle to implement Auto Increment?

Sequences guarantee no gaps between generated numbers. Sequences can be used across multiple tables. Sequences automatically reset when the maximum value is reached.Sequences can only be used in tables with a primary key.

Sequences in Oracle are independent objects and can be used across multiple tables to generate unique values.

Q14

Single Choice

How do you use a sequence to insert an Auto Incremented value into the ID column of the EMPLOYEES table in Oracle?

INSERT INTO EMPLOYEES (ID, NAME) VALUES (emp_seq.NEXTVAL, 'John Doe'); INSERT INTO EMPLOYEES (ID, NAME) VALUES (emp_seq.CURRVAL, 'John Doe'); INSERT INTO EMPLOYEES (ID, NAME) VALUES (emp_seq.VALUE, 'John Doe');INSERT INTO EMPLOYEES (ID, NAME) VALUES (emp_seq.AUTO, 'John Doe');

The correct way to use a sequence in Oracle to insert an Auto Incremented value is by using emp_seq.NEXTVAL.

Q15

Single Choice

In Oracle, what will happen if you attempt to insert a value into a column that uses a sequence for Auto Increment without specifying the sequence?

SQL Code

CREATE TABLE EMPLOYEES (
 ID NUMBER PRIMARY KEY,
 NAME VARCHAR2(100)
);

INSERT INTO EMPLOYEES (NAME)
VALUES ('Jane Smith');

The ID column will automatically use the next value from the sequence. The insert will fail with an error. The ID column will be set to NULL.The ID column will generate a random number.

If you do not specify the sequence for an Auto Incremented column, the insert will fail with an error since the ID column is NOT NULL and no value is provided.

Q16

Single Choice

Which of the following Oracle SQL commands modifies an existing sequence to increment by 10 instead of 1?

ALTER SEQUENCE emp_seq INCREMENT BY 10; MODIFY SEQUENCE emp_seq INCREMENT BY 10; ALTER SEQUENCE emp_seq SET INCREMENT BY 10;MODIFY SEQUENCE emp_seq SET INCREMENT BY 10;

The correct syntax to modify an existing sequence in Oracle to change its increment value is ALTER SEQUENCE emp_seq INCREMENT BY 10.

Q17

Single Choice

Consider the following Oracle SQL sequence creation. What will happen if the sequence reaches its maximum value?

SQL Code

CREATE SEQUENCE emp_seq
 START WITH 1
 INCREMENT BY 1
 MAXVALUE 1000
 CYCLE;

The sequence will stop generating values. The sequence will reset to 1 and continue incrementing. The sequence will generate an error.The sequence will continue from 1000.

The CYCLE option allows the sequence to reset to its MINVALUE after reaching its MAXVALUE and continue incrementing.

Q18

Single Choice

In Oracle, how would you create a sequence that starts at 1000 and increments by 5, with no maximum value?

CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 5 NOMAXVALUE; CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 5 MAXVALUE UNLIMITED; CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 5 NOMAX;CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 5 NO MAXVALUE;

The NOMAXVALUE option in Oracle creates a sequence with no maximum limit.

Q19

Single Choice

What is the purpose of the CACHE option when creating a sequence in Oracle?

SQL Code

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
CACHE 20;

To store the last 20 values generated by the sequence. To preallocate 20 sequence numbers in memory to improve performance. To limit the sequence to generate only 20 values.To ensure the sequence restarts after 20 increments.

The CACHE option in Oracle preallocates a specified number of sequence values in memory, improving performance by reducing disk access.

Q20

Single Choice

In Oracle, which of the following is true when using sequences to implement Auto Increment in a table?

Sequences must always be associated with a primary key column. Sequences can be used to generate unique values across multiple tables. Sequences automatically reset when the database is restarted.Sequences can only generate positive integers.

Sequences in Oracle are independent objects and can be used to generate unique values across multiple tables, not just for primary key columns.

Q21

Multiple Choice

Identify the SQL scripts that correctly define an AUTO INCREMENT column using a sequence in Oracle.

CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1; CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, name VARCHAR2(100) ); CREATE TRIGGER trg_auto_increment BEFORE INSERT ON customers FOR EACH ROW BEGIN SELECT customer_seq.NEXTVAL INTO :NEW.customer_id FROM dual; END; CREATE TABLE customers ( customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR2(100) ); CREATE TABLE customers ( customer_id NUMBER AUTO_INCREMENT PRIMARY KEY, name VARCHAR2(100) );CREATE SEQUENCE customer_seq; CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY DEFAULT customer_seq.NEXTVAL, name VARCHAR2(100) );

In Oracle, an AUTO INCREMENT feature is typically implemented using a sequence and a trigger. The correct scripts will demonstrate how to set up a sequence and use it to auto-increment the 'customer_id' column.

Q22

Multiple Choice

Select the SQL scripts that correctly modify an existing table to add an AUTO INCREMENT column in Oracle.

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1; ALTER TABLE orders ADD order_id NUMBER; CREATE TRIGGER trg_order_auto_increment BEFORE INSERT ON orders FOR EACH ROW BEGIN SELECT order_seq.NEXTVAL INTO :NEW.order_id FROM dual; END; ALTER TABLE orders ADD order_id NUMBER GENERATED BY DEFAULT AS IDENTITY; ALTER TABLE orders ADD order_id NUMBER AUTO_INCREMENT;CREATE SEQUENCE order_seq; ALTER TABLE orders ADD order_id NUMBER DEFAULT order_seq.NEXTVAL;

Adding an AUTO INCREMENT column to an existing table requires creating a sequence and a trigger. The correct scripts will demonstrate how to modify the table and add the necessary sequence and trigger.

Q23

Multiple Choice

Determine the SQL scripts that correctly insert data into a table with an AUTO INCREMENT column in Oracle.

INSERT INTO employees (name, department) VALUES ('John Doe', 'HR'); INSERT INTO employees (employee_id, name, department) VALUES (employee_seq.NEXTVAL, 'John Doe', 'HR'); INSERT INTO employees (employee_id, name, department) VALUES (NULL, 'John Doe', 'HR');INSERT INTO employees (name, department) VALUES ('Jane Smith', 'Finance');

When inserting data into a table with an AUTO INCREMENT column, you typically do not need to specify the value for that column. The correct scripts will demonstrate how to insert data without providing the 'employee_id'.

Q24

Multiple Choice

Identify the SQL scripts that correctly handle updating an AUTO INCREMENT column in Oracle.

UPDATE products SET product_id = 100 WHERE product_name = 'Widget'; ALTER TRIGGER trg_auto_increment DISABLE; UPDATE products SET product_id = 100 WHERE product_name = 'Widget'; ALTER TRIGGER trg_auto_increment ENABLE; UPDATE products SET product_id = product_seq.NEXTVAL WHERE product_name = 'Widget';UPDATE products SET product_id = 100 WHERE product_id = 10;

Directly updating an AUTO INCREMENT column is typically not recommended as it is managed by a sequence. The correct scripts will demonstrate how to handle this scenario.

Q25

Multiple Choice

Select the SQL scripts that correctly enforce an AUTO INCREMENT constraint during table creation in Oracle.

CREATE TABLE invoices ( invoice_id NUMBER GENERATED BY DEFAULT AS IDENTITY, invoice_date DATE, amount NUMBER ); CREATE TABLE invoices ( invoice_id NUMBER AUTO_INCREMENT PRIMARY KEY, invoice_date DATE, amount NUMBER ); CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, invoice_date DATE, amount NUMBER ); CREATE SEQUENCE invoice_seq; CREATE TRIGGER trg_invoice_auto_increment BEFORE INSERT ON invoices FOR EACH ROW BEGIN SELECT invoice_seq.NEXTVAL INTO :NEW.invoice_id FROM dual; END;CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY DEFAULT invoice_seq.NEXTVAL, invoice_date DATE, amount NUMBER );

Creating an AUTO INCREMENT column during table creation in Oracle involves using sequences or the IDENTITY feature. The correct scripts will demonstrate this.

Q26

Multiple Choice

Identify the SQL scripts that correctly drop an AUTO INCREMENT sequence and trigger in Oracle.

DROP SEQUENCE employee_seq; DROP TRIGGER trg_employee_auto_increment; ALTER TABLE employees DROP COLUMN employee_id; DROP TRIGGER trg_employee_auto_increment; DROP SEQUENCE employee_seq;DROP SEQUENCE employee_seq; DROP TRIGGER trg_employee_auto_increment; ALTER TABLE employees MODIFY employee_id SET DEFAULT NULL;

Dropping an AUTO INCREMENT feature involves removing the sequence and trigger associated with it. The correct scripts will demonstrate how to drop these objects.

Q27

Multiple Choice

Determine the SQL scripts that correctly handle inserting a specific value into an AUTO INCREMENT column in Oracle.

INSERT INTO orders (order_id, order_date, amount) VALUES (1000, SYSDATE, 250.00); ALTER TRIGGER trg_order_auto_increment DISABLE; INSERT INTO orders (order_id, order_date, amount) VALUES (1000, SYSDATE, 250.00); ALTER TRIGGER trg_order_auto_increment ENABLE; INSERT INTO orders (order_date, amount) VALUES (SYSDATE, 250.00);INSERT INTO orders (order_id, order_date, amount) VALUES (order_seq.NEXTVAL, SYSDATE, 250.00);

Inserting a specific value into an AUTO INCREMENT column usually requires careful handling to avoid sequence conflicts. The correct scripts will demonstrate how to insert specific values safely.

Q28

Multiple Choice

Select the SQL scripts that correctly reset the AUTO INCREMENT sequence in Oracle.

DROP SEQUENCE invoice_seq; CREATE SEQUENCE invoice_seq START WITH 1 INCREMENT BY 1; ALTER SEQUENCE invoice_seq RESTART WITH 1; ALTER SEQUENCE invoice_seq INCREMENT BY -1 MINVALUE 1 START WITH 1;DROP SEQUENCE invoice_seq; ALTER TABLE invoices MODIFY invoice_id RESTART WITH 1;

Resetting a sequence in Oracle typically involves dropping and recreating the sequence. The correct scripts will demonstrate how to reset the sequence value.

Q29

Multiple Choice

Identify the SQL scripts that correctly use an AUTO INCREMENT feature with composite keys in Oracle.

CREATE SEQUENCE subscription_seq START WITH 1 INCREMENT BY 1; CREATE TABLE subscriptions ( subscription_id NUMBER, user_id NUMBER, PRIMARY KEY (subscription_id, user_id) ); CREATE TRIGGER trg_subscription_auto_increment BEFORE INSERT ON subscriptions FOR EACH ROW BEGIN SELECT subscription_seq.NEXTVAL INTO :NEW.subscription_id FROM dual; END; CREATE TABLE subscriptions ( subscription_id NUMBER GENERATED BY DEFAULT AS IDENTITY, user_id NUMBER, PRIMARY KEY (subscription_id, user_id) ); CREATE TABLE subscriptions ( subscription_id NUMBER AUTO_INCREMENT, user_id NUMBER, PRIMARY KEY (subscription_id, user_id) );CREATE TABLE subscriptions ( subscription_id NUMBER DEFAULT subscription_seq.NEXTVAL, user_id NUMBER, PRIMARY KEY (subscription_id, user_id) );

Implementing AUTO INCREMENT with composite keys involves careful handling of the sequence and key definitions. The correct scripts will demonstrate how to set up this scenario.

Q30

Multiple Choice

Determine the SQL scripts that correctly handle disabling and enabling an AUTO INCREMENT feature in Oracle.

ALTER TRIGGER trg_product_auto_increment DISABLE; -- Perform bulk insert operations ALTER TRIGGER trg_product_auto_increment ENABLE; DROP SEQUENCE product_seq; CREATE SEQUENCE product_seq START WITH (SELECT MAX(product_id) + 1 FROM products) INCREMENT BY 1; -- Perform bulk insert operations ALTER TRIGGER trg_product_auto_increment ENABLE; ALTER TABLE products MODIFY product_id DROP DEFAULT; -- Perform bulk insert operations ALTER TABLE products MODIFY product_id DEFAULT product_seq.NEXTVAL;ALTER TABLE products DISABLE CONSTRAINT product_id_auto_increment; -- Perform bulk insert operations ALTER TABLE products ENABLE CONSTRAINT product_id_auto_increment;

Disabling and enabling the AUTO INCREMENT feature involves manipulating the sequence and trigger associated with it. The correct scripts will demonstrate how to temporarily disable and then re-enable the feature.

Oracle Database Quiz Questions