Introduction
What is Data?
Database
DBMS & RDBMS
What is PostgreSQL
History
Comparison with Different Databases
Installation
& Configuration
Download and Installation
Connecting pgAdmin
Database
Creating Database
Creating Schema
Grant Privileges
Connecting to DB
Data
Types
Primitives: Integer, Numeric, String, Boolean
Structured Date/Time, Array, Range / Multirange, UUID
Document: JSON/JSONB, XML, Key-value (Hstore)
Comments
Single line comment --
Multiline comment /*….*/
Data Definition Language (DDL)
Deep Dive
CREATE TABLE: Data types (beyond standard: SERIAL, UUID, JSONB, arrays, TIMESTAMPTZ).
Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL.
ALTER TABLE: Adding/dropping columns, changing types.
DROP TABLE vs. TRUNCATE TABLE.
Data Manipulation Language (DML)
Mastery
INSERT, UPDATE, DELETE statements.
RETURNING clause: The PostgreSQL superpower for developers.
UPSERT (INSERT ... ON CONFLICT ... DO UPDATE): Handle conflicts gracefully.
Querying Data (DQL)
SELECT: Filtering (WHERE), sorting (ORDER BY), limiting (LIMIT/OFFSET).
Joins: INNER JOIN, LEFT/RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN.
Aggregation and Grouping: GROUP BY, HAVING, aggregate functions (COUNT, SUM, STRING_AGG).
Window Functions: ROW_NUMBER(), RANK(), LAG()/LEAD() for advanced analytics without sub-queries.
Operators
Comparison Operators (=,!=,<,>,>=,<=)
Arithmetic Operators (+,-,*,/)
SQL*PLUS operators : Between, Not Between, IN, NOT IN, IS NULL, IS NOT NULL, REG EXPRESSIONS
Constraints
Primary Key Unique + Not Null, table identifier
Foreign Key /Reference Key References another table’s column
Unique Key Allow Null values and restrict Duplicates
Not Null Allow duplicate values and not allow null values
Check Constraints Applying condition
Default It is not constraint
EXCLUSION CONSTRAINT Prevents overlapping/range conflicts
Functions
General Functions
UPPER
Lower
Case
Decode
NULLIF
COALESCE
Length
INITCAP
Character Functions
UPPER
LOWER
INITCAP
SUBSTRING(SUBSTR)
INSTRING(INSTR)
PADDING LPAD RPAD
TRIM LTRIM RTRIM
REPLACE
TRANSLATE
Numeric Functions
Round
TRUNC
CEIL
FLOOR
MODULE
POWER
SQUARE ROOT (SQRT)
ABSOLUTE
Date Function
CURRENT_DATE
CURRENT_TIME, current_timestamp, , clock_timestamp
NOW(), age, JUSTIFY_DAYS
EXTRACT (YEAR, MONTH, DAY, HOUR)
Intervals (add, Subtracting), Operators (Adding, Subtracting)
Conversion Functions
TO_CHAR
TO_DATE()
TO_TIMESTAMP()
CAST
Aggregation Functions /
Group Functions
SUM, MIN, MAX, AVG, COUNT
array_agg, json_agg, jsonb_object_agg
Conditional Expressions
CASE
COALESCE
NULLIF
GREATEST and LEAST
JOINS
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CORSS JOIN
SUB Queries
EXISTS
Recursive query
SELECT IN WITH
SET OPERATORS
UNION ALL
UNION
INTERSECT
MINUS
VIEWS
View, Materialized views
Managing Views
Index
B-tree, Hash, GiST, SP-GiST, GIN, and BRIN
SYSTEM COLUMNS
oid, tableoid, xmin, cmin, xmax, cmax, ctid, LIMIT, SERIAL
Analytical Functions
RANK
CUME_DIST
FIRST_VALUE
LAST_VALUE
LAG
LEAD
ROW_NUMBER()
Sequences
SYNONYMS
Query Optimization
Interview Question and
Answers
MOCK Interview
Resume Preparations