What is SQL?

SQL (Structured Query Language) is the declarative language for defining, querying, and modifying relational data. MySQL implements ANSI SQL with extensions (LIMIT, ON DUPLICATE KEY UPDATE, SHOW commands).

SQL categories:

Category Commands Purpose
DDL CREATE, ALTER, DROP Schema definition
DML SELECT, INSERT, UPDATE, DELETE Data manipulation
DCL GRANT, REVOKE Access control
TCL START TRANSACTION, COMMIT, ROLLBACK Transaction control

Creating a Database and Table 

  CREATE DATABASE IF NOT EXISTS myapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE myapp;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_email (email),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;

SELECT — Querying Data 

  -- All columns (avoid in production — specify columns)
SELECT * FROM users;

-- Specific columns
SELECT id, name, email FROM users WHERE status = 'active';

-- Pattern matching
SELECT name FROM users WHERE name LIKE 'A%';
SELECT email FROM users WHERE email LIKE '%@example.com';

-- NULL handling
SELECT name FROM users WHERE deleted_at IS NULL;
SELECT COALESCE(nickname, name) AS display_name FROM users;

-- Sorting and pagination
SELECT id, name FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Distinct values
SELECT DISTINCT status FROM users;

Aggregation and GROUP BY 

  SELECT COUNT(*) AS total_users FROM users;
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status;

SELECT user_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
HAVING revenue > 1000
ORDER BY revenue DESC
LIMIT 10;

Rule: columns in SELECT must be in GROUP BY or wrapped in aggregate functions. HAVING filters groups; WHERE filters rows before grouping.

INSERT — Adding Data 

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

INSERT INTO users (name, email) VALUES
    ('Bob', '[email protected]'),
    ('Carol', '[email protected]');

-- Insert from query
INSERT INTO archived_users (id, name, email, archived_at)
SELECT id, name, email, NOW()
FROM users WHERE status = 'inactive';

-- Upsert
INSERT INTO settings (key_name, value)
VALUES ('theme', 'dark')
ON DUPLICATE KEY UPDATE value = VALUES(value), updated_at = NOW();

UPDATE — Modifying Data 

  UPDATE users SET name = 'Alice Smith' WHERE id = 1;

UPDATE users
SET status = 'inactive'
WHERE last_login_at < '2023-01-01';

-- Always verify scope first
SELECT COUNT(*) FROM users WHERE last_login_at < '2023-01-01';
-- Then UPDATE with same WHERE

Critical: UPDATE without WHERE modifies every row. Always run a SELECT with the same condition first.

DELETE — Removing Data 

  DELETE FROM users WHERE id = 3;

DELETE FROM sessions WHERE expires_at < NOW();

-- Prefer soft delete for audit trails
UPDATE users SET deleted_at = NOW() WHERE id = 3;

JOINs Overview 

  SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
Join Type Returns
INNER JOIN Matching rows only
LEFT JOIN All left rows + matches (NULL if none)
RIGHT JOIN All right rows + matches
CROSS JOIN Cartesian product

Useful Clauses Reference

Clause Purpose Example
WHERE Filter rows WHERE status = 'active'
GROUP BY Aggregate groups GROUP BY user_id
HAVING Filter groups HAVING COUNT(*) > 5
ORDER BY Sort ORDER BY created_at DESC
LIMIT Cap rows LIMIT 10 OFFSET 20
DISTINCT Unique values SELECT DISTINCT country

ALTER and DROP 

  ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users ADD INDEX idx_phone (phone);
ALTER TABLE users MODIFY COLUMN name VARCHAR(150) NOT NULL;

DROP TABLE IF EXISTS temp_import;
DROP DATABASE IF EXISTS old_app;

Use migrations tools for production schema changes — never ad-hoc ALTER without backup and review.

EXPLAIN Preview 

  EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

If type is ALL and key is NULL, the query does a full table scan — add an index on email.

Best Practices

  1. Select only needed columns — reduces I/O and network transfer
  2. Use parameterized queries in application code — prevents SQL injection
  3. Index columns in WHERE, JOIN, and ORDER BY clauses
  4. Use transactions for multi-step writes
  5. Prefer EXISTS over IN for large subquery result sets

Common Mistakes

Mistake Problem
SELECT * in production Wastes bandwidth; breaks when schema changes
OR on different columns Often prevents index use — use UNION instead
Implicit type conversion WHERE id = '123' may prevent index use
Comparing NULL with = Always use IS NULL / IS NOT NULL

Troubleshooting

Query returns unexpected empty result:

  -- Check for trailing spaces, case sensitivity (utf8mb4_unicode_ci is case-insensitive)
SELECT * FROM users WHERE email = TRIM(' [email protected] ');

-- Check collation
SHOW FULL COLUMNS FROM users;

“Lock wait timeout exceeded” — another transaction holds a row lock. Find blockers:

  SELECT * FROM information_schema.innodb_trx;

Production Scenario

An API endpoint lists active users paginated by created_at. The query uses a composite index (status, created_at) and keyset pagination (WHERE created_at < ? ORDER BY created_at DESC LIMIT 20) instead of OFFSET for consistent performance at millions of rows.

SQL fluency is the foundation of every MySQL skill in this track — indexing, transactions, and replication all assume solid query fundamentals.