Insert, Update or Delete records

INSERT

  • Purpose: Adds a new record (row) to a table.
  • Syntax:
    INSERT INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...);
  • Rules:
    • Specify the table name and column names to match the values.
    • Values must be in the correct order and data type for the columns.
    • Use DEFAULT or omit columns to automatically set values where applicable.

Example

Add a new student to the "students" table:

INSERT INTO students (student_id, name, grade)
VALUES (104, 'Alice', 90);

Add Multiple Rows

INSERT INTO students (student_id, name, grade)
VALUES (105, 'Bob', 85), (106, 'Charlie', 88);

2. UPDATE

  • Purpose: Modifies data in existing records.
  • Syntax:
    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
WHERE condition;
  • Rules:
    • Use SET to specify which columns to update and their new values.
    • Always use a WHERE clause to target specific rows. Without it, all rows will be updated.

Example

Update the grade of a student with ID 104:

UPDATE students
SET grade = 95
WHERE student_id = 104;

Update Multiple Columns

UPDATE students
SET grade = 92, name = 'Alice Johnson'
WHERE student_id = 104;

3. DELETE

  • Purpose: Removes records from a table.
  • Syntax:
    DELETE FROM table_name
    WHERE condition;
  • Rules:
    • Always use a WHERE clause to avoid deleting all rows in the table.
    • Without a WHERE clause, all rows will be deleted.

Example

Delete a student with ID 105:

DELETE FROM students
WHERE student_id = 105;


Key Rules for GCSE Students
  1. Use Conditions: Always include a WHERE clause in UPDATE and DELETE to avoid unintentional changes or deletions.
  2. Test Before Execution: Use a SELECT query first to see which rows will be affected by your command.
    SELECT * FROM students WHERE condition;
  3. Keep Syntax Correct:
    • Table and column names should match exactly (case sensitivity depends on the database system).
    • Values for INSERT and UPDATE must match the data types of the columns.
  4. Use Safe Practices:
    • Avoid modifying or deleting all rows unless explicitly required.
Example Scenario
Try this
  1. Add a new student (ID 107, name 'Diana', grade 88).
  2. Update Diana's grade to 90.
  3. Delete Diana's record if her grade drops below 50.
The SQL commands would look like this:
INSERT INTO students (student_id, name, grade)
VALUES (107, 'Diana', 88);

UPDATE students
SET grade = 90
WHERE student_id = 107;

DELETE FROM students
WHERE student_id = 107 AND grade < 50;

These examples cover the basic operations of INSERT, UPDATE, and DELETE for GCSE-level understanding.