SQL Basics — CREATE, INSERT, SELECT, UPDATE, DELETE

SQL (Structured Query Language) is how you talk to a relational database. It's the same core language in MySQL, PostgreSQL, Oracle and SQL Server.

1. Create a table (DDL)

CREATE TABLE students (
    roll_no INT PRIMARY KEY,
    name    VARCHAR(50) NOT NULL,
    city    VARCHAR(30),
    cgpa    DECIMAL(3, 1)
);

Common types: INT, DECIMAL(p, s), VARCHAR(n) (text up to n), CHAR(n) (fixed), DATE, BOOLEAN.

2. Insert rows (DML)

INSERT INTO students (roll_no, name, city, cgpa) VALUES (101, 'Asha', 'Jaipur', 8.9);
INSERT INTO students VALUES (102, 'Ravi', 'Pune', 7.4), (103, 'Neha', 'Jaipur', 8.1), (104, 'Om', 'Delhi', 6.8);
SELECT * FROM students;

roll_no | name | city | cgpa
101 | Asha | Jaipur | 8.9
102 | Ravi | Pune | 7.4
103 | Neha | Jaipur | 8.1
104 | Om | Delhi | 6.8

Text goes in single quotes. SELECT * means "all columns" — fine for exploring, but name the columns in real code.

3. Read with SELECT and WHERE (DQL)

SELECT name, cgpa FROM students WHERE city = 'Jaipur' AND cgpa > 8;

name | cgpa
Asha | 8.9
Neha | 8.1

Operators in WHERE: =, <> or !=, >, <, >=, <=, AND, OR, NOT.

4. Update rows

UPDATE students SET cgpa = 7.6 WHERE roll_no = 102;
SELECT name, cgpa FROM students WHERE roll_no = 102;

name | cgpa
Ravi | 7.6

⚠️ Always write the WHERE first. UPDATE students SET cgpa = 7.6; without WHERE changes every row.

5. Delete rows

DELETE FROM students WHERE city = 'Delhi';
SELECT COUNT(*) AS remaining FROM students;

remaining
3

6. Change a table (ALTER)

ALTER TABLE students ADD COLUMN email VARCHAR(80);
SELECT roll_no, name, email FROM students WHERE roll_no = 101;

roll_no | name | email
101 | Asha | NULL

New columns start as NULL (no value) for existing rows.

7. DELETE vs TRUNCATE vs DROP (asked often)

Common mistakes

• UPDATE or DELETE without WHERE.
• Double quotes for text values (in standard SQL, double quotes are for column names).
• Comparing with NULL using = (use IS NULL — next lesson).

Quick recap

• DDL: CREATE, ALTER, DROP, TRUNCATE. DML: INSERT, UPDATE, DELETE. DQL: SELECT.
• INSERT INTO t (cols) VALUES (…); SELECT cols FROM t WHERE ….
• Always put WHERE on UPDATE and DELETE.
• DELETE (rows) vs TRUNCATE (all rows) vs DROP (table).