SQL 2 : CRUD Operations (Create, Read, Insert)

D

Qubits of DPK

April 11, 2026

Core DBMS

1. Recap of SQL 1

Before writing queries, it helps to keep the foundational concepts sharp. A database is an organized collection of data managed by a DBMS (Database Management System). Data lives inside tables, structured as rows (individual records) and columns (attributes of those records). Every table has a Primary Key that uniquely identifies each row, and tables relate to each other through Foreign Keys.

2. What is SQL?

SQL (Structured Query Language) is the standard language for communicating with relational databases. With SQL you can create data, read it, update it, and delete it — the four operations collectively known as CRUD.

3. What is CRUD?

CRUD represents the four fundamental operations that every database-backed application performs. There is no backend system in existence that does not use all four.
Every feature you use in a real application maps to one of these. Instagram creating a post is an INSERT. Amazon showing you a product page is a SELECT. Uber updating your ride status is an UPDATE. Netflix removing a title from your watch history is a DELETE.

4. Creating a Database and Using It

Before storing any data, you need a database to hold your tables. You create one with CREATE DATABASE, then tell MySQL which one you want to work in with USE.
sql
QUBITS OF DPK
1CREATE DATABASE company_db;
2USE company_db;

5. CREATE TABLE

A table definition is essentially a blueprint — it describes what columns exist, what type of data each column holds, and any rules that data must follow. Nothing is stored yet; you are just defining the structure.
sql
QUBITS OF DPK
1CREATE TABLE employees (
2    employee_id INT PRIMARY KEY,
3    name        VARCHAR(100),
4    email       VARCHAR(100),
5    salary      INT
6);
Each column declaration follows the pattern column_name data_type constraints. The PRIMARY KEY constraint on employee_id tells MySQL that this column must be unique and never NULL — it is the definitive identifier for every row in this table.

6. Data Types in MySQL

Choosing the right data type is not just about correctness — it directly affects storage efficiency and query performance. Here are the types you will use most often:
The DECIMAL type deserves special attention. For financial values, never use FLOAT or DOUBLE — they use binary floating-point arithmetic and can introduce small rounding errors. DECIMAL(10, 2) stores up to 10 digits total with exactly 2 decimal places, which is precisely what you need for currency.

7. AUTO_INCREMENT

In practice, you almost never want to manually assign IDs. AUTO_INCREMENT tells MySQL to automatically generate a unique, incrementing integer for each new row — starting at 1 and going up from there.
sql
QUBITS OF DPK
1CREATE TABLE users (
2    id   INT AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(100)
4);
5-- First insert gets id=1, second gets id=2, and so on
This is the standard pattern for surrogate primary keys in production systems. You insert a row without specifying the ID, and MySQL handles it.

8. Primary Keys

A Primary Key is the column (or combination of columns) that uniquely identifies every row in a table. Three rules always apply: the value must be unique across all rows, it can never be NULL, and there can only be one Primary Key per table.
sql
QUBITS OF DPK
1employee_id INT PRIMARY KEY
Think of it as the row's permanent address in the table. Everything else about a row can potentially change — a name, an email, a salary — but the Primary Key stays fixed.

9. Foreign Keys

A Foreign Key is how relational databases enforce relationships between tables. It says: "the value in this column must correspond to an existing value in that other table's Primary Key." This prevents orphaned records — for example, an order that references a customer who doesn't exist.
sql
QUBITS OF DPK
1CREATE TABLE orders (
2    order_id    INT PRIMARY KEY,
3    customer_id INT,
4    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
5);
With this constraint in place, MySQL will reject any INSERT into orders that provides a customer_id with no matching row in the customers table. The database itself enforces data integrity, not your application code.

10. INSERT — Adding Data

INSERT adds one or more rows into a table. You specify the target columns and the corresponding values in order.
sql
QUBITS OF DPK
1-- Single row
2INSERT INTO employees (employee_id, name, email, salary)
3VALUES (101, 'John', 'john@gmail.com', 60000);
4
5-- Multiple rows in one statement (more efficient than separate INSERTs)
6INSERT INTO employees (employee_id, name, salary)
7VALUES
8    (102, 'Alice', 70000),
9    (103, 'Bob',   65000);
Inserting multiple rows in a single statement is significantly faster than running separate INSERT statements in a loop — the database only needs to process the overhead once.

11. SELECT — Reading Data

SELECT retrieves data from one or more tables. SELECT * returns every column, which is convenient for exploration but should be avoided in production — always select only the columns you actually need.
sql
QUBITS OF DPK
1-- Return every column (use sparingly)
2SELECT * FROM employees;
3
4-- Return specific columns only (preferred in production)
5SELECT name, salary FROM employees;
6
7-- Filter rows with WHERE
8SELECT * FROM employees WHERE salary > 60000;
9
10-- Sort results with ORDER BY
11SELECT * FROM employees ORDER BY salary DESC;
12
13-- Limit how many rows are returned
14SELECT * FROM employees LIMIT 5;
LIMIT is especially important in production. APIs that return lists of data — product listings, search results, news feeds — always paginate using LIMIT combined with OFFSET. Without it, a single SELECT could return millions of rows and crash your application.

12. UPDATE — Modifying Data

UPDATE changes the values in existing rows. The WHERE clause is not optional in practice — omitting it applies the change to every row in the table, which is almost always a disaster.
sql
QUBITS OF DPK
1-- Safe: targets exactly one employee
2UPDATE employees
3SET salary = 70000
4WHERE employee_id = 101;
5
6-- Dangerous: updates every employee's salary to 70000
7UPDATE employees
8SET salary = 70000;
This is one of the most common causes of production incidents. Many teams configure MySQL's safe update mode specifically to block UPDATE and DELETE statements that lack a WHERE clause.

13. DELETE — Removing Data

DELETE removes rows from a table. Like UPDATE, the WHERE clause is critical — without it, you wipe the entire table.
sql
QUBITS OF DPK
1-- Remove one specific employee
2DELETE FROM employees WHERE employee_id = 101;
3
4-- Removes every row in the table — be certain this is what you want
5DELETE FROM employees;
DELETE vs TRUNCATE vs DROP are three distinct operations that are frequently confused in interviews:
The mental model: DELETE is surgical, TRUNCATE is a factory reset, and DROP is demolition.

14. Real Backend CRUD Example

Here is how all four operations come together in a real user account flow:
sql
QUBITS OF DPK
1-- User signs up → INSERT
2INSERT INTO users (name, email, password)
3VALUES ('Deepak', 'deepak@gmail.com', 'hashed_password');
4
5-- User logs in → SELECT to verify credentials
6SELECT * FROM users
7WHERE email = 'deepak@gmail.com';
8
9-- User updates their profile name → UPDATE
10UPDATE users
11SET name = 'Deepak Kumar'
12WHERE id = 1;
13
14-- User deletes their account → DELETE
15DELETE FROM users
16WHERE id = 1;

15. The Sakila Database

Throughout SQL practice sessions, the Sakila database is a commonly used learning resource. It models a DVD rental store system and contains realistic, interconnected tables that are perfect for practicing joins, aggregations, and complex queries.

16. Interview Questions

What is CRUD? CRUD stands for Create, Read, Update, Delete — the four fundamental operations performed on any database. They map to INSERT, SELECT, UPDATE, and DELETE in SQL respectively.
Why is WHERE critical in UPDATE and DELETE? Without a WHERE clause, the operation applies to every single row in the table. Running UPDATE users SET password = '123' would overwrite every user's password — a catastrophic and irreversible mistake in production.
What is the difference between DELETE and TRUNCATE? DELETE removes rows one at a time, logs each deletion, and can be rolled back inside a transaction. TRUNCATE removes all rows by deallocating the data pages in a single operation — it is much faster for large tables, but cannot be rolled back in most databases and resets the AUTO_INCREMENT counter.

17. Quick Revision

sql
QUBITS OF DPK
1CREATE DATABASE   → create a new database
2CREATE TABLE      → define a table's structure and column types
3INSERT            → add new rows of data
4SELECT            → read and retrieve data
5UPDATE + WHERE    → modify specific rows (always use WHERE!)
6DELETE + WHERE    → remove specific rows (always use WHERE!)
7TRUNCATE          → fast wipe of all rows, table structure stays
8DROP              → remove the table completely
9
10Primary Key   → unique, non-null identifier for each row
11Foreign Key   → enforces referential integrity between tables
12AUTO_INCREMENT → let MySQL generate IDs automatically
13DECIMAL       → use for money, never FLOAT or DOUBLE