Python / Java (Imperative): You write every loop, step, and algorithm.

SQL (Declarative): You state the desired outcome — the engine compiles the optimal execution plan.

⚡ YOUR FIRST QUERY

SELECT 'Hello, SQL!';

▶

SELECT 100 * 1.18 AS calculator;
SELECT current_timestamp AS clock;
SELECT version() AS system_inspector;

▶

-- Correct termination:
SELECT 42;

-- What happens without semicolon in psql:
retailmart=# SELECT 42
retailmart-# ; -- The dash (# -> -#) indicates continuation line!

▶

-- Without alias: column header defaults to 'current_date'
SELECT current_date;

-- With alias: column header cleanly displays as 'today'
SELECT current_date AS today;

▶

-- Fast math without opening Python or a calculator:
SELECT 100 * 1.18 AS price_with_tax;

-- Test string/date functions before touching production tables:
SELECT UPPER('theshiraverse') AS preview;

-- Generate instant mock sequences on the fly:
SELECT generate_series(1, 5) AS sample_seq;

STEP 1

-- Check active DB, user, host & port:
retailmart=# \conninfo

-- List all databases on the cluster:
retailmart=# \l

-- Switch database connection:
retailmart=# \c retailmart

STEP 2

-- List all 16 schema namespaces:
retailmart=# \dn

-- Inspect tables in specific schema:
retailmart=# \dt sales.*

-- Dump all 55 tables across all schemas:
retailmart=# \dt *.*

▶

-- Describe table columns & datatypes:
retailmart=# \d sales.orders

-- Extended details (indexes, storage):
retailmart=# \d+ sales.orders

-- ALWAYS qualify with schema + LIMIT:
SELECT * FROM sales.orders LIMIT 10;

▶

Data Query Language • Read-Only

-- Inspect customer directory:
SELECT customer_id, first_name, email 
FROM customers.customers 
LIMIT 10;

DDL

Data Definition Language • Blueprints

-- Define table blueprint:
CREATE TABLE college.students (
  student_id TEXT,
  first_name TEXT,
  email      TEXT
);

DML

Data Manipulation Language • Contents

-- Populate row inside table:
INSERT INTO college.students 
VALUES ('1', 'Aarav', 'aarav@edu');

-- Mutate row with condition:
UPDATE college.students 
SET email = 'aarav.s@edu' 
WHERE student_id = '1';

Interview Trap Why TRUNCATE is DDL, NOT DML:

Even though TRUNCATE deletes all data rows from a table, it is classified as DDL. DML (DELETE) scans rows one-by-one and fires triggers. TRUNCATE bypasses row scanning entirely — it drops the underlying disk storage segment and re-initializes an empty table structure in 1 millisecond at the engine metadata level.

PEDAGOGICAL PHILOSOPHY

CREATE TABLE college.students (
  student_id    TEXT,  -- Treated as '1', '2', etc.
  first_name    TEXT,
  email         TEXT,
  date_of_birth TEXT,  -- Stored as '2004-03-15'
  is_active     TEXT   -- Stored as 'true' / 'false'
);

WHY IT IS DESIGNED THIS WAY

CONTAINER 01 • CLUSTER LEVEL

-- Create a brand new database plot:
CREATE DATABASE college_db;

-- Verify on cluster & connect:
retailmart=# \l
retailmart=# \c college_db

CONTAINER 02 • NAMESPACE LEVEL

-- Create domain folder for academic data:
CREATE SCHEMA IF NOT EXISTS college;

-- Create domain folder for supply chain:
CREATE SCHEMA IF NOT EXISTS supply;

-- Verify schemas inside database:
college_db=# \dn

ENGINEERING STANDARDS • 5 RULES

Rule	Standard (Do This)	Anti-Pattern (Avoid)	Why It Matters
Casing	snake_case	CamelCase, UPPER	Postgres folds unquoted to lower
Table Names	students, orders (Plural)	student, order (Singular)	Tables represent row collections
Column Names	email, city (Singular)	emails, cities (Plural)	Each cell holds a single attribute
Primary Keys	student_id, order_id	id	Unambiguous in complex multi-JOINs
Keywords	order_date, booked_at	order, date, user	Reserved words cause parser syntax errors

CI/CD • AUTOMATION STANDARD

-- ❌ Without Guard (Fragile Pipeline):
CREATE TABLE college.students ( student_id TEXT );
-- Run 1: SUCCESS
-- Run 2: ERROR: relation "college.students" already exists! (Pipeline Crashes)

-- ✅ With Guard (Production Idempotent Standard):
CREATE TABLE IF NOT EXISTS college.students (
  student_id TEXT, first_name TEXT
);
-- Run 1: SUCCESS
-- Run 2: NOTICE: relation "college.students" already exists, skipping.

TIER 1 • TABLE 🎯

DROP TABLE

-- Safe drop:
DROP TABLE IF EXISTS 
  shop.products;

TIER 2 • DOMAIN ☣️

DROP SCHEMA CASCADE

-- Safety lock blocks drop:
DROP SCHEMA shop; -- ❌ ERROR!

-- Nuclear bypass:
DROP SCHEMA IF EXISTS 
  shop CASCADE;

TIER 3 • CLUSTER ☠️

DROP DATABASE

-- Cannot drop connected db:
\c postgres
DROP DATABASE IF EXISTS 
  old_test_db;

DDL • DEMOLITION 💥

DROP TABLE

DROP TABLE shop.orders;

DDL • STORAGE RESET 🧹

TRUNCATE TABLE

TRUNCATE shop.orders;

DML • ROW MANIPULATION ✂️

DELETE FROM

DELETE FROM shop.orders WHERE order_status = 'Cancelled';

DML PATTERNS

-- 1. Targeted single-column update:
UPDATE college.students
SET email = '[email protected]'
WHERE student_id = '1';

-- 2. Modify multiple columns simultaneously:
UPDATE college.students
SET is_active = 'false', 
    last_name = 'Sharma-Verma'
WHERE student_id = '3';

-- 3. RETURNING confirms change instantly:
UPDATE college.students
SET is_active = 'true'
WHERE student_id = '3'
RETURNING student_id, first_name, is_active;

DISASTER PREVENTION

UPDATE college.students
SET email = '[email protected]';
-- UPDATE 4 (every student now has Aarav's email!)

UPDATE college.students
SET email = '[email protected]'
WHERE student_id = '1'
RETURNING student_id, email;
-- UPDATE 1 (Only Aarav modified; confirmed instantly)

DML • SURGICAL PRUNING ✂️

DELETE FROM (Targeted Rows)

-- Step 1: Preview rows first with SELECT
SELECT student_id, first_name 
FROM college.students 
WHERE is_active = 'false';

-- Step 2: Swap to DELETE with RETURNING
DELETE FROM college.students 
WHERE is_active = 'false'
RETURNING student_id, first_name;

DDL • BULK STORAGE RESET ⚡

TRUNCATE TABLE (Instant Empty)

-- Fast bulk empty (0 rows in 5ms):
SELECT count(*) FROM college.students; -- 5,00,000
TRUNCATE TABLE college.students;
SELECT count(*) FROM college.students; -- 0

• DELETE FROM table; scans all 1 crore rows • takes minutes
• TRUNCATE TABLE; unlinks storage pages • 5 milliseconds

PAIN POINT 01 • ARITHMETIC 🧮

Clumsy Casting (No Direct Math)

-- ❌ FAILS with operator does not exist: text * numeric
UPDATE fees SET amount = amount * 1.1;

-- ⚠️ Clumsy double-cast workaround:
UPDATE fees 
SET amount = (amount::NUMERIC * 1.1)::TEXT;

PAIN POINT 02 • COMPARISON ⚠️

Lexicographical Sort ('1000' < '900')

-- 🚨 Silent bug: returns only '950', drops '1000'!
SELECT * FROM fees WHERE amount > '900';

-- In text comparison: '1000' < '900' evaluates TRUE!
-- Text sorts like a dictionary: "apple" < "banana"

PSQL META • RECON Terminal Shell

\l                 -- list databases
\c retailmart        -- connect to database
\dn                -- list all schemas
\dt sales.*        -- list tables in schema
\d  sales.orders   -- describe structure

DQL • EVALUATION ENGINE Expressions & Tables

SELECT 42;
SELECT 25 * 4 AS total;
SELECT current_timestamp, version();
SELECT * FROM sales.orders LIMIT 10;

▶ Run Query Copy

DDL • ARCHITECTURE Structures

CREATE SCHEMA IF NOT EXISTS college;
CREATE TABLE IF NOT EXISTS college.students (
  student_id TEXT, first_name TEXT
);
ALTER TABLE college.students ADD COLUMN email TEXT;
TRUNCATE TABLE college.students;

DML • DATA MUTATION Row Records

INSERT INTO college.students (student_id, first_name)
VALUES ('1', 'Arjun') RETURNING *;

UPDATE college.students 
SET email = '[email protected]' WHERE student_id = '1';

DELETE FROM college.students WHERE student_id = '1';