Test Your MySQL DEFAULT Constraint Knowledge with This Quiz

Progress

True / False

The DEFAULT constraint in MySQL automatically assigns a default value to a column if no value is provided during insertion.

True False

The DEFAULT constraint in MySQL is used to provide a default value for a column if no value is specified when a row is inserted.

True / False

In MySQL, the DEFAULT constraint can only be applied to numeric columns.

True False

The DEFAULT constraint in MySQL can be applied to any data type, including numeric, string, and date types.

True / False

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

True False

You can use the ALTER TABLE statement in MySQL to add a DEFAULT constraint to an existing column.

True / False

In MySQL, the DEFAULT constraint can be used with the TIMESTAMP data type to automatically set the current date and time for new rows.

True False

In MySQL, you can set a TIMESTAMP column to have a default value of CURRENT_TIMESTAMP to automatically insert the current date and time for new rows.

True / False

MySQL allows the DEFAULT constraint to be used with the AUTO_INCREMENT attribute.

True False

The AUTO_INCREMENT attribute in MySQL automatically generates a unique value for the column, so the DEFAULT constraint cannot be used with it.

True / False

If a column in MySQL has a DEFAULT constraint and a NULL value is inserted, the default value will be used instead of NULL.

True False

If NULL is explicitly inserted into a column with a DEFAULT constraint, NULL will be stored. The default value is only used when no value is provided.

True / False

In MySQL, you can define a DEFAULT constraint that is a result of an arithmetic operation.

True False

You can define a DEFAULT constraint in MySQL that involves simple arithmetic operations, such as DEFAULT (5 + 3).

True / False

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

True False

The DEFAULT constraint in MySQL cannot reference other columns in the table; it can only be a constant value or a function that returns a constant value.

True / False

In MySQL, you can set the default value of a column to be an expression involving system functions like UUID().

True False

You can use certain functions like UUID() as default values in MySQL, which will generate a unique identifier for each new row.

Q10

True / False

When exporting a MySQL database, the DEFAULT constraints on the columns are not included in the dump file.

True False

When exporting a MySQL database, the DEFAULT constraints on the columns are included in the dump file. This ensures that the database schema, including default values, is preserved during import.

Q11

Single Choice

What is the purpose of the DEFAULT constraint in MySQL?

To set a default storage engine for the database To assign a default value to a column when no value is specified To create a default index on a columnTo automatically update a column with the current timestamp

The DEFAULT constraint in MySQL assigns a default value to a column if no value is provided during the insertion of a new record.

Q12

Single Choice

Which of the following is the correct syntax to set a default value of 'N/A' for a column named status in MySQL?

ALTER TABLE table_name ADD CONSTRAINT DEFAULT 'N/A' TO status; ALTER TABLE table_name SET DEFAULT 'N/A' FOR status; ALTER TABLE table_name MODIFY status DEFAULT 'N/A';ALTER TABLE table_name CHANGE status SET DEFAULT 'N/A';

The correct syntax to set a default value for an existing column in MySQL is by using the MODIFY clause followed by DEFAULT and the default value.

Q13

Single Choice

What will be the default value for a column if no DEFAULT constraint is specified in MySQL?

NULL 0 (empty string)CURRENT_TIMESTAMP

If no DEFAULT constraint is specified for a column in MySQL, the default value will be NULL.

Q14

Single Choice

How can you remove a DEFAULT constraint from a column in MySQL?

ALTER TABLE table_name REMOVE DEFAULT FOR column_name; ALTER TABLE table_name DROP DEFAULT FOR column_name; ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;ALTER TABLE table_name MODIFY column_name DROP DEFAULT;

The correct way to remove a DEFAULT constraint from a column in MySQL is by using the MODIFY clause followed by DROP DEFAULT.

Q15

Single Choice

In MySQL, can the DEFAULT constraint be used with a BLOB or TEXT column?

Yes No NullNull

The DEFAULT constraint cannot be used with BLOB or TEXT columns in MySQL because these data types are meant for large data storage which doesn't support default values.

Q16

Single Choice

Which of the following MySQL queries will correctly set a default value of CURRENT_TIMESTAMP for a created_at column?

Which of the following MySQL queries will correctly set a default value of CURRENT_TIMESTAMP for a created_at column? ALTER TABLE table_name MODIFY created_at SET DEFAULT CURRENT_TIMESTAMP; ALTER TABLE table_name MODIFY created_at DEFAULT CURRENT_TIMESTAMP;ALTER TABLE table_name MODIFY created_at SET DEFAULT 'CURRENT_TIMESTAMP';

The correct syntax to set the default value to CURRENT_TIMESTAMP for a column in MySQL is by using DEFAULT CURRENT_TIMESTAMP without quotes around CURRENT_TIMESTAMP

Q17

Single Choice

How does the DEFAULT constraint interact with the INSERT ... ON DUPLICATE KEY UPDATE statement in MySQL?

The DEFAULT value is ignored during duplicate key updates. The DEFAULT value is always applied during duplicate key updates. The DEFAULT value is only applied if explicitly specified in the UPDATE part.The DEFAULT value is applied if no value is provided during duplicate key updates.

In MySQL, the DEFAULT value is used when no value is provided for the column during an INSERT ... ON DUPLICATE KEY UPDATE operation.

Q18

Single Choice

Can a MySQL column have a DEFAULT value that is derived from another column?

Yes No NullNull

In MySQL, a column cannot have a DEFAULT value that is derived from another column. Default values must be constants.

Q19

Single Choice

Which MySQL version introduced the ability to set a default value for a JSON column?

MySQL 5.5 MySQL 5.7 MySQL 8.0MySQL 8.0.13

MySQL 8.0.13 introduced the ability to set a default value for a JSON column, which was not possible in earlier versions.

Q20

Single Choice

Which statement about DEFAULT constraints in MySQL is true?

A DEFAULT constraint can be defined at the table level A DEFAULT constraint can only be defined on primary key columns. A DEFAULT constraint can provide a function as its default value.A DEFAULT constraint can be specified in a column definition when creating or altering a table.

In MySQL, a DEFAULT constraint can be specified directly within the column definition when creating or altering a table, allowing for default values to be automatically assigned if no value is provided during data insertion.

Q21

Multiple Choice

Identify the correct SQL statement to create a table with a column that has a default value.

SQL Code

CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 signup_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE customers ( customer_id INT PRIMARY KEY, signup_date DATE DEFAULT CURRENT_DATE ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, signup_date DATE DEFAULT '2024-01-01' ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, signup_date DATE DEFAULT NULL );CREATE TABLE customers ( customer_id INT PRIMARY KEY, signup_date DATE );

The DEFAULT constraint is used to provide a default value for a column. If no value is specified during insertion, the default value will be used.

Q22

Multiple Choice

Choose the correct SQL statement to modify an existing column to include a default value.

SQL Code

ALTER TABLE orders
MODIFY COLUMN status VARCHAR(20) DEFAULT 'Pending';

ALTER TABLE orders MODIFY COLUMN status VARCHAR(20) DEFAULT 'Pending'; ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'Pending'; ALTER TABLE orders CHANGE COLUMN status VARCHAR(20) DEFAULT 'Pending';ALTER TABLE orders SET DEFAULT 'Pending' FOR status;

The ALTER TABLE statement with MODIFY COLUMN is used to change the definition of a column, including setting a default value.

Q23

Multiple Choice

Determine the correct SQL statement to create a table where a numeric column has a default value of 0.

SQL Code

CREATE TABLE inventory (
 item_id INT PRIMARY KEY,
 quantity INT DEFAULT 0
);

CREATE TABLE inventory ( item_id INT PRIMARY KEY, quantity INT DEFAULT 0 ); CREATE TABLE inventory ( item_id INT PRIMARY KEY, quantity INT DEFAULT NULL ); CREATE TABLE inventory ( item_id INT PRIMARY KEY, quantity INT DEFAULT 1 );CREATE TABLE inventory ( item_id INT PRIMARY KEY, quantity INT );

The DEFAULT constraint is applied to the quantity column, ensuring it defaults to 0 if no value is provided.

Q24

Multiple Choice

Which SQL statement correctly adds a DEFAULT constraint to an existing column?

SQL Code

ALTER TABLE employees
ALTER COLUMN salary SET DEFAULT 50000;

ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 50000; ALTER TABLE employees ADD CONSTRAINT DEFAULT 50000 FOR salary; ALTER TABLE employees MODIFY COLUMN salary DEFAULT 50000;ALTER TABLE employees CHANGE COLUMN salary SET DEFAULT 50000;

The ALTER COLUMN clause is used to modify an existing column by adding a default value.

Q25

Multiple Choice

Select the correct SQL statement to drop a DEFAULT constraint from a column.

SQL Code

ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

ALTER TABLE orders ALTER COLUMN status DROP DEFAULT; ALTER TABLE orders MODIFY COLUMN status DROP DEFAULT; ALTER TABLE orders CHANGE COLUMN status REMOVE DEFAULT;ALTER TABLE orders ALTER COLUMN status SET DEFAULT NULL;

The DROP DEFAULT clause is used to remove the default value from a column, leaving it with no automatic default.

Q26

Multiple Choice

Identify the correct SQL statement to create a table with multiple columns, each having a different default value.

SQL Code

CREATE TABLE accounts (
 account_id INT PRIMARY KEY,
 account_type VARCHAR(20) DEFAULT 'Checking',
 balance DECIMAL(10, 2) DEFAULT 0.00
);

CREATE TABLE accounts ( account_id INT PRIMARY KEY, account_type VARCHAR(20) DEFAULT 'Checking', balance DECIMAL(10, 2) DEFAULT 0.00 ); CREATE TABLE accounts ( account_id INT PRIMARY KEY, account_type VARCHAR(20) DEFAULT NULL, balance DECIMAL(10, 2) DEFAULT 0.00 ); CREATE TABLE accounts ( account_id INT PRIMARY KEY, account_type VARCHAR(20), balance DECIMAL(10, 2) );CREATE TABLE accounts ( account_id INT PRIMARY KEY, account_type VARCHAR(20) DEFAULT 'Savings', balance DECIMAL(10, 2) DEFAULT NULL );

Each column in the table is assigned a specific default value, which will be used if no other value is provided during insertion.

Q27

Multiple Choice

Determine the correct SQL statement to set a default value for a date column to the current date.

SQL Code

CREATE TABLE subscriptions (
 subscription_id INT PRIMARY KEY,
 start_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE subscriptions ( subscription_id INT PRIMARY KEY, start_date DATE DEFAULT CURRENT_DATE ); CREATE TABLE subscriptions ( subscription_id INT PRIMARY KEY, start_date DATE DEFAULT '2024-01-01' ); CREATE TABLE subscriptions ( subscription_id INT PRIMARY KEY, start_date DATE DEFAULT NOW() );CREATE TABLE subscriptions ( subscription_id INT PRIMARY KEY, start_date DATE DEFAULT TODAY() );

Using CURRENT_DATE as the default value ensures that the start_date will automatically be set to the current date at the time of insertion.

Q28

Multiple Choice

Which SQL statement correctly uses the DEFAULT constraint with an ENUM data type?

SQL Code

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 order_status ENUM('Pending', 'Completed', 'Canceled') DEFAULT 'Pending'
);

CREATE TABLE orders ( order_id INT PRIMARY KEY, order_status ENUM('Pending', 'Completed', 'Canceled') DEFAULT 'Pending' ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_status ENUM('Pending', 'Completed', 'Canceled') DEFAULT NULL ); CREATE TABLE orders ( order_id INT PRIMARY KEY, order_status ENUM('Pending', 'Completed', 'Canceled') DEFAULT 'Completed' );CREATE TABLE orders ( order_id INT PRIMARY KEY, order_status ENUM('Pending', 'Completed', 'Canceled') DEFAULT 'Canceled' );

The DEFAULT constraint can be used with an ENUM data type to specify a default option from the list of allowed values.

Q29

Multiple Choice

Select the correct SQL statement to enforce a NOT NULL constraint along with a DEFAULT constraint.

SQL Code

CREATE TABLE users (
 user_id INT PRIMARY KEY,
 email VARCHAR(100) NOT NULL DEFAULT 'example@example.com'
);

CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL DEFAULT 'example@example.com' ); CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(100) DEFAULT 'example@example.com' ); CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(100) NOT NULL DEFAULT NULL );CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(100) DEFAULT 'example@example.com' NOT NULL );

The column is defined with both NOT NULL and DEFAULT constraints, ensuring it cannot be NULL and will have a default value if no other value is provided.

Q30

Multiple Choice

Identify the correct SQL statement to drop a DEFAULT constraint while retaining the column's NOT NULL constraint.

SQL Code

ALTER TABLE employees
ALTER COLUMN hire_date DROP DEFAULT;

ALTER TABLE employees ALTER COLUMN hire_date DROP DEFAULT; ALTER TABLE employees ALTER COLUMN hire_date DROP DEFAULT KEEP NOT NULL; ALTER TABLE employees MODIFY COLUMN hire_date DROP DEFAULT;ALTER TABLE employees CHANGE COLUMN hire_date DROP DEFAULT;

Dropping the DEFAULT constraint removes the automatic default value, but the NOT NULL constraint remains in effect.

MySQL Database Quiz Questions