SQL CRUD Operations

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations every application performs on stored data. In SQL, these map directly to four core statements: INSERT, SELECT, UPDATE, and DELETE. Mastering these four statements covers the majority of day-to-day database work.

Why it matters

Create, read, update and delete are the four things every application does with data: registering, viewing a profile, editing it, closing the account. These four statements are the SQL you will write most, and a forgotten WHERE clause on an update or delete is one of the classic ways to damage a production database.

INSERT — Creating Rows

INSERT INTO table (columns...) VALUES (values...) adds one or more new rows. Listing column names explicitly (rather than relying on column order) makes statements resilient to future schema changes and easier to read.

SELECT — Reading Rows

SELECT columns FROM table WHERE condition retrieves rows. SELECT * retrieves every column, but naming specific columns is generally preferred in real applications since it avoids pulling unnecessary data and breaking if columns are later added.

UPDATE and DELETE — Modifying and Removing Rows

UPDATE table SET column = value WHERE condition modifies existing rows that match the condition. DELETE FROM table WHERE condition removes matching rows entirely. In both statements, the WHERE clause is what limits the effect to specific rows — omitting it applies the change to every row in the table, which is one of the most damaging mistakes possible in SQL.

Examples

INSERT INTO employees (id, name, department, salary)
VALUES (1, 'Rohit Sharma', 'Sales', 55000);

SELECT id, name, salary FROM employees WHERE department = 'Sales';

UPDATE employees SET salary = 60000 WHERE id = 1;

DELETE FROM employees WHERE id = 1;

INSERT adds 1 row. SELECT returns 1 row: (1, 'Rohit Sharma', 55000). UPDATE changes 1 row. DELETE removes 1 row.

INSERT INTO products (id, name, price) VALUES
    (1, 'Notebook', 4.50),
    (2, 'Pen', 1.20),
    (3, 'Stapler', 6.75);

Inserts 3 rows in one statement.

Exercise

Insert three employees: Asha earning 50000, Ravi earning 40000 and Zoya earning 60000. Raise Ravi's salary to 45000. Delete Zoya. Then list the names and salaries of the remaining employees, ordered by name.

Expected output: name | salary, Asha | 50000, Ravi | 45000 (one per line)

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);

-- TODO: insert Asha (1), Ravi (2) and Zoya (3)

-- TODO: set Ravi's salary to 45000

-- TODO: delete Zoya

-- TODO: name and salary of everyone, ordered by name

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);

INSERT INTO employees (id, name, salary) VALUES
  (1, 'Asha', 50000),
  (2, 'Ravi', 40000),
  (3, 'Zoya', 60000);

UPDATE employees SET salary = 45000 WHERE name = 'Ravi';

DELETE FROM employees WHERE name = 'Zoya';

SELECT name, salary FROM employees ORDER BY name;

Quick quiz

Interview questions