$devvkit learn --librarie sql-guide

SQL Guide

[database][sql][data][query]

Universal

Install

# SQL is a language, not a single tool.
# PostgreSQL: brew install postgresql
# MySQL: brew install mysql
# SQLite: pre-installed on macOS/Linux

# Try it: sqlite3 :memory:

SQL is the lingua franca of data. Every developer needs it for analytics, debugging, and building features.

The core operations are SELECT, INSERT, UPDATE, DELETE, JOIN, and aggregates. Performance is the hard part: indexes, query plans, and join strategies separate competent from expert.

Querying

Select all· Fetch all rows.

SELECT * FROM users;

Select columns· Fetch specific columns.

SELECT id, name, email FROM users;

Limit results· Return first N rows.

SELECT * FROM users LIMIT 10;

Filtering & Sorting

WHERE clause· Filter rows.

SELECT * FROM users WHERE age >= 18;

Multiple conditions· Combine with AND/OR.

SELECT * FROM users WHERE age >= 18 AND country = 'US';

Pattern matching· Search string patterns.

SELECT * FROM users WHERE email LIKE '%@example.com';

Sort results· Order by columns.

SELECT * FROM users ORDER BY created_at DESC;

Joins

INNER JOIN· Return matched rows.

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

LEFT JOIN· All left table rows.

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Aggregation

COUNT with GROUP BY· Count per group.

SELECT country, COUNT(*) FROM users GROUP BY country;

Modifying Data

INSERT· Add new rows.

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

UPDATE· Modify existing rows.

UPDATE users SET status = 'active' WHERE id = 1;

DELETE· Remove rows.

DELETE FROM users WHERE id = 1;

Schema Design

Create table· Define a new table.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Performance

EXPLAIN ANALYZE· Show query execution plan.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';