INSERT with DEFAULT values in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of this INSERT statement?

Given the table users(id SERIAL PRIMARY KEY, name TEXT DEFAULT 'Anonymous', age INT DEFAULT 18), what will be the content of the table after running this query?

INSERT INTO users DEFAULT VALUES;

SQL

CREATE TABLE users(id SERIAL PRIMARY KEY, name TEXT DEFAULT 'Anonymous', age INT DEFAULT 18);
INSERT INTO users DEFAULT VALUES;
SELECT * FROM users;

A [{"id": 1, "name": "Anonymous", "age": 18}]

B SyntaxError

C [{"id": 1, "name": null, "age": null}]

D []

💡 Hint

Think about what happens when you insert with DEFAULT VALUES and the table has default values defined.

The DEFAULT VALUES clause inserts a new row using all default values defined in the table. Since name and age have defaults, they are set accordingly. The id is auto-incremented by SERIAL.

📝 Syntax

intermediate

Which INSERT statement is syntactically correct to insert default values into all columns?

Choose the correct SQL statement to insert a new row with all default values into a table products.

A INSERT INTO products DEFAULT VALUES;

B INSERT INTO products VALUES DEFAULT;

C INSERT INTO products VALUES (DEFAULT);

D INSERT INTO products SET DEFAULT VALUES;

💡 Hint

Remember the exact syntax for inserting default values without specifying columns.

The correct syntax to insert a row with all default values is INSERT INTO table DEFAULT VALUES;. Other options are invalid syntax.

❓ query_result

advanced

What happens when you mix DEFAULT and explicit values in INSERT?

Given the table orders(id SERIAL PRIMARY KEY, status TEXT DEFAULT 'pending', quantity INT DEFAULT 1), what will be the result of this query?

INSERT INTO orders(status, quantity) VALUES (DEFAULT, 5);
SELECT * FROM orders;

SQL

CREATE TABLE orders(id SERIAL PRIMARY KEY, status TEXT DEFAULT 'pending', quantity INT DEFAULT 1);
INSERT INTO orders(status, quantity) VALUES (DEFAULT, 5);
SELECT * FROM orders;

A [{"id": 1, "status": null, "quantity": 5}]

B [{"id": 1, "status": "DEFAULT", "quantity": 5}]

C SyntaxError

D [{"id": 1, "status": "pending", "quantity": 5}]

💡 Hint

Consider what DEFAULT means when used as a value in an INSERT statement.

Using DEFAULT as a value tells the database to use the column's default value. Here, status gets 'pending', and quantity is explicitly set to 5.

🔧 Debug

advanced

Why does this INSERT with DEFAULT VALUES fail?

Given the table employees(id INT PRIMARY KEY, name TEXT DEFAULT 'John Doe'), why does this query fail?

INSERT INTO employees DEFAULT VALUES;

A Because 'name' column default is invalid

B Because DEFAULT VALUES is not supported on this table

C Because the column 'id' has no default and is NOT NULL

D Because the table has no columns

💡 Hint

Check which columns require values and if they have defaults.

The 'id' column is NOT NULL and has no default value, so inserting default values without specifying 'id' causes an error.

🧠 Conceptual

expert

What is the effect of using DEFAULT VALUES in an INSERT statement?

Choose the best description of what INSERT INTO table DEFAULT VALUES; does.

A Inserts a new row with all columns set to zero or empty string

B Inserts a new row where all columns get their default values, or NULL if no default is defined

C Inserts a new row with all columns set to NULL, ignoring defaults

D Deletes all rows and inserts a default row

💡 Hint

Think about how default values work in SQL inserts.

The DEFAULT VALUES clause inserts a new row where each column is set to its default value if defined, otherwise NULL if allowed.