CREATE TABLE with PostgreSQL types - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this CREATE TABLE statement?

Consider the following SQL statement in PostgreSQL:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  salary NUMERIC(10,2),
  hired_on DATE
);

What is the data type of the id column after table creation?

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  salary NUMERIC(10,2),
  hired_on DATE
);

A INTEGER with auto-increment behavior

B VARCHAR with length 50

C NUMERIC with precision 10 and scale 2

D DATE type

💡 Hint

Remember that SERIAL is a shorthand for an integer column that auto-increments.

In PostgreSQL, SERIAL is a pseudo-type that creates an integer column with auto-incrementing behavior using a sequence. So the 'id' column becomes an INTEGER with auto-increment.

❓ query_result

intermediate

What is the maximum length of the 'description' column?

Given this table creation:

CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  description TEXT,
  price MONEY
);

What is the maximum length allowed for the description column?

PostgreSQL

CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  description TEXT,
  price MONEY
);

A 1000 characters

B 255 characters

C 50 characters

D Unlimited length (only limited by system memory)

💡 Hint

TEXT type in PostgreSQL can store very large strings.

The TEXT data type in PostgreSQL can store strings of any length, limited only by the maximum size of a field in the database system.

📝 Syntax

advanced

Which CREATE TABLE statement is syntactically correct for a JSONB column?

Choose the correct PostgreSQL CREATE TABLE statement that defines a column named data with JSONB type:

A CREATE TABLE logs (id SERIAL PRIMARY KEY, data JSONB NOT NULL);

B CREATE TABLE logs (id SERIAL PRIMARY KEY, data JSON NOTB NULL);

C CREATE TABLE logs (id SERIAL PRIMARY KEY, data JSONB NULLABLE);

D CREATE TABLE logs (id SERIAL PRIMARY KEY, data JSONB NOTNULL);

💡 Hint

Check the spelling and syntax of the NOT NULL constraint.

Option A uses the correct JSONB type and the correct NOT NULL syntax. Other options have syntax errors or invalid keywords.

❓ optimization

advanced

Which data type is best for storing a US phone number in PostgreSQL?

You want to store US phone numbers in a PostgreSQL table. Which data type is the most appropriate for efficient storage and querying?

A VARCHAR(14) to store formatted phone numbers like '[phone]'

B BIGINT to store only digits without formatting

C TEXT to allow any phone number format

D CHAR(10) to store only digits without formatting

💡 Hint

Consider storage size and ability to perform numeric comparisons.

BIGINT stores numeric digits efficiently and allows numeric operations. VARCHAR or TEXT store strings but use more space and are slower for numeric queries.

🧠 Conceptual

expert

What happens if you define a column as SERIAL and also specify a default value?

In PostgreSQL, consider this table definition:

CREATE TABLE test (
  id SERIAL DEFAULT 1000 PRIMARY KEY
);

What will be the behavior of the id column when inserting rows without specifying id?

A The sequence starts at 1000 and increments from there

B An error occurs because SERIAL and DEFAULT cannot be combined

C The default value 1000 is used for all inserts, ignoring the sequence

D The sequence generated by SERIAL overrides the default 1000 value

💡 Hint

Think about how DEFAULT interacts with SERIAL's implicit sequence.

Specifying DEFAULT 1000 overrides the implicit sequence default created by SERIAL, so all inserts without id use 1000, causing duplicate key errors if multiple rows inserted.

Practice

1. Which PostgreSQL data type is best to store a person's full name in a CREATE TABLE statement?

easy

A. INTEGER

B. VARCHAR or TEXT

C. BOOLEAN

D. DATE

Solution

1. Step 1: Understand the data type purpose

   Names are text data, so we need a type that stores characters.

2. Step 2: Choose appropriate text type in PostgreSQL

   VARCHAR or TEXT types store strings of characters, perfect for names.

3. Final Answer:

   VARCHAR or TEXT -> Option B

4. Quick Check:

   Text data = VARCHAR or TEXT [OK]

Hint: Use VARCHAR or TEXT for any text data like names [OK]

Common Mistakes:

• Using INTEGER for text data
• Using BOOLEAN for names
• Using DATE for text fields

2. Which of the following is the correct syntax to create a table named employees with an integer primary key id and a text column name?

easy

A. CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT);

B. CREATE employees TABLE (id INT PRIMARY, name STRING);

C. CREATE TABLE employees {id INT PRIMARY KEY, name TEXT};

D. CREATE TABLE employees (id INT, name TEXT PRIMARY KEY);

Solution

1. Step 1: Check the basic CREATE TABLE syntax

   PostgreSQL uses: CREATE TABLE table_name (column definitions); with commas separating columns.

2. Step 2: Verify data types and constraints

   INTEGER or INT is valid; PRIMARY KEY follows the column type; TEXT is valid for strings.

3. Final Answer:

   CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT); -> Option A

4. Quick Check:

   Correct syntax and types = CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT); [OK]

Hint: Use parentheses and commas correctly in CREATE TABLE [OK]

Common Mistakes:

• Wrong keyword order like PRIMARY before type
• Using curly braces instead of parentheses
• Wrong data types like STRING instead of TEXT

3. Given the table creation:
CREATE TABLE products (product_id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, price NUMERIC(10,2));
What will be the data type of product_id and what does SERIAL do?

medium

A. product_id is an auto-incrementing integer used as a unique ID

B. product_id is a string that stores numbers

C. product_id is a floating-point number

D. product_id is a boolean flag

Solution

1. Step 1: Understand SERIAL type in PostgreSQL

   SERIAL creates an integer column that auto-increments with each new row, perfect for unique IDs.

2. Step 2: Confirm data type and purpose

   It is an integer type, not string or float, used as a primary key to uniquely identify rows.

3. Final Answer:

   product_id is an auto-incrementing integer used as a unique ID -> Option A

4. Quick Check:

   SERIAL = auto-increment integer [OK]

Hint: SERIAL means auto-increment integer primary key [OK]

Common Mistakes:

• Thinking SERIAL is a string type
• Confusing SERIAL with floating-point
• Assuming SERIAL is a boolean

4. Identify the error in this table creation statement:
CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATE NOT NULL, total_amount FLOAT(10,2));

medium

A. Missing comma between columns

B. PRIMARY KEY cannot be used on INT columns

C. FLOAT(10,2) is invalid syntax in PostgreSQL

D. DATE cannot be NOT NULL

Solution

1. Step 1: Check FLOAT data type usage

   PostgreSQL FLOAT accepts at most one precision argument (1-53 digits); FLOAT(10,2) uses two arguments like NUMERIC, which is invalid syntax for FLOAT. Use NUMERIC(10,2) for precise money values.

2. Step 2: Verify other parts of the statement

   INT can be PRIMARY KEY, DATE can be NOT NULL, commas are correctly placed.

3. Final Answer:

   FLOAT(10,2) is invalid syntax in PostgreSQL -> Option C

4. Quick Check:

   FLOAT(p,s) invalid; use NUMERIC(p,s) [OK]

Hint: FLOAT(p) ok, but not FLOAT(p,s); use NUMERIC(p,s) for money [OK]

Common Mistakes:

• Using FLOAT(10,2) in PostgreSQL
• Thinking PRIMARY KEY can't be INT
• Assuming DATE can't be NOT NULL

5. You want to create a table users with columns: user_id as a UUID primary key, email as unique and not null, and created_at as timestamp with default current time. Which is the correct CREATE TABLE statement?

hard

A. CREATE TABLE users (user_id SERIAL PRIMARY KEY, email VARCHAR UNIQUE, created_at TIMESTAMP);

B. CREATE TABLE users (user_id UUID, email TEXT NOT NULL, created_at DATE DEFAULT NOW());

C. CREATE TABLE users (user_id UUID PRIMARY KEY, email VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT NOW());

D. CREATE TABLE users (user_id UUID PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Solution

1. Step 1: Check UUID primary key syntax

   UUID is a valid type for unique IDs; PRIMARY KEY constraint is correctly placed.

2. Step 2: Validate email constraints and type

   VARCHAR(255) with UNIQUE and NOT NULL ensures emails are unique and always present.

3. Step 3: Confirm timestamp default value

   DEFAULT CURRENT_TIMESTAMP sets creation time automatically; this is correct syntax.

4. Final Answer:

   CREATE TABLE users (user_id UUID PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); -> Option D

5. Quick Check:

   UUID PK, UNIQUE email, timestamp default = CREATE TABLE users (user_id UUID PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); [OK]

Hint: Use UUID for IDs, UNIQUE + NOT NULL for emails, CURRENT_TIMESTAMP for defaults [OK]

Common Mistakes:

• Using SERIAL instead of UUID for user_id
• Missing UNIQUE on email
• Using NOW() instead of CURRENT_TIMESTAMP in DEFAULT