SQL Overview

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- Oracle-specific: ROWNUM for top-N queries (legacy)
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 10;
 
-- Modern Oracle (12c+): Standard FETCH syntax
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
 
-- Oracle-specific: CONNECT BY for hierarchical queries
SELECT employee_id, name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
 
-- Oracle-specific: DECODE (instead of CASE, legacy)
SELECT name, DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
FROM employees;
 
-- Oracle-specific: NVL for NULL handling
SELECT name, NVL(commission, 0) AS commission
FROM employees;
 
-- Oracle-specific: Date arithmetic
SELECT SYSDATE, SYSDATE + 30 AS thirty_days_later
FROM DUAL;  -- DUAL is Oracle's dummy table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- SQL Server-specific: TOP for limiting rows
SELECT TOP 10 * FROM employees
ORDER BY salary DESC;
 
-- SQL Server-specific: WITH (NOLOCK) hint
SELECT * FROM large_table WITH (NOLOCK)
WHERE category = 'Electronics';
 
-- SQL Server-specific: String concatenation with +
SELECT first_name + ' ' + last_name AS full_name
FROM employees;
 
-- SQL Server-specific: ISNULL (not COALESCE or NVL)
SELECT name, ISNULL(commission, 0) AS commission
FROM employees;
 
-- SQL Server-specific: Identity columns
CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME2 DEFAULT GETDATE()
);
 
-- SQL Server-specific: Common table expression with OUTPUT
;WITH ToDelete AS (
    SELECT TOP 1000 * FROM logs WHERE created_at < '2023-01-01'
)
DELETE FROM ToDelete
OUTPUT DELETED.*;  -- Return deleted rows
 
-- SQL Server-specific: CROSS APPLY / OUTER APPLY
SELECT c.name, recent.order_date
FROM customers c
OUTER APPLY (
    SELECT TOP 3 order_date FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
) AS recent;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- PostgreSQL: JSONB for semi-structured data
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Query JSON fields
SELECT data->>'name' AS name, data->'address'->>'city' AS city
FROM events
WHERE data @> '{"type": "customer"}';  -- JSONB containment
 
-- PostgreSQL: Array types and operations
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[] DEFAULT '{}'
);
 
SELECT * FROM products WHERE 'electronics' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['featured', 'sale'];
 
-- PostgreSQL: Generate series
SELECT generate_series(1, 10) AS num;
SELECT generate_series('2024-01-01'::date, '2024-12-31', '1 month') AS month;
 
-- PostgreSQL: Range types
CREATE TABLE reservations (
    room_id INT,
    during TSTZRANGE
);
SELECT * FROM reservations
WHERE during && '[2024-03-01, 2024-03-05)';  -- Overlaps
 
-- PostgreSQL: DISTINCT ON
SELECT DISTINCT ON (department) name, department, salary
FROM employees
ORDER BY department, salary DESC;
 
-- PostgreSQL: Upsert with ON CONFLICT
INSERT INTO inventory (product_id, quantity)
VALUES (123, 50)
ON CONFLICT (product_id)
DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- MySQL-specific: LIMIT with comma syntax (legacy)
SELECT * FROM employees ORDER BY salary DESC LIMIT 10, 20;
-- Skips 10, returns 20 (confusing order vs. standard OFFSET/FETCH)
 
-- MySQL-specific: Backticks for identifiers
SELECT `order`, `group`, `select` FROM `table`;
 
-- MySQL-specific: ON DUPLICATE KEY UPDATE
INSERT INTO counters (page, hits)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;
 
-- MySQL-specific: REPLACE (delete + insert)
REPLACE INTO settings (key, value)
VALUES ('theme', 'dark');
 
-- MySQL-specific: Group by extension (non-standard)
SELECT department, name, MAX(salary)
FROM employees
GROUP BY department;  -- name not in GROUP BY, but MySQL allows it
 
-- MySQL-specific: STRAIGHT_JOIN hint
SELECT STRAIGHT_JOIN * 
FROM large_table l
JOIN small_table s ON l.id = s.id;
 
-- MySQL-specific: Multi-table UPDATE
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'vip'
WHERE c.total_spent > 10000;
 
-- MySQL-specific: Variables in queries
SET @row_num = 0;
SELECT @row_num := @row_num + 1 AS row_num, name
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- SQLite: Dynamic typing (unlike other SQL databases)
CREATE TABLE flexible (
    id INTEGER PRIMARY KEY,  -- Only INTEGER PRIMARY KEY is truly typed
    data ANY                  -- Can store any type
);
 
INSERT INTO flexible VALUES (1, 'text'), (2, 42), (3, 3.14), (4, NULL);
 
-- SQLite: ROWID is always available (unless WITHOUT ROWID table)
SELECT rowid, * FROM my_table;
 
-- SQLite: INSERT OR REPLACE (upsert)
INSERT OR REPLACE INTO settings (key, value)
VALUES ('theme', 'dark');
 
-- SQLite: Date/time as text (no native date type)
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_date TEXT,  -- Stored as ISO8601 string
    timestamp TEXT DEFAULT CURRENT_TIMESTAMP
);
 
-- SQLite: JSON functions (3.9+)
SELECT json_extract(data, '$.name') AS name
FROM json_table
WHERE json_extract(data, '$.active') = 1;
 
-- SQLite: Full-text search with FTS5
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, content, author
);
SELECT * FROM articles_fts WHERE articles_fts MATCH 'database SQL';
 
-- SQLite: Window functions (3.25+)
SELECT name, salary,
    SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Snowflake: Semi-structured data querying
SELECT
    raw_data:customer:name::string AS customer_name,
    raw_data:items[0]:product::string AS first_product
FROM orders
WHERE raw_data:total::number > 1000;
 
-- BigQuery: ARRAY and STRUCT operations
SELECT
    user_id,
    ARRAY(SELECT product FROM UNNEST(cart) AS product WHERE price > 50) AS expensive_items
FROM shopping_sessions;
 
-- Redshift: Approximate distinct count (HyperLogLog)
SELECT 
    date,
    APPROXIMATE COUNT(DISTINCT user_id) AS approx_users
FROM page_views
GROUP BY date;
 
-- All platforms: Window functions at scale
SELECT
    product_category,
    product_name,
    revenue,
    revenue / SUM(revenue) OVER (PARTITION BY product_category) AS category_share,
    RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales
WHERE sale_date >= '2024-01-01';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- CockroachDB: Locality-aware partitioning
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING UNIQUE,
    region STRING,
    created_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (region) (
    PARTITION us_east VALUES IN ('us-east'),
    PARTITION us_west VALUES IN ('us-west'),
    PARTITION eu VALUES IN ('eu-west', 'eu-central')
);
 
-- CockroachDB: Configure zone constraints
ALTER PARTITION us_east OF TABLE users
CONFIGURE ZONE USING constraints='[+region=us-east1]';
 
-- Standard SQL works across the distributed system
INSERT INTO users (email, region) VALUES ('user@example.com', 'us-east');
 
SELECT COUNT(*) FROM users WHERE region = 'eu-west';  -- Efficient with partitioning
 
-- Distributed transactions work transparently
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Both accounts may be on different nodes; ACID still guaranteed