SQL : Inserting Data

Objectives:

• Learn the INSERT INTO statement syntax
• Insert single rows and multiple rows into a table
• Understand and use the INSERT INTO ... SELECT statement

1. Introduction to INSERT INTO Statement (5 minutes)

Syntax Overview:

• The INSERT INTO statement is used to add new rows to a table.

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

Example:

INSERT INTO TestTable (ID, Name, DateOfBirth)
VALUES (1, 'John Doe', '1990-01-01');

2. Inserting a Single Row (10 minutes)

Basic Syntax:

• Insert a single row into a table.

  INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
  VALUES (1, 'John', 'Doe', '2024-01-15');

Exercise:

• Write SQL queries to insert the following data into Students table:

1. (2, 'Jane', 'Smith', '2024-01-16')
2. (3, 'Emily', 'Johnson', '2024-01-17')

3. Inserting Multiple Rows (10 minutes)

Syntax for Multiple Rows:

• Insert multiple rows in a single statement.

  INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
  VALUES 
      (4, 'Alice', 'Brown', '2024-01-18'),
      (5, 'Bob', 'White', '2024-01-19');

Example:

INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
VALUES 
    (6, 'Charlie', 'Davis', '2024-01-20'),
    (7, 'Dana', 'Evans', '2024-01-21');

Exercise:

• Write SQL queries to insert the following data into Students table:

1. (8, 'Frank', 'Miller', '2024-01-22')
2. (9, 'Grace', 'Wilson', '2024-01-23')
3. (10, 'Hannah', 'Moore', '2024-01-24')

4. Using INSERT INTO … SELECT (10 minutes)

Syntax Overview:

• The INSERT INTO ... SELECT statement is used to insert data from one table into another.

  INSERT INTO destination_table (column1, column2, ...)
  SELECT column1, column2, ...
  FROM source_table
  WHERE condition;

Example:

INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
SELECT StudentID, FirstName, LastName, EnrollmentDate
FROM OldStudentsTable
WHERE EnrollmentDate > '2024-01-01';

Exercise:

• Write SQL queries to insert data from OldStudentsTable to Students table where EnrollmentDate is after ‘2024-01-01’.

5. Practical Exercise: Inserting Data (10 minutes)

• Task:
• Continue using the Students table.
• Insert more sample data into Students table:

  INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
  VALUES 
      (11, 'Ian', 'Walker', '2024-01-25'),
      (12, 'Jack', 'Green', '2024-01-26');

• Exercises:

1. Insert the following data into Students table:
   • (13, 'Kelly', 'Lewis', '2024-01-27')
   • (14, 'Liam', 'Young', '2024-01-28')
   • (15, 'Mona', 'Hall', '2024-01-29')
2. Use the INSERT INTO ... SELECT statement to copy rows from OldStudentsTable to Students where EnrollmentDate is before ‘2024-01-20’.

Exercises

1. Inserting Single Rows:

• Exercise: Write SQL queries to insert the following rows into Students table:
  1. (1, 'John', 'Doe', '2024-01-15')
  2. (2, 'Jane', 'Smith', '2024-01-16')

1. Inserting Multiple Rows:

• Exercise: Insert the following data into Students table:
  1. (6, 'Charlie', 'Davis', '2024-01-20')
  2. (7, 'Dana', 'Evans', '2024-01-21')

1. Using INSERT INTO … SELECT:

• Exercise: Write a query to insert data from OldStudentsTable to Students where EnrollmentDate is after ‘2024-01-01’.

1. Sample Insertion Tasks:

• Exercise: Execute the following tasks:
  1. Insert (8, 'Frank', 'Miller', '2024-01-22'), (9, 'Grace', 'Wilson', '2024-01-23'), and (10, 'Hannah', 'Moore', '2024-01-24') into Students.
  2. Insert data from OldStudentsTable to Students where EnrollmentDate is before ‘2024-01-20’.

Homework:

• Practice Questions:
• Write SQL queries to:
  • Insert multiple rows into Students table.
  • Copy rows from OldStudentsTable to Students where EnrollmentDate is between ‘2024-01-15’ and ‘2024-01-20’.