DEFAULT values in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INSERT with DEFAULT values?

Consider a table users with columns id INT PRIMARY KEY, name VARCHAR(50) DEFAULT 'Guest', and age INT DEFAULT 18. What will be the result of this query?

INSERT INTO users (id) VALUES (1);
SELECT * FROM users WHERE id = 1;

SQL

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50) DEFAULT 'Guest', age INT DEFAULT 18);
INSERT INTO users (id) VALUES (1);
SELECT * FROM users WHERE id = 1;

A (1, 'Guest', 18)

B (1, NULL, NULL)

C (1, '', 0)

D Error: Missing values for name and age

💡 Hint

If a column has a DEFAULT value and you don't provide a value, the default is used.

When inserting a row without specifying columns that have DEFAULT values, the database fills those columns with their default values automatically.

🧠 Conceptual

intermediate

Which statement about DEFAULT values is true?

Choose the correct statement about DEFAULT values in SQL columns.

A DEFAULT values are used only when explicitly specified in the INSERT statement.

B DEFAULT values are ignored if NULL is inserted explicitly into the column.

C DEFAULT values can only be numeric constants.

D DEFAULT values are applied when the column is omitted in the INSERT statement.

💡 Hint

Think about what happens when you don't mention a column in an INSERT.

If a column is omitted in an INSERT, the database uses its DEFAULT value if defined. Explicit NULL insertion overrides the default.

📝 Syntax

advanced

Which CREATE TABLE statement correctly sets a DEFAULT value for a timestamp column?

You want to create a table events with a created_at column that defaults to the current timestamp. Which option is correct?

A CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT NOW());

B CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

C CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP');

D CREATE TABLE events (id INT PRIMARY KEY, created_at TIMESTAMP DEFAULT current_timestamp());

💡 Hint

Look for the correct syntax to use a function as a default value without parentheses.

The correct syntax uses DEFAULT CURRENT_TIMESTAMP without parentheses or quotes. NOW() is a function call and not allowed as default in standard SQL.

❓ optimization

advanced

How can DEFAULT values improve INSERT performance?

Which of the following explains how using DEFAULT values can optimize database INSERT operations?

A DEFAULT values require explicit values in every INSERT, increasing data size.

B DEFAULT values force the database to compute values on every INSERT, slowing performance.

C DEFAULT values reduce the amount of data sent in INSERT statements by omitting columns with defaults.

D DEFAULT values cause the database to lock the table during INSERTs.

💡 Hint

Think about how omitting columns with defaults affects the size of the INSERT statement.

By omitting columns that have DEFAULT values, INSERT statements are smaller and simpler, which can improve performance.

🔧 Debug

expert

Why does this INSERT fail despite DEFAULT values?

Given this table:

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) DEFAULT 'Unknown', price DECIMAL(10,2) NOT NULL);

Why does this INSERT fail?

INSERT INTO products (id) VALUES (10);

A Because price has no DEFAULT and is NOT NULL, so it must be provided.

B Because DEFAULT values cannot be used with DECIMAL columns.

C Because id is missing in the INSERT statement.

D Because name DEFAULT value is invalid for VARCHAR columns.

💡 Hint

Check which columns require values and which have defaults.

The price column has no DEFAULT and is NOT NULL, so it must be given a value in the INSERT.