INSERT, UPDATE, DELETE

-- Single row
INSERT INTO students (name, email, age, grade)
VALUES ('Gita Poudel', 'gita@example.com', 19, 'B');

-- Multiple rows
INSERT INTO students (name, email, age, grade) VALUES
('Bikash Rai', 'bikash@example.com', 22, 'A'),
('Anita Lama', 'anita@example.com', 20, 'A'),
('Deepak Magar', 'deepak@example.com', 21, 'C');

-- Insert from another table
INSERT INTO archived_students
SELECT * FROM students WHERE grade = 'F';

-- Update specific rows
UPDATE students
SET grade = 'A+'
WHERE name = 'Ram Thapa';

-- Update multiple columns
UPDATE students
SET grade = 'B', email = 'updated@example.com'
WHERE id = 5;

-- Update with conditions from another table
UPDATE students s
SET s.grade = 'C'
WHERE s.id IN (
    SELECT e.student_id
    FROM enrollments e
    WHERE e.score < 40
);

-- ⚠️ Without WHERE, every row is updated!
-- UPDATE students SET grade = 'A';  -- DANGEROUS

-- Delete specific rows
DELETE FROM students WHERE id = 5;

-- Delete with conditions
DELETE FROM students WHERE grade = 'F' AND age < 18;

-- Delete all rows (table stays, data gone)
DELETE FROM students;

-- ⚠️ Without WHERE, every row is deleted!

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- If the second update fails, the first is also undone
COMMIT;   -- save changes permanently
-- or
ROLLBACK; -- undo everything since BEGIN

-- PostgreSQL / SQLite syntax
INSERT INTO students (name, email, age)
VALUES ('Ram', 'ram@example.com', 22)
ON CONFLICT (email)
DO UPDATE SET age = EXCLUDED.age;

-- MySQL syntax
INSERT INTO students (name, email, age)
VALUES ('Ram', 'ram@example.com', 22)
ON DUPLICATE KEY UPDATE age = VALUES(age);