Lesson 5: Data Manipulation with SQL

In this lesson, we will explore how to manipulate data within a SQL database using the INSERT, UPDATE, and DELETE commands. These commands are essential for modifying records in your database tables.

1. INSERT Command

The INSERT command is used to add new records to a table. You can insert a single record or multiple records at once.

Syntax

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

Example

Let's say we have a table named employees:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50)
);

To insert a new employee:

INSERT INTO employees (id, name, position)
VALUES (1, 'John Doe', 'Software Engineer');

Inserting Multiple Records

You can also insert multiple records in a single query:

INSERT INTO employees (id, name, position)
VALUES 
    (2, 'Jane Smith', 'Project Manager'),
    (3, 'Emily Johnson', 'Data Analyst');

2. UPDATE Command

The UPDATE command is used to modify existing records in a table. You must specify which records to update using the WHERE clause.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example

To update the position of an employee:

UPDATE employees
SET position = 'Senior Software Engineer'
WHERE id = 1;

Best Practices

Always use a WHERE clause with UPDATE to avoid updating all records unintentionally.

3. DELETE Command

The DELETE command is used to remove existing records from a table. Like UPDATE, you should use the WHERE clause to specify which records to delete.

Syntax

DELETE FROM table_name
WHERE condition;

Example

To delete an employee from the employees table:

DELETE FROM employees
WHERE id = 3;

Common Mistakes

If you omit the WHERE clause in a DELETE statement, all records in the table will be deleted!

Summary

• Use INSERT to add new records to a table.
• Use UPDATE to modify existing records, always including a WHERE clause.
• Use DELETE to remove records, ensuring a WHERE clause is present to avoid accidental deletions.

With these commands, you can effectively manage the data within your SQL databases. Practice these commands to become proficient in data manipulation!

Exercises

1. Insert New Records: Add three new employees to the employees table with different IDs, names, and positions.

2. Update Existing Records: Change the position of the employee with id = 2 to 'Senior Project Manager'.

3. Delete a Record: Remove the employee with id = 1 from the employees table.

• The INSERT command adds new records to a table.
• The UPDATE command modifies existing records; always use a WHERE clause.
• The DELETE command removes records; ensure a WHERE clause is included to prevent deleting all records.
• Practice these commands to master data manipulation in SQL.