Test Your Oracle UNIQUE Constraint Knowledge

Progress

True / False

In Oracle, a UNIQUE constraint ensures that all values in a column are distinct.

True False

The UNIQUE constraint enforces the uniqueness of the values in a specified column, ensuring no duplicate values.

True / False

A table in Oracle can have multiple UNIQUE constraints.

True False

A table can have multiple UNIQUE constraints, each applied to different columns or sets of columns.

True / False

A UNIQUE constraint in Oracle allows multiple NULL values in the column.

True False

Unlike primary key constraints, UNIQUE constraints allow multiple NULL values in the column because NULLs are considered distinct from each other.

True / False

In Oracle, the UNIQUE constraint can be added to an existing table using the ALTER TABLE statement.

True False

The UNIQUE constraint can be added to an existing table using the ALTER TABLE statement with the ADD CONSTRAINT clause.

True / False

In Oracle, a UNIQUE constraint automatically creates a unique index on the column.

True False

When a UNIQUE constraint is defined, Oracle automatically creates a unique index on the column to enforce the constraint.

True / False

In Oracle, the UNIQUE constraint can only be applied to a single column in a table.

True False

The UNIQUE constraint can be applied to a single column or a combination of columns (composite unique constraint) in a table.

True / False

In Oracle, a composite UNIQUE constraint can include columns of different data types.

True False

A composite UNIQUE constraint can span multiple columns, which can be of different data types, to ensure the combined values are unique.

True / False

In Oracle, you can disable a UNIQUE constraint temporarily using the ALTER TABLE statement.

True False

You can disable a UNIQUE constraint using the ALTER TABLE statement with the DISABLE CONSTRAINT clause and enable it again when needed.

True / False

In Oracle, the UNIQUE constraint can include a USING INDEX clause to specify an existing index for enforcing the constraint.

True False

The USING INDEX clause allows you to specify an existing index to enforce the UNIQUE constraint, which can help optimize performance.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on creating, managing, and optimizing UNIQUE constraints in database tables.

True False

The OCP certification covers advanced topics, including the creation, management, and optimization of UNIQUE constraints to ensure data integrity in Oracle databases.

Q11

Single Choice

What is the primary purpose of the UNIQUE constraint in Oracle?

To enforce a foreign key relationship between tables. To ensure that all values in a column or a set of columns are distinct. To set a default value for a column.To automatically generate unique values for a column.

The UNIQUE constraint in Oracle ensures that all values in a column or a set of columns are distinct, preventing duplicate entries.

Q12

Single Choice

In Oracle, which of the following SQL commands correctly defines a UNIQUE constraint on the EMAIL column in the EMPLOYEES table?

CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), EMAIL VARCHAR2(100) UNIQUE ); CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), EMAIL VARCHAR2(100) CONSTRAINT email_unique UNIQUE ); CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), EMAIL VARCHAR2(100) UNIQUE );CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), EMAIL VARCHAR2(100) NOT NULL UNIQUE );

The UNIQUE constraint can be defined directly in the column definition as shown in option A, ensuring that all values in the EMAIL column are unique.

Q13

Single Choice

Which of the following SQL statements will cause a UNIQUE constraint violation in Oracle?

SQL Code

CREATE TABLE USERS (
 USER_ID NUMBER PRIMARY KEY,
 USERNAME VARCHAR2(50) UNIQUE,
 EMAIL VARCHAR2(100) UNIQUE
);

INSERT INTO USERS (USER_ID, USERNAME, EMAIL)
VALUES (1, 'johndoe', 'john@example.com');

INSERT INTO USERS (USER_ID, USERNAME, EMAIL)
VALUES (2, 'johndoe', 'john@example.com');

The first INSERT statement. The second INSERT statement. Both INSERT statements.Neither, as both are valid.

The second INSERT statement will cause a UNIQUE constraint violation because both the USERNAME and EMAIL columns must have unique values, and the USERNAME value 'johndoe' is duplicated.

Q14

Single Choice

In Oracle, how can you modify an existing table to add a UNIQUE constraint on the PHONE_NUMBER column?

ALTER TABLE EMPLOYEES ADD CONSTRAINT phone_unique UNIQUE (PHONE_NUMBER); ALTER TABLE EMPLOYEES ADD UNIQUE (PHONE_NUMBER); ALTER TABLE EMPLOYEES MODIFY PHONE_NUMBER UNIQUE;ALTER TABLE EMPLOYEES ADD UNIQUE CONSTRAINT (PHONE_NUMBER);

The correct syntax in Oracle to add a UNIQUE constraint to an existing column is to use ALTER TABLE with ADD CONSTRAINT followed by the constraint name and the UNIQUE keyword.

Q15

Single Choice

Given the following Oracle SQL code, what will happen if you try to insert a row with a duplicate value in the SSN column?

SQL Code

CREATE TABLE CITIZENS (
 ID NUMBER PRIMARY KEY,
 NAME VARCHAR2(100),
 SSN VARCHAR2(11) UNIQUE
);

INSERT INTO CITIZENS (ID, NAME, SSN)
VALUES (1, 'Alice', '123-45-6789');

The row will be inserted with the duplicate SSN value. The UNIQUE constraint will be ignored. An error will be thrown, and the insert operation will fail.The SSN column will be set to NULL.

The UNIQUE constraint on the SSN column ensures that no duplicate values are allowed. Inserting a duplicate value will result in an error.

Q16

Single Choice

How would you define a composite UNIQUE constraint on the FIRST_NAME and LAST_NAME columns in the EMPLOYEES table in Oracle?

ALTER TABLE EMPLOYEES ADD CONSTRAINT name_unique UNIQUE (FIRST_NAME, LAST_NAME); ALTER TABLE EMPLOYEES MODIFY FIRST_NAME, LAST_NAME UNIQUE; ALTER TABLE EMPLOYEES ADD UNIQUE (FIRST_NAME, LAST_NAME);ALTER TABLE EMPLOYEES MODIFY FIRST_NAME LAST_NAME UNIQUE;

A composite UNIQUE constraint can be defined on multiple columns using the ADD CONSTRAINT syntax, ensuring that the combination of values in these columns is unique.

Q17

Single Choice

Consider the following Oracle SQL table creation. What will happen if an attempt is made to insert a row with duplicate values for the CODE and DESCRIPTION columns?

SQL Code

CREATE TABLE PRODUCTS (
 PRODUCT_ID NUMBER PRIMARY KEY,
 CODE VARCHAR2(50),
 DESCRIPTION VARCHAR2(100),
 CONSTRAINT code_desc_unique UNIQUE (CODE, DESCRIPTION)
);

The row will be inserted successfully. The UNIQUE constraint will be violated, and an error will be thrown. The DESCRIPTION column will be set to NULL.The CODE column will be set to NULL.

The UNIQUE constraint on the CODE and DESCRIPTION columns ensures that no two rows can have the same combination of these values. A duplicate insertion will result in an error.

Q18

Single Choice

In Oracle, how can you temporarily disable a UNIQUE constraint on the EMAIL column in the USERS table?

ALTER TABLE USERS DISABLE CONSTRAINT email_unique; ALTER TABLE USERS REMOVE CONSTRAINT email_unique; ALTER TABLE USERS MODIFY EMAIL DROP UNIQUE;ALTER TABLE USERS DISABLE UNIQUE CONSTRAINT email_unique;

The DISABLE CONSTRAINT command in Oracle is used to temporarily disable a UNIQUE constraint on a column.

Q19

Single Choice

What is the impact of defining a UNIQUE constraint on a column that already contains duplicate values in Oracle?

The UNIQUE constraint will be created, and duplicates will be automatically removed. The UNIQUE constraint will fail to be created, and an error will be thrown. The duplicate values will be ignored, and the constraint will be applied only to new rows.The column will be automatically set to NULL.

In Oracle, if you attempt to define a UNIQUE constraint on a column that already contains duplicate values, the constraint creation will fail, and an error will be thrown.

Q20

Single Choice

In Oracle, which of the following is true about a UNIQUE constraint that includes NULL values?

A UNIQUE constraint allows only one NULL value in the column. A UNIQUE constraint does not allow any NULL values in the column. A UNIQUE constraint allows multiple NULL values in the column.A UNIQUE constraint converts all NULL values to a default value.

In Oracle, a UNIQUE constraint allows multiple NULL values in a column because NULL values are considered distinct from each other.

Q21

Multiple Choice

Identify the SQL scripts that correctly define a UNIQUE constraint on a column in Oracle.

CREATE TABLE users ( email VARCHAR2(255) UNIQUE ); CREATE TABLE users ( email VARCHAR2(255) CONSTRAINT uq_email UNIQUE ); CREATE TABLE users ( email VARCHAR2(255) PRIMARY KEY );CREATE TABLE users ( email VARCHAR2(255) NOT NULL );

The UNIQUE constraint ensures that all values in a column are different. The correct scripts will demonstrate how to define a UNIQUE constraint on the 'email' column.

Q22

Multiple Choice

Select the SQL scripts that correctly modify an existing column to add a UNIQUE constraint in Oracle.

ALTER TABLE accounts ADD CONSTRAINT uq_username UNIQUE (username); ALTER TABLE accounts MODIFY username UNIQUE; ALTER TABLE accounts ALTER COLUMN username SET UNIQUE;ALTER TABLE accounts ADD UNIQUE (username);

Modifying an existing column to add a UNIQUE constraint involves using the ALTER TABLE statement. The correct scripts will demonstrate how to add a UNIQUE constraint to the 'username' column.

Q23

Multiple Choice

Determine the SQL scripts that correctly query rows while enforcing a UNIQUE constraint in Oracle.

SELECT * FROM employees WHERE employee_code IS UNIQUE; SELECT employee_code, COUNT(*) FROM employees GROUP BY employee_code HAVING COUNT(*) = 1; SELECT DISTINCT employee_code FROM employees;SELECT * FROM employees WHERE employee_code IS DISTINCT;

Querying rows based on uniqueness involves using conditions that reflect the UNIQUE constraint. The correct scripts will demonstrate how to query rows ensuring the uniqueness of 'employee_code'.

Q24

Multiple Choice

Identify the SQL scripts that correctly insert data into a table with a UNIQUE constraint in Oracle.

INSERT INTO customers (customer_id, name) VALUES (1, 'Alice'); INSERT INTO customers (customer_id, name) VALUES (1, 'Bob'); INSERT INTO customers (customer_id, name) VALUES (2, 'Alice');INSERT INTO customers (customer_id, name) VALUES (2, 'Bob');

Inserting data into a column with a UNIQUE constraint requires ensuring that the inserted value does not already exist in the table. The correct scripts will demonstrate this.

Q25

Multiple Choice

Select the SQL scripts that correctly enforce a UNIQUE constraint across multiple columns in Oracle.

CREATE TABLE orders ( order_id NUMBER, product_id NUMBER, CONSTRAINT uq_order_product UNIQUE (order_id, product_id) ); CREATE TABLE orders ( order_id NUMBER, product_id NUMBER, UNIQUE (order_id, product_id) ); CREATE TABLE orders ( order_id NUMBER, product_id NUMBER, CONSTRAINT uq_order_product PRIMARY KEY (order_id, product_id) );CREATE TABLE orders ( order_id NUMBER, product_id NUMBER, UNIQUE order_product (order_id, product_id) );

A UNIQUE constraint can be applied to multiple columns to ensure that the combination of their values is unique across all rows. The correct scripts will demonstrate how to define this constraint.

Q26

Multiple Choice

Identify the SQL scripts that correctly drop a UNIQUE constraint from a column in Oracle.

ALTER TABLE users DROP CONSTRAINT uq_email; ALTER TABLE users DROP UNIQUE uq_email; ALTER TABLE users REMOVE CONSTRAINT uq_email;ALTER TABLE users DROP CONSTRAINT UNIQUE uq_email;

Dropping a UNIQUE constraint involves using the ALTER TABLE statement along with the DROP CONSTRAINT clause. The correct scripts will demonstrate how to remove the UNIQUE constraint.

Q27

Multiple Choice

Determine the SQL scripts that correctly handle conflicts when inserting duplicate values into a UNIQUE column in Oracle.

INSERT INTO employees (employee_id, name) VALUES (1, 'John'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Handle duplicate value error; INSERT INTO employees (employee_id, name) VALUES (1, 'John') ON DUPLICATE KEY UPDATE name = 'John'; INSERT INTO employees (employee_id, name) VALUES (1, 'John') ON CONFLICT (employee_id) DO UPDATE SET name = 'John';BEGIN INSERT INTO employees (employee_id, name) VALUES (1, 'John'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Handle duplicate value error; END;

Handling conflicts with UNIQUE constraints can involve using conditional logic or handling exceptions. The correct scripts will demonstrate how to handle a violation of a UNIQUE constraint during insertion.

Q28

Multiple Choice

Select the SQL scripts that correctly enforce a UNIQUE constraint with a WHERE clause in Oracle.

CREATE UNIQUE INDEX uq_email_active ON contacts (email) WHERE status = 'Active'; ALTER TABLE contacts ADD CONSTRAINT uq_email_active UNIQUE (email) WHERE status = 'Active'; CREATE UNIQUE INDEX uq_email_active ON contacts (email, status) WHERE status = 'Active';ALTER TABLE contacts ADD CONSTRAINT uq_email_active UNIQUE (email, status) WHERE status = 'Active';

Using a UNIQUE constraint with a WHERE clause allows enforcing uniqueness conditionally. The correct scripts will demonstrate how to define such a constraint.

Q29

Multiple Choice

Identify the SQL scripts that correctly validate data against a UNIQUE constraint in Oracle.

SELECT department_name, COUNT(*) FROM departments GROUP BY department_name HAVING COUNT(*) > 1; SELECT department_name FROM departments WHERE department_name IS NOT UNIQUE; SELECT department_name, COUNT(*) FROM departments GROUP BY department_name HAVING COUNT(*) = 1;SELECT DISTINCT department_name FROM departments WHERE department_name IS NOT NULL;

Validating data against an existing UNIQUE constraint can involve checking for violations or duplicates. The correct scripts will demonstrate how to validate that the data complies with the UNIQUE constraint.

Q30

Multiple Choice

Determine the SQL scripts that correctly handle disabling and enabling a UNIQUE constraint in Oracle.

ALTER TABLE users DISABLE CONSTRAINT uq_username; ALTER TABLE users ENABLE CONSTRAINT uq_username; ALTER TABLE users DROP UNIQUE uq_username;ALTER TABLE users MODIFY CONSTRAINT uq_username ENABLED;

Disabling and enabling UNIQUE constraints can be done using the ALTER TABLE statement. The correct scripts will demonstrate how to temporarily disable and then re-enable the UNIQUE constraint.

Oracle Database Quiz Questions