Test Your Oracle Surrogate Key Knowledge with This Quiz

Progress

True / False

In ORACLE, a Surrogate Key is a unique identifier that is not derived from application data.

True False

A Surrogate Key is an artificial identifier that is not derived from the actual data in the table but is generated by the system to uniquely identify rows.

True / False

Surrogate Keys in ORACLE are often created using sequences.

True False

Sequences are commonly used in ORACLE to generate Surrogate Keys, providing a unique value for each row.

True / False

A Surrogate Key in ORACLE can be used as a Primary Key.

True False

Surrogate Keys are frequently used as Primary Keys because they provide a unique and stable identifier for each row.

True / False

Surrogate Keys in ORACLE should be meaningful to the business.

True False

Surrogate Keys are intentionally meaningless to the business and are only used to uniquely identify rows within the database.

True / False

In ORACLE, using a Surrogate Key can help improve performance when joining tables.

True False

Surrogate Keys, being typically numeric and indexed, can improve join performance compared to Natural Keys which may be composite or string-based.

True / False

Surrogate Keys in ORACLE should be updated when the business requirements change.

True False

Surrogate Keys should never be updated because they are system-generated and do not change based on business requirements.

True / False

In ORACLE, a Surrogate Key can be generated using a UUID.

True False

A UUID (Universal Unique Identifier) can be used as a Surrogate Key, ensuring uniqueness across different tables or databases.

True / False

Surrogate Keys in ORACLE can cause issues with data migration between systems.

True False

Surrogate Keys generally simplify data migration because they are system-generated and consistent, unlike Natural Keys which may change or be inconsistent.

True / False

In ORACLE, a Surrogate Key should be indexed to ensure fast lookups.

True False

Indexing a Surrogate Key ensures fast lookups and efficient data retrieval.

Q10

True / False

In ORACLE, a Surrogate Key can be a composite key.

True False

A Surrogate Key is typically a single column, unique identifier generated by the system. Composite keys are usually formed from Natural Keys or business-related attributes.

Q11

Multiple Choice

Identify the SQL statements that correctly define a surrogate key in an Oracle table.

SQL Code

CREATE TABLE customers (
 customer_id NUMBER,
 customer_name VARCHAR2(100)
);

CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1; CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY DEFAULT customer_seq.NEXTVAL, customer_name VARCHAR2(100) ); CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100) ); ALTER TABLE customers ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id);CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100) ); ALTER TABLE customers MODIFY customer_id DEFAULT customer_seq.NEXTVAL;

A surrogate key is a unique identifier for each record in a table, typically implemented as an auto-incrementing number that is not derived from the data. The correct SQL statements will involve creating a sequence and using it to generate unique keys.

Q12

Multiple Choice

Select the SQL statements that correctly demonstrate how to handle surrogate keys during data insertion.

INSERT INTO orders (order_id, order_date) VALUES (order_seq.NEXTVAL, '2024-01-01'); INSERT INTO orders (order_date) VALUES ('2024-01-01'); INSERT INTO orders (order_id, order_date) VALUES (1, '2024-01-01');INSERT INTO orders (order_id, order_date) VALUES (order_seq.CURRVAL, '2024-01-01');

When inserting data into a table with a surrogate key, the key value should be automatically generated using a sequence. The correct SQL statements will ensure that the sequence is used during data insertion.

Q13

Multiple Choice

Determine the SQL statements that demonstrate the use of surrogate keys in composite key scenarios.

CREATE SEQUENCE enrollment_seq START WITH 1 INCREMENT BY 1; CREATE TABLE enrollments ( enrollment_id NUMBER DEFAULT enrollment_seq.NEXTVAL, course_id NUMBER, PRIMARY KEY (enrollment_id, course_id) ); CREATE TABLE enrollments ( enrollment_id NUMBER, course_id NUMBER, PRIMARY KEY (enrollment_id, course_id) ); ALTER TABLE enrollments ADD CONSTRAINT pk_enrollment PRIMARY KEY (enrollment_id, course_id);CREATE TABLE enrollments ( enrollment_id NUMBER PRIMARY KEY, course_id NUMBER ); ALTER TABLE enrollments ADD PRIMARY KEY (course_id);

In composite key scenarios, a surrogate key can be one part of the composite primary key. The correct SQL statements will define the surrogate key using a sequence and include it as part of the composite key.

Q14

Multiple Choice

Identify the SQL statements that enforce referential integrity using surrogate keys in a foreign key relationship.

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE ); CREATE TABLE order_items ( item_id NUMBER PRIMARY KEY, order_id NUMBER, CONSTRAINT fk_order_item FOREIGN KEY (order_id) REFERENCES orders(order_id) ); CREATE TABLE orders ( order_id NUMBER, order_date DATE ); ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id); CREATE TABLE order_items ( item_id NUMBER PRIMARY KEY, order_id NUMBER, CONSTRAINT fk_order_item FOREIGN KEY (order_id) REFERENCES orders(order_id) ); ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (order_id); ALTER TABLE order_items ADD CONSTRAINT fk_order_item FOREIGN KEY (order_id) REFERENCES orders(order_id);CREATE TABLE order_items ( item_id NUMBER PRIMARY KEY, order_id NUMBER, CONSTRAINT fk_order_item FOREIGN KEY (order_id) REFERENCES orders(order_id) );

Surrogate keys can be used as foreign keys to establish relationships between tables. The correct SQL statements will define the foreign key constraint referencing the surrogate key in the parent table.

Q15

Multiple Choice

Choose the SQL statements that correctly demonstrate the use of surrogate keys in data partitioning.

CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, invoice_date DATE, amount NUMBER ) PARTITION BY HASH (invoice_id) PARTITIONS 4; CREATE TABLE invoices ( invoice_id NUMBER, invoice_date DATE, amount NUMBER, PRIMARY KEY (invoice_id) ) PARTITION BY RANGE (invoice_date) ( PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')), PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) ); CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, invoice_date DATE, amount NUMBER ) PARTITION BY LIST (invoice_id) ( PARTITION p1 VALUES (1, 2, 3), PARTITION p2 VALUES (4, 5, 6) );ALTER TABLE invoices PARTITION BY RANGE (invoice_date) ( PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')), PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) );

Partitioning tables based on surrogate keys can significantly improve performance by distributing the data across multiple storage segments. The correct SQL statements will include the partitioning clauses.

Q16

Multiple Choice

Which SQL statements demonstrate how to modify an existing surrogate key constraint in Oracle?

CREATE SEQUENCE new_employee_seq START WITH 1 INCREMENT BY 1; ALTER TABLE employees MODIFY employee_id DEFAULT new_employee_seq.NEXTVAL; ALTER TABLE employees DROP CONSTRAINT pk_employee; CREATE SEQUENCE new_employee_seq START WITH 1 INCREMENT BY 1; ALTER TABLE employees ADD PRIMARY KEY (employee_id) USING INDEX DEFAULT new_employee_seq.NEXTVAL; CREATE SEQUENCE new_employee_seq START WITH 1 INCREMENT BY 1; ALTER TABLE employees MODIFY CONSTRAINT pk_employee DEFAULT new_employee_seq.NEXTVAL;ALTER TABLE employees DROP CONSTRAINT pk_employee; ALTER TABLE employees ADD CONSTRAINT pk_employee PRIMARY KEY (employee_id) USING INDEX DEFAULT new_employee_seq.NEXTVAL;

Modifying an existing surrogate key usually involves creating a new sequence and then associating it with the surrogate key. The correct SQL statements will reflect these steps.

Q17

Multiple Choice

Determine the SQL statements that demonstrate the use of surrogate keys with unique constraints.

CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_code VARCHAR2(100) UNIQUE ); CREATE SEQUENCE product_seq START WITH 1 INCREMENT BY 1; CREATE TABLE products ( product_id NUMBER PRIMARY KEY DEFAULT product_seq.NEXTVAL, product_code VARCHAR2(100) UNIQUE ); ALTER TABLE products ADD PRIMARY KEY (product_id); ALTER TABLE products ADD CONSTRAINT unique_product_code UNIQUE (product_code);CREATE TABLE products ( product_id NUMBER, product_code VARCHAR2(100) ); ALTER TABLE products ADD CONSTRAINT unique_product_code UNIQUE (product_code);

Surrogate keys ensure uniqueness and can be used in conjunction with unique constraints on other columns. The correct SQL statements will define both the surrogate key and the unique constraint.

Q18

Multiple Choice

Identify the SQL statements that demonstrate the use of surrogate keys in distributed databases.

CREATE SEQUENCE global_branch_seq START WITH 1 INCREMENT BY 1; CREATE TABLE branch ( branch_id NUMBER PRIMARY KEY DEFAULT global_branch_seq.NEXTVAL, branch_name VARCHAR2(100) ); CREATE TABLE branch ( branch_id NUMBER PRIMARY KEY, branch_name VARCHAR2(100) ); CREATE SEQUENCE global_branch_seq START WITH 1 INCREMENT BY 1; ALTER TABLE branch MODIFY branch_id DEFAULT global_branch_seq.NEXTVAL; CREATE SEQUENCE local_branch_seq START WITH 1 INCREMENT BY 10; CREATE TABLE branch ( branch_id NUMBER PRIMARY KEY DEFAULT local_branch_seq.NEXTVAL, branch_name VARCHAR2(100) );CREATE TABLE branch ( branch_id NUMBER PRIMARY KEY, branch_name VARCHAR2(100) ); ALTER TABLE branch ADD CONSTRAINT unique_branch_id UNIQUE (branch_id);

In distributed databases, ensuring unique surrogate keys across multiple sites can be achieved by using global sequences or other mechanisms that prevent key collisions. The correct SQL statements will implement such mechanisms.

Q19

Multiple Choice

Select the SQL statements that correctly demonstrate the use of surrogate keys with auto-increment features in Oracle.

CREATE SEQUENCE invoice_seq START WITH 1 INCREMENT BY 1; CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY DEFAULT invoice_seq.NEXTVAL, invoice_date DATE, amount NUMBER ); CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, invoice_date DATE, amount NUMBER ); CREATE SEQUENCE invoice_seq START WITH 1 INCREMENT BY 1; ALTER TABLE invoices MODIFY invoice_id DEFAULT invoice_seq.NEXTVAL; CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY DEFAULT invoice_seq.NEXTVAL, invoice_date DATE, amount NUMBER );CREATE SEQUENCE invoice_seq START WITH 1 INCREMENT BY 1; CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, invoice_date DATE, amount NUMBER ); ALTER TABLE invoices MODIFY invoice_id DEFAULT invoice_seq.CURRVAL;

In Oracle, auto-increment behavior is typically implemented using sequences in conjunction with surrogate keys. The correct SQL statements will demonstrate how to create and use a sequence to achieve this.

Q20

Multiple Choice

Choose the SQL statements that demonstrate how to troubleshoot surrogate key constraint violations in Oracle.

SELECT * FROM products WHERE product_id = product_seq.CURRVAL; INSERT INTO products (product_id, product_name) VALUES (product_seq.NEXTVAL, 'New Product'); INSERT INTO products (product_id, product_name) VALUES (product_seq.NEXTVAL, 'New Product'); COMMIT; DELETE FROM products WHERE product_id = product_seq.CURRVAL; INSERT INTO products (product_id, product_name) VALUES (product_seq.NEXTVAL, 'New Product');ALTER TABLE products DISABLE CONSTRAINT pk_product;

Surrogate key constraint violations occur when there is an attempt to insert a duplicate key or a NULL value in the surrogate key column. The correct SQL statements will include queries to identify the issue and methods to resolve it.

Q21

Single Choice

What is a Surrogate Key in Oracle databases?

A key that is automatically generated and has no business meaning. A key that is manually entered by users. A key that is a combination of multiple columns.A key that is derived from business data.

A Surrogate Key is an artificially created key that is used as a unique identifier for a record. It does not have any inherent meaning in the business context.

Q22

Single Choice

Which of the following Oracle SQL operations is typically used to create a Surrogate Key?

INSERT INTO CREATE SEQUENCE CREATE INDEXCREATE VIEW

In Oracle, a Surrogate Key is often created using the CREATE SEQUENCE command, which generates unique numeric values that can be used as keys.

Q23

Single Choice

Why might you use a Surrogate Key instead of a Natural Key in an Oracle database?

Surrogate Keys improve the performance of SQL queries. Surrogate Keys are more meaningful to end-users. Surrogate Keys are easier to maintain because they are not subject to changes.Surrogate Keys are always smaller in size than Natural Keys.

Surrogate Keys are independent of business logic and remain constant even if the business attributes change, making them easier to maintain.

Q24

Single Choice

Consider the following Oracle SQL table definition:What type of key is emp_id in this context?

SQL Code

CREATE TABLE employees (
 emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 emp_name VARCHAR2(50),
 emp_ssn VARCHAR2(11),
 PRIMARY KEY (emp_id)
);

Natural Key Composite Key Surrogate KeyCandidate Key

The emp_id is a Surrogate Key because it is generated by the database using the IDENTITY feature and does not have any business meaning.

Q25

Single Choice

In Oracle, how is a Surrogate Key different from a Natural Key?

A Surrogate Key is automatically generated, while a Natural Key is derived from business data. A Surrogate Key is always larger in size than a Natural Key. A Surrogate Key must be unique, while a Natural Key does not have to be.A Surrogate Key is used only in temporary tables.

A Surrogate Key is system-generated and does not have business meaning, whereas a Natural Key is derived from the actual data and has business significance.

Q26

Single Choice

Given the following Oracle SQL code:What is the purpose of customer_id in this table?

SQL Code

CREATE SEQUENCE customer_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE customers (
 customer_id NUMBER DEFAULT customer_seq.NEXTVAL,
 customer_name VARCHAR2(100),
 PRIMARY KEY (customer_id)
);

It serves as a Natural Key. It is a Foreign Key. It is a Surrogate Key.It is a Composite Key.

The customer_id is a Surrogate Key generated by the customer_seq sequence, ensuring each record has a unique, system-generated identifier.

Q27

Single Choice

Why might using a Surrogate Key be advantageous when dealing with complex joins in Oracle?

Surrogate Keys simplify join conditions as they are always numeric and unique. Surrogate Keys require fewer resources to create indexes. Surrogate Keys eliminate the need for foreign keys.Surrogate Keys improve the readability of SQL code.

Surrogate Keys, being numeric and system-generated, provide a simple and consistent way to join tables, which can be particularly useful in complex database designs.

Q28

Single Choice

Examine the following Oracle table:What is the main reason for using order_id as a Surrogate Key instead of order_number?

SQL Code

CREATE TABLE orders (
 order_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 order_number VARCHAR2(20),
 customer_id NUMBER,
 order_date DATE,
 PRIMARY KEY (order_id)
);

order_id ensures uniqueness even if order_number changes. order_id is easier to index than order_number. order_id can be automatically generated, while order_number cannot.order_id is more meaningful to users than order_number.

Using order_id as a Surrogate Key ensures that each record remains unique even if the order_number (a business attribute) changes, which maintains data integrity.

Q29

Single Choice

In a data warehouse built on Oracle, why might Surrogate Keys be preferred over Natural Keys?

Surrogate Keys can better handle changes in business logic. Surrogate Keys are more meaningful in reports. Surrogate Keys can directly reference external systems.Surrogate Keys are required by Oracle for partitioning tables.

In a data warehouse, Surrogate Keys are preferred because they are stable and not affected by changes in the underlying business logic, which is crucial for maintaining historical accuracy.

Q30

Single Choice

Consider the following Oracle SQL script:Which statement best describes the role of employee_id?

SQL Code

CREATE SEQUENCE employee_seq START WITH 1000 INCREMENT BY 10;

CREATE TABLE employees (
 employee_id NUMBER DEFAULT employee_seq.NEXTVAL,
 employee_name VARCHAR2(100),
 department_id NUMBER,
 PRIMARY KEY (employee_id)
);

employee_id is a Natural Key used for business processes. employee_id is a Surrogate Key used for uniquely identifying records. employee_id is a Foreign Key linking to another table.employee_id is a Candidate Key but not the primary key.

The employee_id is a Surrogate Key, automatically generated by the employee_seq sequence, and serves as the primary key for the employees table.

Oracle Database Quiz Questions