Test Your MySQL AUTO INCREMENT Knowledge with This Quiz

Progress

True / False

In MySQL, the AUTO_INCREMENT attribute can be applied to any column.

True False

The AUTO_INCREMENT attribute can only be applied to columns defined as PRIMARY KEY or UNIQUE in MySQL.

True / False

In MySQL, only one column per table can be assigned the AUTO_INCREMENT attribute.

True False

MySQL allows only one column per table to be assigned the AUTO_INCREMENT attribute.

True / False

The AUTO_INCREMENT value in MySQL starts at 1 by default.

True False

By default, MySQL starts the AUTO_INCREMENT value at 1 and increments it by 1 for each new row.

True / False

In MySQL, you can set the initial value for an AUTO_INCREMENT column using the AUTO_INCREMENT table option.

True False

You can set the initial value for an AUTO_INCREMENT column by using the AUTO_INCREMENT table option in the CREATE TABLE or ALTER TABLE statement.

True / False

The AUTO_INCREMENT attribute in MySQL can be used with VARCHAR data type columns.

True False

The AUTO_INCREMENT attribute can only be used with numeric data types such as INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT.

True / False

In MySQL, deleting the highest-numbered row in an AUTO_INCREMENT column and then inserting a new row will reuse the deleted number.

True False

By default, MySQL will not reuse the deleted number. It will continue to increment the AUTO_INCREMENT value.

True / False

MySQL’s AUTO_INCREMENT attribute can be used in conjunction with the PARTITION feature.

True False

MySQL’s AUTO_INCREMENT attribute can be used with tables that are partitioned, and each partition will maintain its own AUTO_INCREMENT sequence.

True / False

In MySQL, if the AUTO_INCREMENT column reaches its maximum value, it will automatically reset to 1.

True False

If the AUTO_INCREMENT column reaches its maximum value, MySQL will generate an error, and you need to handle it manually. It will not reset to 1 automatically.

True / False

In MySQL, you can reset the AUTO_INCREMENT value of a table without deleting any rows.

True False

You can reset the AUTO_INCREMENT value by using the ALTER TABLE statement to set a new AUTO_INCREMENT value.

Q10

True / False

In MySQL, using INSERT IGNORE with an AUTO_INCREMENT column will skip rows that would cause a duplicate key error and continue to increment the AUTO_INCREMENT value.

True False

The INSERT IGNORE statement in MySQL will skip rows that would cause a duplicate key error and will continue to increment the AUTO_INCREMENT value without interruption.

Q11

Single Choice

What is the primary use of the AUTO_INCREMENT attribute in MySQL?

To create a unique identifier for each row automatically To increment values in a column manually To decrease values in a column automaticallyTo reset values in a column

The AUTO_INCREMENT attribute is used to generate a unique identifier for new rows in a table automatically.

Q12

Single Choice

Which MySQL data type is commonly used with AUTO_INCREMENT?

VARCHAR TEXT INTNull

The AUTO_INCREMENT attribute is typically used with integer data types like INT.

Q13

Single Choice

How do you set a column to auto increment in a MySQL table creation statement?

CREATE TABLE example (id VARCHAR AUTO_INCREMENT PRIMARY KEY); CREATE TABLE example (id INT AUTO_INCREMENT PRIMARY KEY); CREATE TABLE example (id DATE AUTO_INCREMENT PRIMARY KEY);CREATE TABLE example (id TEXT AUTO_INCREMENT PRIMARY KEY);

The correct syntax to set a column as AUTO_INCREMENT is by specifying it as an INT and setting it as the primary key.

Q14

Single Choice

What happens if you insert a row with a NULL value in an AUTO_INCREMENT column in MySQL?

It generates an error. It assigns a default value of 0. It assigns the next available auto increment value.It skips the insertion.

Inserting a NULL value in an AUTO_INCREMENT column results in assigning the next available increment value.

Q15

Single Choice

Can you have more than one AUTO_INCREMENT column in a MySQL table?

Yes No NullNull

A MySQL table can have only one AUTO_INCREMENT column.

Q16

Single Choice

How do you reset the AUTO_INCREMENT value for a table in MySQL?

A) ALTER TABLE example AUTO_INCREMENT = 1; SET AUTO_INCREMENT example = 1; RESET AUTO_INCREMENT exampleCHANGE AUTO_INCREMENT example TO 1

The ALTER TABLE statement is used to reset the AUTO_INCREMENT value.

Q17

Single Choice

How can you retrieve the current AUTO_INCREMENT value for a specific table in MySQL?

SELECT AUTO_INCREMENT FROM example; SHOW TABLE STATUS LIKE 'example GET AUTO_INCREMENT FROM example;DESCRIBE AUTO_INCREMENT FROM example;

The SHOW TABLE STATUS command returns information about the table, including the current AUTO_INCREMENT value.

Q18

Single Choice

If an AUTO_INCREMENT column is dropped and recreated, what happens to the increment value in MySQL?

It resets to 1 It continues from the last used value It starts from a user-defined valueIt generates an error

Dropping and recreating an AUTO_INCREMENT column will reset the increment value to 1.

Q19

Single Choice

Which MySQL statement can affect the AUTO_INCREMENT value due to deletion of rows?

DELETE UPDATE TRUNCATESELECT

The TRUNCATE statement resets the AUTO_INCREMENT value to its starting value.

Q20

Single Choice

In MySQL, which statement would correctly alter an existing column to add the AUTO_INCREMENT attribute?

ALTER TABLE example CHANGE id id INT AUTO_INCREMENT; MODIFY TABLE example ADD AUTO_INCREMENT TO id; ALTER TABLE example SET AUTO_INCREMENT ON id;CHANGE TABLE example id INT AUTO_INCREMENT;

The ALTER TABLE statement with CHANGE is used to modify the column to add the AUTO_INCREMENT attribute.

Q21

Multiple Choice

Identify the correct SQL statement to create a table with an AUTO_INCREMENT primary key.

SQL Code

CREATE TABLE employees (
 employee_id INT AUTO_INCREMENT PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, employee_name VARCHAR(100) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100) AUTO_INCREMENT ); CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, employee_name VARCHAR(100) NOT NULL );CREATE TABLE employees ( employee_id INT AUTO_INCREMENT, employee_name VARCHAR(100) PRIMARY KEY );

The AUTO_INCREMENT keyword allows the employee_id column to automatically generate unique integer values for each new row.

Q22

Multiple Choice

Choose the correct SQL statement to add an AUTO_INCREMENT column to an existing table.

SQL Code

ALTER TABLE orders
ADD COLUMN order_id INT AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE orders ADD COLUMN order_id INT AUTO_INCREMENT PRIMARY KEY; ALTER TABLE orders MODIFY COLUMN order_id INT AUTO_INCREMENT; ALTER TABLE orders ADD AUTO_INCREMENT order_id INT PRIMARY KEY;ALTER TABLE orders ADD COLUMN order_id INT PRIMARY KEY AUTO_INCREMENT;

The ALTER TABLE statement with ADD COLUMN is used to introduce an AUTO_INCREMENT column to an existing table, ensuring it serves as the primary key.

Q23

Multiple Choice

Determine the correct SQL statement to reset the AUTO_INCREMENT value of a table to start from a specific number.

SQL Code

ALTER TABLE products
AUTO_INCREMENT = 100;

ALTER TABLE products AUTO_INCREMENT = 100; ALTER TABLE products SET AUTO_INCREMENT = 100; ALTER TABLE products MODIFY AUTO_INCREMENT = 100;ALTER TABLE products RESET AUTO_INCREMENT = 100;

The AUTO_INCREMENT option allows you to reset the starting value of the auto-incrementing column to a specified number, which in this case is 100.

Q24

Multiple Choice

Which SQL statement correctly uses AUTO_INCREMENT for a non-primary key column?

SQL Code

CREATE TABLE inventory (
 item_id INT PRIMARY KEY,
 serial_number INT AUTO_INCREMENT,
 product_name VARCHAR(100)
);

CREATE TABLE inventory ( item_id INT PRIMARY KEY, serial_number INT AUTO_INCREMENT, product_name VARCHAR(100) ); CREATE TABLE inventory ( item_id INT PRIMARY KEY, serial_number INT, product_name VARCHAR(100) AUTO_INCREMENT ); CREATE TABLE inventory ( item_id INT PRIMARY KEY AUTO_INCREMENT, serial_number INT, product_name VARCHAR(100) );CREATE TABLE inventory ( item_id INT AUTO_INCREMENT PRIMARY KEY, serial_number INT, product_name VARCHAR(100) );

While AUTO_INCREMENT is commonly used for primary keys, it can also be applied to a non-primary key column to ensure unique sequential values.

Q25

Multiple Choice

Select the correct SQL statement to ensure that an AUTO_INCREMENT column starts at 1 by default.

SQL Code

CREATE TABLE accounts (
 account_id INT AUTO_INCREMENT PRIMARY KEY,
 balance DECIMAL(10, 2)
);

CREATE TABLE accounts ( account_id INT AUTO_INCREMENT PRIMARY KEY, balance DECIMAL(10, 2) ); CREATE TABLE accounts ( account_id INT AUTO_INCREMENT PRIMARY KEY DEFAULT 1, balance DECIMAL(10, 2) ); CREATE TABLE accounts ( account_id INT PRIMARY KEY AUTO_INCREMENT, balance DECIMAL(10, 2) );CREATE TABLE accounts ( account_id INT PRIMARY KEY, balance DECIMAL(10, 2) );

By default, the AUTO_INCREMENT column starts at 1 unless explicitly defined otherwise, ensuring each new row gets a unique sequential integer.

Q26

Multiple Choice

Determine the correct SQL statement to enforce an AUTO_INCREMENT constraint on a new table while setting the initial value.

SQL Code

CREATE TABLE memberships (
 membership_id INT AUTO_INCREMENT PRIMARY KEY,
 start_date DATE,
 AUTO_INCREMENT = 500
);

CREATE TABLE memberships ( membership_id INT AUTO_INCREMENT PRIMARY KEY, start_date DATE, AUTO_INCREMENT = 500 ); CREATE TABLE memberships ( membership_id INT AUTO_INCREMENT PRIMARY KEY, start_date DATE, SET AUTO_INCREMENT = 500 ); CREATE TABLE memberships ( membership_id INT PRIMARY KEY, start_date DATE, AUTO_INCREMENT = 500 );CREATE TABLE memberships ( membership_id INT PRIMARY KEY AUTO_INCREMENT = 500, start_date DATE );

In this scenario, the AUTO_INCREMENT value is set to start at 500, meaning the first row inserted will have a membership_id of 500.

Q27

Multiple Choice

Which SQL statement correctly modifies an existing AUTO_INCREMENT column to start from a higher value?

SQL Code

ALTER TABLE invoices
AUTO_INCREMENT = 1000;

ALTER TABLE invoices AUTO_INCREMENT = 1000; ALTER TABLE invoices SET AUTO_INCREMENT TO 1000; ALTER TABLE invoices MODIFY COLUMN invoice_id AUTO_INCREMENT = 1000;ALTER TABLE invoices CHANGE AUTO_INCREMENT = 1000;

The AUTO_INCREMENT option in the ALTER TABLE statement is used to modify the starting point of the auto-incrementing sequence.

Q28

Multiple Choice

Identify the correct SQL statement to reset the AUTO_INCREMENT value after deleting rows from a table.

SQL Code

ALTER TABLE orders
AUTO_INCREMENT = 1;

ALTER TABLE orders AUTO_INCREMENT = 1; ALTER TABLE orders SET AUTO_INCREMENT = 1; ALTER TABLE orders RESET AUTO_INCREMENT TO 1;ALTER TABLE orders CHANGE AUTO_INCREMENT TO 1;

After deleting rows, the AUTO_INCREMENT value can be reset to start from a lower value, such as 1, for future inserts.

Q29

Multiple Choice

Select the correct SQL statement to ensure an AUTO_INCREMENT column increments by 2 instead of 1.

SQL Code

SET @@auto_increment_increment = 2;
CREATE TABLE subscriptions (
 subscription_id INT AUTO_INCREMENT PRIMARY KEY,
 subscriber_name VARCHAR(100)
);

SET @@auto_increment_increment = 2; CREATE TABLE subscriptions ( subscription_id INT AUTO_INCREMENT PRIMARY KEY, subscriber_name VARCHAR(100) ); CREATE TABLE subscriptions ( subscription_id INT AUTO_INCREMENT(2) PRIMARY KEY, subscriber_name VARCHAR(100) ); CREATE TABLE subscriptions ( subscription_id INT AUTO_INCREMENT PRIMARY KEY INCREMENT BY 2, subscriber_name VARCHAR(100) );ALTER TABLE subscriptions AUTO_INCREMENT INCREMENT BY 2; CREATE TABLE subscriptions ( subscription_id INT PRIMARY KEY, subscriber_name VARCHAR(100) );

By setting the @@auto_increment_increment system variable to 2, the subscription_id column will increment by 2 for each new row.

Q30

Multiple Choice

Determine the correct SQL statement to ensure that the AUTO_INCREMENT column does not reuse numbers after deletion.

SQL Code

ALTER TABLE reservations
AUTO_INCREMENT = 500;

ALTER TABLE reservations AUTO_INCREMENT = 500; ALTER TABLE reservations SET AUTO_INCREMENT = 500; ALTER TABLE reservations MODIFY AUTO_INCREMENT = 500;ALTER TABLE reservations CHANGE AUTO_INCREMENT = 500;

After rows are deleted, the AUTO_INCREMENT column will continue from the highest existing value plus one, ensuring no reuse of deleted numbers.

MySQL Database Quiz Questions