AUTO_INCREMENT vs SERIAL vs IDENTITY in SQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a MySQL table with an auto-incrementing primary key.

SQL

CREATE TABLE users (id INT [1] PRIMARY KEY, name VARCHAR(100));

Drag options to blanks, or click blank then click option'

AAUTO_INCREMENT

BSERIAL

CGENERATED

DIDENTITY

Attempts:

3 left

💡 Hint

Common Mistakes

Using SERIAL which is not valid in MySQL.

Using IDENTITY which is used in other databases like SQL Server.

In MySQL, AUTO_INCREMENT is used to create a column that automatically increases its value for each new row.

2fill in blank

medium

Complete the code to create a PostgreSQL table with a serial primary key.

SQL

CREATE TABLE products (product_id [1] PRIMARY KEY, product_name TEXT);

Drag options to blanks, or click blank then click option'

AAUTO_INCREMENT

BBIGSERIAL

CIDENTITY

DSERIAL

Attempts:

3 left

💡 Hint

Common Mistakes

Using AUTO_INCREMENT which is not valid in PostgreSQL.

Using IDENTITY which is a newer alternative but not SERIAL.

In PostgreSQL, SERIAL is a special data type that creates an auto-incrementing integer column.

3fill in blank

hard

Fix the error in the SQL Server table creation by completing the identity column syntax.

SQL

CREATE TABLE orders (order_id INT [1](1,1) PRIMARY KEY, order_date DATE);

Drag options to blanks, or click blank then click option'

AIDENTITY

BSERIAL

CAUTO_INCREMENT

DGENERATED

Attempts:

3 left

💡 Hint

Common Mistakes

Using AUTO_INCREMENT which is MySQL specific.

Using SERIAL which is PostgreSQL specific.

In SQL Server, IDENTITY(1,1) defines an auto-incrementing column starting at 1 and incrementing by 1.

4fill in blank

hard

Fill in the blank to create a PostgreSQL table using the IDENTITY syntax for auto-increment.

SQL

CREATE TABLE employees (emp_id INT GENERATED [1] AS IDENTITY PRIMARY KEY, emp_name TEXT);

Drag options to blanks, or click blank then click option'

ANOT NULL

BALWAYS

CBY DEFAULT

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOT NULL or NULL which are unrelated here.

PostgreSQL supports GENERATED BY DEFAULT AS IDENTITY to create auto-incrementing columns as a modern alternative to SERIAL.

5fill in blank

hard

Fill in the blanks to create a MySQL table with an auto-incrementing primary key and a default value.

SQL

CREATE TABLE sessions (session_id INT [1] PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(20) DEFAULT [2]);

Drag options to blanks, or click blank then click option'

AAUTO_INCREMENT

B'active'

C'pending'

DSERIAL

Attempts:

3 left

💡 Hint

Common Mistakes

Using SERIAL in MySQL which is invalid.

Not quoting default string values.

In MySQL, AUTO_INCREMENT creates an auto-incrementing column. The DEFAULT keyword sets a default value for a column.