SQL Fundamentals Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: sql for comprehensive documentation.

SQL Statement Categories

SELECT Statement

SELECT [DISTINCT] columns
FROM table
[JOIN other_table ON condition]
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY columns [ASC|DESC]]
[LIMIT n OFFSET m];

Execution Order

1. FROM (and JOINs)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET

INSERT Patterns

-- Single row
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

-- Multiple rows
INSERT INTO users (name, email) VALUES
    ('John', 'john@example.com'),
    ('Jane', 'jane@example.com');

-- Insert from SELECT
INSERT INTO users_backup (name, email)
SELECT name, email FROM users WHERE created_at < '2024-01-01';

-- Insert with RETURNING (PostgreSQL)
INSERT INTO users (name, email) VALUES ('John', 'john@example.com')
RETURNING id, created_at;

UPDATE Patterns

-- Simple update
UPDATE users SET name = 'John Doe' WHERE id = 1;

-- Multiple columns
UPDATE users SET name = 'John', status = 'active' WHERE id = 1;

-- Update with subquery
UPDATE orders SET status = 'shipped'
WHERE user_id IN (SELECT id FROM users WHERE is_premium = true);

-- Update with JOIN (varies by database)
-- PostgreSQL
UPDATE orders o SET status = 'vip'
FROM users u WHERE o.user_id = u.id AND u.is_premium = true;

DELETE Patterns

-- Delete with condition
DELETE FROM users WHERE id = 1;

-- Delete with subquery
DELETE FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE status = 'deleted'
);

-- Soft delete pattern (prefer this)
UPDATE users SET deleted_at = NOW() WHERE id = 1;

JOIN Types

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

-- LEFT JOIN (include users without orders)
SELECT u.name, COALESCE(o.total, 0) as total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Self JOIN (hierarchical data)
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Aggregations

-- Basic aggregates
SELECT
    COUNT(*) as total,
    COUNT(DISTINCT user_id) as unique_users,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount,
    MIN(amount) as min_amount,
    MAX(amount) as max_amount
FROM orders;

-- GROUP BY
SELECT user_id, COUNT(*) as order_count, SUM(amount) as total
FROM orders
GROUP BY user_id;

-- HAVING (filter after GROUP BY)
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

DDL - Table Definition

CREATE TABLE users (
    id SERIAL PRIMARY KEY,              -- PostgreSQL auto-increment
    -- id INT AUTO_INCREMENT PRIMARY KEY -- MySQL
    -- id INT IDENTITY(1,1) PRIMARY KEY  -- SQL Server
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_status CHECK (status IN ('active', 'inactive', 'deleted'))
);

-- Foreign key
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,

    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

ALTER TABLE

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Modify column
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);

-- Add constraint
ALTER TABLE users ADD CONSTRAINT uq_phone UNIQUE (phone);

-- Drop constraint
ALTER TABLE users DROP CONSTRAINT uq_phone;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Rename table
ALTER TABLE users RENAME TO customers;

Indexes

-- Create index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Drop index
DROP INDEX idx_users_email;

Transactions

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

-- With savepoint
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    SAVEPOINT after_debit;

    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    -- Something went wrong with the credit
    ROLLBACK TO after_debit;

    -- Try different approach
    UPDATE accounts SET balance = balance + 100 WHERE id = 3;
COMMIT;

-- Rollback on error
BEGIN;
    -- operations...
ROLLBACK;  -- cancel all changes

Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    -- operations
COMMIT;

NULL Handling

-- Check for NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- COALESCE (first non-null)
SELECT COALESCE(phone, 'N/A') as phone FROM users;

-- NULLIF (return NULL if equal)
SELECT NULLIF(status, 'unknown') FROM users;

-- NULL in aggregates (ignored except COUNT(*))
SELECT AVG(score) FROM tests;  -- NULLs ignored
SELECT COUNT(*) FROM tests;    -- counts all rows
SELECT COUNT(score) FROM tests; -- counts non-NULL only

Subqueries

-- Scalar subquery
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

-- IN subquery
SELECT * FROM users WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE total > 100
);

-- EXISTS subquery (often faster than IN)
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100
);

-- Correlated subquery
SELECT * FROM orders o1 WHERE total > (
    SELECT AVG(total) FROM orders o2 WHERE o2.user_id = o1.user_id
);

Best Practices

DO

• Use parameterized queries (prevent SQL injection)
• Add indexes on WHERE/JOIN columns
• Use appropriate data types
• Define foreign keys for data integrity
• Use transactions for multiple related operations
• Use EXPLAIN to analyze query performance

DON'T

• Use SELECT * in production
• UPDATE/DELETE without WHERE clause
• Store comma-separated values in columns
• Use reserved words as identifiers
• Ignore NULL handling

When NOT to Use This Skill

• Advanced SQL (CTEs, window functions, recursive queries) - Use sql-advanced skill
• PostgreSQL specifics (arrays, JSONB, extensions) - Use postgresql skill
• MySQL specifics (engine selection, stored procedures) - Use mysql skill
• Document databases - Use mongodb for document-oriented data
• Caching - Use redis for caching needs

Anti-Patterns

Quick Troubleshooting

Reference Documentation

• DML Patterns
• DDL Patterns
• JOIN Patterns
• Aggregations & Window Functions
• Transactions