SQL

Master relational databases from the command line. Covers SQLite, PostgreSQL, MySQL, and SQL Server with battle-tested patterns for schema design, querying, migrations, and operations.

When to Use

Working with relational databases—designing schemas, writing queries, building migrations, optimizing performance, or managing backups. Applies to SQLite, PostgreSQL, MySQL, and SQL Server.

Quick Reference

Topic	File
Query patterns	`patterns.md`
Schema design	`schemas.md`
Operations	`operations.md`

Core Rules

1. Choose the Right Database

Use Case	Database	Why
Local/embedded	SQLite	Zero setup, single file
General production	PostgreSQL	Best standards, JSONB, extensions
Legacy/hosting	MySQL	Wide hosting support
Enterprise/.NET	SQL Server	Windows integration

2. Always Parameterize Queries

# ❌ NEVER
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")
✅ ALWAYS
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

3. Index Your Filters

Any column in WHERE, JOIN ON, or ORDER BY on large tables needs an index.

4. Use Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

5. Prefer EXISTS Over IN

-- ✅ Faster (stops at first match)
SELECT * FROM orders o WHERE EXISTS (
  SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active
);

Quick Start

SQLite

sqlite3 mydb.sqlite                              # Create/open
sqlite3 mydb.sqlite "SELECT * FROM users;"       # Query
sqlite3 -header -csv mydb.sqlite "SELECT *..." > out.csv
sqlite3 mydb.sqlite "PRAGMA journal_mode=WAL;"   # Better concurrency

PostgreSQL

psql -h localhost -U myuser -d mydb              # Connect
psql -c "SELECT NOW();" mydb                     # Query
psql -f migration.sql mydb                       # Run file
\dt  \d+ users  \di+                             # List tables/indexes

MySQL

mysql -h localhost -u root -p mydb               # Connect
mysql -e "SELECT NOW();" mydb                    # Query

SQL Server

sqlcmd -S localhost -U myuser -d mydb            # Connect
sqlcmd -Q "SELECT GETDATE()"                     # Query
sqlcmd -S localhost -d mydb -E                   # Windows auth

Common Traps

NULL Traps

```
NOT IN (subquery)
```
returns empty if subquery has NULL → use
```
NOT EXISTS
```
```
NULL = NULL
```
is NULL, not true → use
```
IS NULL
```
```
COUNT(column)
```
excludes NULLs,
```
COUNT(*)
```
counts all

Index Killers

Functions on columns →
```
WHERE YEAR(date) = 2024
```
scans full table
Type conversion →
```
WHERE varchar_col = 123
```
skips index
```
LIKE '%term'
```
can't use index → only
```
LIKE 'term%'
```
works
Composite
```
(a, b)
```
won't help filtering only on
```
b
```

Join Traps

LEFT JOIN with WHERE on right table becomes INNER JOIN
Missing JOIN condition = Cartesian product
Multiple LEFT JOINs can multiply rows

EXPLAIN

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5;
-- SQLite
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;

Red flags:

```
Seq Scan
```
on large tables → needs index
```
Rows Removed by Filter
```
high → index doesn't cover filter
Actual vs estimated rows differ → run
```
ANALYZE tablename;
```

Index Strategy

-- Composite index (equality first, range last)
CREATE INDEX idx_orders ON orders(user_id, status);
-- Covering index (avoids table lookup)
CREATE INDEX idx_orders ON orders(user_id) INCLUDE (total);
-- Partial index (smaller, faster)
CREATE INDEX idx_pending ON orders(user_id) WHERE status = 'pending';

Portability

Feature	PostgreSQL	MySQL	SQLite	SQL Server
LIMIT	LIMIT n	LIMIT n	LIMIT n	TOP n
UPSERT	ON CONFLICT	ON DUPLICATE KEY	ON CONFLICT	MERGE
Boolean	true/false	1/0	1/0	1/0
Concat	\|\|	CONCAT()	\|\|	+

Related Skills

Install with

clawhub install <slug>

if user confirms:

```
prisma
```
— Node.js ORM
```
sqlite
```
— SQLite-specific patterns
```
analytics
```
— data analysis queries

Feedback

If useful:
```
clawhub star sql
```
Stay updated:
```
clawhub sync
```

SQL

AI Skill Market Insights

Be Part of the 0+ Developer Community

SQL

When to Use

Quick Reference

Core Rules

1. Choose the Right Database

2. Always Parameterize Queries

✅ ALWAYS

3. Index Your Filters

4. Use Transactions

5. Prefer EXISTS Over IN

Quick Start

SQLite

PostgreSQL

MySQL

SQL Server

Common Traps

NULL Traps

Index Killers

Join Traps

EXPLAIN

Index Strategy

Portability

Related Skills

Feedback

Quick Start

Manual Installation

TEAR & SHARE

Tags

Skills of A-share market data released by ft.tech.

AI Data Analysis

Options Strategy Advisor

Analytics Platform Base

Simul8or Trader

Channels

Learn

Compare

Company