Test Your PostgreSQL AUTO INCREMENT Knowledge with This Quiz

Progress

True / False

In PostgreSQL, the SERIAL keyword is used to create an auto-incrementing integer column.

True False

The SERIAL keyword in PostgreSQL creates an integer column that automatically increments by 1 for each new row inserted.

True / False

PostgreSQL does not support auto-incrementing columns.

True False

PostgreSQL supports auto-incrementing columns using the SERIAL keyword and sequences.

True / False

The SERIAL keyword automatically creates a sequence in PostgreSQL.

True False

When using the SERIAL keyword, PostgreSQL automatically creates a sequence to handle the auto-incrementing values.

True / False

You can create an auto-incrementing column in PostgreSQL without using the SERIAL keyword.

True False

You can create an auto-incrementing column using a custom sequence and the DEFAULT keyword with the nextval() function.

True / False

The BIGSERIAL keyword in PostgreSQL is used to create a 64-bit auto-incrementing integer column.

True False

The BIGSERIAL keyword creates a column that auto-increments using a 64-bit integer, which is suitable for tables with a large number of rows.

True / False

Using SERIAL in PostgreSQL guarantees that the sequence values will always be continuous without gaps.

True False

Sequences in PostgreSQL can have gaps, especially if transactions are rolled back, as the sequence values generated are not reused.

True / False

In PostgreSQL, you can alter an existing column to make it an auto-incrementing column using the SERIAL keyword.

True False

The SERIAL keyword cannot be used with the ALTER TABLE command. Instead, you would need to create a sequence and set the column's default value using nextval().

True / False

In PostgreSQL, the SERIAL data type can be used in a composite primary key.

True False

You can use the SERIAL data type in a composite primary key, but you need to define the sequence and default value explicitly as part of the composite key definition.

True / False

In PostgreSQL, you can reset a sequence used for auto-incrementing columns using the ALTER SEQUENCE command.

True False

The ALTER SEQUENCE command can be used to restart a sequence at a specified value, effectively resetting the auto-incrementing sequence.

Q10

True / False

When migrating from MySQL to PostgreSQL, you can directly map MySQL's AUTO_INCREMENT to PostgreSQL's SERIAL without any issues.

True False

While MySQL's AUTO_INCREMENT can be mapped to PostgreSQL's SERIAL, there are differences in behavior and syntax that need to be considered during migration. For example, sequence handling and default values need to be explicitly managed in PostgreSQL.

Q11

Single Choice

AUTO_INCREMENT attribute in MySQL?

To automatically increase the value of an integer column when a new row is inserted. To set a column as the primary key. To set a column to automatically generate random numbers.To automatically delete rows when they are no longer needed.

The AUTO_INCREMENT attribute is used in MySQL to automatically increment the value of a column each time a new row is inserted into the table.

Q12

Single Choice

In MySQL, which data types can be used with AUTO_INCREMENT?

INT and FLOAT CHAR and VARCHAR INT, TINYINT, SMALLINT, MEDIUMINT, BIGINTDATE and DATETIME

The AUTO_INCREMENT attribute can only be used with integer types (INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT) in MySQL.

Q13

Single Choice

Which MySQL statement correctly creates a table with an AUTO_INCREMENT column

SQL Code

CREATE TABLE users (
 user_id VARCHAR(10) AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50)
);

CREATE TABLE users ( user_id VARCHAR(10) AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) ); CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) ); CREATE TABLE users ( user_id INT AUTO_INCREMENT, username VARCHAR(50) AUTO_INCREMENT );CREATE TABLE users ( user_id INT, username VARCHAR(50) AUTO_INCREMENT PRIMARY KEY );

The correct syntax to create an AUTO_INCREMENT column in MySQL involves using an integer type and designating it as the primary key.

Q14

Single Choice

How can you set the initial value of an AUTO_INCREMENT column in MySQL?

SQL Code

ALTER TABLE table_name AUTO_INCREMENT = 100;

By using the SET AUTO_INCREMENT keyword in the column definition. By using the ALTER TABLE statement with AUTO_INCREMENT option. By using the INITIAL VALUE keyword in the CREATE TABLE statement.It cannot be changed; it always starts at 1.

To set the initial value for an AUTO_INCREMENT column, you can use the ALTER TABLE statement with the AUTO_INCREMENT option. For example:

Q15

Single Choice

If you insert a new row without specifying a value for order_id, what value will order_id have?

SQL Code

CREATE TABLE orders (
 order_id INT AUTO_INCREMENT PRIMARY KEY,
 order_date DATE,
 customer_id INT
);

It will be NULL It will automatically be set to 0. It will be the next available integer value.The insertion will fail due to missing data.

The order_id will automatically be set to the next available integer value, which starts at 1 by default and increments with each new row.

Q16

Single Choice

What will happen if you try to insert a value for an AUTO_INCREMENT column in MySQL?

SQL Code

INSERT INTO users (user_id, username) VALUES (5, 'JohnDoe');

MySQL will ignore the provided value and use the next AUTO_INCREMENT value instead. MySQL will insert the provided value and continue incrementing from it. MySQL will throw an error, as you cannot manually insert values into an AUTO_INCREMENT column.MySQL will insert the value and reset the AUTO_INCREMENT sequence.

If you manually insert a value into an AUTO_INCREMENT column, MySQL will accept it and the AUTO_INCREMENT sequence will continue from the inserted value or the next highest value.

Q17

Single Choice

What is the purpose of the LAST_INSERT_ID() function in MySQL?

To retrieve the last AUTO_INCREMENT value inserted into any table. To retrieve the last AUTO_INCREMENT value inserted in the current session. To reset the AUTO_INCREMENT value to the last inserted ID.To manually set the AUTO_INCREMENT value for the next insert.

The LAST_INSERT_ID() function returns the most recent automatically generated AUTO_INCREMENT value that was inserted during the current session.

Q18

Single Choice

In a MySQL table with an AUTO_INCREMENT column, what will happen if you delete the row with the highest AUTO_INCREMENT value and then insert a new row?

The AUTO_INCREMENT value will be reused for the new row. The AUTO_INCREMENT value will be reset to 1. The AUTO_INCREMENT value will continue incrementing from the last highest value.The AUTO_INCREMENT column will throw an error during insertion.

Deleting a row with the highest AUTO_INCREMENT value does not affect the sequence; the next insertion will still increment from the highest value previously generated.

Q19

Single Choice

What problem can arise from using AUTO_INCREMENT in this self-referencing table?

SQL Code

CREATE TABLE employees (
 emp_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50),
 manager_id INT,
 FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

The AUTO_INCREMENT will cause an infinite loop. The foreign key constraint can lead to a circular dependency. The foreign key constraint can lead to a circular dependency.MySQL will automatically handle all dependencies without any issues.

When using AUTO_INCREMENT with self-referencing tables, a circular dependency issue can arise because the foreign key constraint depends on the AUTO_INCREMENT value being generated correctly.

Q20

Single Choice

In MySQL 8.0, how can you configure an AUTO_INCREMENT column to increment by a value other than 1?

By setting the AUTO_INCREMENT column to the desired value in the CREATE TABLE statement. By using the AUTO_INCREMENT_INCREMENT system variable. By using the SET AUTO_INCREMENT_STEP statement.By using a custom trigger to manually increment the value.

In MySQL 8.0, you can change the increment value of an AUTO_INCREMENT column by setting the AUTO_INCREMENT_INCREMENT system variable, which defines the interval between consecutive values of the AUTO_INCREMENT column.

Q21

Multiple Choice

Which SQL command creates a table with an AUTO INCREMENT column in PostgreSQL using the SERIAL data type?

SQL Code

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), last_name VARCHAR(50) ); CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50) ); CREATE TABLE employees ( employee_id BIGSERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );

This command creates an 'employees' table where the 'employee_id' column is automatically incremented using the SERIAL data type, which is a shorthand for creating an auto-incrementing integer column.

Q22

Multiple Choice

Which SQL command defines an AUTO INCREMENT column using the IDENTITY keyword in PostgreSQL?

SQL Code

CREATE TABLE orders (
 order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 order_date DATE NOT NULL,
 customer_id INT
);

CREATE TABLE orders ( order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_date DATE NOT NULL, customer_id INT ); CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, order_date DATE NOT NULL, customer_id INT ); CREATE TABLE orders ( order_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, order_date DATE NOT NULL, customer_id INT );CREATE TABLE orders ( order_id INTEGER AUTO_INCREMENT PRIMARY KEY, order_date DATE NOT NULL, customer_id INT );

This command creates an 'orders' table where the 'order_id' column is automatically incremented using the IDENTITY keyword, which is a more flexible alternative to SERIAL.

Q23

Multiple Choice

Which SQL command adds an AUTO INCREMENT column to an existing table in PostgreSQL?

SQL Code

ALTER TABLE customers
ADD COLUMN customer_id SERIAL PRIMARY KEY;

ALTER TABLE customers ADD COLUMN customer_id SERIAL PRIMARY KEY; ALTER TABLE customers ADD COLUMN customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY; ALTER TABLE customers ADD COLUMN customer_id BIGSERIAL PRIMARY KEY;ALTER TABLE customers ADD COLUMN customer_id INTEGER AUTO_INCREMENT PRIMARY KEY;

This command alters the 'customers' table by adding a new column 'customer_id' that is automatically incremented using the SERIAL data type.

Q24

Multiple Choice

Which SQL command creates a table with a BIGINT AUTO INCREMENT column using the BIGSERIAL data type in PostgreSQL?

SQL Code

CREATE TABLE transactions (
 transaction_id BIGSERIAL PRIMARY KEY,
 amount NUMERIC(10, 2),
 transaction_date TIMESTAMPTZ NOT NULL
);

CREATE TABLE transactions ( transaction_id BIGSERIAL PRIMARY KEY, amount NUMERIC(10, 2), transaction_date TIMESTAMPTZ NOT NULL ); CREATE TABLE transactions ( transaction_id SERIAL PRIMARY KEY, amount NUMERIC(10, 2), transaction_date TIMESTAMPTZ NOT NULL ); CREATE TABLE transactions ( transaction_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, amount NUMERIC(10, 2), transaction_date TIMESTAMPTZ NOT NULL );CREATE TABLE transactions ( transaction_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, amount NUMERIC(10, 2), transaction_date TIMESTAMPTZ NOT NULL );

This command creates a 'transactions' table where the 'transaction_id' column is automatically incremented using the BIGSERIAL data type, which is used for larger integer ranges.

Q25

Multiple Choice

Which SQL command uses the GENERATED BY DEFAULT AS IDENTITY option to define an AUTO INCREMENT column in PostgreSQL?

SQL Code

CREATE TABLE invoices (
 invoice_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 invoice_date DATE NOT NULL,
 total NUMERIC(10, 2)
);

CREATE TABLE invoices ( invoice_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, invoice_date DATE NOT NULL, total NUMERIC(10, 2) ); CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, invoice_date DATE NOT NULL, total NUMERIC(10, 2) ); CREATE TABLE invoices ( invoice_id BIGSERIAL PRIMARY KEY, invoice_date DATE NOT NULL, total NUMERIC(10, 2) );CREATE TABLE invoices ( invoice_id INTEGER AUTO_INCREMENT PRIMARY KEY, invoice_date DATE NOT NULL, total NUMERIC(10, 2) );

This command creates an 'invoices' table where the 'invoice_id' column is automatically incremented using the GENERATED BY DEFAULT AS IDENTITY option, allowing manual inserts if needed.

Q26

Multiple Choice

Which SQL command resets the sequence of an AUTO INCREMENT column in PostgreSQL?

SQL Code

ALTER SEQUENCE employees_employee_id_seq RESTART WITH 1;

ALTER SEQUENCE employees_employee_id_seq RESTART WITH 1; ALTER SEQUENCE employees_employee_id_seq SET RESTART WITH 1; ALTER TABLE employees ALTER COLUMN employee_id RESTART WITH 1;ALTER SEQUENCE employees_employee_id_seq RESTART 1;

This command resets the sequence associated with the 'employee_id' column in the 'employees' table, starting the next value at 1.

Q27

Multiple Choice

Which SQL command creates a table with a custom start value for an AUTO INCREMENT column using the IDENTITY keyword in PostgreSQL?

SQL Code

CREATE TABLE tickets (
 ticket_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1000) PRIMARY KEY,
 issue_description TEXT NOT NULL
);

CREATE TABLE tickets ( ticket_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1000) PRIMARY KEY, issue_description TEXT NOT NULL ); CREATE TABLE tickets ( ticket_id SERIAL PRIMARY KEY, issue_description TEXT NOT NULL ); CREATE TABLE tickets ( ticket_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000) PRIMARY KEY, issue_description TEXT NOT NULL );CREATE TABLE tickets ( ticket_id BIGSERIAL PRIMARY KEY, issue_description TEXT NOT NULL );

This command creates a 'tickets' table where the 'ticket_id' column starts incrementing from 1000 using the IDENTITY keyword with a custom start value.

Q28

Multiple Choice

Which SQL command removes the AUTO INCREMENT property from a column in PostgreSQL?

SQL Code

ALTER TABLE employees
ALTER COLUMN employee_id DROP IDENTITY;

ALTER TABLE employees ALTER COLUMN employee_id DROP IDENTITY; ALTER TABLE employees ALTER COLUMN employee_id DROP AUTO_INCREMENT; ALTER TABLE employees ALTER COLUMN employee_id DROP SEQUENCE;ALTER TABLE employees ALTER COLUMN employee_id REMOVE IDENTITY;

This command removes the AUTO INCREMENT (IDENTITY) property from the 'employee_id' column in the 'employees' table, stopping automatic incrementation.

Q29

Multiple Choice

Which SQL command modifies the increment value of an existing AUTO INCREMENT column in PostgreSQL?

SQL Code

ALTER SEQUENCE employees_employee_id_seq INCREMENT BY 10;

ALTER SEQUENCE employees_employee_id_seq INCREMENT BY 10; ALTER TABLE employees ALTER COLUMN employee_id SET INCREMENT 10; ALTER SEQUENCE employees_employee_id_seq SET INCREMENT 10;ALTER TABLE employees ALTER COLUMN employee_id SET AUTO_INCREMENT BY 10;

This command modifies the sequence associated with the 'employee_id' column in the 'employees' table, setting the increment value to 10.

Q30

Multiple Choice

Which SQL command creates a table with a BIGINT AUTO INCREMENT column starting from a custom value in PostgreSQL?

SQL Code

CREATE TABLE logs (
 log_id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1000000) PRIMARY KEY,
 log_message TEXT NOT NULL,
 log_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE logs ( log_id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1000000) PRIMARY KEY, log_message TEXT NOT NULL, log_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE logs ( log_id BIGSERIAL PRIMARY KEY, log_message TEXT NOT NULL, log_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE logs ( log_id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1000000) PRIMARY KEY, log_message TEXT NOT NULL, log_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP );CREATE TABLE logs ( log_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, log_message TEXT NOT NULL, log_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP );

This command creates a 'logs' table where the 'log_id' column starts incrementing from 1000000 using the BIGINT data type and the IDENTITY keyword.

PostgreSQL Database Quiz Questions