Test Your Oracle CHECK Constraint Knowledge

Progress

True / False

In Oracle, a CHECK constraint is used to enforce a condition on each row in a table.

True False

A CHECK constraint is used to ensure that all values in a column meet a specific condition.

True / False

A CHECK constraint in Oracle can only be applied to a single column in a table.

True False

A CHECK constraint can be applied to multiple columns in a table by specifying a condition that involves more than one column.

True / False

In Oracle, a CHECK constraint can be added to a table at the time of table creation using the CREATE TABLE statement.

True False

You can define a CHECK constraint at the time of table creation within the CREATE TABLE statement.

True / False

In Oracle, the ALTER TABLE statement can be used to add a CHECK constraint to an existing table.

True False

The ALTER TABLE statement can be used to add a CHECK constraint to an existing table using the ADD CONSTRAINT clause.

True / False

In Oracle, a CHECK constraint cannot reference other tables or columns outside of the table it is defined on.

True False

A CHECK constraint in Oracle can only reference columns within the same table and cannot reference columns in other tables.

True / False

In Oracle, the CHECK constraint can include functions and expressions.

True False

The CHECK constraint can include functions and expressions to enforce more complex conditions on the data.

True / False

In Oracle, if a CHECK constraint condition fails during an INSERT or UPDATE operation, the operation will still succeed but will generate a warning.

True False

If a CHECK constraint condition fails, the INSERT or UPDATE operation will fail and generate an error, not a warning.

True / False

In Oracle, a disabled CHECK constraint will still be enforced during data modifications.

True False

If a CHECK constraint is disabled, it will not be enforced during data modifications until it is enabled again.

True / False

In Oracle, a CHECK constraint can be defined using the USING INDEX clause to specify an index for fast validation.

True False

The USING INDEX clause is used for unique and primary key constraints to specify an index, not for CHECK constraints.

Q10

True / False

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

True False

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

Q11

Single Choice

Which of the following is true about the Oracle CHECK constraint?

It automatically enforces a primary key on a column. It ensures that values in a column satisfy a specific condition. It restricts the range of dates in a DATE column.It is used to enforce referential integrity between tables.

The CHECK constraint in Oracle ensures that the values in a column satisfy a specific condition, such as a range or a particular format.

Q12

Single Choice

In Oracle, which of the following SQL commands would create a CHECK constraint to ensure that the column AGE in the EMPLOYEES table is always greater than 18?

CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), AGE NUMBER ); ALTER TABLE EMPLOYEES ADD CONSTRAINT chk_age CHECK (AGE > 18); ALTER TABLE EMPLOYEES ADD CONSTRAINT chk_age CHECK (AGE >= 18);None of the above.

The ALTER TABLE command with the CHECK constraint ensures that the AGE column always contains values greater than 18.

Q13

Single Choice

Which of the following is the correct syntax to create a table in Oracle with a CHECK constraint that ensures the SALARY column has values between 1000 and 10000?

CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), SALARY NUMBER, CHECK (SALARY BETWEEN 1000 AND 10000) ); CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), SALARY NUMBER, CONSTRAINT chk_salary CHECK (SALARY BETWEEN 1000 AND 10000) ); CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), SALARY NUMBER, CHECK SALARY BETWEEN 1000 AND 10000 );CREATE TABLE EMPLOYEES ( ID NUMBER PRIMARY KEY, NAME VARCHAR2(100), SALARY NUMBER, CHECK SALARY >= 1000 AND SALARY <= 10000 );

The correct syntax to include a CHECK constraint in the CREATE TABLE statement in Oracle is to use CONSTRAINT chk_salary CHECK (SALARY BETWEEN 1000 AND 10000).

Q14

Single Choice

In Oracle, if a CHECK constraint on a table is violated, what is the typical outcome?

The invalid row is inserted with a NULL value. The transaction is rolled back. An error is thrown, and the insert/update operation fails.The row is inserted, but the constraint is logged as violated.

When a CHECK constraint is violated in Oracle, the operation fails, and an error is thrown.

Q15

Single Choice

Given the following Oracle SQL code, which of the following will result in an error when trying to insert a row?

SQL Code

CREATE TABLE PRODUCTS (
 PRODUCT_ID NUMBER PRIMARY KEY,
 PRODUCT_NAME VARCHAR2(100),
 PRICE NUMBER,
 CONSTRAINT chk_price CHECK (PRICE > 0)
);

INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE) VALUES (1, 'Laptop', 999); INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE) VALUES (2, 'Phone', 0); INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE) VALUES (3, 'Tablet', 499);INSERT INTO PRODUCTS (PRODUCT_ID, PRODUCT_NAME, PRICE) VALUES (4, 'Monitor', 150);

The CHECK constraint chk_price enforces that the PRICE must be greater than 0. An attempt to insert a PRICE of 0 will result in an error.

Q16

Single Choice

Which Oracle SQL statement would correctly add a CHECK constraint on the QUANTITY column of an existing ORDERS table to ensure it is always a positive integer?

ALTER TABLE ORDERS ADD CONSTRAINT chk_quantity CHECK (QUANTITY > 0); ALTER TABLE ORDERS ADD CONSTRAINT chk_quantity CHECK (QUANTITY >= 0); ALTER TABLE ORDERS ADD CONSTRAINT chk_quantity CHECK (QUANTITY > 1);ALTER TABLE ORDERS ADD CONSTRAINT chk_quantity CHECK (QUANTITY > -1);

The CHECK constraint chk_quantity ensures that the QUANTITY must be greater than 0.

Q17

Single Choice

Consider an Oracle table EMPLOYEES with a CHECK constraint on the DEPARTMENT_ID column. Which of the following changes would require disabling and re-enabling the CHECK constraint?

Adding a new column to the table. Renaming the DEPARTMENT_ID column. Changing the CHECK constraint to include new valid department IDs.Deleting rows that violate the CHECK constraint.

Modifying the CHECK constraint would require it to be disabled first, then re-enabled with the updated condition.

Q18

Single Choice

Which of the following Oracle SQL commands will result in a CHECK constraint violation error?

SQL Code

CREATE TABLE INVENTORY (
 ITEM_ID NUMBER PRIMARY KEY,
 ITEM_NAME VARCHAR2(100),
 STOCK NUMBER,
 CONSTRAINT chk_stock CHECK (STOCK >= 0 AND STOCK <= 100)
);

INSERT INTO INVENTORY (ITEM_ID, ITEM_NAME, STOCK)
VALUES (1, 'Widget', 120);

The CREATE TABLE statement The INSERT INTO statement Both the CREATE TABLE and INSERT INTO statementsNeither, as both are valid.

The INSERT INTO statement violates the CHECK constraint chk_stock, which ensures STOCK must be between 0 and 100.

Q19

Single Choice

How can you ensure in Oracle that a column GRADE in the STUDENTS table only contains values 'A', 'B', 'C', 'D', or 'F'?

ALTER TABLE STUDENTS ADD CONSTRAINT chk_grade CHECK (GRADE IN ('A', 'B', 'C', 'D', 'F')); ALTER TABLE STUDENTS ADD CONSTRAINT chk_grade CHECK (GRADE IN ('A', 'B', 'C', 'D')); ALTER TABLE STUDENTS ADD CONSTRAINT chk_grade CHECK (GRADE IN ('A', 'B', 'C', 'F'));ALTER TABLE STUDENTS ADD CONSTRAINT chk_grade CHECK (GRADE BETWEEN 'A' AND 'F');

The CHECK constraint ensures that the GRADE column only contains the specified valid values: 'A', 'B', 'C', 'D', or 'F'.

Q20

Single Choice

In Oracle, if a CHECK constraint is defined on multiple columns, which of the following is true?

The CHECK constraint can reference columns from other tables. The CHECK constraint applies to each column independently. The CHECK constraint applies to the combination of columns in a single row.Oracle does not allow CHECK constraints on multiple columns.

When a CHECK constraint is defined on multiple columns, it ensures that the combination of values in those columns satisfies the condition for each row.

Q21

Multiple Choice

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

CREATE TABLE employees ( salary NUMBER CONSTRAINT chk_salary CHECK (salary > 0) ); CREATE TABLE employees ( salary NUMBER CHECK (salary > 0) ); CREATE TABLE employees ( salary NUMBER CHECK (salary >= 0) );CREATE TABLE employees ( salary NUMBER CONSTRAINT chk_salary CHECK (salary > 1000) );

The CHECK constraint is used to enforce a condition on a column's values. The correct scripts will demonstrate how to define a CHECK constraint that ensures the 'salary' is greater than 0.

Q22

Multiple Choice

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

ALTER TABLE customers ADD CONSTRAINT chk_age CHECK (age BETWEEN 18 AND 65); ALTER TABLE customers MODIFY age CHECK (age BETWEEN 18 AND 65); ALTER TABLE customers ADD CHECK (age BETWEEN 18 AND 65);ALTER TABLE customers ADD CONSTRAINT chk_age CHECK (age > 18 AND age < 65);

Adding a CHECK constraint to an existing table requires using the ALTER TABLE statement. The correct scripts will demonstrate how to add a CHECK constraint to the 'age' column.

Q23

Multiple Choice

Determine the SQL scripts that correctly query data based on a CHECK constraint in Oracle.

SELECT * FROM orders WHERE order_quantity > 0; SELECT * FROM orders WHERE order_quantity = 0; SELECT * FROM orders WHERE order_quantity >= 0;SELECT * FROM orders WHERE order_quantity > 0 AND order_quantity IS NOT NULL;

Querying data based on a CHECK constraint involves ensuring that the condition enforced by the constraint is reflected in the query results. The correct scripts will demonstrate how to query orders where 'order_quantity' is greater than 0.

Q24

Multiple Choice

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

ALTER TABLE employees DROP CONSTRAINT chk_age; ALTER TABLE employees DROP CHECK chk_age; ALTER TABLE employees REMOVE CHECK chk_age;ALTER TABLE employees DROP CONSTRAINT age;

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

Q25

Multiple Choice

Select the SQL scripts that correctly define a complex CHECK constraint involving multiple columns in Oracle.

CREATE TABLE promotions ( start_date DATE, end_date DATE, CONSTRAINT chk_dates CHECK (start_date < end_date) ); CREATE TABLE promotions ( start_date DATE, end_date DATE, CHECK (start_date < end_date) ); CREATE TABLE promotions ( start_date DATE, end_date DATE, CHECK (start_date <= end_date) );CREATE TABLE promotions ( start_date DATE, end_date DATE, CONSTRAINT chk_dates CHECK (start_date > end_date) );

A complex CHECK constraint can involve multiple columns and conditions. The correct scripts will demonstrate how to define a CHECK constraint that ensures 'start_date' is earlier than 'end_date'.

Q26

Multiple Choice

Determine the SQL scripts that correctly enforce a CHECK constraint with a subquery in Oracle.

ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price <= (SELECT AVG(price) FROM products WHERE category_id = products.category_id)); ALTER TABLE products ADD CHECK (price <= (SELECT AVG(price) FROM products WHERE category_id = products.category_id)); ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price <= (SELECT AVG(price) FROM products WHERE category_id = category_id));ALTER TABLE products MODIFY CHECK (price <= (SELECT AVG(price) FROM products WHERE category_id = products.category_id));

CHECK constraints can use subqueries to enforce more complex rules. The correct scripts will demonstrate how to define a CHECK constraint with a subquery.

Q27

Multiple Choice

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

SELECT * FROM employees WHERE salary <= 0; SELECT * FROM employees WHERE salary IS NULL; SELECT * FROM employees WHERE salary <= 0 OR salary IS NULL;SELECT * FROM employees WHERE salary = 0;

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

Q28

Multiple Choice

Determine the SQL scripts that correctly enforce a CHECK constraint on a VARCHAR2 column in Oracle.

ALTER TABLE tasks ADD CONSTRAINT chk_status CHECK (status IN ('Pending', 'In Progress', 'Completed')); ALTER TABLE tasks MODIFY status CHECK (status IN ('Pending', 'In Progress', 'Completed')); ALTER TABLE tasks ADD CHECK (status = 'Pending' OR status = 'In Progress' OR status = 'Completed');ALTER TABLE tasks ADD CONSTRAINT chk_status CHECK (status IN ('Pending', 'Completed'));

A CHECK constraint can enforce a specific set of values on a VARCHAR2 column. The correct scripts will demonstrate how to define a CHECK constraint that limits the 'status' column to specific values.

Q29

Multiple Choice

Select the SQL scripts that correctly define a table with multiple CHECK constraints in Oracle.

CREATE TABLE contracts ( contract_length NUMBER CONSTRAINT chk_length CHECK (contract_length > 0), contract_type VARCHAR2(20) CONSTRAINT chk_type CHECK (contract_type IN ('Full-Time', 'Part-Time')) ); CREATE TABLE contracts ( contract_length NUMBER CHECK (contract_length > 0), contract_type VARCHAR2(20) CHECK (contract_type IN ('Full-Time', 'Part-Time')) ); CREATE TABLE contracts ( contract_length NUMBER CHECK (contract_length >= 0), contract_type VARCHAR2(20) CHECK (contract_type IN ('Full-Time', 'Part-Time')) );CREATE TABLE contracts ( contract_length NUMBER CONSTRAINT chk_length CHECK (contract_length > 0), contract_type VARCHAR2(20) CHECK (contract_type = 'Full-Time' OR contract_type = 'Part-Time') );

Multiple CHECK constraints can be defined on different columns within a table. The correct scripts will demonstrate how to define these constraints for the 'contract_length' and 'contract_type' columns.

Q30

Multiple Choice

Identify the SQL scripts that correctly disable and enable a CHECK constraint in Oracle.

ALTER TABLE employees DISABLE CONSTRAINT chk_salary; ALTER TABLE employees ENABLE CONSTRAINT chk_salary; ALTER TABLE employees DISABLE CHECK chk_salary;ALTER TABLE employees ENABLE CHECK chk_salary;

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

Oracle Database Quiz Questions