Fabric Patterns Cheat Sheet

🔍 Schema & Meta

📂 List databases: \l or SELECT datname FROM pg_database;
📋 Show tables: \dt
🔬 Show columns: \d employees
🧠 All schemas: SELECT schema_name FROM information_schema.schemata;
🧾 Table DDL: pg_dump -s -t employees mydb

📝 Basic Data Operations

SELECT – fetch data
SELECT name FROM employees;
INSERT – add data
INSERT INTO employees(name, age) VALUES('John', 30);
UPDATE – change data
UPDATE employees SET age = 31 WHERE name = 'John';
DELETE – remove data
DELETE FROM employees WHERE name = 'John';

🤝 Standard Joins

INNER JOIN – match both sides
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
LEFT JOIN – all left + match
SELECT * FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;
RIGHT JOIN – all right + match
SELECT * FROM emp RIGHT JOIN dept ON emp.dept_id = dept.id;
FULL JOIN – everything
SELECT * FROM emp FULL JOIN dept ON emp.dept_id = dept.id;

🔄 Special Join

CROSS JOIN – cartesian
SELECT * FROM emp CROSS JOIN dept;

🖼️ Joins Visualized

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

🧪 Data Types

INTEGER, BIGINT – whole numbers
VARCHAR(n), TEXT – text values
BOOLEAN – true/false
DATE, TIMESTAMP – date/time
JSON/JSONB – flexible data
SELECT data->>'name' FROM users;

🏗️ Table Management

CREATE – new table
CREATE TABLE employees(id SERIAL, name TEXT);
DROP – delete table
DROP TABLE employees;
ALTER – change table
ALTER TABLE employees ADD email TEXT;

🔎 Query Clauses

WHERE – filter rows
SELECT * FROM employees WHERE age > 25;
ORDER BY – sort rows
SELECT * FROM employees ORDER BY age DESC;
ORDER BY ASC – ascending (default)
SELECT * FROM employees ORDER BY name ASC;
ORDER BY DESC – descending
SELECT * FROM employees ORDER BY salary DESC;
ORDER BY multiple columns
SELECT * FROM employees ORDER BY dept ASC, salary DESC;
GROUP BY – aggregate
SELECT dept, COUNT(*) FROM employees GROUP BY dept;

🔢 Aggregate Functions

COUNT(*) – total rows
SELECT COUNT(*) FROM employees;
AVG(age) – average
SELECT AVG(age) FROM employees;
SUM(salary) – total salary
SELECT SUM(salary) FROM employees;
MAX(age) – oldest
SELECT MAX(age) FROM employees;
COALESCE(a,b) – default
SELECT COALESCE(phone, 'N/A') FROM employees;

➗ Arithmetic & Rounding

ROUND(num, n) – round to n digits
SELECT ROUND(salary, 2) FROM employees;
ROUND(num, -n) – round to left of decimal
SELECT ROUND(salary, -3) FROM employees;
CEIL(num) – round up
SELECT CEIL(price) FROM products;
FLOOR(num) – round down
SELECT FLOOR(price) FROM products;
Arithmetic (+, -, *, /)
SELECT price * 1.1 AS increased FROM products;
Divide floats
SELECT 5.0 / 2 AS result;
Alias with arithmetic
SELECT salary * 1.05 AS new_salary FROM employees;

⏰ Date & Time Functions

NOW() – current timestamp
SELECT NOW();
CURRENT_DATE – current date
SELECT CURRENT_DATE;
CURRENT_TIME – current time
SELECT CURRENT_TIME;
AGE() – interval between dates
SELECT AGE(NOW(), '2000-01-01');
DATE_TRUNC() – truncate to unit
SELECT DATE_TRUNC('month', NOW());
EXTRACT() – get part of date/time
SELECT EXTRACT(YEAR FROM NOW());

🔐 Permissions

GRANT – give access
GRANT SELECT ON employees TO analyst;
REVOKE – remove access
REVOKE SELECT ON employees FROM analyst;

📤 Exporting Data from PostgreSQL

Export entire database (SQL dump):
pg_dump -U username dbname > dbname.sql
Export a table to CSV (psql):
\copy tablename TO 'output.csv' CSV HEADER
Export a table to CSV (SQL):
COPY tablename TO '/path/output.csv' DELIMITER ',' CSV HEADER;
Export schema only:
pg_dump -s -U username dbname > schema.sql

📥 Importing Data into PostgreSQL

Import SQL dump:
psql -U username dbname < dbname.sql
Import CSV (psql):
\copy tablename FROM 'input.csv' CSV HEADER
Import CSV (SQL):
COPY tablename FROM '/path/input.csv' DELIMITER ',' CSV HEADER;
Import Parquet (with extension):
CREATE EXTENSION parquet_fdw;
CREATE SERVER parquet_srv FOREIGN DATA WRAPPER parquet_fdw;
CREATE FOREIGN TABLE parquet_table (...) SERVER parquet_srv OPTIONS (filename '/path/file.parquet');
Import from other DBs (brief):
Use pgloader, postgres_fdw, or DB-specific tools for migrations.

🗂️ Working with JSON Fields

Select a JSON property:
SELECT data->>'name' FROM users;
Filter by JSON property:
SELECT * FROM users WHERE data->>'status' = 'active';
Update a JSON property:
UPDATE users SET data = jsonb_set(data, '{name}', '"Omar"') WHERE id = 1;
Index a JSONB field:
CREATE INDEX idx_users_data ON users USING GIN (data jsonb_path_ops);

🔭 Creating & Using Views

Create a view:
CREATE VIEW active_employees AS SELECT * FROM employees WHERE active = true;
Query a view:
SELECT * FROM active_employees;
Drop a view:
DROP VIEW active_employees;

🧩 Building Nested SQL Queries

Subquery in SELECT:

SELECT name,
       (SELECT MAX(salary)
          FROM employees) AS max_salary
  FROM employees;

Subquery in FROM:

SELECT dept, avg_age
  FROM (
        SELECT dept, AVG(age) AS avg_age
          FROM employees
         GROUP BY dept
       ) AS dept_ages;

Subquery in WHERE:

SELECT *
  FROM employees
 WHERE salary > (SELECT AVG(salary)
                   FROM employees);

Correlated subquery:

SELECT name
  FROM employees e
 WHERE salary > (SELECT AVG(salary)
                   FROM employees
                  WHERE dept = e.dept);

🔑 Essential SQL Keywords I

DISTINCT – remove duplicates
SELECT DISTINCT dept FROM employees;
LIMIT & OFFSET – restrict results
SELECT * FROM employees LIMIT 5 OFFSET 10;
UNION / UNION ALL – combine results
SELECT name FROM a UNION SELECT name FROM b;
EXISTS – check for subquery existence
SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM dept WHERE dept.id = employees.dept_id);
IN – match any in list
SELECT * FROM employees WHERE dept IN ('HR', 'IT');
BETWEEN – range filter
SELECT * FROM employees WHERE age BETWEEN 20 AND 30;
LIKE / ILIKE – pattern matching (ILIKE is case-insensitive)
SELECT * FROM employees WHERE name LIKE 'A%';
CASE – conditional logic
SELECT name, CASE WHEN age > 30 THEN 'Senior' ELSE 'Junior' END FROM employees;
CAST – convert data types
SELECT '123'::INTEGER;
IS NULL / IS NOT NULL – null checks
SELECT * FROM employees WHERE email IS NULL;
DEFAULT – set default value
CREATE TABLE t (id SERIAL, active BOOLEAN DEFAULT true);
PRIMARY KEY – unique row identifier
CREATE TABLE t (id SERIAL PRIMARY KEY);
FOREIGN KEY – reference other table
CREATE TABLE orders (user_id INT REFERENCES users(id));

🔎 WHERE vs HAVING

WHERE filters rows before aggregation (GROUP BY), and cannot use column aliases created in SELECT.
HAVING filters groups after aggregation, and can use aliases from SELECT.

WHERE Example:

SELECT dept, AVG(salary) AS avg_salary
  FROM employees
 WHERE salary > 50000
 GROUP BY dept;

HAVING Example (with alias):

SELECT dept, AVG(salary) AS avg_salary
  FROM employees
 GROUP BY dept
HAVING avg_salary > 60000;

Note: In PostgreSQL, WHERE cannot use avg_salary because aliases are not available until after the SELECT clause is processed. HAVING can use avg_salary because it is evaluated after aggregation and SELECT.

🧩 Essential SQL Keywords II

SERIAL – auto-increment integer
CREATE TABLE t (id SERIAL PRIMARY KEY);
UNIQUE – enforce unique values
CREATE TABLE t (email TEXT UNIQUE);
CHECK – enforce condition
CREATE TABLE t (age INT CHECK (age >= 18));
INDEX – speed up lookups
CREATE INDEX idx_name ON employees(name);
EXPLAIN – show query plan
EXPLAIN SELECT * FROM employees;
ANALYZE – collect stats
ANALYZE employees;
WITH (CTE) – common table expressions
WITH recent AS (SELECT * FROM employees WHERE hired > '2024-01-01') SELECT * FROM recent;
RETURNING – return rows from DML
INSERT INTO employees(name) VALUES('Anna') RETURNING id;

📊 SQL Order of Execution

Order of execution in SQL:
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET

PostgreSQL SQL Syntax Cheatsheet 📄