Test Your PostgreSQL DEFAULT Constraint Knowledge with this Quiz

Progress

True / False

The DEFAULT constraint in PostgreSQL is used to provide a default value for a column if no value is specified.

True False

In PostgreSQL, the DEFAULT constraint automatically assigns a default value to a column if no value is provided during the insert.

True / False

In PostgreSQL, a column with a DEFAULT constraint must always have a value specified during the insertion of a new row.

True False

The purpose of the DEFAULT constraint is to provide a default value when no value is specified, so it's not necessary to provide a value explicitly during insertion.

True / False

You can add a DEFAULT constraint to an existing column in a PostgreSQL table.

True False

PostgreSQL allows you to add a DEFAULT constraint to an existing column using the ALTER TABLE statement.

True / False

In PostgreSQL, the DEFAULT constraint can reference other columns in the same table.

True False

A DEFAULT constraint cannot reference other columns or tables; it must be a constant expression.

True / False

Changing the default value of a column in PostgreSQL using the DEFAULT constraint will automatically update the existing rows with the new default value.

True False

Changing the default value only affects rows inserted after the change. Existing rows will retain their original values unless explicitly updated.

True / False

In PostgreSQL, the DEFAULT constraint can be used with all data types.

True False

PostgreSQL supports DEFAULT constraints for all data types, including integers, text, date, and more.

True / False

You can set a DEFAULT constraint to use a PostgreSQL function, such as NOW() for a timestamp column.

True False

PostgreSQL allows the use of functions as default values. For example, NOW() can be used to set the current timestamp as the default value for a column.

True / False

In PostgreSQL, removing a DEFAULT constraint from a column will remove all the existing default values from that column.

True False

Removing a DEFAULT constraint does not affect existing rows; it only prevents new rows from using the default value.

True / False

In PostgreSQL, the DEFAULT constraint can include arithmetic expressions, such as setting the default value of a column to 100 + 50.

True False

PostgreSQL supports arithmetic expressions in DEFAULT constraints, allowing calculations to be used as default values.

Q10

True / False

When a DEFAULT constraint is specified for a column in PostgreSQL, it ensures that all values inserted into the column adhere to the specified default value.

True False

The DEFAULT constraint only provides a default value if no value is specified during insertion. It does not enforce that all inserted values must be the default; explicit values can still be inserted.

Q11

Single Choice

What is the primary purpose of the DEFAULT constraint in MySQL?

To enforce uniqueness of column values To set a default value for a column when no value is provided To prevent NULL values in a columnTo define a foreign key relationship

The DEFAULT constraint is used to provide a default value to a column when an INSERT statement does not provide a value for that column.

Q12

Single Choice

Which of the following SQL statements correctly uses the DEFAULT constraint when creating a table?

CREATE TABLE employees (id INT, name VARCHAR(50), salary DECIMAL(10, 2) DEFAULT 5000); CREATE TABLE employees (id INT, name VARCHAR(50), salary DECIMAL(10, 2) SET DEFAULT 5000); CREATE TABLE employees (id INT, name VARCHAR(50), salary DEFAULT DECIMAL(10, 2) 5000);CREATE TABLE employees (id INT, name VARCHAR(50), salary DECIMAL(10, 2) 5000 DEFAULT);

The correct syntax for using the DEFAULT constraint in a CREATE TABLE statement is to place the DEFAULT keyword followed by the value after the column's data type.

Q13

Single Choice

If a column with a DEFAULT constraint is not included in an INSERT statement, what value will be assigned to that column?

NULL The DEFAULT value specified An empty string ''The value from the previous row

When a column with a DEFAULT constraint is omitted from an INSERT statement, the value defined by the DEFAULT constraint is automatically assigned to that column.

Q14

Single Choice

What will be the value of the price column when the following INSERT statement is executed? INSERT INTO products (id, name) VALUES (1, 'Product A');

SQL Code

CREATE TABLE products (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 price DECIMAL(10, 2) DEFAULT 19.99
);

NULL 19.99 0.00The INSERT statement will fail

Since the price column has a DEFAULT constraint of 19.99 and the INSERT statement does not provide a value for price, the value 19.99 is automatically assigned.

Q15

Single Choice

How can you modify an existing table to add a DEFAULT constraint to an existing column in MySQL?

ALTER TABLE table_name MODIFY column_name SET DEFAULT default_value; ALTER TABLE table_name CHANGE column_name column_name DEFAULT default_value; ALTER TABLE table_name ALTER column_name SET DEFAULT default_value;ALTER TABLE table_name MODIFY column_name DEFAULT default_value;

In MySQL, you can add a DEFAULT constraint to an existing column using the ALTER TABLE statement with the MODIFY clause.

Q16

Single Choice

Which of the following SQL statements removes the DEFAULT constraint from a column in MySQL?

ALTER TABLE table_name DROP DEFAULT; ALTER TABLE table_name MODIFY column_name DROP DEFAULT; ALTER TABLE table_name MODIFY column_name DROP DEFAULT;ALTER TABLE table_name ALTER column_name DROP DEFAULT;

To remove a DEFAULT constraint from a column in MySQL, you use the ALTER TABLE statement with the MODIFY clause and the DROP DEFAULT option.

Q17

Single Choice

Consider the following MySQL table definition:If an INSERT statement is executed without providing values for both order_date and status, what will be the default values for these columns?

SQL Code

CREATE TABLE orders (
 id INT PRIMARY KEY,
 order_date DATE DEFAULT CURRENT_DATE,
 status VARCHAR(20) DEFAULT 'pending'
);

order_date = NULL, status = 'pending' order_date = NULL, status = NULL order_date = CURRENT_DATE, status = 'pending'The INSERT statement will fail

The order_date column has a DEFAULT value of CURRENT_DATE, and the status column has a DEFAULT value of 'pending'. If these columns are omitted in the INSERT statement, these default values will be used.

Q18

Single Choice

What happens when you try to insert a NULL value into a column with both a DEFAULT constraint and a NOT NULL constraint?

SQL Code

CREATE TABLE employees (
 id INT PRIMARY KEY,
 name VARCHAR(50) NOT NULL DEFAULT 'John Doe',
 age INT NOT NULL DEFAULT 30
);

INSERT INTO employees (id, name, age) VALUES (1, NULL, 25);

The DEFAULT value will be inserted instead of NULL The INSERT statement will succeed, inserting NULL values The INSERT statement will fail due to the NOT NULL constraintThe DEFAULT constraint will override the NOT NULL constraint

The NOT NULL constraint requires that a column cannot have NULL values. If you attempt to insert NULL into a NOT NULL column, the INSERT statement will fail, even if a DEFAULT constraint is present

Q19

Single Choice

Consider the following MySQL table and INSERT statement:

SQL Code

CREATE TABLE customers (
 id INT PRIMARY KEY,
 first_name VARCHAR(50) DEFAULT 'Guest',
 last_name VARCHAR(50) DEFAULT 'User'
);

INSERT INTO customers (id, first_name, last_name) VALUES (1, DEFAULT, 'Smith');

DEFAULT Guest NULL'' (empty string)

In MySQL, using the keyword DEFAULT in the INSERT statement for a specific column will insert the column's defined default value. In this case, the default value of first_name is 'Guest'

Q20

Single Choice

Given the following MySQL table:

SQL Code

CREATE TABLE inventory (
 product_id INT PRIMARY KEY,
 quantity INT DEFAULT 100,
 last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The quantity will change, but last_updated will remain the same The last_updated column will be set to NULL The quantity and last_updated columns will both be updatedThe UPDATE statement will fail

The last_updated column uses the ON UPDATE CURRENT_TIMESTAMP clause, which updates the timestamp whenever the row is modified. Therefore, when the quantity is updated, last_updated will also be updated to the current timestamp.

Q21

Multiple Choice

Which SQL command creates a table with a DEFAULT constraint on the 'status' column in PostgreSQL?

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, status VARCHAR(20) NOT NULL DEFAULT 'pending' ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, status VARCHAR(20) DEFAULT 'pending' ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, status VARCHAR(20) NOT NULL DEFAULT 'open' );CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, status VARCHAR(20) NOT NULL );

This command creates an 'orders' table where the 'status' column has a DEFAULT constraint with a value of 'pending'. If no value is provided during insertion, 'pending' will be used.

Q22

Multiple Choice

Which SQL command adds a DEFAULT constraint to an existing column in PostgreSQL?

SQL Code

ALTER TABLE users
ALTER COLUMN signup_date SET DEFAULT CURRENT_DATE;

ALTER TABLE users ALTER COLUMN signup_date SET DEFAULT CURRENT_DATE; ALTER TABLE users ADD DEFAULT CURRENT_DATE FOR signup_date; ALTER TABLE users ALTER signup_date SET DEFAULT CURRENT_DATE;ALTER TABLE users ALTER COLUMN signup_date SET DEFAULT '2024-01-01';

This command adds a DEFAULT constraint to the 'signup_date' column in the 'users' table, setting the default value to the current date.

Q23

Multiple Choice

Which SQL command creates a table with a DEFAULT constraint that uses an expression in PostgreSQL?

SQL Code

CREATE TABLE accounts (
 account_id SERIAL PRIMARY KEY,
 balance NUMERIC(12, 2) DEFAULT 1000 * 0.05
);

CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(12, 2) DEFAULT 1000 * 0.05 ); CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(12, 2) DEFAULT 1000 * 5 ); CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(12, 2) DEFAULT 1000 + 0.05 );CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, balance NUMERIC(12, 2) DEFAULT 1000 / 20 );

This command creates an 'accounts' table where the 'balance' column has a DEFAULT constraint that uses an expression (1000 * 0.05). The default value will be 50.00 if no value is provided.

Q24

Multiple Choice

Which SQL command removes an existing DEFAULT constraint from a column in PostgreSQL?

SQL Code

ALTER TABLE employees
ALTER COLUMN job_title DROP DEFAULT;

ALTER TABLE employees ALTER COLUMN job_title DROP DEFAULT; ALTER TABLE employees ALTER COLUMN job_title REMOVE DEFAULT; ALTER TABLE employees ALTER COLUMN job_title DELETE DEFAULT;ALTER TABLE employees ALTER job_title DROP DEFAULT;

This command removes the DEFAULT constraint from the 'job_title' column in the 'employees' table, meaning no default value will be applied if the column is omitted during insertion.

Q25

Multiple Choice

Which SQL command creates a table with multiple columns having DEFAULT constraints in PostgreSQL?

SQL Code

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 price NUMERIC(10, 2) DEFAULT 9.99,
 stock INT DEFAULT 0
);

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2) DEFAULT 9.99, stock INT DEFAULT 0 ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2) NOT NULL DEFAULT 9.99, stock INT DEFAULT 0 ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2) DEFAULT 9.99, stock INT );CREATE TABLE products ( product_id SERIAL PRIMARY KEY, price NUMERIC(10, 2) DEFAULT 0, stock INT DEFAULT 10 );

This command creates a 'products' table with both 'price' and 'stock' columns having DEFAULT constraints. The 'price' column defaults to 9.99 and the 'stock' column defaults to 0.

Q26

Multiple Choice

Which SQL command modifies an existing column to change its DEFAULT value in PostgreSQL?

SQL Code

ALTER TABLE employees
ALTER COLUMN salary SET DEFAULT 50000;

ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 50000; ALTER TABLE employees ALTER COLUMN salary CHANGE DEFAULT 50000; ALTER TABLE employees MODIFY COLUMN salary SET DEFAULT 50000;ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 40000;

This command changes the DEFAULT value of the 'salary' column in the 'employees' table to 50000. Any new row inserted without a specified salary will default to 50000.

Q27

Multiple Choice

Which SQL command ensures that a DEFAULT constraint on a column uses a function as the default value in PostgreSQL?

SQL Code

CREATE TABLE logs (
 log_id SERIAL PRIMARY KEY,
 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ DEFAULT NOW() );CREATE TABLE logs ( log_id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ DEFAULT CURRENT_DATE );

This command creates a 'logs' table where the 'created_at' column has a DEFAULT constraint using the CURRENT_TIMESTAMP function, which automatically sets the current date and time.

Q28

Multiple Choice

Which SQL command creates a table with a DEFAULT constraint on a JSONB column in PostgreSQL?

SQL Code

CREATE TABLE configurations (
 config_id SERIAL PRIMARY KEY,
 settings JSONB DEFAULT '{}'
);

CREATE TABLE configurations ( config_id SERIAL PRIMARY KEY, settings JSONB DEFAULT '{}' ); CREATE TABLE configurations ( config_id SERIAL PRIMARY KEY, settings JSON DEFAULT '{}' ); CREATE TABLE configurations ( config_id SERIAL PRIMARY KEY, settings TEXT DEFAULT '{}' );CREATE TABLE configurations ( config_id SERIAL PRIMARY KEY, settings JSONB DEFAULT '[]' );

This command creates a 'configurations' table where the 'settings' column is of JSONB type with a DEFAULT value of an empty JSON object '{}'.

Q29

Multiple Choice

Which SQL command defines a DEFAULT constraint on a BOOLEAN column in PostgreSQL?

SQL Code

CREATE TABLE features (
 feature_id SERIAL PRIMARY KEY,
 is_enabled BOOLEAN DEFAULT TRUE
);

CREATE TABLE features ( feature_id SERIAL PRIMARY KEY, is_enabled BOOLEAN DEFAULT TRUE ); CREATE TABLE features ( feature_id SERIAL PRIMARY KEY, is_enabled BOOLEAN DEFAULT FALSE ); CREATE TABLE features ( feature_id SERIAL PRIMARY KEY, is_enabled BOOLEAN NOT NULL DEFAULT TRUE );CREATE TABLE features ( feature_id SERIAL PRIMARY KEY, is_enabled BOOLEAN );

This command creates a 'features' table where the 'is_enabled' column is of BOOLEAN type with a DEFAULT value of TRUE.

Q30

Multiple Choice

Which SQL command defines a DEFAULT constraint on a column with a UUID data type in PostgreSQL?

SQL Code

CREATE TABLE devices (
 device_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
 device_name VARCHAR(100) NOT NULL
);

CREATE TABLE devices ( device_id UUID DEFAULT gen_random_uuid() PRIMARY KEY, device_name VARCHAR(100) NOT NULL ); CREATE TABLE devices ( device_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, device_name VARCHAR(100) NOT NULL ); CREATE TABLE devices ( device_id UUID DEFAULT uuid_generate_v1() PRIMARY KEY, device_name VARCHAR(100) NOT NULL );CREATE TABLE devices ( device_id UUID DEFAULT '00000000-0000-0000-0000-000000000000' PRIMARY KEY, device_name VARCHAR(100) NOT NULL );

This command creates a 'devices' table where the 'device_id' column is of UUID type with a DEFAULT value generated by the 'gen_random_uuid()' function.

PostgreSQL Database Quiz Questions