Test Your Oracle Primary Key Knowledge with This Interactive Quiz

Progress

True / False

In Oracle, a primary key is a column or a set of columns that uniquely identifies each row in a table.

True False

A primary key is used to uniquely identify each row in a table and ensures that no duplicate or NULL values exist in the primary key columns.

True / False

In Oracle, a table can have multiple primary keys.

True False

A table can have only one primary key, although that primary key can consist of multiple columns (composite key).

True / False

The primary key in Oracle automatically creates a unique index on the primary key column(s).

True False

When a primary key is defined, Oracle automatically creates a unique index on the primary key column(s) to enforce uniqueness and improve query performance.

True / False

In Oracle, the primary key constraint can be defined at the column level or the table level.

True False

The primary key constraint can be specified directly in the column definition or separately after all column definitions at the table level.

True / False

In Oracle, a primary key column can contain NULL values.

True False

Primary key columns cannot contain NULL values. Each value in the primary key column(s) must be unique and non-NULL.

True / False

In Oracle, you can add a primary key constraint to an existing table using the ALTER TABLE statement.

True False

You can add a primary key constraint to an existing table using the ALTER TABLE statement with the ADD CONSTRAINT clause.

True / False

In Oracle, if a primary key constraint is defined on multiple columns (composite key), the combination of values in those columns must be unique.

True False

A composite primary key ensures that the combination of values in the specified columns is unique across all rows in the table.

True / False

In Oracle, you can create a primary key on a column that already has a unique constraint.

True False

While a column with a unique constraint can serve the purpose of uniqueness, it cannot be directly converted into a primary key if it is already part of a composite unique constraint or if there are NULL values present.

True / False

Dropping a primary key constraint in Oracle also drops the unique index associated with the primary key.

True False

Dropping a primary key constraint in Oracle will also drop the unique index that was created to enforce the primary key constraint.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on creating, modifying, and managing primary key constraints, as well as understanding their impact on database integrity and performance.

True False

The OCP certification covers advanced topics, including the creation, modification, and management of primary key constraints, and understanding their impact on data integrity and query performance.

Q11

Single Choice

What is the primary function of a primary key in an Oracle database?

To allow duplicate values in a column To uniquely identify each row in a table To establish a relationship between two tablesTo create an index on a column

A primary key uniquely identifies each row in a table, ensuring that no two rows have the same primary key value.

Q12

Single Choice

In Oracle SQL, which constraint is automatically associated with a primary key?

UNIQUE NOT NULL Both UNIQUE and NOT NULLCHECK

A primary key in Oracle automatically has both the UNIQUE and NOT NULL constraints to ensure that all values are distinct and non-null.

Q13

Single Choice

Which of the following SQL commands creates a primary key on the employee_id column of a table named employees?

ALTER TABLE employees ADD PRIMARY KEY (employee_id); CREATE PRIMARY KEY ON employees(employee_id); CREATE INDEX PRIMARY KEY ON employees(employee_id);ALTER TABLE employees ADD UNIQUE (employee_id);

The correct command to add a primary key to an existing table is ALTER TABLE followed by ADD PRIMARY KEY.

Q14

Single Choice

Consider the following Oracle SQL query:What does this query do?

SQL Code

CREATE TABLE departments (
 department_id NUMBER PRIMARY KEY,
 department_name VARCHAR2(50)
);

Creates a table without any constraints Creates a table with a UNIQUE constraint on department_name Creates a table with department_id as the primary keyCreates a table with department_name as the primary key

The query creates a table named departments with department_id as the primary key, ensuring that it uniquely identifies each row.

Q15

Single Choice

Which Oracle SQL command can be used to create a composite primary key on the employee_id and department_id columns?

CREATE PRIMARY KEY ON employees(employee_id, department_id); ALTER TABLE employees ADD PRIMARY KEY (employee_id, department_id); ALTER TABLE employees ADD UNIQUE (employee_id, department_id);CREATE PRIMARY KEY ON employees(employee_id) AND department_id;

To create a composite primary key, you use the ALTER TABLE command with ADD PRIMARY KEY, specifying both columns.

Q16

Single Choice

Given the Oracle SQL query:What will be the result?

SQL Code

ALTER TABLE employees DROP PRIMARY KEY;

The primary key constraint is removed from the employees table The employees table is deleted The employee_id column is removedThe UNIQUE constraint is removed from the employees table

The command removes the primary key constraint from the employees table, allowing duplicate and null values in the previously constrained columns.

Q17

Single Choice

What will happen if you try to insert a row with a duplicate value in a primary key column in Oracle?

The row will be inserted successfully The row will be updated An error will occurThe duplicate value will be ignored

Oracle will raise an error when attempting to insert a row with a duplicate value in a primary key column because the primary key constraint enforces uniqueness.

Q18

Single Choice

Consider this Oracle SQL query:What type of primary key is being created?

Single-column primary key Composite primary key Foreign keyPartial primary key

The query creates a composite primary key on the order_id and customer_id columns, meaning both columns together must be unique and not null.

Q19

Single Choice

Which of the following is true about primary keys and foreign keys in an Oracle database?

A primary key can be null A foreign key must reference a primary key or a unique key in another table A table can have multiple primary keysA foreign key must be unique

A foreign key in one table references the primary key (or a unique key) in another table to establish a relationship between the two tables.

Q20

Single Choice

Consider the following Oracle SQL scenario:What does this SQL code demonstrate?

SQL Code

CREATE TABLE products (
 product_id NUMBER PRIMARY KEY,
 product_name VARCHAR2(50),
 category_id NUMBER,
 CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

The creation of a composite primary key and a foreign key The creation of a primary key on product_id and a foreign key constraint linking category_id to another table The use of product_id as a foreign keyThe removal of a primary key

The code creates a table with a primary key on product_id and a foreign key constraint on category_id, linking it to the categories table's category_id.

Q21

Multiple Choice

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

SQL Code

CREATE TABLE customers (
 customer_id NUMBER,
 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); ALTER TABLE customers ADD PRIMARY KEY (customer_name);CREATE TABLE customers ( customer_id NUMBER, customer_name VARCHAR2(100), PRIMARY KEY (customer_id) );

A primary key uniquely identifies each record in a table. The correct SQL statements will involve either defining the primary key during table creation or altering the table to add the primary key constraint.

Q22

Multiple Choice

Select the SQL statements that correctly enforce primary key constraints in a composite key scenario.

CREATE TABLE orders ( order_id NUMBER, product_id NUMBER, PRIMARY KEY (order_id, product_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, product_id NUMBER PRIMARY KEY ); ALTER TABLE orders ADD CONSTRAINT pk_order PRIMARY KEY (order_id, product_id);CREATE TABLE orders ( order_id NUMBER, product_id NUMBER, CONSTRAINT pk_order PRIMARY KEY (order_id, product_id) );

In a composite primary key, two or more columns together form the primary key. The correct SQL statements will specify all columns involved in the primary key constraint.

Q23

Multiple Choice

Which of the following SQL statements will correctly modify the primary key?

ALTER TABLE departments DROP PRIMARY KEY; ALTER TABLE departments ADD PRIMARY KEY (department_id, location_id); ALTER TABLE departments MODIFY PRIMARY KEY (department_id, location_id); ALTER TABLE departments DROP CONSTRAINT pk_dept; ALTER TABLE departments ADD CONSTRAINT pk_dept PRIMARY KEY (department_id, location_id);ALTER TABLE departments ALTER PRIMARY KEY (department_id, location_id);

Modifying a primary key usually involves dropping the existing constraint and then adding a new one with the desired columns. The correct SQL statements will reflect these steps.

Q24

Multiple Choice

Identify the SQL statements that ensure data integrity by using primary key constraints in Oracle.

CREATE TABLE inventory ( item_id NUMBER PRIMARY KEY, item_name VARCHAR2(100) ); ALTER TABLE inventory ADD CONSTRAINT pk_inventory PRIMARY KEY (item_id); CREATE TABLE inventory ( item_id NUMBER, item_name VARCHAR2(100) ); ALTER TABLE inventory ADD PRIMARY KEY (item_id);CREATE TABLE inventory ( item_id NUMBER, item_name VARCHAR2(100) );

Primary keys ensure data integrity by preventing duplicate entries in the specified column(s). The correct SQL statements will define 'item_id' as the primary key, enforcing uniqueness.

Q25

Multiple Choice

Choose the SQL statements that demonstrate the importance of primary keys in table relationships.

CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_customer_order FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE customers ( customer_id NUMBER, customer_name VARCHAR2(100) ); ALTER TABLE customers ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_customer_order FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_customer_order FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );ALTER TABLE customers ADD PRIMARY KEY (customer_id); CREATE TABLE orders ( order_id NUMBER, customer_id NUMBER, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Primary keys are essential in establishing relationships between tables by acting as a reference point in foreign key constraints. The correct SQL statements will define and utilize the primary key in this context.

Q26

Multiple Choice

Which SQL statements demonstrate the handling of duplicate values when defining a primary key?

DELETE FROM suppliers WHERE supplier_id IN (SELECT supplier_id FROM suppliers GROUP BY supplier_id HAVING COUNT(*) > 1); ALTER TABLE suppliers ADD CONSTRAINT pk_supplier PRIMARY KEY (supplier_id); CREATE TABLE suppliers ( supplier_id NUMBER PRIMARY KEY, supplier_name VARCHAR2(100) ); ALTER TABLE suppliers DROP DUPLICATE supplier_id; ALTER TABLE suppliers ADD PRIMARY KEY (supplier_id);CREATE TABLE suppliers ( supplier_id NUMBER, supplier_name VARCHAR2(100) ); DELETE FROM suppliers WHERE supplier_id IN (SELECT supplier_id FROM suppliers GROUP BY supplier_id HAVING COUNT(*) > 1); ALTER TABLE suppliers ADD PRIMARY KEY (supplier_id);

A primary key must contain unique values; therefore, attempting to create a primary key on a column with duplicates will result in an error. The correct SQL statements will involve either cleaning the data first or using constraints to enforce uniqueness.

Q27

Multiple Choice

Determine the SQL statements that demonstrate the use of primary keys in conjunction with unique constraints.

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, email VARCHAR2(100) UNIQUE ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, email VARCHAR2(100) ); ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email); ALTER TABLE employees ADD PRIMARY KEY (employee_id); ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, email VARCHAR2(100) ); ALTER TABLE employees ADD UNIQUE (email);

Primary keys ensure uniqueness and can be used in conjunction with unique constraints on other columns. The correct SQL statements will define both constraints appropriately.

Q28

Multiple Choice

Which of the following SQL statements correctly create a partitioned table?

CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, sale_date DATE, amount NUMBER ) PARTITION BY HASH (sale_id) PARTITIONS 4; CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, amount NUMBER, PRIMARY KEY (sale_id) ) PARTITION BY RANGE (sale_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 sales ( sale_id NUMBER PRIMARY KEY, sale_date DATE, amount NUMBER ) PARTITION BY LIST (sale_id) ( PARTITION p1 VALUES (1, 2, 3), PARTITION p2 VALUES (4, 5, 6) );ALTER TABLE sales PARTITION BY RANGE (sale_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 primary keys can significantly improve performance by distributing the data across multiple storage segments. The correct SQL statements will include the partitioning clauses.

Q29

Multiple Choice

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

CREATE SEQUENCE product_seq START WITH 1 INCREMENT BY 1; CREATE TABLE products ( product_id NUMBER PRIMARY KEY DEFAULT product_seq.NEXTVAL, product_name VARCHAR2(100) ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100) ); CREATE SEQUENCE product_seq START WITH 1 INCREMENT BY 1; ALTER TABLE products MODIFY product_id DEFAULT product_seq.NEXTVAL; CREATE TABLE products ( product_id NUMBER PRIMARY KEY DEFAULT product_seq.NEXTVAL, product_name VARCHAR2(100) );CREATE SEQUENCE product_seq START WITH 1 INCREMENT BY 1; CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100) ); ALTER TABLE products MODIFY product_id DEFAULT product_seq.CURRVAL;

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

Q30

Multiple Choice

Choose the SQL statements that demonstrate the use of primary keys in distributed databases.

CREATE SEQUENCE global_location_seq START WITH 1 INCREMENT BY 1; CREATE TABLE location ( location_id NUMBER PRIMARY KEY DEFAULT global_location_seq.NEXTVAL, location_name VARCHAR2(100) ); CREATE TABLE location ( location_id NUMBER PRIMARY KEY, location_name VARCHAR2(100) ); CREATE SEQUENCE global_location_seq START WITH 1 INCREMENT BY 1; ALTER TABLE location MODIFY location_id DEFAULT global_location_seq.NEXTVAL; CREATE SEQUENCE local_location_seq START WITH 1 INCREMENT BY 10; CREATE TABLE location ( location_id NUMBER PRIMARY KEY DEFAULT local_location_seq.NEXTVAL, location_name VARCHAR2(100) );CREATE TABLE location ( location_id NUMBER PRIMARY KEY, location_name VARCHAR2(100) ); ALTER TABLE location ADD CONSTRAINT unique_location_id UNIQUE (location_id);

In distributed databases, ensuring unique primary 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.

Oracle Database Quiz Questions