Serial and identity columns in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

PostgreSQL

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

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

AINTEGER

BTEXT

CSERIAL

DBOOLEAN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTEGER without auto-increment will require manual value insertion.

Using TEXT or BOOLEAN types for primary keys is not appropriate.

The SERIAL keyword automatically creates an integer column that auto-increments with each new row, perfect for primary keys.

2fill in blank

medium

Complete the code to define an identity column that auto-generates values starting from 1.

PostgreSQL

CREATE TABLE orders (order_id INT GENERATED ALWAYS AS IDENTITY (START WITH [1]), order_date DATE);

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

A0

B100

CNULL

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Starting identity columns at 0 may cause confusion or errors.

Using NULL as a start value is invalid.

The START WITH 1 clause sets the first generated value to 1 for the identity column.

3fill in blank

hard

Fix the error in the code to correctly create a serial column named 'product_id'.

PostgreSQL

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

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

ASERIAL

BSERIAL PRIMARY

CSERIAL PRIMARY KEY

DINTEGER SERIAL

Attempts:

3 left

💡 Hint

Common Mistakes

Adding 'PRIMARY' inside the type causes syntax errors.

Using 'INTEGER SERIAL' is invalid syntax.

The correct syntax is to use SERIAL for the column type and specify PRIMARY KEY separately. The code already has PRIMARY KEY after the blank, so just SERIAL is needed.

4fill in blank

hard

Fill both blanks to create a table with an identity column that increments by 5 starting at 10.

PostgreSQL

CREATE TABLE invoices (invoice_id INT GENERATED [1] AS IDENTITY (START WITH [2], INCREMENT BY 5));

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

AALWAYS

BBY DEFAULT

C10

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'BY DEFAULT' when 'ALWAYS' is required.

Starting at 1 instead of 10.

GENERATED ALWAYS AS IDENTITY ensures the database always generates the value. START WITH 10 sets the first value to 10, and INCREMENT BY 5 increases by 5 each time.

5fill in blank

hard

Fill all three blanks to create a table with a serial column and a name column with a default value.

PostgreSQL

CREATE TABLE employees (emp_id [1] PRIMARY KEY, name VARCHAR(50) DEFAULT [2], hired_date DATE DEFAULT [3]);

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

ASERIAL

B'Unknown'

CCURRENT_DATE

DINTEGER

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTEGER instead of SERIAL for auto-increment.

Missing quotes around default string values.

Using wrong default for date column.

SERIAL creates an auto-incrementing primary key. The name column defaults to the string 'Unknown' if no name is given. The hired_date column defaults to the current date.