INSERT with specific columns in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL INSERT statement do when specifying specific columns?

It adds new rows to a table by inserting values only into the specified columns, leaving other columns to their default values or NULL.

beginner

Write the basic syntax of an INSERT statement with specific columns.

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

beginner

Why is it useful to specify columns in an INSERT statement?

It allows you to insert data only into certain columns, which is helpful if you don't have values for all columns or want to use default values for some.

intermediate

What happens if you omit a column in the INSERT statement and it has no default value?

The database will raise an error because it expects a value for that column unless it allows NULL values.

beginner

Example: Insert a new user with only 'name' and 'email' columns into a 'users' table.

INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]');

What is the correct way to insert data into only 'name' and 'age' columns of a 'people' table?

A INSERT INTO people (name) VALUES ('John', 30);

B INSERT INTO people VALUES ('John', 30);

C INSERT people (name, age) VALUES ('John', 30);

D INSERT INTO people (name, age) VALUES ('John', 30);

Option D correctly specifies the columns and values. Option B omits columns, which may cause errors if the table has more columns. Option A has mismatched columns and values. Option C has wrong syntax.

If a column is not listed in the INSERT statement, what value does it get?

A Random value

B The last inserted value

C NULL or default value if defined

D It causes the database to crash

Columns not specified get NULL or their default value if the table defines one.

Which keyword is used to specify the columns in an INSERT statement?

A INTO

B VALUES

C SELECT

D None, columns are not specified

The INTO keyword is followed by the table name and the list of columns in parentheses.

What happens if you try to insert a row without specifying a NOT NULL column and no default value?

A The database ignores the column

B The insert fails with an error

C The database inserts a zero

D The column is set to NULL

NOT NULL columns without default values must be given a value; otherwise, the insert fails.

Which of these is a valid INSERT statement with specific columns?

A INSERT INTO orders (order_id, customer) VALUES (101, 'Bob');

B INSERT orders (order_id, customer) VALUES (101, 'Bob');

C INSERT INTO orders VALUES (101, 'Bob');

D INSERT INTO orders (order_id customer) VALUES (101, 'Bob');

Option A is correct syntax. Option B misses INTO. Option C omits columns but may be valid only if all columns are provided. Option D misses a comma between columns.

Explain how to use the INSERT statement to add data to specific columns in a table.

Think about how you tell the database which columns you want to fill.

What are the consequences of not specifying all columns in an INSERT statement?

Consider what happens to columns you don't mention.

Practice

1. What does the SQL statement INSERT INTO employees (name, age) VALUES ('Alice', 30); do?

easy

A. Deletes rows where name is 'Alice' and age is 30.

B. Inserts a new row filling all columns with 'Alice' and 30.

C. Updates existing rows setting name to 'Alice' and age to 30.

D. Inserts a new row with only the name and age columns filled, others get default or NULL.

Solution

1. Step 1: Understand the INSERT with specific columns

   The statement specifies only the columns 'name' and 'age' to insert values into.

2. Step 2: Effect on other columns

   Columns not listed get their default values or NULL if allowed.

3. Final Answer:

   Inserts a new row with only the name and age columns filled, others get default or NULL. -> Option D

4. Quick Check:

   INSERT with columns fills only those columns [OK]

Hint: List columns to insert only those, others get defaults [OK]

Common Mistakes:

• Thinking all columns get values from VALUES list
• Confusing INSERT with UPDATE
• Assuming unlisted columns get random data

2. Which of the following is the correct syntax to insert only the columns id and email into a table users?

easy

A. INSERT INTO users (id, email) VALUES (1, '[e-mail]');

B. INSERT INTO users SET id=1, email='[e-mail]';

C. INSERT users (id, email) VALUES (1, '[e-mail]');

D. INSERT INTO users VALUES (1, '[e-mail]');

Solution

1. Step 1: Identify correct INSERT syntax with specific columns

   The correct syntax requires specifying the table, columns in parentheses, then VALUES with matching values.

2. Step 2: Check each option

   INSERT INTO users (id, email) VALUES (1, '[e-mail]'); matches the correct syntax: INSERT INTO table (columns) VALUES (values).

3. Final Answer:

   INSERT INTO users (id, email) VALUES (1, '[e-mail]'); -> Option A

4. Quick Check:

   INSERT INTO table (cols) VALUES (vals) is correct [OK]

Hint: Use INSERT INTO table (cols) VALUES (vals) format [OK]

Common Mistakes:

• Omitting INTO keyword
• Using SET instead of VALUES
• Not specifying columns when needed

3. Given the table products(id INT, name VARCHAR, price DECIMAL DEFAULT 0), what will be the result of this query?
INSERT INTO products (id, name) VALUES (101, 'Pen');
Then selecting all rows?

medium

A. [{id: 101, name: 'Pen', price: NULL}]

B. [{id: 101, name: 'Pen', price: 0}]

C. [{id: 101, name: 'Pen'}]

D. Syntax error

Solution

1. Step 1: Understand default values on missing columns

   The 'price' column has a default value of 0, so if not specified, it gets 0.

2. Step 2: Result of the INSERT and SELECT

   The inserted row will have id=101, name='Pen', and price=0 from default.

3. Final Answer:

   [{id: 101, name: 'Pen', price: 0}] -> Option B

4. Quick Check:

   Default values fill missing columns [OK]

Hint: Unlisted columns get default values if defined [OK]

Common Mistakes:

• Assuming missing columns become NULL despite default
• Expecting syntax error without all columns
• Ignoring default values

4. Identify the error in this SQL statement:
INSERT INTO orders (order_id, customer) VALUES (123);

medium

A. Missing value for 'customer' column

B. Table name is incorrect

C. VALUES keyword is missing

D. Extra parentheses around columns

Solution

1. Step 1: Check number of columns vs values

   The statement lists two columns but provides only one value.

2. Step 2: Identify mismatch error

   SQL requires the number of values to match the number of columns exactly.

3. Final Answer:

   Missing value for 'customer' column -> Option A

4. Quick Check:

   Columns and values count must match [OK]

Hint: Count columns and values to match [OK]

Common Mistakes:

• Providing fewer values than columns
• Assuming missing values default to NULL always
• Ignoring syntax error messages

5. You have a table students(id INT PRIMARY KEY, name VARCHAR, grade CHAR DEFAULT 'F'). You want to insert a new student with id=5 and name='John', but leave grade as default. Which query is correct?

hard

A. INSERT INTO students (id, name, grade) VALUES (5, 'John', 'F');

B. INSERT INTO students VALUES (5, 'John');

C. INSERT INTO students (id, name) VALUES (5, 'John');

D. INSERT INTO students (name, grade) VALUES ('John', 'F');

Solution

1. Step 1: Understand default column behavior

   Omitting 'grade' column in INSERT lets it use its default 'F'.

2. Step 2: Check each option

   INSERT INTO students (id, name) VALUES (5, 'John'); omits grade, using default. INSERT INTO students (id, name, grade) VALUES (5, 'John', 'F'); sets it explicitly. INSERT INTO students VALUES (5, 'John'); has value count mismatch. INSERT INTO students (name, grade) VALUES ('John', 'F'); misses id primary key.

3. Final Answer:

   INSERT INTO students (id, name) VALUES (5, 'John'); -> Option C

4. Quick Check:

   Omit columns to use default values [OK]

Hint: Omit columns to use their default values [OK]

Common Mistakes:

• Providing all columns when default is desired
• Omitting primary key column causing errors
• Assuming VALUES must match all columns always