Test Your Oracle NULL vs NOT NULL Knowledge

Progress

True / False

In Oracle, a NULL value represents the absence of a value.

True False

A NULL value in Oracle indicates that the data is missing or not applicable, representing the absence of a value.

True / False

In Oracle, a column defined with the NOT NULL constraint can contain NULL values.

True False

A column defined with the NOT NULL constraint cannot contain NULL values; it must always contain a value.

True / False

The NULL keyword in Oracle can be used in SQL statements to insert a NULL value into a column.

True False

The NULL keyword is used in SQL statements to insert a NULL value into a column.

True / False

In Oracle, the expression NULL = NULL returns TRUE.

True False

In Oracle, the expression NULL = NULL returns UNKNOWN, because NULL represents an unknown value, and comparing two unknowns does not yield a definitive TRUE or FALSE.

True / False

In Oracle, the NVL function can be used to replace NULL values with a specified value.

True False

The NVL function is used to replace NULL values with a specified value, allowing for handling of NULL values in queries.

True / False

The IS NOT NULL condition in Oracle is used to filter out rows that contain NULL values in a specified column.

True False

The IS NOT NULL condition is used to filter out rows where a specified column contains NULL values.

True / False

In Oracle, a unique constraint on a column allows multiple NULL values.

True False

In Oracle, a unique constraint allows multiple NULL values because NULLs are considered distinct from each other.

True / False

In Oracle, the COALESCE function returns the first non-NULL value from a list of expressions.

True False

The COALESCE function returns the first non-NULL value from a list of expressions, providing a way to handle NULL values in queries.

True / False

Oracle treats empty strings ('') and NULL as the same in VARCHAR2 columns.

True False

In Oracle, empty strings in VARCHAR2 columns are treated as NULL, meaning '' is considered equivalent to NULL.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes topics on handling NULL values and using functions like NVL and COALESCE to manage NULLs in SQL statements.

True False

The OCP certification exam covers advanced topics, including how to handle NULL values and use functions like NVL and COALESCE to manage NULLs in SQL statements, reflecting the candidate's comprehensive understanding of NULL handling in Oracle.

Q11

Single Choice

In Oracle, what does the NOT NULL constraint do when applied to a column?

Allows only NULL values in the column. Prevents NULL values from being inserted into the column. Automatically sets the column to NULL if no value is provided.Converts all NULL values to a default value.

The NOT NULL constraint ensures that a column cannot contain NULL values; it must always have a value.

Q12

Single Choice

What is the default behavior of a column in Oracle if no NOT NULL constraint is specified?

The column defaults to NOT NULL. The column allows NULL values by default. The column cannot accept any values.The column automatically sets a default value of 0.

If no NOT NULL constraint is specified, the column allows NULL values by default.

Q13

Single Choice

Which Oracle SQL command is used to add a NOT NULL constraint to an existing column?

ALTER TABLE table_name ADD NOT NULL (column_name); ALTER TABLE table_name MODIFY column_name NOT NULL; UPDATE table_name SET column_name NOT NULL;ALTER TABLE table_name SET column_name NOT NULL;

The ALTER TABLE ... MODIFY command is used to add a NOT NULL constraint to an existing column in Oracle.

Q14

Single Choice

ALTER TABLE table_name SET column_name NOT NULL;

SQL Code

SELECT * FROM employees WHERE department_id IS NULL;

Returns all rows where department_id is equal to an empty string. Returns all rows where department_id is 0. Returns all rows where department_id is NULL.Returns all rows where department_id is NOT NULL.

The IS NULL condition is used to filter rows where the specified column has a NULL value.

Q15

Single Choice

Consider the following SQL command:What happens if you try to insert a row without specifying a value for order_date?

SQL Code

CREATE TABLE orders (
 order_id NUMBER PRIMARY KEY,
 order_date DATE NOT NULL,
 customer_id NUMBER
);

The row will be inserted with order_date set to NULL. The row will be inserted with order_date set to the current date. The row will not be inserted, and an error will be raised.The row will be inserted with order_date set to a default value.

The NOT NULL constraint on order_date requires that a value must be provided for this column. If no value is provided, Oracle will raise an error.

Q16

Single Choice

Which of the following Oracle SQL commands will remove a NOT NULL constraint from a column?

SQL Code

ALTER TABLE customers MODIFY customer_name NULL;

Adds a NOT NULL constraint to the column. Drops the NOT NULL constraint, allowing NULL values in the column. Renames the column.Drops the column from the table.

The ALTER TABLE ... MODIFY ... NULL command removes the NOT NULL constraint, making it possible to insert NULL values into the column.

Q17

Single Choice

What is the outcome of the following Oracle SQL statement?

SQL Code

ALTER TABLE products ADD (price NUMBER DEFAULT 0 NOT NULL);

Adds a new column price with a NOT NULL constraint, defaulting to 0 if no value is provided. Adds a new column price that allows NULL values. Modifies an existing column price to have a default value of 0.Returns an error because a NOT NULL constraint cannot have a default value.

The statement adds a new column price with a NOT NULL constraint and a default value of 0, ensuring that all new rows will have a value of 0 for price if no value is specified.

Q18

Single Choice

How does Oracle handle NULL values in aggregate functions like SUM() or AVG()?

NULL values are treated as zero. NULL values are ignored. NULL values cause the aggregate function to return NULL.NULL values raise an error.

In Oracle, aggregate functions like SUM() or AVG() ignore NULL values in their calculations.

Q19

Single Choice

What is the result of the following Oracle SQL query if discount is NULL?

SQL Code

SELECT NVL(discount, 0) FROM promotions WHERE promotion_id = 1;

Returns NULL. Returns 0. Returns the value of discount.Raises an error because NULL cannot be replaced.

The NVL() function replaces NULL with a specified value. In this case, if discount is NULL, it returns 0.

Q20

Single Choice

Which of the following is true about the use of NOT NULL constraints in Oracle?

A NOT NULL constraint can be deferred. A NOT NULL constraint can be applied to a primary key column. A NOT NULL constraint can be temporarily disabled.A NOT NULL constraint can be applied to a column after data has been inserted.

A NOT NULL constraint is implicitly applied to a primary key column to ensure that the key values are always present and unique.

Q21

Multiple Choice

Identify the SQL scripts that correctly define a column with the NOT NULL constraint in Oracle.

CREATE TABLE employees ( first_name VARCHAR2(50) NOT NULL ); CREATE TABLE employees ( first_name VARCHAR2(50) ); CREATE TABLE employees ( first_name VARCHAR2(50) CONSTRAINT nn_first_name NOT NULL );CREATE TABLE employees ( first_name VARCHAR2(50) CONSTRAINT nn_first_name NULL );

The NOT NULL constraint ensures that a column cannot have a NULL value. The correct scripts will demonstrate how to define a column with this constraint.

Q22

Multiple Choice

Select the SQL scripts that correctly modify a column to allow NULL values in Oracle.

ALTER TABLE customers MODIFY email VARCHAR2(100) NULL; ALTER TABLE customers MODIFY email NULL; ALTER TABLE customers ALTER COLUMN email DROP NOT NULL;ALTER TABLE customers CHANGE email NULL;

Altering a column to allow NULL values involves removing the NOT NULL constraint. The correct scripts will demonstrate how to modify the column definition.

Q23

Multiple Choice

Determine the SQL scripts that correctly query rows where a specific column is NULL in Oracle.

SELECT * FROM orders WHERE shipped_date IS NULL; SELECT * FROM orders WHERE shipped_date = NULL; SELECT * FROM orders WHERE shipped_date IS NULL;SELECT * FROM orders WHERE shipped_date = '';

Querying rows where a column is NULL requires using the IS NULL condition. The correct scripts will demonstrate how to retrieve rows based on this condition.

Q24

Multiple Choice

Identify the SQL scripts that correctly insert NULL values into a table in Oracle.

INSERT INTO employees (first_name, last_name, middle_name) VALUES ('John', 'Doe', NULL); INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe'); INSERT INTO employees (first_name, last_name, middle_name) VALUES ('John', 'Doe', '');INSERT INTO employees (first_name, last_name, middle_name) VALUES ('John', 'Doe');

Inserting NULL values can be done by either explicitly specifying NULL or omitting the column in the INSERT statement. The correct scripts will demonstrate these methods.

Q25

Multiple Choice

Select the SQL scripts that correctly enforce a NOT NULL constraint on an existing column in Oracle.

ALTER TABLE contacts MODIFY phone_number VARCHAR2(15) NOT NULL; ALTER TABLE contacts ADD CONSTRAINT nn_phone_number CHECK (phone_number IS NOT NULL); ALTER TABLE contacts MODIFY phone_number CONSTRAINT nn_phone_number NOT NULL;ALTER TABLE contacts MODIFY phone_number NOT NULL;

Enforcing a NOT NULL constraint on an existing column requires modifying the column definition to include NOT NULL. The correct scripts will demonstrate this process.

Q26

Multiple Choice

Identify the SQL scripts that correctly drop the NOT NULL constraint from an existing column in Oracle.

ALTER TABLE employees MODIFY salary NULL; ALTER TABLE employees MODIFY salary DROP NOT NULL; ALTER TABLE employees ALTER COLUMN salary NULL;ALTER TABLE employees DROP CONSTRAINT NOT NULL;

Dropping the NOT NULL constraint from a column involves altering the column definition to remove the NOT NULL requirement. The correct scripts will demonstrate this.

Q27

Multiple Choice

Determine the SQL scripts that correctly define a table with multiple NOT NULL columns in Oracle.

CREATE TABLE projects ( project_name VARCHAR2(100) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL ); CREATE TABLE projects ( project_name VARCHAR2(100) CONSTRAINT nn_project_name NOT NULL, start_date DATE CONSTRAINT nn_start_date NOT NULL, end_date DATE CONSTRAINT nn_end_date NOT NULL ); CREATE TABLE projects ( project_name VARCHAR2(100), start_date DATE, end_date DATE );CREATE TABLE projects ( project_name VARCHAR2(100) NOT NULL, start_date DATE NOT NULL, end_date DATE );

Defining multiple NOT NULL columns in a table involves specifying the NOT NULL constraint for each column. The correct scripts will demonstrate how to define these columns.

Q28

Multiple Choice

Identify the SQL scripts that correctly handle checking for NULL values in Oracle.

SELECT first_name, last_name FROM employees WHERE phone_number IS NULL; SELECT first_name, last_name FROM employees WHERE phone_number = NULL; SELECT first_name, last_name FROM employees WHERE phone_number IS NULL;SELECT first_name, last_name FROM employees WHERE phone_number = '';

Checking for NULL values requires using the IS NULL condition in the WHERE clause. The correct scripts will demonstrate how to use this condition to retrieve the required data.

Q29

Multiple Choice

Select the SQL scripts that correctly enforce the NOT NULL constraint during table creation in Oracle.

CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) NOT NULL ); CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) CONSTRAINT nn_department_name NOT NULL ); CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) );CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) NULL );

Enforcing the NOT NULL constraint during table creation involves specifying the NOT NULL constraint directly in the column definition. The correct scripts will demonstrate this.

Q30

Multiple Choice

Determine the SQL scripts that correctly alter a table to add a NOT NULL column in Oracle.

ALTER TABLE tasks ADD task_name VARCHAR2(100) NOT NULL; ALTER TABLE tasks MODIFY task_name VARCHAR2(100) NOT NULL; ALTER TABLE tasks ADD task_name VARCHAR2(100) CONSTRAINT nn_task_name NOT NULL;ALTER TABLE tasks ADD task_name VARCHAR2(100);

Altering a table to add a NOT NULL column involves defining the new column with the NOT NULL constraint. The correct scripts will demonstrate this process.

Oracle Database Quiz Questions