SQL Fundamentals

Beginner Databases
MySQLPostgreSQLSELECTINSERTUPDATEDELETE

Objective

Master the four core SQL DML operations and filter/sort query results.

Tools & Technologies

  • MySQL
  • PostgreSQL
  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Key Commands

SELECT * FROM users WHERE active=1 ORDER BY name;
INSERT INTO users (name,email) VALUES ('Alice','[email protected]');
UPDATE users SET role='admin' WHERE id=1;
DELETE FROM sessions WHERE expires < NOW();

Architecture Overview

flowchart LR DB[(Database)] --> SELECT[SELECT\nRead data] DB --> INSERT[INSERT\nAdd rows] DB --> UPDATE[UPDATE\nModify rows] DB --> DELETE[DELETE\nRemove rows] SELECT --> RESULT[Result set] style DB fill:#1a1a2e,stroke:#00d4ff,color:#e0e0e0 style RESULT fill:#1a1a2e,stroke:#00ff88,color:#e0e0e0

Step-by-Step Process

01
SELECT

Query and filter data.

-- Basic select
SELECT * FROM employees;
SELECT name, salary FROM employees WHERE dept='IT';

-- Sort and limit
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

-- Aggregates
SELECT dept, COUNT(*), AVG(salary) FROM employees GROUP BY dept HAVING COUNT(*) > 5;
02
INSERT, UPDATE, DELETE

Modify data safely.

-- INSERT
INSERT INTO users (name, email, role) VALUES ('Alice', '[email protected]', 'user');

-- UPDATE (always use WHERE!)
UPDATE users SET last_login = NOW() WHERE id = 42;

-- DELETE (always test with SELECT first)
SELECT * FROM sessions WHERE expires < NOW();  -- verify first
DELETE FROM sessions WHERE expires < NOW();
03
Transactions

Wrap multi-step changes in a transaction.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- verify
SELECT id, balance FROM accounts WHERE id IN (1,2);
COMMIT;  -- or ROLLBACK;

Challenges & Solutions

  • DELETE without WHERE deletes ALL rows — test with SELECT first
  • UPDATE without WHERE modifies ALL rows

Key Takeaways

  • Wrap multi-table modifications in transactions for atomicity
  • Use EXPLAIN/EXPLAIN ANALYZE to understand query execution