Learning of SQL Day 6

 

Day 6: INSERT Statement

Introduction:

• The INSERT statement is used to add new rows of data to a table. This is fundamental for populating your database with meaningful information.

Key Concepts:

• Inserting Single Rows: Adding one row of data at a time.

• Inserting Multiple Rows: Adding multiple rows of data in a single statement.

• Inserting Data with Default Values: Using default values for certain columns.

SQL Commands and Examples:

1. Inserting a Single Row:

   • The basic syntax for inserting a single row into a table.

sql

-- Insert a single row of data into the Courses table
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'Introduction to SQL', 3);

2. Inserting Multiple Rows:

   • Adding multiple rows in a single INSERT statement can be more efficient.

sql

-- Insert multiple rows of data into the Courses table
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES 
(102, 'Advanced SQL', 4),
(103, 'Database Management Systems', 3);

3. Inserting Data with Default Values:

   • If a column has a default value, you can omit it from the INSERT statement.

sql

-- Assuming Credits column has a default value of 3
INSERT INTO Courses (CourseID, CourseName)
VALUES (104, 'Data Structures');

Practice Exercise:

1. Create a table named Students with the following columns:

   • StudentID (INT, Primary Key)

   • FirstName (VARCHAR)

   • LastName (VARCHAR)

   • Age (INT)

2. Insert a single student into the Students table.

3. Insert multiple students into the Students table.

4. Assume Age has a default value of 20 and insert a student without specifying the age.

sql

-- Create the Students table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Age INT DEFAULT 20
);

-- Insert a single student
INSERT INTO Students (StudentID, FirstName, LastName, Age)
VALUES (1, 'Alice', 'Johnson', 22);

-- Insert multiple students
INSERT INTO Students (StudentID, FirstName, LastName, Age)
VALUES 
(2, 'Bob', 'Smith', 21),
(3, 'Charlie', 'Brown', 23);

-- Insert a student with default age
INSERT INTO Students (StudentID, FirstName, LastName)
VALUES (4, 'Daisy', 'Miller');

Inserting data effectively is vital for database management.