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

Source: https://skills101.in/learn/dbms-sql/sql-basics — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

**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.

![SQL commands in four families: DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE), DQL (SELECT) and TCL/DCL (COMMIT, ROLLBACK, GRANT, REVOKE)](https://skills101.in/learn-img/dbms-sql/sql-families.svg)

## 1. Create a table (DDL)

```sql
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)

```sql
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;
```

```text
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)

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

```text
name | cgpa
Asha | 8.9
Neha | 8.1
```

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

## 4. Update rows

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

```text
name | cgpa
Ravi | 7.6
```

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

## 5. Delete rows

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

```text
remaining
3
```

## 6. Change a table (ALTER)

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

```text
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)

| | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Removes | chosen rows (WHERE) | **all** rows | the **whole table** (structure too) |
| Type | DML | DDL | DDL |
| Can roll back? | yes (in a transaction) | usually no | no |
| Speed | slower (row by row) | fast | fast |

## 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).


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/sql-basics