Database Languages: The Complete Language Toolkit

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
45
46
47
48
49
50
51
52
53
54

-- =====================================================
-- Complete Workflow Using All SQL Sublanguages
-- =====================================================
 
-- PHASE 1: DDL - Define the structure (done by DBA/architect)
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
);
 
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
 
-- PHASE 2: DCL - Grant permissions (done by DBA)
CREATE ROLE order_processor;
GRANT SELECT, INSERT, UPDATE ON orders TO order_processor;
GRANT USAGE ON SEQUENCE orders_order_id_seq TO order_processor;
 
CREATE ROLE order_viewer;
GRANT SELECT ON orders TO order_viewer;
 
-- PHASE 3: DML + TCL - Application operations (done by application)
BEGIN;  -- TCL: Start transaction
 
-- DML: Insert new order
INSERT INTO orders (customer_id, total, status)
VALUES (100, 299.99, 'PENDING')
RETURNING order_id;  -- Returns new order_id, e.g., 5001
 
-- DML: Add order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES 
    (5001, 'PROD-A', 2, 99.99),
    (5001, 'PROD-B', 1, 100.01);
 
-- DML: Update inventory
UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 'PROD-A';
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 'PROD-B';
 
COMMIT;  -- TCL: Make permanent
 
-- PHASE 4: Ongoing DML - Report queries (done by analysts)
SELECT 
    c.customer_name,
    COUNT(*) AS order_count,
    SUM(o.total) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.customer_id, c.customer_name
ORDER BY total_revenue DESC;

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

-- =====================================================
-- Procedural SQL Extensions: Comparison Examples
-- =====================================================
 
-- PROBLEM: Update all employees' salaries based on department budget
 
-- PostgreSQL PL/pgSQL:
CREATE OR REPLACE FUNCTION apply_department_raises()
RETURNS void AS $$
DECLARE
    dept RECORD;
    raise_pct DECIMAL;
BEGIN
    -- Loop through departments
    FOR dept IN SELECT department_id, budget FROM departments LOOP
        -- Calculate raise percentage based on budget
        raise_pct := CASE
            WHEN dept.budget > 1000000 THEN 0.10   -- 10% raise
            WHEN dept.budget > 500000 THEN 0.05    -- 5% raise
            ELSE 0.02                               -- 2% raise
        END;
        
        -- Apply raise to all employees in department
        UPDATE employees 
        SET salary = salary * (1 + raise_pct)
        WHERE department_id = dept.department_id;
        
        -- Log the action
        RAISE NOTICE 'Department %: applied % raise', 
            dept.department_id, raise_pct * 100 || '%';
    END LOOP;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error applying raises: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;
 
-- Oracle PL/SQL equivalent:
-- CREATE OR REPLACE PROCEDURE apply_department_raises AS
--     raise_pct NUMBER;
-- BEGIN
--     FOR dept IN (SELECT department_id, budget FROM departments) LOOP
--         raise_pct := CASE
--             WHEN dept.budget > 1000000 THEN 0.10
--             WHEN dept.budget > 500000 THEN 0.05
--             ELSE 0.02
--         END;
--         
--         UPDATE employees 
--         SET salary = salary * (1 + raise_pct)
--         WHERE department_id = dept.department_id;
--         
--         DBMS_OUTPUT.PUT_LINE('Department ' || dept.department_id || 
--                              ': applied ' || (raise_pct * 100) || '% raise');
--     END LOOP;
-- EXCEPTION
--     WHEN OTHERS THEN
--         RAISE_APPLICATION_ERROR(-20001, 'Error: ' || SQLERRM);
-- END;
-- /
 
-- SQL Server T-SQL equivalent:
-- CREATE PROCEDURE apply_department_raises AS
-- BEGIN
--     DECLARE @dept_id INT, @budget DECIMAL(15,2), @raise_pct DECIMAL(5,4)
--     
--     DECLARE dept_cursor CURSOR FOR
--         SELECT department_id, budget FROM departments
--     
--     OPEN dept_cursor
--     FETCH NEXT FROM dept_cursor INTO @dept_id, @budget
--     
--     WHILE @@FETCH_STATUS = 0
--     BEGIN
--         SET @raise_pct = CASE
--             WHEN @budget > 1000000 THEN 0.10
--             WHEN @budget > 500000 THEN 0.05
--             ELSE 0.02
--         END
--         
--         UPDATE employees 
--         SET salary = salary * (1 + @raise_pct)
--         WHERE department_id = @dept_id
--         
--         FETCH NEXT FROM dept_cursor INTO @dept_id, @budget
--     END
--     
--     CLOSE dept_cursor
--     DEALLOCATE dept_cursor
-- END

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
45
46
47
48
49
50
51
52
53
54

========================================
Query By Example: Conceptual Illustration
========================================
 
SQL Query:
    SELECT first_name, last_name, salary 
    FROM employees 
    WHERE department_id = 10 AND salary > 50000;
 
QBE Equivalent (table-based interface):
┌────────────────────────────────────────────────────────────────────┐
│ employees                                                          │
├─────────────┬────────────┬────────────┬───────┬──────────────────┤
│ first_name  │ last_name  │ salary     │ dept  │ employee_id      │
├─────────────┼────────────┼────────────┼───────┼──────────────────┤
│ P._fn       │ P._ln      │ P.> 50000  │ 10    │                  │
└─────────────┴────────────┴────────────┴───────┴──────────────────┘
 
Key:
- "P." prefix = Print this column (equivalent to SELECT)
- "_fn", "_ln" = Example element variables (names for output)
- "> 50000" = Condition (equivalent to WHERE salary > 50000)
- "10" = Exact match condition (WHERE department_id = 10)
- Empty cells = Not involved in query
 
========================================
More Complex QBE Example (Join)
========================================
 
SQL:
    SELECT e.first_name, d.department_name 
    FROM employees e JOIN departments d 
    ON e.department_id = d.department_id
    WHERE d.location = 'New York';
 
QBE:
┌──────────────────────────────────────────────────────────────────┐
│ employees                                                        │
├───────────────┬──────────────────┬────────────┬─────────────────┤
│ first_name    │ department_id     │ ...        │                 │
├───────────────┼──────────────────┼────────────┼─────────────────┤
│ P._name       │ _d                │            │                 │
└───────────────┴──────────────────┴────────────┴─────────────────┘
 
┌──────────────────────────────────────────────────────────────────┐
│ departments                                                       │
├───────────────────┬───────────────────┬────────────────────────────┤
│ department_id     │ department_name   │ location                   │
├───────────────────┼───────────────────┼────────────────────────────┤
│ _d                │ P._dname          │ New York                   │
└───────────────────┴───────────────────┴────────────────────────────┘
 
Key:
- _d appears in both tables → implicitly joins on this column

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

// =====================================================
// NoSQL Query Language Examples
// =====================================================
 
// MongoDB MQL: Find orders for customer 100 with total > $100
// SQL:  SELECT * FROM orders WHERE customer_id = 100 AND total > 100
 
db.orders.find({
  customer_id: 100,
  total: { $gt: 100 }
})
 
// MongoDB Aggregation Pipeline: Group by customer, sum totals
// SQL: SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id
 
db.orders.aggregate([
  { $group: { 
      _id: "$customer_id", 
      total_spent: { $sum: "$total" } 
  }},
  { $sort: { total_spent: -1 } }
])
 
// =====================================================
// Cassandra CQL: Similar to SQL but with partition key requirements
// =====================================================
 
-- Create table with partition key
CREATE TABLE orders (
  customer_id INT,
  order_id UUID,
  order_date TIMESTAMP,
  total DECIMAL,
  PRIMARY KEY (customer_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC);
 
-- Query requires partition key
SELECT * FROM orders WHERE customer_id = 100;
 
-- Range queries only on clustering columns
SELECT * FROM orders 
WHERE customer_id = 100 
  AND order_date > '2024-01-01';
 
// =====================================================
// Neo4j Cypher: Graph pattern matching
// =====================================================
 
// Find all friends of Alice
// SQL: Difficult - requires recursive CTEs or multiple joins
 
MATCH (alice:Person {name: "Alice"})-[:FRIEND]->(friend)
RETURN friend.name
 
// Friends of friends who like "Basketball"
MATCH (alice:Person {name: "Alice"})-[:FRIEND*2]->(fof)-[:LIKES]->(sport:Sport {name: "Basketball"})
RETURN fof.name
 
// Shortest path between two people
MATCH path = shortestPath(
  (a:Person {name: "Alice"})-[:FRIEND*]-(b:Person {name: "Bob"})
)
RETURN path
 
// =====================================================
// Redis Commands: Imperative key-value operations
// =====================================================
 
// Set a value
SET user:100:name "Alice"
 
// Get a value
GET user:100:name
 
// Hash operations (object-like)
HSET user:100 name "Alice" email "alice@example.com"
HGET user:100 name
HGETALL user:100
 
// Sorted set for leaderboard
ZADD leaderboard 1500 "player1" 1200 "player2" 1800 "player3"
ZREVRANGE leaderboard 0 9 WITHSCORES  // Top 10

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

-- =====================================================
-- Hybrid Query Examples: SQL with Modern Extensions
-- =====================================================
 
-- 1. PostgreSQL: SQL + JSON
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    event_data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO events (event_type, event_data) VALUES 
('page_view', '{"page": "/home", "user_id": 123, "duration_ms": 5000}'),
('click', '{"element": "button#submit", "user_id": 123}');
 
-- Query JSON fields directly in SQL
SELECT 
    event_type,
    event_data->>'page' AS page,
    (event_data->>'duration_ms')::int AS duration
FROM events
WHERE event_type = 'page_view'
  AND (event_data->>'duration_ms')::int > 1000;
 
-- JSON containment query (efficient with GIN index)
SELECT * FROM events 
WHERE event_data @> '{"user_id": 123}';
 
-- 2. PostgreSQL: Full-text search (alternative to Elasticsearch)
ALTER TABLE products ADD COLUMN search_vector tsvector;
UPDATE products SET search_vector = to_tsvector('english', name || ' ' || description);
CREATE INDEX idx_products_search ON products USING GIN(search_vector);
 
SELECT name, description 
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & headphones')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'wireless & headphones')) DESC;
 
-- 3. PostgreSQL: Recursive queries (graph-like traversal)
-- Find all subordinates of employee 1 (hierarchical query)
WITH RECURSIVE subordinates AS (
    -- Base case: direct reports
    SELECT employee_id, first_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id = 1
    
    UNION ALL
    
    -- Recursive case: reports of reports
    SELECT e.employee_id, e.first_name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY level, first_name;
 
-- 4. Time-series extensions (like TimescaleDB on PostgreSQL)
-- Create hypertable for time-series data
-- SELECT create_hypertable('sensor_data', 'time');
 
-- Efficient time-bucketed aggregation
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(value) AS avg_value,
    MAX(value) AS max_value
FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY hour, sensor_id
ORDER BY hour DESC;