Test Your PostgreSQL NULL vs NOT NULL Knowledge with This Quiz

Progress

True / False

In PostgreSQL, a column defined as NOT NULL cannot store NULL values.

True False

A NOT NULL constraint ensures that a column cannot have a NULL value. Any attempt to insert a NULL value into this column will result in an error.

True / False

The default setting for a column in PostgreSQL is NOT NULL.

True False

By default, columns in PostgreSQL are allowed to store NULL values unless explicitly defined as NOT NULL.

True / False

In PostgreSQL, a column with a NOT NULL constraint can be altered to allow NULL values.

True False

You can alter a column to remove the NOT NULL constraint using the ALTER TABLE statement, allowing it to store NULL values.

True / False

You can use the IS NULL condition in a PostgreSQL SELECT statement to filter rows with NULL values in a specific column.

True False

The IS NULL condition is used to filter records with NULL values in a specified column.

True / False

In PostgreSQL, the COALESCE function can be used to return the first non-NULL value from a list of arguments.

True False

The COALESCE function is used to return the first non-NULL value from its list of arguments.

True / False

In PostgreSQL, you cannot add a NOT NULL constraint to an existing column that contains NULL values

True False

PostgreSQL will not allow you to add a NOT NULL constraint to a column that already contains NULL values. You must first update or remove those NULL values.

True / False

PostgreSQL treats empty strings as NULL values.

True False

PostgreSQL distinguishes between NULL values and empty strings. An empty string is a valid value, while NULL represents the absence of a value.

True / False

You can create a PostgreSQL table with a column that has both NULL and NOT NULL constraints.

True False

A column cannot have both NULL and NOT NULL constraints simultaneously. It must be either NULL or NOT NULL.

True / False

In PostgreSQL, using the DISTINCT keyword will ignore NULL values in the result set.

True False

The DISTINCT keyword does not ignore NULL values. It treats NULL as a unique value, so all NULL values are grouped together as one.

Q10

True / False

: In PostgreSQL, when a NOT NULL constraint is added to an existing column using ALTER TABLE, the database performs a check to ensure there are no NULL values in that column before applying the constraint.

True False

When adding a NOT NULL constraint to an existing column, PostgreSQL checks the column for any NULL values. If any are found, the operation fails until all NULL values are removed.

Q11

Single Choice

What will the following SQL query return if the info column contains JSON data and info->"$.name" is NULL?

SQL Code

SELECT JSON_NULL(info->"$.name") FROM users;

NULL "NULL" EMPTY0

In MySQL, JSON_NULL represents the JSON value null, which is returned as the string "null" when queried.

Q12

Single Choice

Which of the following is a valid check for a JSON field in MySQL that is NOT NULL?

SELECT * FROM users WHERE JSON_NULL(info->"$.name") IS NOT NULL; SELECT * FROM users WHERE JSON_IS_NULL(info->"$.name"); SELECT * FROM users WHERE info->"$.name" IS NOT NULL;SELECT * FROM users WHERE info->"$.name" IS NULL;

The correct way to check if a JSON field is NOT NULL in MySQL is to use the IS NOT NULL operator.

Q13

Single Choice

Which MySQL function can be used to check if a JSON path expression returns NULL?

JSON_NULL() IS NULL JSON_CONTAINS_NULL()JSON_IS_NULL()

MySQL's JSON_IS_NULL() is used to check if a JSON path expression results in NULL. However, it's essential to note that JSON_IS_NULL() is not a real function in MySQL. The correct method is to check the JSON value directly using IS NULL.

Q14

Single Choice

Given the following table orders with a JSON column order_details, how would you retrieve rows where the JSON field order_details->"$.status" is NULL?

SQL Code

SELECT * FROM orders WHERE _________;

JSON_IS_NULL(order_details->"$.status"); order_details->"$.status" IS NULL; JSON_NULL(order_details->"$.status");order_details->"$.status" IS NOT NULL;

The correct query is to use IS NULL to check for NULL values within JSON fields.

Q15

Single Choice

In MySQL, what is the difference between JSON_NULL and IS NULL when querying JSON data?

JSON_NULL is used for JSON data, and IS NULL is used for non-JSON data. JSON_NULL represents JSON "null", while IS NULL checks if a value is NULL. JSON_NULL returns TRUE if a value is "null", while IS NULL returns TRUE if a value is NULL.There is no difference between them.

JSON_NULL is used to identify JSON's null value, represented as "null", whereas IS NULL checks for SQL NULL values.

Q16

Single Choice

How can you check if a specific key in a JSON column is NOT NULL in MySQL?

SQL Code

SELECT * FROM products WHERE _________;

JSON_IS_NULL(json_column->"$.key"); json_column->"$.key" IS NOT NULL; JSON_CONTAINS(json_column, "key") IS NOT NULL;json_column->"$.key" IS NULL;

The IS NOT NULL operator is used in MySQL to check if a specific key in a JSON column is NOT NULL.

Q17

Single Choice

What does the following SQL query return if the settings column contains JSON data and the value of settings->"$.enabled" is explicitly null?

SQL Code

SELECT settings->"$.enabled" IS NOT NULL FROM users;

1 0 NullEmpty

If settings->"$.enabled" is null, IS NOT NULL will return 0 since the value is indeed NULL.

Q18

Single Choice

How would you filter rows in a MySQL table where a JSON field metadata->"$.isActive" is NOT NULL and its value is TRUE?

SQL Code

SELECT * FROM users WHERE _________;

metadata->"$.isActive" IS NOT NULL AND metadata->"$.isActive" = TRUE; JSON_IS_NULL(metadata->"$.isActive") AND metadata->"$.isActive" = TRUE; metadata->"$.isActive" IS NULL AND metadata->"$.isActive" = TRUE;metadata->"$.isActive" = "TRUE" IS NOT NULL;

The correct syntax ensures that the JSON field metadata->"$.isActive" is NOT NULL and that its value is TRUE.

Q19

Single Choice

How can you handle a situation in MySQL where a JSON column may contain a NULL value that needs to be replaced with a default string "N/A"?

SQL Code

SELECT _________ FROM orders;

IFNULL(order_data->"$.customer_name", "N/A"); COALESCE(order_data->"$.customer_name", "N/A"); JSON_REPLACE(order_data->"$.customer_name", NULL, "N/A");JSON_CONTAINS(order_data->"$.customer_name", "N/A");

The COALESCE() function in MySQL returns the first non-NULL value in the list, effectively replacing NULL JSON values with the default string "N/A".

Q20

Single Choice

Consider a MySQL table configurations with a JSON column settings containing various configuration parameters. How would you write a query to select rows where the timeout parameter in the settings JSON field is NULL and also where the maxRetries parameter is NOT NULL?

SQL Code

SELECT * FROM configurations WHERE _________;

settings->"$.timeout" IS NULL AND settings->"$.maxRetries" IS NOT NULL; JSON_CONTAINS(settings, "timeout") AND JSON_NOT_NULL(settings->"$.maxRetries"); settings->"$.timeout" = "NULL" AND settings->"$.maxRetries" != "NULL";JSON_IS_NULL(settings->"$.timeout") AND JSON_IS_NOT_NULL(settings->"$.maxRetries");

The query correctly checks if the timeout parameter is NULL and the maxRetries parameter is NOT NULL in the JSON column settings. This ensures the query retrieves only those rows where both conditions are satisfied.

Q21

Multiple Choice

Which SQL command creates a table with a column that allows NULL values in PostgreSQL?

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50) NULL
);

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) NULL ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NULL, last_name VARCHAR(50) NULL ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) );

This command creates an 'employees' table where the 'last_name' column is explicitly allowed to contain NULL values.

Q22

Multiple Choice

Which SQL command creates a table with a column that does not allow NULL values in PostgreSQL?

SQL Code

CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 email VARCHAR(100) NOT NULL
);

CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, email VARCHAR(100) NOT NULL ); CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, email VARCHAR(100) NULL ); CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, email VARCHAR(100) );CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, email VARCHAR(100) NOT NULL DEFAULT 'none@example.com' );

This command creates a 'customers' table where the 'email' column is defined as NOT NULL, ensuring that every record must have an email value.

Q23

Multiple Choice

Which SQL command is used to alter an existing column to disallow NULL values in PostgreSQL?

SQL Code

ALTER TABLE orders
ALTER COLUMN order_date SET NOT NULL;

ALTER TABLE orders ALTER COLUMN order_date SET NOT NULL; ALTER TABLE orders MODIFY COLUMN order_date NOT NULL; ALTER TABLE orders ALTER order_date SET NOT NULL;ALTER TABLE orders CHANGE COLUMN order_date NOT NULL;

This command alters the 'order_date' column in the 'orders' table to disallow NULL values, enforcing that every row must have a value for this column.

Q24

Multiple Choice

Which SQL command is used to alter a column to allow NULL values in PostgreSQL?

SQL Code

ALTER TABLE products
ALTER COLUMN price DROP NOT NULL;

ALTER TABLE products ALTER COLUMN price DROP NOT NULL; ALTER TABLE products ALTER COLUMN price SET NULL; ALTER TABLE products MODIFY COLUMN price NULL;ALTER TABLE products CHANGE price NULL;

This command modifies the 'price' column in the 'products' table to allow NULL values by removing the NOT NULL constraint.

Q25

Multiple Choice

Which SQL command creates a table with both NULL and NOT NULL columns in PostgreSQL?

SQL Code

CREATE TABLE inventory (
 item_id SERIAL PRIMARY KEY,
 item_name VARCHAR(100) NOT NULL,
 item_description TEXT,
 quantity INT NOT NULL
);

CREATE TABLE inventory ( item_id SERIAL PRIMARY KEY, item_name VARCHAR(100) NOT NULL, item_description TEXT, quantity INT NOT NULL ); CREATE TABLE inventory ( item_id SERIAL PRIMARY KEY, item_name VARCHAR(100), item_description TEXT, quantity INT NOT NULL ); CREATE TABLE inventory ( item_id SERIAL PRIMARY KEY, item_name VARCHAR(100) NULL, item_description TEXT NOT NULL, quantity INT );CREATE TABLE inventory ( item_id SERIAL PRIMARY KEY, item_name VARCHAR(100) NOT NULL, item_description TEXT NOT NULL, quantity INT NOT NULL );

This command creates an 'inventory' table where 'item_name' and 'quantity' columns are NOT NULL, while 'item_description' can be NULL.

Q26

Multiple Choice

Which SQL command creates a table with a default value that is NOT NULL in PostgreSQL?

SQL Code

CREATE TABLE users (
 user_id SERIAL PRIMARY KEY,
 username VARCHAR(50) NOT NULL,
 status VARCHAR(20) NOT NULL DEFAULT 'active'
);

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'active' ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, status VARCHAR(20) DEFAULT 'active' ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50), status VARCHAR(20) DEFAULT 'active' );CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, status VARCHAR(20) );

This command creates a 'users' table where the 'status' column is NOT NULL and has a default value of 'active', ensuring that every row will have a value for this column.

Q27

Multiple Choice

Which SQL command would you use to enforce a NOT NULL constraint on an existing column with NULL values in PostgreSQL?

SQL Code

UPDATE employees
SET email = 'unknown@example.com'
WHERE email IS NULL;

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

UPDATE employees SET email = 'unknown@example.com' WHERE email IS NULL; ALTER TABLE employees ALTER COLUMN email SET NOT NULL; ALTER TABLE employees ALTER COLUMN email SET NOT NULL; ALTER TABLE employees SET email = 'unknown@example.com';UPDATE employees SET email = NULL; ALTER TABLE employees ALTER COLUMN email SET NOT NULL;

This command first updates all NULL values in the 'email' column to a default value and then alters the column to enforce the NOT NULL constraint.

Q28

Multiple Choice

Which SQL command removes the NOT NULL constraint from a column that has a default value in PostgreSQL?

SQL Code

ALTER TABLE orders
ALTER COLUMN delivery_date DROP NOT NULL;

ALTER TABLE orders ALTER COLUMN delivery_date DROP NOT NULL; ALTER TABLE orders ALTER COLUMN delivery_date DROP NULL; ALTER TABLE orders ALTER COLUMN delivery_date SET NULL;ALTER TABLE orders ALTER COLUMN delivery_date DROP DEFAULT;

This command modifies the 'delivery_date' column in the 'orders' table to allow NULL values by removing the NOT NULL constraint, even if a default value is defined.

Q29

Multiple Choice

Which SQL command defines a column as NOT NULL but provides a default value in PostgreSQL?

SQL Code

CREATE TABLE accounts (
 account_id SERIAL PRIMARY KEY,
 balance NUMERIC(10, 2) NOT NULL DEFAULT 0.00
);

CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(10, 2) NOT NULL DEFAULT 0.00 ); CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(10, 2) DEFAULT 0.00 ); CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(10, 2) NOT NULL );CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(10, 2) DEFAULT 0.00 NOT NULL );

This command creates an 'accounts' table where the 'balance' column is NOT NULL and provides a default value of 0.00, ensuring every record has a balance.

Q30

Multiple Choice

Which SQL command creates a table with a combination of NULL, NOT NULL, and DEFAULT constraints in PostgreSQL?

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(50),
 hire_date DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50), hire_date DATE NOT NULL DEFAULT CURRENT_DATE ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NULL, last_name VARCHAR(50), hire_date DATE NOT NULL DEFAULT CURRENT_DATE ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) NOT NULL, hire_date DATE NOT NULL DEFAULT CURRENT_DATE );CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL DEFAULT 'Unknown', last_name VARCHAR(50), hire_date DATE DEFAULT CURRENT_DATE );

This command creates an 'employees' table with a mix of NULL, NOT NULL, and DEFAULT constraints, ensuring that some columns must have values while others can be NULL.

PostgreSQL Database Quiz Questions