🗂️ SQL Notes

A curated list of SQL snippets and syntax comparisons for Oracle and PostgreSQL.
Shared syntax is shown once; differing syntax appears in Oracle / PostgreSQL tables.

---

📘 Basic SELECT

SELECT * FROM table_a;

---

🔹 Selecting Specific Columns

SELECT col1, col2, col3
FROM table_a;

---

🔹 Filtering Rows (WHERE)

SELECT *
FROM table_a
WHERE status = 'ACTIVE'

---

🔹 GROUP BY

SELECT category, COUNT(*)
FROM table_a
GROUP BY category;

---

🔹 ORDER BY

SELECT *
FROM table_a
ORDER BY created_date DESC;

---

🔹 Common Table Expression (CTE)

WITH temp AS (
    SELECT *
    FROM table_a
    WHERE amount > 5000
)
SELECT *
FROM temp;

---

🔹 CASE WHEN

SELECT
    id,
    CASE
        WHEN amount > 5000 THEN 'High'
        WHEN amount BETWEEN 3000 AND 5000 THEN 'Medium'
        ELSE 'Low'
    END AS amount_level
FROM table_a;

---

🔹 Joins

SELECT a.id, b.name
FROM table_a a
JOIN table_b b ON a.id = b.id;

---

🔹 JSON Functions

Description	Oracle	PostgreSQL
Extract JSON value	sql SELECT json_value(data, '$.name') FROM table_json;	sql SELECT data->>'name' FROM table_json;
Extract nested JSON object	sql SELECT json_query(data, '$.address') FROM table_json;	sql SELECT data->'address' FROM table_json;

---

🔹 Date & Time

Description	Oracle	PostgreSQL
Current timestamp	sql SELECT SYSTIMESTAMP FROM dual;	sql SELECT NOW();
Add days to a date	sql SELECT SYSDATE + 7 FROM dual;	sql SELECT NOW() + INTERVAL '7 days';

---

🔹 UNNEST / Split String to Rows

Description	Oracle	PostgreSQL
Split comma-separated string into rows	sql SELECT REGEXP_SUBSTR('A,B,C', '[^,]+', 1, LEVEL) AS val FROM dual CONNECT BY REGEXP_SUBSTR('A,B,C', '[^,]+', 1, LEVEL) IS NOT NULL;	sql SELECT unnest(string_to_array('A,B,C', ',')) AS val;

---

🔹 Metadata & Schema Information

Description	Oracle	PostgreSQL
Get table DDL	sql SELECT DBMS_METADATA.GET_DDL('TABLE', 'TABLE_NAME') AS create_sql FROM dual;	sql SELECT table_name, column_name, data_type FROM information_schema.columns WHERE table_schema = 'public';

---

🔹 Compare Data Between Two Tables

SELECT code, description
FROM table_a
WHERE code NOT IN (
    SELECT code
    FROM table_b
);

---

🔹 Join with Subquery

SELECT t.id,
       f.type_code
FROM table_t t
LEFT JOIN (
    SELECT sub.id,
           REGEXP_SUBSTR(sub.codes, '[^,]+', 1, LEVEL) AS type_code
    FROM table_t sub
    CONNECT BY REGEXP_SUBSTR(sub.codes, '[^,]+', 1, LEVEL) IS NOT NULL
) f
ON t.id = f.id;

---

🔹 UNNEST & GROUP BY

Description	Oracle	PostgreSQL
Unnest rating or score list	sql SELECT score_rating, code FROM table_t WHERE score_rating IS NOT NULL GROUP BY score_rating, code;	sql SELECT unnest(string_to_array(score_rating, ',')) AS rating, code FROM table_t WHERE score_rating IS NOT NULL;

---

🔹 FLOOR and Boolean Comparison (CTE Style)

WITH alive AS (
    SELECT *
    FROM players
    WHERE status = 'alive' AND is_insider IS FALSE
)
SELECT
    FLOOR(COUNT(*) * 0.90) AS needed_count,
    FLOOR(COUNT(*) * 0.90) <= (SELECT total FROM resources) AS has_enough
FROM alive;

---

🔹 LOWER and UPPER Functions

Convert text to lowercase or uppercase.

-- Convert to lowercase
SELECT LOWER(name) AS lower_name
FROM table_a;

-- Convert to uppercase
SELECT UPPER(name) AS upper_name
FROM table_a;

---

🔹 Logical Conditions (AND / OR)

-- Using AND
SELECT *
FROM table_a
WHERE status = 'ACTIVE'
  AND category = 'A';

-- Using OR
SELECT *
FROM table_a
WHERE status = 'ACTIVE'
   OR category = 'A';

---

🔹 Multiple OR Conditions Example

SELECT *
FROM table_a
WHERE code = 'A'
   OR code = '1';

---

🔹 Combined AND / OR Example

SELECT *
FROM table_a
WHERE (code = 'A' OR code = '1')
  AND status = 'ACTIVE';

---