Introduction to SQL

┌────┬──────────┬──────────┬──────────┬──────┐
│ id │ name     │ email    │ city     │ age  │
├────┼──────────┼──────────┼──────────┼──────┤
│ 1  │ Alice    │ a@m.com  │ New York │ 28   │
│ 2  │ Bob      │ b@m.com  │ London   │ 35   │
│ 3  │ Charlie  │ c@m.com  │ Tokyo    │ 22   │
│ 4  │ Diana    │ d@m.com  │ New York │ 31   │
└────┴──────────┴──────────┴──────────┴──────┘

-- Common data types
CREATE TABLE examples (
    -- Text
    short_text  VARCHAR(100),     -- Variable-length string
    long_text   TEXT,              -- Unlimited text
    fixed_text  CHAR(10),         -- Fixed-length string
    
    -- Numbers
    whole_num   INTEGER,           -- -2B to 2B
    big_num     BIGINT,           -- Very large numbers
    decimal_num DECIMAL(10, 2),   -- Exact decimal (10 digits, 2 after point)
    float_num   REAL,             -- Approximate decimal
    double_num  DOUBLE PRECISION, -- Higher precision float
    
    -- Date/Time
    date_val    DATE,             -- 2024-01-15
    time_val    TIME,             -- 14:30:00
    datetime    TIMESTAMP,        -- 2024-01-15 14:30:00
    
    -- Boolean
    flag        BOOLEAN,          -- TRUE or FALSE
    
    -- Binary
    data        BYTEA,            -- Binary data
    json_data   JSONB             -- JSON (PostgreSQL)
);

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Aliases
SELECT name AS full_name, email AS contact FROM users;

-- Distinct values
SELECT DISTINCT city FROM users;

-- Limit results
SELECT * FROM users LIMIT 10;

-- Offset (pagination)
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Skip first 20, get next 10

-- Comparison operators
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age >= 25 AND age <= 35;
SELECT * FROM users WHERE city = 'New York';
SELECT * FROM users WHERE city != 'London';

-- Logical operators
SELECT * FROM users WHERE age > 25 AND city = 'New York';
SELECT * FROM users WHERE age < 25 OR city = 'Tokyo';
SELECT * FROM users WHERE NOT city = 'London';

-- IN operator
SELECT * FROM users WHERE city IN ('New York', 'London', 'Tokyo');

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 25 AND 35;

-- LIKE (pattern matching)
SELECT * FROM users WHERE name LIKE 'A%';      -- Starts with A
SELECT * FROM users WHERE name LIKE '%son';     -- Ends with son
SELECT * FROM users WHERE name LIKE '%li%';     -- Contains li
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- NULL checks
SELECT * FROM users WHERE city IS NULL;
SELECT * FROM users WHERE city IS NOT NULL;

-- Sort ascending (default)
SELECT * FROM users ORDER BY name ASC;

-- Sort descending
SELECT * FROM users ORDER BY age DESC;

-- Multiple sort criteria
SELECT * FROM users ORDER BY city ASC, age DESC;

-- Get the 5 oldest users
SELECT * FROM users ORDER BY age DESC LIMIT 5;

-- Pagination: page 3 (items 21-30)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- Insert single row
INSERT INTO users (name, email, city, age)
VALUES ('Eve', 'eve@example.com', 'Paris', 29);

-- Insert multiple rows
INSERT INTO users (name, email, city, age) VALUES
('Frank', 'frank@example.com', 'Berlin', 33),
('Grace', 'grace@example.com', 'Sydney', 27),
('Hank', 'hank@example.com', 'Toronto', 41);

-- Insert with defaults
INSERT INTO users (name, email) VALUES ('Ivy', 'ivy@example.com');
-- city and age will use their DEFAULT values

-- Update single row
UPDATE users SET city = 'San Francisco' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET city = 'Amsterdam', age = 30
WHERE name = 'Alice';

-- Update based on condition
UPDATE users
SET age = age + 1
WHERE city = 'New York';

-- Update all rows (DANGEROUS!)
UPDATE users SET is_active = true;

-- Delete specific row
DELETE FROM users WHERE id = 5;

-- Delete with condition
DELETE FROM users WHERE age < 18;

-- Delete all rows (DANGEROUS!)
DELETE FROM users;

-- Truncate (faster, resets auto-increment)
TRUNCATE TABLE users;