Welcome to my fridge door!

1. INTEGER (INT)
Stores whole numbers (no decimals).
Typical range: -2,147,483,648 to 2,147,483,647 (4 bytes).
Good for things like IDs, counts, ages.
Variants: SMALLINT (2 bytes), BIGINT (8 bytes).

2. SMALLINT
Smaller range than INT.
Typical range: -32,768 to 32,767 (2 bytes).
Saves storage if you know values are small (e.g., number of items in stock, months of the year).

3. BIGINT
Larger range than INT.
Typical range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (8 bytes).
Use for very large counts (e.g., social media user IDs, transaction logs).

4. DECIMAL(p, s) / NUMERIC(p, s)
Stores exact fixed-point numbers.
p = precision (total number of digits).
s = scale (digits after the decimal point).
Example: DECIMAL(10,2) can store up to 99999999.99.
Great for money, prices, financial data (no rounding errors).

5. FLOAT / REAL / DOUBLE PRECISION
Stores approximate values with floating-point representation.
FLOAT → database decides precision (often 4 bytes, ~7 digits).
DOUBLE PRECISION → higher precision (8 bytes, ~15 digits).
Faster than DECIMAL, but not always exact (can have rounding errors).
Use for scientific data, measurements, percentages, but avoid for money.

These are all floating-point numeric types, meaning they store approximate numbers (not exact). The main difference is in precision (how many digits they can represent reliably) and storage size.
REAL = small, fast, less precise.
DOUBLE PRECISION = bigger, slower, much more precise.
FLOAT(p) = SQL standard that maps to REAL or DOUBLE depending on p.

6. SERIAL / BIGSERIAL (PostgreSQL only)
Auto-incrementing integer.
SERIAL = INTEGER + auto-increment.
BIGSERIAL = BIGINT + auto-increment.
Perfect for primary keys.

in PostgreSQL, it does not matter whether you use DECIMAL or NUMERIC

1. CHAR(n)
Fixed-length string.
Always stores exactly n characters → pads with spaces if shorter.
Example: CHAR(5)
'hi' is stored as 'hi ' (3 spaces added).
Efficient for codes of fixed length (e.g., country codes like USA, postal codes).

2. VARCHAR(n)
Variable-length string.
Stores up to n characters, without extra padding.
Example: VARCHAR(50) → can store between 0 and 50 characters.
More flexible than CHAR.
VARCHAR (without n) → unlimited length (same as TEXT).
Common for names, emails, descriptions.

3. TEXT
Stores unlimited-length text (well, very large).
Example: in PostgreSQL, up to 1 GB per value.
No need to define a max length.
Good for long text (comments, articles, logs).
But often lacks some indexing/constraint options compared to VARCHAR.

4. NVARCHAR / NCHAR (Unicode types, mainly SQL Server / Oracle)
Like VARCHAR/CHAR, but stores Unicode characters.
Needed when working with multilingual data (Chinese, Arabic, emojis, etc.).
Each character can take more bytes (UTF-16 encoding).

5. Other Useful String Types (DB-specific)
UUID (PostgreSQL, MySQL 8.0) → special string for universally unique identifiers.
JSON / JSONB (PostgreSQL, MySQL, SQL Server) → structured text data.
ENUM (MySQL, PostgreSQL) → predefined list of string values (like "pending", "approved", "rejected").

1. DATE
Stores a calendar date (year, month, day).
Format: YYYY-MM-DD.
'2025-09-09'
No time of day is stored.

2. TIME [WITHOUT TIME ZONE]
Stores only the time of day (hours, minutes, seconds, fractions).
Format: HH:MI:SS[.ffff].
'14:35:20'

3. TIMESTAMP [WITHOUT TIME ZONE]
Stores both date and time together.
Format: YYYY-MM-DD HH:MI:SS[.ffff].
'2025-09-09 14:35:20'

4. TIMESTAMP WITH TIME ZONE (timestamptz in PostgreSQL)
Same as TIMESTAMP, but also stores a time zone offset.
'2025-09-09 14:35:20+02'
Useful when working across multiple time zones.

5. INTERVAL (PostgreSQL, some others)
Stores a time duration (years, months, days, hours, minutes, seconds).
'2 days 5 hours 30 minutes'

6. DATETIME (MySQL, SQL Server)
Equivalent to TIMESTAMP in many systems.
Stores both date and time (without timezone).
Copy code
'2025-09-09 14:35:20'

>>> General SQL Rules for Dates & Intervals

Type awareness

You can only add/subtract intervals to/from DATE or TIMESTAMP types.
Example: DATE '2025-01-01' + INTERVAL '10 days' → 2025-01-11.

Valid units for INTERVAL
Standard SQL supports: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

Some databases add extras like WEEK, MICROSECOND, etc.

Addition vs. subtraction

date + interval → moves forward.
date - interval → moves backward.
date1 - date2 → returns an interval (the difference).

Overflow handling (months/years)

Adding months is tricky because months have different lengths.
Example in PostgreSQL:
DATE '2025-01-31' + INTERVAL '1 month' → 2025-02-28 (it clamps to the last valid day).

MySQL might behave differently.

In PostgreSQL, there’s no separate “bare” date literal without quotes. Every date you write in SQL is either:

A string literal -> 'YYYY-MM-DD' -> PostgreSQL automatically casts to DATE if needed.
A string explicitly cast to date -> 'YYYY-MM-DD'::DATE.
A date literal using the DATE keyword -> DATE 'YYYY-MM-DD'.

There isn’t a scenario where you literally type something like 2025-09-23 without quotes and PostgreSQL interprets it as a date—it would be treated as a numeric expression (2025 minus 9 minus 23 = 1993).

CREATE TYPE type_mood AS ENUM ('happy', 'relaxed', 'stressed', 'sad');
ALTER TABLE minions_info ADD COLUMN mood type_mood;

RANDOM() generates a pseudo-random floating-point number

0.0 ≤ RANDOM() < 1.0 0.0≤RANDOM()<1.0

    SELECT RANDOM();
    # Might output: 0.374829


Multiplying RANDOM() by a number scales it
0.0≤RANDOM() * 100<100.0
    SELECT RANDOM() * 100;
    # Might output: 37.4829

random integer between 0 and 99:
    SELECT FLOOR(RANDOM() * 100);

To get between 1 and 100:
    SELECT FLOOR(RANDOM() * 100) + 1;

Random row selection:
    SELECT *
    FROM users
    ORDER BY RANDOM()
    LIMIT 5;
Picks 5 random rows from users.


RANDOM() → 0 ≤ x < 1

RANDOM() * N → scales to 0 ≤ x < N

FLOOR(RANDOM() * N) → integer 0..(N-1)

FLOOR(RANDOM() * N) + 1 → integer 1..N

ALTER TABLE    
Table schema    Add/drop columns, change types, constraints

    UPDATE    
Table data    Modify or fill in values in existing rows


ALTER TABLE countries
ADD COLUMN capital_code CHAR(2);

ALTER TABLE countries
DROP COLUMN capital_code;

ALTER TABLE countries
ALTER COLUMN population TYPE BIGINT;
Add a constraint (e.g., primary key or foreign key):

ALTER TABLE countries
ADD CONSTRAINT pk_country_id PRIMARY KEY (id);


UPDATE countries
SET capital_code = SUBSTRING(capital, 1, 2);

UPDATE countries
SET population = population * 1000
WHERE population < 1000;

UPDATE always uses SET

LIKE is used in WHERE clauses to match text using wildcards.

% → matches any sequence of characters (including empty).
_ → matches exactly one character.

-- Find all cities starting with 'New'
SELECT * FROM cities
WHERE name LIKE 'New%';

-- Find all cities with 3 letters
SELECT * FROM cities
WHERE name LIKE '___';

>>
WHERE LOWER("companion_full_name") LIKE '%and%'
    "Ms. Brandy Rice"
    "Terrell Blanda IV"
    "Andrew Gottlieb PhD"
    "Sandra Langosh"

AND "email" NOT LIKE '%@gmail';
    "Rosalind_Hudson@hotmail.com"
    "Macie87@hotmail.com"
    "Alfred.Purdy2@gmail.com"
    "Thalia_Wehner78@gmail.com"


AND "email" NOT LIKE '%@gmail%';
    "Ms. Brandy Rice"    "Rosalind_Hudson@hotmail.com"
    "Terrell Blanda IV"    "Macie87@hotmail.com"

-- Find names that literally contain '50%'

SELECT * FROM products
WHERE name LIKE '%50!%%' ESCAPE '!';

! is defined as the escape character.
!% means "treat % literally."

SELECT * FROM products
WHERE name LIKE '%50#%%' ESCAPE '#';


-- Find strings ending with '_end'

SELECT * FROM logs
WHERE message LIKE '%!_end' ESCAPE '!';

TO_CHAR in PostgreSQL is a formatting function — it converts numbers or dates/timestamps into nicely formatted strings.

1. Numbers → String
SELECT TO_CHAR(1234.567, 'FM9999.00');

-- "1234.57"
'9' → digit placeholder (optional if leading zeros aren’t needed)
'0' → digit placeholder (always show zero if missing)
FM → fill mode (removes padding spaces)
. → decimal point
, → thousands separator

SELECT TO_CHAR(12345.678, '999,999.99'); -- "12,345.68"
SELECT TO_CHAR(5, '0000'); -- "0005"


2. Dates / Timestamps → String

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
-- e.g. "2025-09-23 14:45:30"


YYYY → 4-digit year
YY → 2-digit year
MM → month (01–12)
MON → abbreviated month (e.g., JAN)
MONTH → full month name
DD → day of month
DY → abbreviated weekday (e.g., MON)
DAY → full weekday name
HH24 → hour (24-hour)
HH12 → hour (12-hour)
MI → minutes
SS → seconds
AM / PM → meridiem

SELECT TO_CHAR(NOW(), 'Day, DD Mon YYYY');
-- "Tuesday , 23 Sep 2025"

SELECT TO_CHAR(NOW(), 'HH12:MI:SS AM');
-- "02:45:30 PM"

EXTRACT in PostgreSQL always returns a double precision number.

EXTRACT(field FROM source)
field → what you want (like year, month, day, epoch…)
source → a DATE, TIMESTAMP, or INTERVAL

1. From a date

SELECT EXTRACT(YEAR FROM DATE '2025-09-23');
-- 2025

SELECT EXTRACT(MONTH FROM DATE '2025-09-23');
-- 9

SELECT EXTRACT(DAY FROM DATE '2025-09-23');
-- 23

2. From a timestamp

SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-09-23 14:45:30');
-- 14

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2025-09-23 14:45:30');
-- 45

SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-09-23 14:45:30');
-- 30

3. From intervals

SELECT EXTRACT(DAY FROM INTERVAL '3 days 5 hours');
-- 3

SELECT EXTRACT(HOUR FROM INTERVAL '3 days 5 hours');
-- 5

4. Special one: epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-09-23 14:45:30');
-- 1769246730 (Unix timestamp)

SELECT TO_TIMESTAMP(1769246730);
-- "2025-09-23 14:45:30+00"

AGE() in PostgreSQL is for working out differences between dates/timestamps, but instead of giving you just a number, it returns an interval.

SELECT AGE(TIMESTAMP '2025-09-23', TIMESTAMP '2000-09-23');
-- 25 years 0 mons 0 days

SELECT AGE(TIMESTAMP '2000-09-23');
-- relative to NOW(), e.g. "25 years 0 mons 0 days"


SELECT '2025-09-23'::date - '2000-09-23'::date;
-- 9132 (days)

SELECT AGE('2025-09-23'::date, '2000-09-23'::date);
-- 25 years

NOW() in PostgreSQL is a function that returns the current date and time as a TIMESTAMP WITH TIME ZONE.

Returns current timestamp at the moment the query runs.
Includes date and time, e.g., 2025-09-23 14:35:12.123456+00.

.123456 → the microseconds (fractional part of a second, up to 6 digits)
+00 → the time zone offset from UTC (in this case, +00 means UTC itself)

-- Insert a record with the current timestamp
INSERT INTO orders (order_date, customer_id)
VALUES (NOW(), 42);

-- Select events happening from now onwards
SELECT * FROM events
WHERE start_time >= NOW();

Equivalent to CURRENT_TIMESTAMP

SELECT NOW()::DATE; -- returns '2025-09-23'

In PostgreSQL, :: is shorthand for casting a value from one type to another. It’s equivalent to using the CAST() function.

'2025-09-23'::DATE
CAST('2025-09-23' AS DATE)

SELECT 123::TEXT; -- Converts number 123 to text
SELECT '12.34'::NUMERIC; -- Converts string to numeric
SELECT 'true'::BOOLEAN; -- Converts string to boolean

:: is PostgreSQL-specific shorthand.
CAST() is standard SQL and works in other databases too.

>>
CAST(b.starts_at, DATE) -> WRONG

CAST(b.starts_at AS DATE) -> RIGHT

SELECT ROUND(12.34); -- 12
SELECT ROUND(12.56); -- 13

-- Round to 1 decimal place
SELECT ROUND(12.345, 1); -- 12.3

SELECT ROUND(0.55); -- 1
SELECT ROUND(-0.55); -- -1

SELECT CEIL(12.01); -- 13
SELECT CEILING(12.99); -- 13

SELECT FLOOR(12.99); -- 12
SELECT FLOOR(12.01); -- 12


ROUND    12.56    13
CEIL    12.01    13
FLOOR    12.99    12

In PostgreSQL FLOOR() and CEIL() (or CEILING()) only work on the whole number—they do not take a precision argument like ROUND() does.

>> How to format decimals:

CAST(salary AS DECIMAL(18,2))
18 total digits, 2 after the decimal.

This is a common safe default — enough for salaries, prices, even big amounts.
Max: 9999999999999999.99

>>
Also works:

TRUNC(salary, 2)

TRANSLATE() is used to replace characters in a string, one-to-one. It's different from REPLACE() because it works character by character, not substrings.

SELECT TRANSLATE('abcdef', 'abc', '123');
-- Result: '123def'
-- 'a' → '1', 'b' → '2', 'c' → '3'

SELECT TRANSLATE('hello world', 'ld', 'XY');
-- Result: 'heXXo worY'
-- 'l' → 'X', 'd' → 'Y'

SELECT TRANSLATE('123-456-789', '123456789', '987654321');
-- Result: '987-654-321'

TRANSLATE(string, from_chars, to_chars)
Characters in from_chars without a matching character in to_chars are removed.

REPLACE() is used to replace substrings within a string, not individual characters.

REPLACE(string, from_substring, to_substring)

SELECT REPLACE('hello world', 'world', 'PostgreSQL');
-- Result: 'hello PostgreSQL'

SELECT REPLACE('ababab', 'ab', 'cd');
-- Result: 'cdcdcd'

SELECT REPLACE('2025-09-23', '-', '/');
-- Result: '2025/09/23'

SELECT LEFT('PostgreSQL', 4); -- 'Post'
SELECT LEFT('PostgreSQL', 0); -- ''
SELECT LEFT('PostgreSQL', -3); --Postgre


SELECT RIGHT('PostgreSQL', 3); -- 'SQL'
SELECT RIGHT('PostgreSQL', 0); -- ''
SELECT RIGHT('PostgreSQL', -3); -- tgreSQL


SELECT SUBSTRING('PostgreSQL' FROM 5 FOR 3); -- 'gre'
SELECT SUBSTRING('PostgreSQL' FROM -3 FOR 2); -- Null
SELECT SUBSTRING('PostgreSQL' FROM -3); -- PostgreSQL (seems to break)

SELECT SUBSTRING('PostgreSQL' FROM 5);
-- Result: 'greSQL' (till end of string)

SELECT RIGHT('PostgreSQL', -1);
ostgreSQL

SELECT LEFT('PostgreSQL', -1);
PostgreSQ

>>
SELECT SUBSTRING('PostgreSQL', 1, 4); -- Output: 'Post'
SELECT SUBSTRING('PostgreSQL', 5, 6); -- Output: 'greSQL'

SELECT LENGTH('PostgreSQL'); -- 10
SELECT LENGTH('こんにちは'); -- 5 (counts characters, even multibyte)
SELECT LENGTH(''); -- 0

-- Get last 3 characters
SELECT SUBSTRING('PostgreSQL' FROM LENGTH('PostgreSQL')-2 FOR 3); -- 'SQL'

-- Remove last 3 characters
SELECT LEFT('PostgreSQL', LENGTH('PostgreSQL')-3); -- 'Postgre'

BIT_LENGTH() returns the number of bits used to store a string (or bytea). Since each character in a standard string is typically 1 byte (8 bits), BIT_LENGTH() is usually 8 × LENGTH(string).

SELECT BIT_LENGTH('PostgreSQL'); -- 80 (10 chars × 8 bits)
SELECT BIT_LENGTH(''); -- 0

-- Multibyte characters (UTF-8)
SELECT BIT_LENGTH('こんにちは'); -- 40 (5 characters × 8 bits, in UTF-8 each char may take more bytes but BIT_LENGTH counts actual storage in bits)

POSITION() finds the starting position of a substring within a string. It returns an integer indicating the 1-based index of the first occurrence, or 0 if the substring is not found.

POSITION(substring IN string)

SELECT POSITION('SQL' IN 'PostgreSQL'); -- 9
SELECT POSITION('Post' IN 'PostgreSQL'); -- 1
SELECT POSITION('x' IN 'PostgreSQL'); -- 0 (not found)

SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World'
SELECT CONCAT('Value: ', NULL, '123'); -- 'Value: 123'

SELECT CONCAT_WS('-', '2025', '09', '23'); -- '2025-09-23'
SELECT CONCAT_WS(' ', 'Hello', NULL, 'World'); -- 'Hello World'

SELECT 'Hello' || ' ' || 'World'; -- 'Hello World'

SELECT 'Value: ' || NULL || '123'; -- NULL
Here NULL propagates and the entire concatenation returns NULL.

SELECT 'Value: ' || COALESCE(NULL, '') || '123'; -- 'Value: 123'

concat ignores null but || does not

1. TRIM()
Removes leading and/or trailing characters (by default, whitespace) from a string.

TRIM([LEADING | TRAILING | BOTH] [characters FROM] string)
BOTH (default) → removes from both ends.
LEADING → removes from the start only.
TRAILING → removes from the end only.

If no characters are specified, spaces are removed.


SELECT TRIM(' hello '); -- 'hello'
SELECT TRIM(BOTH 'x' FROM 'xxxhelloxx'); -- 'hello'
SELECT TRIM(LEADING 'x' FROM 'xxxhelloxx'); -- 'helloxx'
SELECT TRIM(TRAILING 'x' FROM 'xxxhelloxx'); -- 'xxxhello'

2. LTRIM()
Removes characters from the left (start) of the string.

SELECT LTRIM(' hello'); -- 'hello'
SELECT LTRIM('xxxhello', 'x'); -- 'hello'

3. RTRIM()
Removes characters from the right (end) of the string.

SELECT RTRIM('hello '); -- 'hello'
SELECT RTRIM('helloxxx', 'x'); -- 'hello'

-- Trim whitespace and handle empty result
IF TRIM(full_name) = '' THEN
full_name := NULL;
END IF;

SELECT *
FROM employees
WHERE manager_id = NULL;
This will return no rows, even if some manager_id values are NULL.

The correct way
Use IS NULL or IS NOT NULL instead:

-- Find rows where manager_id is NULL
SELECT *
FROM employees
WHERE manager_id IS NULL;

-- Find rows where manager_id is not NULL
SELECT *
FROM employees
WHERE manager_id IS NOT NULL;

1. SET in UPDATE statements
Here, the = is required — it assigns a value to a column.

UPDATE employees
SET salary = 50000
WHERE id = 123;

2. ALTER statements

ALTER TABLE my_table
ALTER COLUMN my_column TYPE varchar(100);

ALTER TABLE my_table
ALTER COLUMN my_column SET NOT NULL;

ALTER TABLE employees
ALTER COLUMN salary SET DEFAULT 500;
This doesn’t change existing rows. It only affects new inserts where salary isn’t specified.

SET search_path TO public;
ALTER ROLE yoana SET search_path TO public;

This allows for the table and column names to pop up as suggestioons in the QT

CREATE TABLE sales (
price numeric,
quantity int,
total numeric GENERATED ALWAYS AS (price * quantity) STORED
);

GENERATED ALWAYS AS (expression) defines the computed column, i.e., it specifies how the value is derived.

By itself, it doesn’t store anything physically. To actually save the value in the table, you need to add STORED.


CREATE TABLE users (
id SERIAL PRIMARY KEY,
name text
);

INSERT INTO users (name) VALUES ('Alice');
-- id = 1 automatically, then 2, 3, ...

GENERATED ALWAYS AS automatically computes the column value and does not allow manual inserts, while SERIAL uses a sequence to auto-fill values but still lets you manually insert if desired.

>>>
ALTER TABLE
    countries
ADD COLUMN
    capital_code CHAR(2) GENERATED ALWAYS AS (SUBSTRING(capital FROM 1 FOR 2)) STORED;

>>

CREATE TABLE example (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT
);

GENERATED ALWAYS AS IDENTITY → PostgreSQL always generates the value. If you try to insert into id, it will fail (unless you use OVERRIDING SYSTEM VALUE).

GENERATED BY DEFAULT AS IDENTITY → PostgreSQL generates a value if you don’t supply one.

PRIMARY KEY    

Uniquely identifies each row in a table. No two rows can have the same value, and the column(s) cannot be NULL.
Number of constraints per table:    1 - one table cannot have more than 1 PRIMARY KEY columns

UNIQUE    
Ensures all values in the column(s) are distinct, but NULLs are allowed (except in some DBMS where multiple NULLs are allowed).
Allows NULLs    
Can be used many times in a table


CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
ssn CHAR(9) UNIQUE
);
emp_id uniquely identifies each row.
email and ssn must be unique, but they are separate from the primary key.


PRIMARY KEY = unique + not null + only one per table
UNIQUE = unique values (can be multiple per table, can allow NULL)

RETURNING only works with data-modifying statements:

INSERT
UPDATE
DELETE


1. INSERT … RETURNING
Use it when you want to know what values were inserted, especially useful for auto-generated IDs.

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, name;

Returns the id and name of the newly inserted row(s).


2. UPDATE … RETURNING
Use it when you want to see the new state of updated rows.

UPDATE users
SET email = 'alice@newdomain.com'
WHERE name = 'Alice'
RETURNING id, email;

Returns the id and updated email for the rows that were changed.


3. DELETE … RETURNING
Use it when you want to know what was deleted.

DELETE FROM users
WHERE name = 'Alice'
RETURNING id, name;
Returns the id and name of the deleted row(s).

GROUP BY is used to aggregate data. It takes a table with many rows and groups them into “buckets” based on one or more columns. Then you can apply aggregate functions (COUNT, SUM, AVG, etc.) to each group.

SELECT
LEFT(first_name, 2) AS initials,
COUNT(*) AS user_count
FROM users
GROUP BY initials

GROUP BY initials tells SQL to group all rows by the first two letters of first_name.
Within each group, COUNT(*) counts how many users are in that group.

SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

SQL looked at the city column.
Made one group for each unique city.
Counted how many rows were in each group using COUNT(*).

COUNT(*) → counts all rows in the group, including duplicates and NULLs.
COUNT(column_name) → counts only rows where the column is not NULL.

SELECT city,
COUNT(*) AS total_users,
AVG(age) AS avg_age,
MIN(age) AS youngest,
MAX(age) AS oldest
FROM users
GROUP BY city;

>>
count(distinct) returns the count of groups
count(*) returns the count of rows

WHERE filters before the aggregation, HAVING filetrs after the aggregation (COUNT, AVG, SUM, MIN, etc)

>>
In SQL, the key thing to understand is the order in which the clauses are logically processed:

SELECT
department_id,
SUM(salary) AS "Total Salary",
COUNT(*) AS "Num Employees"
FROM employees
WHERE salary > 1000 -- filter individual rows
GROUP BY department_id
HAVING SUM(salary) < 4200 -- filter grouped results
ORDER BY department_id;

1. FROM employees → take the table.
2. WHERE salary > 1000 → only rows with salary > 1000 remain.
3. GROUP BY department_id → group those rows by department.
4. SUM(salary) + COUNT(*) are calculated per group.
5. HAVING SUM(salary) < 4200 → throw away groups that don’t meet this condition.
6. SELECT → output the columns (including aliases).
7. ORDER BY department_id → sort the result.

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
END

SELECT
    e.id,
    e.first_name,
    e.last_name,
    CAST(e.salary AS DECIMAL(18,2)),
    e.department_id,
    CASE
WHEN d.name NOT IN ('Management', 'Kitchen Staff', 'Service Staff')
    THEN 'Other'
ELSE d.name
    END AS department_name

FROM employees as e
JOIN departments as d
ON e.department_id = d.id

>>
CASE department_id
WHEN 1 THEN salary*1.15
WHEN 2 THEN salary*1.10
ELSE salary*1.05
END

>>
UPDATE employees
SET
salary = CASE
WHEN hire_date < '2015-01-16' THEN salary + 2500
WHEN hire_date < '2020-03-04' THEN salary + 1500
ELSE salary
END,
job_title = CASE
WHEN hire_date < '2015-01-16' THEN CONCAT('Senior ', job_title)
WHEN hire_date < '2020-03-04' THEN CONCAT('Mid-', job_title)
ELSE job_title
END;

>>
Nested:
SELECT "first_name", "last_name", "job_title", "salary", "department_id",

CASE
    WHEN salary >= 25000 THEN
        CASE
            WHEN job_title LIKE 'Senior%' THEN 'High-performing Senior'
            ELSE 'High-performing Employee'
        END
    ELSE 'Average-performing'
END AS performance_rating

FROM "employees"

>>
SELECT
SUM(CASE WHEN department_id = 1 THEN 1 ELSE 0 END) AS "Engineering",
SUM(CASE WHEN department_id = 2 THEN 1 ELSE 0 END) AS "Tool Design",
SUM(CASE WHEN department_id = 3 THEN 1 ELSE 0 END) AS "Sales",
SUM(CASE WHEN department_id = 4 THEN 1 ELSE 0 END) AS "Marketing",
SUM(CASE WHEN department_id = 5 THEN 1 ELSE 0 END) AS "Purchasing",
SUM(CASE WHEN department_id = 6 THEN 1 ELSE 0 END) AS "Research and Development",
SUM(CASE WHEN department_id = 7 THEN 1 ELSE 0 END) AS "Production"
FROM employees;


SET
salary = CASE
WHEN hire_date < '2015-01-16' THEN salary + 2500
WHEN hire_date < '2020-03-04' THEN salary + 1500
ELSE salary
END,
job_title = CASE
WHEN hire_date < '2015-01-16' THEN CONCAT('Senior ', job_title)
WHEN hire_date < '2020-03-04' THEN CONCAT('Mid-', job_title)
ELSE job_title
END;

1. INNER JOIN

Returns only the rows that match in both tables.
If there’s no match, the row is not included.

SELECT students.id, students.student_name, exams.exam_name
FROM students
INNER JOIN exams
ON students.id = exams.id;

Here, only students who have matching id in exams will appear.

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table (first table), and the matching rows from the right table.
If there’s no match in the right table, you get NULL for the right table’s columns.

SELECT students.student_name, exams.exam_name
FROM students
LEFT JOIN exams
ON students.id = exams.id;
All students appear. If a student has no exam, exam_name will be NULL.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Opposite of LEFT JOIN: returns all rows from the right table, and matching rows from the left table.
If there’s no match in the left table, you get NULL for the left table’s columns.

4. FULL JOIN (or FULL OUTER JOIN)

Returns all rows from both tables, with NULL where there’s no match.

5. CROSS JOIN

Returns every combination of rows from the two tables.
If Table A has 3 rows and Table B has 4 rows, the result has 3 × 4 = 12 rows.

>>>
JOIN customers AS c
USING customer_id -> WRONG

JOIN customers AS c
USING (customer_id) -> RIGHT

SELECT TRUNC(123.456); -- Result: 123
SELECT TRUNC(123.456, 2); -- Result: 123.45
SELECT TRUNC(-123.456, 1); -- Result: -123.4
SELECT TRUNC(123.456, -1); -- Result: 120

SELECT DATE_TRUNC('year', CURRENT_TIMESTAMP); -- Jan 1, 00:00:00 of current year
SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP); -- 1st of month, 00:00:00
SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP); -- Today at 00:00:00
SELECT DATE_TRUNC('hour', CURRENT_TIMESTAMP); -- Current hour, minutes and seconds zeroed

In Oracle SQL, DUAL is a special one-row, one-column table that exists by default in every Oracle database. It’s mainly used when you need to select a value or evaluate an expression without querying a real table. The column is named DUMMY and contains the value 'X'.

SELECT 1 + 1 FROM dual;
-- Result: 2

In PostgreSQL, there is no DUAL table like in Oracle. PostgreSQL allows you to select expressions or call functions without specifying a table at all.

SELECT 1 + 1; -- Result: 2

COALESCE() is a SQL function used to return the first non-NULL value from a list of expressions. It’s very useful when you want to handle NULLs gracefully in queries.

SELECT COALESCE(NULL, NULL, 'Hello', 'World');
--Hello

COALESCE(country, 'Unknown')
--If country is NOT NULL, it returns the value of country.
--If country is NULL, it returns 'Unknown'.

>>
COALESCE() is not for conditional logic—it’s only for replacing NULLs.

Bad example:
COALESCE(COUNT(salary) > 1000, 'Rich')
--COUNT(salary) returns a number.
--COUNT(salary) > 1000 is a boolean expression (true/false in some SQL dialects, 1/0 in others).
--COALESCE() is meant to return the first non-NULL value, but COUNT(salary) > 1000 is almost never NULL—it will be either true/false or 1/0. So COALESCE() won’t behave the way you want here.

In SQL, you can’t use the alias (age_group) inside the same SELECT for aggregation, hence this lame repeated block:

SELECT
CASE
WHEN age BETWEEN 11 AND 20 THEN '[11-20]'
WHEN age BETWEEN 21 AND 30 THEN '[21-30]'
WHEN age BETWEEN 31 AND 40 THEN '[31-40]'
WHEN age BETWEEN 41 AND 50 THEN '[41-50]'
WHEN age BETWEEN 51 AND 60 THEN '[51-60]'
ELSE '[61+]'
END AS age_group,
COUNT(*) AS group_count
FROM wizard_deposits
GROUP BY
CASE
WHEN age BETWEEN 11 AND 20 THEN '[11-20]'
WHEN age BETWEEN 21 AND 30 THEN '[21-30]'
WHEN age BETWEEN 31 AND 40 THEN '[31-40]'
WHEN age BETWEEN 41 AND 50 THEN '[41-50]'
WHEN age BETWEEN 51 AND 60 THEN '[51-60]'
ELSE '[61+]'
END
ORDER BY age_group;

It can be resolved with CTE:

WITH age_buckets AS (
SELECT
CASE
WHEN age BETWEEN 11 AND 20 THEN '[11-20]'
WHEN age BETWEEN 21 AND 30 THEN '[21-30]'
WHEN age BETWEEN 31 AND 40 THEN '[31-40]'
WHEN age BETWEEN 41 AND 50 THEN '[41-50]'
WHEN age BETWEEN 51 AND 60 THEN '[51-60]'
ELSE '[61+]'
END AS age_group
FROM wizard_deposits
)
SELECT age_group, COUNT(*) AS group_count
FROM age_buckets
GROUP BY age_group
ORDER BY age_group;

>>>

WITH
    results
AS (
    SELECT
        c.country_name,
        COALESCE(p.peak_name, '(no highest peak)') AS highest_peak_name,
        COALESCE(p.elevation, 0) AS highest_peak_elevation,
        COALESCE(m.mountain_range, '(no mountain)') AS mountain,
        ROW_NUMBER() OVER (PARTITION BY c.country_name ORDER BY p.elevation DESC) AS row_num
    FROM
        countries AS c
    LEFT JOIN
        mountains_countries AS mc
    USING
        (country_code)
    LEFT JOIN
        peaks AS p
    USING
        (mountain_id)
    LEFT JOIN
        mountains AS m
    ON
        m.id = p.mountain_id
)


SELECT
    country_name,
    highest_peak_name,
    highest_peak_elevation,
    mountain
FROM
    results
WHERE
    row_num = 1
ORDER BY
    country_name ASC,
    highest_peak_elevation DESC;

When you transform row values into columns, that operation is generally called a pivot in SQL.

SELECT
SUM(CASE WHEN department_id = 1 THEN 1 ELSE 0 END) AS "Engineering",
SUM(CASE WHEN department_id = 2 THEN 1 ELSE 0 END) AS "Tool Design",
SUM(CASE WHEN department_id = 3 THEN 1 ELSE 0 END) AS "Sales",
SUM(CASE WHEN department_id = 4 THEN 1 ELSE 0 END) AS "Marketing",
SUM(CASE WHEN department_id = 5 THEN 1 ELSE 0 END) AS "Purchasing",
SUM(CASE WHEN department_id = 6 THEN 1 ELSE 0 END) AS "Research and Development",
SUM(CASE WHEN department_id = 7 THEN 1 ELSE 0 END) AS "Production"
FROM employees;

>>>
SQL Server/ Oracle (Postgre does not have PIVOT()):

SELECT *
FROM (
SELECT employee_id, department_code
FROM employees
) AS src
PIVOT (
COUNT(employee_id)
FOR department_code IN ([1],[2],[3])
) AS pvt;

CREATE TABLE employees_projects (
id SERIAL PRIMARY KEY,
employee_id INT NOT NULL,
project_id INT NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees(id),
FOREIGN KEY (project_id) REFERENCES projects(id)
);

Foreign Key points to a column in another table (usually that table’s primary key).
You define it as a normal column, then add the FOREIGN KEY constraint.

>>
In Postgre SQL we can reference upon creation:

mountain_id INT REFERENCES mountains --(id by default)

>>>
We can name constraints like this:
CREATE TABLE peaks (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(50),
mountain_id INT NOT NULL,
CONSTRAINT fk_peaks_mountains
FOREIGN KEY (mountain_id) REFERENCES mountains(id)
);

>> NOT NULL placement
brand_id INT REFERENCES brands(id) NOT NULL ON DELETE CASCADE ON UPDATE CASCADE, -> WRONG
brand_id INT NOT NULL REFERENCES brands(id) ON DELETE CASCADE ON UPDATE CASCADE, -> RIGHT

~    Matches regex (case-sensitive)
~*    Matches regex (case-insensitive)
!~    Does NOT match regex (case-sensitive)
!~*    Does NOT match regex (case-insensitive)

-- Case-sensitive match
SELECT 'Hello123' ~ '^[A-Z][a-z]+[0-9]+$'; -- true

-- Case-insensitive match
SELECT 'hello123' ~* '^[A-Z][a-z]+[0-9]+$'; -- true

-- Negation
SELECT 'Hello123' !~ '[0-9]$'; -- false


1. REGEXP_MATCHES

    SELECT REGEXP_MATCHES('abc123xyz', '[0-9]+');
    -- {123}

Can return multiple matches with the g (global) flag:

    SELECT REGEXP_MATCHES('abc123xyz456', '[0-9]+', 'g');
    -- {123}, {456}

2. REGEXP_REPLACE

    SELECT REGEXP_REPLACE('My phone is 408-123-4567', '[0-9]', 'X', 'g');
    -- 'My phone is XXX-XXX-XXXX'
'g' = global replacement (all matches).
Without 'g', only the first match is replaced.

3. REGEXP_SPLIT_TO_ARRAY
    SELECT REGEXP_SPLIT_TO_ARRAY('apple,banana;cherry', '[,;]');
    -- {apple,banana,cherry}

4. REGEXP_SPLIT_TO_TABLE
Splits a string into multiple rows instead of an array.
    SELECT REGEXP_SPLIT_TO_TABLE('apple,banana;cherry', '[,;]');
    -- apple
    -- banana
    -- cherry

PRIMARY KEY -> Uniquely identifies each row. Implies NOT NULL. -> id INT PRIMARY KEY
FOREIGN KEY -> Ensures a column’s values match values in another table (referential integrity) -> customer_id INT REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE
UNIQUE -> Ensures all values in a column (or combination of columns) are distinct -> email VARCHAR(50) UNIQUE
CHECK -> Ensures values meet a condition -> age INT CHECK (age >= 18)
NOT NULL -> Ensures a column cannot be NULL -> name VARCHAR(50) NOT NULL
DEFAULT -> Assigns a default value if none is provided -> status VARCHAR(10) DEFAULT 'active'


a) Foreign Key

Foreign Key points to a column in another table (usually that table’s primary key).
You define it as a normal column, then add the FOREIGN KEY constraint.

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

ON DELETE/UPDATE actions:

NO ACTION    Default. Don’t allow the change if it breaks the foreign key.
RESTRICT    Same as NO ACTION, but checked immediately.
CASCADE    Automatically update/delete the child row when the parent is updated/deleted.
SET NULL    Set the foreign key in the child row to NULL if the parent row is deleted/updated.
SET DEFAULT    Set the foreign key in the child row to its default value if the parent row changes.

When crateing a table:

passport_id INT REFERENCES passports(id) --> CORRECT
passport_id REFERENCES passports(id) --> CORRECT --> INCORRECT
passport_id INT FOREIGN KEY REFERENCES passports(id); --> INCORRECT

The FOREIGN KEY keyword is only allowed in table-level constraints, not inline.

department_id INT,
CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id) --> CORRECT

b) Unique

Copy code
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

c) Check

ALTER TABLE employees
ADD CONSTRAINT check_name_length CHECK (char_length(name) > 2);

Each ADD CONSTRAINT must be its own ALTER TABLE.

ALTER TABLE countries
ADD CONSTRAINT fk_currency_code
FOREIGN KEY (currency_code)
REFERENCES currencies(currency_code)
ON DELETE CASCADE;

ALTER TABLE countries
ADD CONSTRAINT fk_continent_code
FOREIGN KEY (continent_code)
REFERENCES continents(continent_code)
ON DELETE CASCADE;

>>>

customer_id INT REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE ---> WRONG

Inline foreign key definition (inside the column definition) does not always support ON UPDATE and ON DELETE actions in all databases.
Most SQL databases (PostgreSQL, MySQL, etc.) require the more explicit CONSTRAINT syntax if you want to define actions like ON DELETE SET NULL or ON UPDATE CASCADE.

customer_id INT,
CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE SET NULL
ON UPDATE CASCADE ---> CORRECT

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO customers (customer_name)
VALUES
('BlueBird Inc'),
('Dolphin LLC');


Insert into all columns without specifying column names:

INSERT INTO students
VALUES (1, 'Alice');

SERIAL: Old-style PostgreSQL pseudo-type. Automatically creates a sequence behind the scenes. You cannot directly set START WITH or INCREMENT BY in the column definition; you have to modify the sequence manually.

GENERATED AS IDENTITY: SQL standard-compliant. Supports options like START WITH and INCREMENT BY directly.

CREATE TABLE students (
id INT GENERATED ALWAYS AS IDENTITY
(START WITH 101 INCREMENT BY 1) PRIMARY KEY,
student_name VARCHAR(50)
);
START WITH 101 → The first value of id will be 101.
INCREMENT BY 1 → Each new row increases by 1 (can be any number).

CREATE TABLE students (
id INT GENERATED ALWAYS AS IDENTITY
(START WITH 100 -- first value
INCREMENT BY 5 -- step size
MINVALUE 100 -- minimum value allowed
MAXVALUE 200 -- maximum value allowed
CYCLE) -- when max is reached, restart from min
PRIMARY KEY,
student_name VARCHAR(50) NOT NULL
);

START WITH    The first value assigned to the column (here: 100)
INCREMENT BY    How much the value increases with each new row (here: 5 → 100, 105, 110…)
MINVALUE    Minimum allowed value (here: 100)
MAXVALUE    Maximum allowed value (here: 200)
CYCLE    After reaching MAXVALUE, the sequence restarts at MINVALUE
NO CYCLE    Default behavior; sequence stops at MAXVALUE instead of restarting

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);

CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(100)
);

--MAPPING TABLE
CREATE TABLE student_courses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

This table connects students and courses.

The composite primary key here is:
PRIMARY KEY (student_id, course_id)

That means:
The combination of student_id and course_id must be unique.
A student can’t be enrolled in the same course twice.

COUNT(DISTINCT m.mountain_range)

COUNT(...) → counts the number of rows
DISTINCT → ensures duplicates are removed before counting

If you had a countries table and wanted to know how many unique mountain ranges per country:

SELECT country, COUNT(DISTINCT mountain_range) AS unique_ranges
FROM mountains
GROUP BY country;

>>>
SELECT country_code
from countries
LEFT JOIN countries_rivers
USING (country_code)
LEFT JOIN rivers
ON rivers.id = countries_rivers.river_id
GROUP BY country_code
HAVING COUNT(rivers.river_name) >= 3


COUNT(rivers.river_name) counts how many rivers are linked to each country.

HAVING COUNT(r.river_name) >= 3 filters to only those countries with at least 3 rivers.

COUNT() in SQL doesn’t count globally — it counts within the group defined by GROUP BY.

So, COUNT(rivers.river_name) gives you the number of non-null rivers.river_name values in each group (i.e., for that country).

It does not count all rivers in the entire dataset.
>>>

1. Relation
→ A table in the relational database sense.
A relation is a mathematical concept from relational algebra (the theory behind SQL).
In practice, it means a table with:
    Rows (called tuples)
    Columns (called attributes)
    Each row represents one record.
    Each column represents a property of that record.

2. Relationship (One-to-One, One-to-Many, Many-to-Many)
→ A connection between two (or more) relations.
A relationship describes how tables (relations) are linked to each other.

It’s part of data modeling — in ER diagrams, relationships connect entities.

CREATE SEQUENCE my_sequence
START WITH 100
INCREMENT BY 10;

START WITH 100    First number generated will be 100
INCREMENT BY 10    Each next number increases by 10: 100, 110, 120, …


INSERT INTO employees (emp_id, name)
VALUES (my_sequence.NEXTVAL, 'Alice');

SERIAL and GENERATED AS IDENTITY are also called sequences.

In PostgreSQL, the SERIAL type does not support START WITH or INCREMENT BY directly — those options are only available with GENERATED ... AS IDENTITY.

CREATE TABLE employees (
emp_id INT GENERATED ALWAYS AS IDENTITY
(START WITH 100 INCREMENT BY 10),
name VARCHAR(100)
);

SELECT
MIN(average) AS min_average_area
FROM (
    SELECT
        AVG(area_in_sq_km) AS average
        FROM countries
        GROUP BY continent_code ) AS average_area

The alieses are mandatory in older versions.

Inner query → calculates average area per continent.
Outer query → finds the minimum among these averages.
Use of subquery is necessary because you need to aggregate twice:
First by continent (AVG)
Then across all continents (MIN)

>>>

UPDATE monasteries
SET three_rivers = TRUE
WHERE country_code IN (
    SELECT country_code AS CC
    from countries
    LEFT JOIN countries_rivers
    USING (country_code)
    LEFT JOIN rivers
    ON rivers.id = countries_rivers.river_id
    GROUP BY country_code
    HAVING COUNT(rivers.river_name) >= 3)

The outer UPDATE checks monasteries.country_code against the subquery.

For any monastery in a country with 3+ rivers, three_rivers is set to TRUE

>>>

UPDATE countries
SET three_rivers = (
SELECT COUNT(*) >= 3
FROM countries_rivers AS cr
WHERE cr.country_code = countries.country_code
);

For each row in countries, the subquery:
    Counts how many rows exist in countries_rivers for that country.
    Evaluates COUNT(*) >= 3 → returns true or false.
    That boolean value is assigned to countries.three_rivers.
    No grouping needed — it’s evaluated per row (correlated subquery).


three_rivers = FALSE     -> Does not match null, only rows that are explicitly FALSE
three_rivers IS FALSE    -> Does not match null, only rows that are explicitly FALSE
three_rivers IS NOT TRUE    -> Does match null
NOT tree_rivers -> Does match null

>>

CREATE VIEW
    continent_currency_usage
AS
SELECT
    ra.continent_code,
    ra.currency_code,
    ra.currency_usage
FROM (
    SELECT
        ct.continent_code,
        ct.currency_code,
        ct.currency_usage,
        DENSE_RANK() OVER (PARTITION BY ct.continent_code ORDER BY ct.currency_usage DESC) AS ranked_usage
    FROM (
        SELECT
            continent_code,
            currency_code,
            COUNT(currency_code) AS currency_usage
        FROM
            countries
        GROUP BY
            continent_code,
            currency_code
        HAVING
            COUNT(currency_code) > 1
    ) AS ct
) AS ra
WHERE
    ra.ranked_usage = 1
ORDER BY
    ra.currency_usage DESC;

1ROW_NUMBER()
Assigns a unique sequential number to each row within a partition (or entire table if no partition).
No ties — each row gets a different number even if values are the same.

Copy code
SELECT
country, population,
ROW_NUMBER() OVER (ORDER BY population DESC) AS row_num
FROM countries;

country    population     row_num
A    1000000     1
B    800000     2
C    800000     3
D    500000     4


2️RANK()
Assigns a rank based on the ordering of a column.
Tied values get the same rank, but gaps appear after ties.

SELECT
country, population,
RANK() OVER (ORDER BY population DESC) AS rnk
FROM countries;

country    population     rnk
A    1000000     1
B    800000     2
C    800000     2
D    500000     4


3 DENSE_RANK()
Similar to RANK(), but no gaps after ties.
Tied values get the same rank, next distinct value gets the next consecutive rank.

SELECT
country, population,
DENSE_RANK() OVER (ORDER BY population DESC) AS dense_rnk
FROM countries;

country    population     dense_rnk
A    1000000     1
B    800000     2
C    800000     2
D    500000     3


ROW_NUMBER → when you need a unique identifier for each row.

RANK → when you want competition-style ranking (like 1st, 2nd, 2nd, 4th).

DENSE_RANK → when you want ranking without skipping numbers (1, 2, 2, 3).

SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
schemaname = 'public'
ORDER BY
tablename ASC,
indexname ASC;

pg_indexes is a PostgreSQL system view that stores metadata about all indexes in the database.
schemaname = 'public' filters only indexes belonging to the public schema.
ORDER BY tablename ASC, indexname ASC ensures the sorting order matches the requirement — first by table name, then by index name alphabetically.

1 What an Index Is
An index is a database structure that speeds up data retrieval.

Common uses:
    WHERE filters
    ORDER BY
    JOIN conditions
    GROUP BY queries

2 Types of Indexes in PostgreSQL

a) B-tree Index (default)

Most commonly used.
Maintains sorted order of the indexed column.
Supports:
=, <, <=, >, >=

ORDER BY optimization
Default for PRIMARY KEY and UNIQUE.

b) Hash Index
Optimized for equality comparisons (=).

Faster than B-tree for equality, but:
Cannot be used for range queries (<, >)
Less commonly used in practice

c) GIN (Generalized Inverted Index)
Optimized for array, JSON, full-text search.
Great for containment queries, e.g., WHERE column @> '{value}'.

d) GiST (Generalized Search Tree)
Flexible, supports geometric data types and full-text search.
Can handle range, nearest neighbor, and other complex queries.

e) BRIN (Block Range Index)
Very small and fast for large, naturally ordered tables.
Stores summary info per block instead of per row.
Good for time-series or sequential data.

3 Clustered Index
PostgreSQL does not automatically cluster tables like SQL Server or MySQL.
A clustered index in PostgreSQL is actually a table physically reordered according to an index.

CLUSTER table_name USING index_name;

Reorders table rows on disk to match the index order.

Makes range queries and sequential scans faster.
Note: PostgreSQL does not maintain clustering automatically.
If you insert new rows later, they may not follow the clustered order.
You need to CLUSTER again manually if desired.

Key: In PostgreSQL, every index is technically separate from the table. Clustered indexes only affect physical storage order, not logical indexing.

>>
CREATE INDEX first_name_idx ON people (first_name)

Type: B-tree
Single-column: Indexes only first_name.
Column: first_name
Unique: No (unless you add UNIQUE)
Separate structure: The index is stored independently from the table data.
Not clustered: PostgreSQL does not automatically cluster tables. Rows in people are not reordered on disk unless you use CLUSTER.
Purpose: Speeds up equality and range queries (=, <, >, etc.), ORDER BY, JOIN, and GROUP BY operations.

>>>
Three Main Types of Functions (by return type)

1. Scalar Function    
Returns a single value (one number, text, date, etc.)    
LENGTH('hello') → 5

2. Aggregate Function
Returns one value per group of rows    
AVG(salary) or SUM(amount)

3. Table-Valued Function (Set-Returning Function)    
Returns a set of rows (like a table)    
generate_series(1,5) or custom RETURNS TABLE(...)

>>>
In PostgreSQL, a function declared with RETURNS void means:
It performs some work (like logging, inserting, updating) but doesn’t return any data.

CREATE OR REPLACE FUNCTION log_employee_action(emp_id INT, action TEXT)
RETURNS void
AS $$
BEGIN
INSERT INTO employee_log(employee_id, action, action_time)
VALUES (emp_id, action, NOW());
END;
$$ LANGUAGE plpgsql;

You would call it like this:
SELECT log_employee_action(101, 'Promoted');

or inside a trigger:
PERFORM log_employee_action(OLD.id, 'Deleted');

Only in plpgsql, cannot be used in plain SQL
That’s why you use CASE inside expressions — it’s the SQL way of doing conditional logic.


UPDATE reviews
SET rating = CASE
WHEN item_id = customer_id THEN 10.0
WHEN customer_id > item_id THEN 5.5
ELSE rating
END;

PL/pgSQL (procedural SQL — used inside functions or DO blocks). This is PostgreSQL’s procedural language, used inside CREATE FUNCTION or DO blocks.

DO $$
BEGIN
IF EXISTS (SELECT 1 FROM reviews WHERE item_id = customer_id) THEN
UPDATE reviews
SET rating = 10.0
WHERE item_id = customer_id;
ELSIF EXISTS (SELECT 1 FROM reviews WHERE customer_id > item_id) THEN
UPDATE reviews
SET rating = 5.5
WHERE customer_id > item_id;
END IF;
END $$;

INVALID:

DELETE FROM customers
AS
SELECT customers.id FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
WHERE orders.id IS NULL
This fails because:

DELETE FROM ... AS SELECT ... is not valid PostgreSQL syntax.
You can’t use SELECT directly after DELETE.

VALID:

DELETE FROM customers
USING orders
WHERE customers.id = orders.customer_id
AND orders.id IS NULL;

❌ BUT — this specific version won’t work as expected because orders.id IS NULL can’t be checked like that inside a USING join (since orders.id will never be null in the joined table).
So this is not ideal for “no match” situations.


DELETE FROM customers
WHERE NOT EXISTS (
SELECT 1
FROM orders
WHERE orders.customer_id = customers.id
);

___________

BEGIN    
Starts a new transaction block.

COMMIT    
Saves all changes made in the current transaction.

ROLLBACK    
Cancels all changes made in the current transaction.

SAVEPOINT name    
Sets a point inside a transaction you can roll back to.

ROLLBACK TO name    
Undoes changes back to a specific savepoint.

RELEASE SAVEPOINT name    
Removes a savepoint (can’t roll back to it anymore).

SET TRANSACTION    
Sets options like isolation level or read/write mode.

END    
Same as COMMIT (alias).
>>>

BEGIN;

SAVEPOINT before_update;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Suppose we detect an issue:
ROLLBACK TO before_update;

COMMIT;

CREATE OR REPLACE FUNCTION trigger_fn_insert_new_entry_into_logs()
RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO
        logs(account_id, old_sum, new_sum)
    VALUES
        (OLD.id, OLD.balance, NEW.balance);
        
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER    
    tr_account_balance_change
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
WHEN
    (NEW.balance <> OLD.balance)
EXECUTE FUNCTION
    trigger_fn_insert_new_entry_into_logs();

>
A trigger in PostgreSQL is a piece of code that runs automatically when a specific event happens on a table — for example, when a row is:
INSERTED
UPDATED
DELETED

Triggers are often used for:
Automatic logging
Validation before changes
Keeping audit trails
Enforcing business rules

In an INSERT trigger: only NEW exists.
In a DELETE trigger: only OLD exists.
In an UPDATE trigger: both exist — OLD shows the original row, NEW shows the modified version.

Why You Need RETURN NEW (or RETURN OLD)
In a trigger function, PostgreSQL expects you to return a record that represents what should happen to the row being affected.

BEFORE INSERT / BEFORE UPDATE    RETURN NEW;    Tells PostgreSQL to continue with the new (possibly modified) row.
BEFORE DELETE    RETURN OLD;    The deleted row is being removed — you return the old one to confirm deletion.
AFTER triggers    RETURN NEW; (or nothing meaningful)    Row is already changed — return value is ignored but required syntactically.

If you return the wrong thing (like returning NULL or OLD when PostgreSQL expects NEW), your trigger can become useless — or even break your data logic entirely.

CREATE TABLE deleted_employees (
employee_id SERIAL PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
middle_name VARCHAR(20),
job_title VARCHAR(50),
department_id INTEGER,
salary NUMERIC(19,4)
);

CREATE OR REPLACE FUNCTION deleted_employees_backup()
RETURNS TRIGGER
AS
$$
BEGIN
    INSERT INTO deleted_employees(first_name, last_name, middle_name, job_title, department_id, salary)
    VALUES (old.first_name, old.last_name, old.middle_name, old.job_title, old.department_id, old.salary);
    RETURN OLD;

END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER backup_deleted_employees
AFTER DELETE ON employees
FOR EACH ROW EXECUTE PROCEDURE deleted_employees_backup();


FOR EACH ROW    
The trigger fires once for each row affected by the operation. You can access OLD and NEW for that specific row.

FOR EACH STATEMENT    
The trigger fires once per SQL statement, regardless of how many rows are affected. You cannot access OLD or NEW because multiple rows may have changed.

1. BEFORE Trigger
Runs before the triggering operation (INSERT, UPDATE, DELETE).
Often used to validate or modify data before it’s written.

You can modify the row by changing NEW, or cancel the operation by returning NULL.
Example: Ensure salary is positive before insert

CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.salary < 0 THEN
RAISE EXCEPTION 'Salary cannot be negative';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION check_salary()

2. AFTER Trigger
Runs after the operation is complete.
Often used for logging, auditing, or cascading actions.
Can’t prevent the operation (it’s already done), but you can react to it.
Example: Backup deleted employees

CREATE TRIGGER after_delete_employee
AFTER DELETE ON employees
FOR EACH ROW
EXECUTE FUNCTION deleted_employees_backup();

3. INSTEAD OF Trigger (only on views)
Runs instead of the operation.
Only available for views, because views can’t normally be directly modified.
Lets you simulate inserts, updates, or deletes on views by performing operations on underlying tables.
Example: Update a view instead of the underlying tables

CREATE OR REPLACE VIEW employee_view AS
SELECT id, first_name, last_name, department_id FROM employees;

CREATE OR REPLACE FUNCTION instead_of_update_employee()
RETURNS TRIGGER AS $$
BEGIN
UPDATE employees
SET first_name = NEW.first_name,
last_name = NEW.last_name,
department_id = NEW.department_id
WHERE id = OLD.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER view_update_trigger
INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
EXECUTE FUNCTION instead_of_update_employee();

Now updating the view actually updates the underlying table.

SELECT INITCAP('hello world');
-- Output: Hello World

SELECT INITCAP('pOstGreSQL is awEsome');
-- Output: Postgresql Is Awesome

SELECT INITCAP('john doe');
-- Output: John Doe

Use POWER(base, exponent) in SQL because the ** operator does not work in all databases and can fail with certain numeric types, whereas POWER() is standard and works reliably across PostgreSQL, MySQL, SQL Server, and Oracle.

DO $$
DECLARE
i INT := 0;
BEGIN
WHILE i < 5 LOOP
RAISE NOTICE 'i = %', i;
i := i + 1;
END LOOP;
END $$;

DO $$
DECLARE
i INT;
BEGIN
FOR i IN 0..4 LOOP
RAISE NOTICE 'i = %', i;
END LOOP;
END $$;

:= in PostgreSQL (PL/pgSQL)
:= is the assignment operator.
It’s used to give a value to a variable inside a PL/pgSQL block.

Unlike = (used in SQL comparisons), := is specifically for variable assignment.


Declare and initialize a variable:

DO $$
DECLARE
counter INT := 0; -- initialize with 0
BEGIN
RAISE NOTICE 'Counter = %', counter;
END $$;