Inserting Data into Tables

Imagine you have a big notebook where you write down details about your favorite superheroes. Each page represents a database table, and every row is a new superhero you add to your list. In SQL, we use the INSERT statement when we want to add new information to a table.

Let’s explore how we can insert new data into a table using SQL! 🚀

Understanding the INSERT Statement

The INSERT statement helps us add new rows to a table. The basic structure of an INSERT statement looks like this:

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

Breaking It Down:

1. INSERT INTO → This tells SQL that we want to add new data into a table.
2. table_name → This is the name of the table where we are inserting data.
3. (column1, column2, column3, …) → These are the column names in the table where we will insert values.
4. VALUES → This keyword tells SQL that we are providing values for the columns.
5. (value1, value2, value3, …) → These are the actual values we are adding.

Example: Inserting Data into a Table

Let’s say we have a Student table where we store student details.

Step 1: Creating the Student’s Table

Before inserting data, we first need to create a table.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade CHAR(2)
);

This table has:
✅ StudentID → A unique number for each student.
✅ Name → The student’s name.
✅ Age → The student’s age.
✅ Grade → The grade they are in (e.g., “A+”, “B-“, etc.).

Step 2: Inserting a Single Record

Now, let’s add a student named John who is 10 years old and in Grade “A”.

INSERT INTO Student (StudentID, Name, Age, Grade)
VALUES (1, 'John', 10, 'A');

🔹 This adds a student named John to the Student table.

Step 3: Inserting Multiple Records

We can also add multiple students at once!

INSERT INTO Student (StudentID, Name, Age, Grade)
VALUES 
    (2, 'Emma', 11, 'A+'),
    (3, 'Liam', 12, 'B'),
    (4, 'Sophia', 10, 'A');

🔹 This inserts Emma, Liam, and Sophia into the table.

Inserting Data Without Specifying Column Names

If you are inserting data into all columns in the correct order, you can skip the column names:

INSERT INTO Student 
VALUES (5, 'Olivia', 11, 'B+');

⚠️ Warning: Always make sure the values are in the same order as the columns in the table.

Handling NULL Values (Missing Data)

What if a student hasn’t received a grade yet? We can insert NULL (which means “empty” or “unknown”).

INSERT INTO Student (StudentID, Name, Age, Grade)
VALUES (6, 'Ethan', 12, NULL);

🔹 This means Ethan’s grade is unknown for now.

Using DEFAULT Values

Sometimes, a column may have a default value. If we don’t specify a value, the default will be used.

Example:

CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    Name VARCHAR(50),
    Subject VARCHAR(50) DEFAULT 'Math'
);

Now, if we insert a teacher without specifying a subject, it will default to “Math”:

INSERT INTO Teachers (TeacherID, Name)
VALUES (1, 'Mr. Smith');

🔹 “Mr. Smith” will teach Math by default.

Practice Questions 🏆

Try these exercises on your own!

Q1: Insert a New Student

Write an SQL query to insert a student named Noah, who is 13 years old and in grade A+.

👉 Answer:

INSERT INTO Student (StudentID, Name, Age, Grade)
VALUES (7, 'Noah', 13, 'A+');

Q2: Insert a Student with a Missing Grade

Insert a student named Ava, aged 9, but we don’t know her grade yet.

👉 Answer:

INSERT INTO Students (StudentID, Name, Age, Grade)
VALUES (8, 'Ava', 9, NULL);

Q3: Insert Multiple Students

Write an SQL query to insert these students:

👉 Answer:

INSERT INTO Students (StudentID, Name, Age, Grade)
VALUES 
    (9, 'Mia', 12, 'A'),
    (10, 'Jacob', 11, 'B+'),
    (11, 'Isabella', 10, 'A-');

Interview Questions & Answers 🎤

Here are some common SQL interview questions related to inserting data.

Q1: What happens if we don’t provide a value for a column that has NO DEFAULT?

👉 If a column doesn’t allow NULL and has no default value, the query will fail.

Example:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT
);

If we run:

sqlCopyEditINSERT INTO Users (UserID, Age) VALUES (1, 25);

🚨 Error! The name is required but missing.

Q2: What’s the difference between INSERT INTO and INSERT IGNORE?

👉 INSERT INTO fails if a duplicate primary key is inserted.
👉 INSERT IGNORE skips duplicate primary keys without an error.

Example:

INSERT IGNORE INTO Students (StudentID, Name, Age, Grade)
VALUES (1, 'John', 10, 'A');

🔹 If StudentID 1 already exists, this query will do nothing instead of failing.

Q3: How do you insert data from one table into another?

👉 Use INSERT INTO ... SELECT.

Example:

INSERT INTO Students_Backup (StudentID, Name, Age, Grade)
SELECT StudentID, Name, Age, Grade FROM Students;

🔹 This copies all students into a backup table.

Final Thoughts 🎯

Now you know how to insert data into SQL tables using the INSERT statement! You also learned how to insert multiple records, handle NULL values, and even copy data from one table to another.

💡 Key Takeaways: ✅ Always specify column names to avoid mistakes.
✅ Use NULL when data is missing.
✅ Use DEFAULT to automatically assign values.
✅ INSERT IGNORE helps avoid errors for duplicates.

🎉 Next Step: Try writing INSERT queries in your own SQL database! 🚀