SQL Fundamentals Core Knowledge

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

SQL Statement Categories

Category	Statements	Purpose
DML	SELECT, INSERT, UPDATE, DELETE, MERGE	Data manipulation
DDL	CREATE, ALTER, DROP, TRUNCATE	Schema definition
DCL	GRANT, REVOKE	Access control
TCL	BEGIN, COMMIT, ROLLBACK, SAVEPOINT	Transaction control

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

Join Type	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All left + matching right (NULL if no match)
RIGHT JOIN	All right + matching left (NULL if no match)
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Cartesian product (all combinations)

-- 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

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SERIALIZABLE	No	No	No

-- 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

Anti-Pattern	Problem	Solution
SELECT * in production	Transfers unnecessary data	Specify only needed columns
No WHERE on UPDATE/DELETE	Unintended changes to all rows	Always add WHERE clause
Missing indexes on JOIN columns	Slow queries, full table scans	Add indexes on foreign keys
String concatenation in SQL	SQL injection vulnerability	Use parameterized queries
Implicit data type conversions	Performance loss, unexpected results	Use explicit CAST
Storing CSV in columns	Violates 1NF, hard to query	Normalize into separate table
Using reserved words as identifiers	Syntax errors, portability issues	Choose different names

Quick Troubleshooting

Problem	Diagnostic	Fix
Syntax errors	Check SQL dialect	Use correct syntax for your database
Slow queries	EXPLAIN or EXPLAIN ANALYZE	Add indexes, rewrite query
Deadlocks	Check transaction logs	Reduce transaction scope, consistent ordering
Foreign key violation	Check referenced table data	Insert parent record first
Duplicate key error	Check UNIQUE constraints	Use UPSERT or handle conflict
NULL comparison fails	Remember NULL != NULL	Use IS NULL, IS NOT NULL

Reference Documentation

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