INSERT with specific columns in SQL - Mini Project: Build & Apply

Insert Data into Specific Columns in SQL

📖 Scenario: You are managing a small bookstore database. You need to add new books to the books table. Sometimes you only have partial information, so you want to insert data into specific columns without filling all columns.

🎯 Goal: Learn how to insert new rows into a SQL table by specifying only certain columns, leaving others with default or NULL values.

📋 What You'll Learn

Create a table called books with columns id, title, author, and year_published

Insert a new book specifying only the title and author columns

Insert another book specifying title, author, and year_published

Insert a book specifying all columns including id

Use the INSERT INTO statement with specific columns listed

💡 Why This Matters

🌍 Real World

In real databases, you often insert data when you only have some information available. Knowing how to insert into specific columns helps keep data accurate and flexible.

💼 Career

Database administrators and developers frequently insert data into tables. Understanding how to specify columns in INSERT statements is a fundamental skill for managing data.

1

Create the books table

Write a SQL statement to create a table called books with these columns: id as an integer primary key, title as text, author as text, and year_published as an integer.

SQL

-- Your code here to create the books table

Hint

Use CREATE TABLE books and define each column with its type. Make id the primary key.

2

Insert a book with only title and author

Write a SQL INSERT INTO statement to add a new book with title 'The Great Gatsby' and author 'F. Scott Fitzgerald'. Specify only the title and author columns in the insert statement.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  year_published INTEGER
);
-- Your code here to insert the first book with title and author only

Hint

Use INSERT INTO books (title, author) and provide values for those columns only.

3

Insert a book with title, author, and year_published

Write a SQL INSERT INTO statement to add a new book with title '1984', author 'George Orwell', and year_published 1949. Specify these three columns in the insert statement.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  year_published INTEGER
);

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald');
-- Your code here to insert the second book with title, author, and year_published

Hint

Specify all three columns in the INSERT INTO statement and provide matching values.

4

Insert a book specifying all columns including id

Write a SQL INSERT INTO statement to add a new book with id 3, title 'To Kill a Mockingbird', author 'Harper Lee', and year_published 1960. Specify all columns in the insert statement.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  year_published INTEGER
);

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald');

INSERT INTO books (title, author, year_published) VALUES ('1984', 'George Orwell', 1949);
-- Your code here to insert the third book with all columns including id

Hint

List all columns in the INSERT INTO statement and provide values for each.

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