SQL & Django ORM Masterclass - Complete Study Guide

🎯

Introduction & Setup

📖What is a Database?

▼

A database is like a super-organized digital filing cabinet that stores information in a structured way so you can easily find, add, update, or delete data.

💡 Real-World Analogy: Library

Library building = Database
Bookshelves = Tables
Books = Rows (Records)
Book properties (title, author, ISBN) = Columns (Fields)

📊DBMS vs RDBMS

▼

Feature	DBMS	RDBMS
Full Form	Database Management System	Relational Database Management System
Data Storage	Files (hierarchical/network)	Tables with rows & columns
Relationships	No relationships	Supported via Foreign Keys
Data Integrity	Limited	Strong (ACID properties)
Examples	File systems, XML	PostgreSQL, MySQL, SQLite

💡 Key Insight

RDBMS is a type of DBMS that organizes data into related tables. All modern databases (PostgreSQL, MySQL, SQLite) are RDBMS.

🗃️SQL Command Types

▼

Type	Purpose	Commands
DDL (Data Definition)	Structure	CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation)	Data	SELECT, INSERT, UPDATE, DELETE
DCL (Data Control)	Permissions	GRANT, REVOKE
TCL (Transaction Control)	Transactions	COMMIT, ROLLBACK, SAVEPOINT

🔄What is ORM?

▼

ORM (Object-Relational Mapping) lets you interact with your database using Python objects instead of raw SQL.

Without ORM (Raw SQL)	With ORM (Django)
Write SQL strings in Python	Write Python code
Prone to SQL injection	Automatically escaped
Database-specific syntax	Database-agnostic
Manual connection handling	Automatic management

Without ORM

cursor.execute("SELECT * FROM users WHERE age > 18")

With ORM

User.objects.filter(age__gt=18)

🛠️Sample Database Models

▼

Django Models (models.py)

All examples in this guide use these models:

Django ORM

from django.db import models

class Author(models.Model):
    """Represents a book author"""
    name = models.CharField(max_length=100)
    email = models.EmailField(unique=True)
    birth_date = models.DateField(null=True, blank=True)
    country = models.CharField(max_length=50, default='Unknown')

class Category(models.Model):
    """Book categories/genres"""
    name = models.CharField(max_length=50)
    description = models.TextField(blank=True)

class Book(models.Model):
    """Represents a book in the store"""
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    stock = models.IntegerField(default=0)
    published_date = models.DateField()
    is_available = models.BooleanField(default=True)
    rating = models.FloatField(default=0.0)

class Order(models.Model):
    """Customer orders"""
    customer_name = models.CharField(max_length=100)
    book = models.ForeignKey(Book, on_delete=models.CASCADE)
    quantity = models.IntegerField(default=1)
    order_date = models.DateTimeField(auto_now_add=True)
    total_price = models.DecimalField(max_digits=10, decimal_places=2)

Equivalent SQL Tables

SQL

CREATE TABLE author (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    birth_date DATE,
    country VARCHAR(50) DEFAULT 'Unknown'
);

CREATE TABLE category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT
);

CREATE TABLE book (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES author(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES category(id) ON DELETE SET NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    published_date DATE NOT NULL,
    is_available BOOLEAN DEFAULT TRUE,
    rating FLOAT DEFAULT 0.0
);

CREATE TABLE "order" (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    book_id INTEGER REFERENCES book(id) ON DELETE CASCADE,
    quantity INTEGER DEFAULT 1,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_price DECIMAL(10, 2) NOT NULL
);

📊Sample Data

▼

Book Table (used in all examples)

id	title	author_id	price	stock	published_date	is_available	rating
1	Python Mastery	1	49.99	25	2023-01-15	TRUE	4.5
2	Django Deep Dive	1	59.99	18	2023-06-20	TRUE	4.8
3	SQL Fundamentals	2	34.99	42	2022-11-10	TRUE	4.2
4	Web Development	3	79.99	0	2024-02-28	FALSE	4.9
5	Data Science 101	2	44.99	31	2023-09-05	TRUE	4.6

📗

Level 1: Beginner (Foundation)

1.1SELECT * (Get All Data)

▼

🎯 What is it?

Fetches ALL rows and ALL columns from a table.

SQL

SELECT * FROM book;

📊 Output

id	title	author_id	price	stock	is_available
1	Python Mastery	1	49.99	25	TRUE
2	Django Deep Dive	1	59.99	18	TRUE
3	SQL Fundamentals	2	34.99	42	TRUE
4	Web Development	3	79.99	0	FALSE
5	Data Science 101	2	44.99	31	TRUE

Django ORM

books = Book.objects.all()
# Output: <QuerySet [<Book: Python Mastery>, <Book: Django Deep Dive>, ...]>

💡 Pro Tips

Avoid SELECT * in production - select only needed columns
Django QuerySets are LAZY - data isn't fetched until you iterate

1.2SELECT Columns (Specific Fields)

▼

🎯 What is it?

Fetches only the columns you need instead of everything.

SQL

SELECT title, price FROM book;

📊 Output

title	price
Python Mastery	49.99
Django Deep Dive	59.99
SQL Fundamentals	34.99
Web Development	79.99
Data Science 101	44.99

Django ORM

# Method 1: values() - Returns dictionaries
Book.objects.values('title', 'price')
# Output: [{'title': 'Python Mastery', 'price': 49.99}, ...]

# Method 2: values_list() - Returns tuples
Book.objects.values_list('title', 'price')
# Output: [('Python Mastery', 49.99), ...]

# Method 3: values_list(flat=True) - Flat list (single column)
Book.objects.values_list('title', flat=True)
# Output: ['Python Mastery', 'Django Deep Dive', ...]

1.3WHERE Clause (Filtering)

▼

🎯 What is it?

Filters rows based on conditions. Only matching rows are returned.

SQL

SELECT * FROM book WHERE price < 50;

📊 Output (WHERE price < 50)

id	title	price	stock
1	Python Mastery	49.99	25
3	SQL Fundamentals	34.99	42
5	Data Science 101	44.99	31

→ Only 3 rows returned (books under $50)

Django ORM

Book.objects.filter(price__lt=50)   # price < 50
Book.objects.filter(price__lte=50)  # price <= 50
Book.objects.filter(is_available=True)

SQL Operator	Django Lookup	Example
=	field=value	filter(price=50)
<	__lt	filter(price__lt=50)
>	__gt	filter(price__gt=50)
<=	__lte	filter(price__lte=50)
>=	__gte	filter(price__gte=50)
!= (NOT)	exclude()	exclude(price=50)
BETWEEN	__range	filter(price__range=(10,50))
IS NULL	__isnull	filter(author__isnull=True)

1.4Multiple Conditions (AND / OR)

▼

SQL

-- AND: Both conditions must be true
SELECT * FROM book WHERE price < 50 AND is_available = TRUE;

-- OR: Either condition can be true
SELECT * FROM book WHERE price < 20 OR stock > 100;

Django ORM

from django.db.models import Q

# AND: Chain filter() or use comma
Book.objects.filter(price__lt=50, is_available=True)
Book.objects.filter(price__lt=50).filter(is_available=True)

# OR: Use Q objects with |
Book.objects.filter(Q(price__lt=20) | Q(stock__gt=100))

# NOT: Use ~ with Q
Book.objects.filter(~Q(is_available=True))  # Not available

Logic	SQL	Django Q Object
AND	AND	Q(...) & Q(...)
OR	OR	Q(...) \| Q(...)
NOT	NOT	~Q(...)

1.5LIKE (Text Pattern Matching)

▼

SQL

SELECT * FROM book WHERE title LIKE 'Python%';   -- Starts with
SELECT * FROM book WHERE title LIKE '%Science%'; -- Contains
SELECT * FROM book WHERE title ILIKE '%python%'; -- Case-insensitive

Django ORM

Book.objects.filter(title__startswith='Python')   # Starts with
Book.objects.filter(title__endswith='Guide')      # Ends with
Book.objects.filter(title__contains='Science')    # Contains (case-sensitive)
Book.objects.filter(title__icontains='python')    # Contains (case-insensitive)

SQL LIKE	Django Lookup	Case-Insensitive
LIKE 'abc%'	__startswith	__istartswith
LIKE '%abc'	__endswith	__iendswith
LIKE '%abc%'	__contains	__icontains
= 'abc'	__exact	__iexact

1.6IN Clause (Match Multiple Values)

▼

SQL

SELECT * FROM book WHERE id IN (1, 3, 5);
SELECT * FROM book WHERE author_id IN (SELECT id FROM author WHERE country='USA');

Django ORM

Book.objects.filter(id__in=[1, 3, 5])

# With subquery
usa_authors = Author.objects.filter(country='USA').values('id')
Book.objects.filter(author_id__in=usa_authors)

1.7ORDER BY (Sorting)

▼

SQL

SELECT * FROM book ORDER BY price ASC;   -- Ascending (default)
SELECT * FROM book ORDER BY price DESC;  -- Descending
SELECT * FROM book ORDER BY author_id, price DESC;  -- Multiple columns

📊 Output (ORDER BY price ASC)

title	price
SQL Fundamentals	34.99
Data Science 101	44.99
Python Mastery	49.99
Django Deep Dive	59.99
Web Development	79.99

Django ORM

Book.objects.order_by('price')         # Ascending
Book.objects.order_by('-price')        # Descending (use minus!)
Book.objects.order_by('author_id', '-price')  # Multiple
Book.objects.order_by('?')             # Random

1.8LIMIT & OFFSET (Pagination)

▼

SQL

SELECT * FROM book LIMIT 10;           -- First 10
SELECT * FROM book LIMIT 10 OFFSET 20; -- Skip 20, then 10

Django ORM

Book.objects.all()[:10]      # First 10 (LIMIT 10)
Book.objects.all()[20:30]    # Skip 20, get 10 (OFFSET 20 LIMIT 10)
Book.objects.all()[5]        # 6th item (0-indexed)
Book.objects.first()         # First item
Book.objects.last()          # Last item

💡 Pagination Formula

Page N with size S: Book.objects.all()[(N-1)*S : N*S]
Page 1 (10 items): [0:10]
Page 2 (10 items): [10:20]
Page 3 (10 items): [20:30]

1.9INSERT (Creating Data)

▼

SQL

INSERT INTO book (title, author_id, price, stock) 
VALUES ('New Book', 1, 29.99, 50);

Django ORM

# Method 1: create() - one step, returns object
book = Book.objects.create(title='New Book', author_id=1, price=29.99, stock=50)

# Method 2: save() - two steps
book = Book(title='New Book', author_id=1, price=29.99)
book.save()

# Method 3: bulk_create() - multiple at once (FAST)
Book.objects.bulk_create([
    Book(title='Book 1', author_id=1, price=19.99),
    Book(title='Book 2', author_id=2, price=24.99),
])

# Method 4: get_or_create() - avoid duplicates
book, created = Book.objects.get_or_create(
    title='Unique Title',
    defaults={'author_id': 1, 'price': 29.99}
)

1.10UPDATE (Modifying Data)

▼

SQL

UPDATE book SET price = 19.99 WHERE id = 1;
UPDATE book SET stock = stock + 10 WHERE is_available = TRUE;

Django ORM

# Method 1: QuerySet update() - FAST, single query
Book.objects.filter(id=1).update(price=19.99)

# Method 2: Instance save() - slower, but triggers signals
book = Book.objects.get(id=1)
book.price = 19.99
book.save()

# Atomic increment with F()
from django.db.models import F
Book.objects.filter(is_available=True).update(stock=F('stock') + 10)

1.11DELETE (Removing Data)

▼

SQL

DELETE FROM book WHERE id = 1;
DELETE FROM book WHERE stock = 0;

Django ORM

Book.objects.filter(id=1).delete()
Book.objects.filter(stock=0).delete()  # Delete multiple

# Instance delete
book = Book.objects.get(id=1)
book.delete()

⚠️ CASCADE Warning

on_delete=CASCADE means deleting a parent deletes all children!

1.12Getting Single Objects

▼

Django ORM

# get() - returns ONE object, raises error if not found or multiple
book = Book.objects.get(id=1)  
# Raises: DoesNotExist or MultipleObjectsReturned

# first() - returns first or None
book = Book.objects.filter(author_id=1).first()

# last() - returns last or None  
book = Book.objects.filter(author_id=1).last()

# Safe pattern with try/except
try:
    book = Book.objects.get(id=999)
except Book.DoesNotExist:
    book = None

1.13COUNT & EXISTS

▼

SQL

SELECT COUNT(*) FROM book;
SELECT COUNT(*) FROM book WHERE is_available = TRUE;

📊 Output

count: 5

Django ORM

Book.objects.count()                        # 5
Book.objects.filter(is_available=True).count()  # 4

# exists() - True/False (faster than count() > 0)
if Book.objects.filter(stock=0).exists():
    print("Out of stock books exist!")

📘

Level 2: Intermediate (Building Skills)

2.1Aggregation Functions (COUNT, SUM, AVG, MIN, MAX)

▼

SQL

SELECT COUNT(*) FROM book;
SELECT SUM(price), AVG(price) FROM book;
SELECT MIN(price), MAX(price) FROM book;

📊 Output

Function	Result
COUNT(*)	5
SUM(price)	269.95
AVG(price)	53.99
MIN(price)	34.99
MAX(price)	79.99

Django ORM

from django.db.models import Count, Sum, Avg, Min, Max

Book.objects.count()
Book.objects.aggregate(Sum('price'))      # {'price__sum': 269.95}
Book.objects.aggregate(Avg('price'))      # {'price__avg': 53.99}
Book.objects.aggregate(min_p=Min('price'), max_p=Max('price'))

2.2GROUP BY (Grouping Data)

▼

SQL

SELECT author_id, COUNT(*) as book_count 
FROM book GROUP BY author_id;

📊 Output

author_id	book_count
1	2
2	2
3	1

Django ORM

Book.objects.values('author_id').annotate(book_count=Count('id'))
# [{'author_id': 1, 'book_count': 2}, ...]

2.3HAVING (Filter Groups)

▼

🎯 What is it?

HAVING filters groups AFTER aggregation (WHERE filters BEFORE).

SQL

SELECT author_id, COUNT(*) as cnt FROM book 
GROUP BY author_id HAVING COUNT(*) >= 2;

Django ORM

Book.objects.values('author_id').annotate(cnt=Count('id')).filter(cnt__gte=2)

2.4INNER JOIN (Related Data)

▼

🎯 What is it?

Returns only rows that have matching values in BOTH tables.

SQL

SELECT book.title, author.name FROM book 
INNER JOIN author ON book.author_id = author.id;

📊 Output

title	author_name
Python Mastery	John Doe
Django Deep Dive	John Doe
SQL Fundamentals	Jane Smith

Django ORM

# Django does JOINs automatically!
Book.objects.select_related('author')  # Eager load

for book in Book.objects.select_related('author'):
    print(f"{book.title} by {book.author.name}")

💡 select_related vs prefetch_related

select_related: JOIN query (ForeignKey, OneToOne)
prefetch_related: Separate queries (ManyToMany, reverse FK)

2.5LEFT JOIN (Include All Left)

▼

SQL

SELECT author.name, book.title FROM author 
LEFT JOIN book ON author.id = book.author_id;

Django ORM

# Authors with book count (including 0)
Author.objects.annotate(book_count=Count('books'))

# Authors with no books
Author.objects.filter(books__isnull=True)

2.6prefetch_related (Optimize ManyToMany)

▼

Django ORM

# BAD: N+1 query problem
for author in Author.objects.all():      # 1 query
    for book in author.books.all():      # N queries!
        print(book.title)

# GOOD: prefetch_related (only 2 queries!)
for author in Author.objects.prefetch_related('books'):
    for book in author.books.all():
        print(book.title)

2.7Subqueries

▼

SQL

SELECT * FROM book WHERE price > (SELECT AVG(price) FROM book);

Django ORM

from django.db.models import Subquery, OuterRef, Avg

avg_price = Book.objects.aggregate(avg=Avg('price'))['avg']
Book.objects.filter(price__gt=avg_price)

2.8DISTINCT (Unique Values)

▼

SQL

SELECT DISTINCT author_id FROM book;

Django ORM

Book.objects.values('author_id').distinct()
Book.objects.order_by('author_id').distinct('author_id')  # PostgreSQL

2.9UNION (Combine Results)

▼

SQL

SELECT title FROM book WHERE price < 30
UNION
SELECT title FROM book WHERE stock > 50;

Django ORM

q1 = Book.objects.filter(price__lt=30).values('title')
q2 = Book.objects.filter(stock__gt=50).values('title')
q1.union(q2)

2.10CASE WHEN (Conditional Logic)

▼

SQL

SELECT title, CASE 
    WHEN price < 30 THEN 'Budget'
    WHEN price < 60 THEN 'Standard'
    ELSE 'Premium'
END as tier FROM book;

📊 Output

title	tier
Python Mastery	Standard
Web Development	Premium
SQL Fundamentals	Standard

Django ORM

from django.db.models import Case, When, Value, CharField

Book.objects.annotate(tier=Case(
    When(price__lt=30, then=Value('Budget')),
    When(price__lt=60, then=Value('Standard')),
    default=Value('Premium'),
    output_field=CharField()
))

2.11COALESCE (Handle NULL)

▼

SQL

SELECT COALESCE(description, 'No description') FROM category;

Django ORM

from django.db.models.functions import Coalesce
from django.db.models import Value

Category.objects.annotate(desc=Coalesce('description', Value('No description')))

📕

Level 3: Advanced (Mastery)

3.1F() Expressions (Field References)

▼

🎯 What is it?

Reference fields in the same row for comparisons or atomic updates (race-condition safe!).

SQL

UPDATE book SET stock = stock + 10 WHERE id = 1;

Django ORM

from django.db.models import F

# Atomic increment (no race conditions!)
Book.objects.filter(id=1).update(stock=F('stock') + 10)

# Compare fields
Book.objects.filter(stock__lt=F('sold_count'))

# In annotations
Book.objects.annotate(profit=F('price') - F('cost'))

💡 Why F() Matters

Without F(), two simultaneous requests could both read stock=10, increment, and write 11 (instead of 12). F() makes it atomic!

3.2Q() Objects (Complex Queries)

▼

Django ORM

from django.db.models import Q

# OR: |
Book.objects.filter(Q(price__lt=30) | Q(stock__gt=100))

# AND: &
Book.objects.filter(Q(price__lt=50) & Q(is_available=True))

# NOT: ~
Book.objects.filter(~Q(is_available=True))

# Complex nested
Book.objects.filter((Q(price__lt=30) | Q(stock__gt=100)) & Q(is_available=True))

# Dynamic filter building
conditions = Q()
if title: conditions &= Q(title__icontains=title)
if min_price: conditions &= Q(price__gte=min_price)
Book.objects.filter(conditions)

3.3Window Functions (ROW_NUMBER, RANK)

▼

SQL

SELECT title, price,
    ROW_NUMBER() OVER (ORDER BY price DESC) as rank
FROM book;

Django ORM

from django.db.models import F, Window
from django.db.models.functions import RowNumber, Rank

Book.objects.annotate(
    price_rank=Window(expression=RowNumber(), order_by=F('price').desc())
)

3.4Transactions (ACID)

▼

SQL

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

Django ORM

from django.db import transaction

# Decorator
@transaction.atomic
def transfer(from_acc, to_acc, amount):
    from_acc.balance -= amount; from_acc.save()
    to_acc.balance += amount; to_acc.save()

# Context manager
with transaction.atomic():
    book.stock -= 1; book.save()
    Order.objects.create(book=book)

3.5SELECT FOR UPDATE (Row Locking)

▼

SQL

SELECT * FROM book WHERE id = 1 FOR UPDATE;

Django ORM

with transaction.atomic():
    book = Book.objects.select_for_update().get(id=1)
    book.stock -= 1  # Other transactions WAIT
    book.save()

# NOWAIT: Raise error instead of waiting
Book.objects.select_for_update(nowait=True).get(id=1)

# SKIP LOCKED: Skip locked rows
Book.objects.select_for_update(skip_locked=True).filter(stock__gt=0)

3.6Indexing & Query Optimization

▼

SQL

CREATE INDEX idx_book_title ON book(title);
CREATE INDEX idx_composite ON book(author_id, price);
EXPLAIN ANALYZE SELECT * FROM book WHERE title = 'Python';

Django ORM

class Book(models.Model):
    title = models.CharField(max_length=200, db_index=True)
    
    class Meta:
        indexes = [
            models.Index(fields=['author', 'price']),
            models.Index(fields=['-price']),  # Descending
        ]

# View SQL
print(Book.objects.filter(title='X').query)

# EXPLAIN
Book.objects.filter(title='X').explain()

3.7Raw SQL Queries

▼

Django ORM

# raw() - returns model instances
Book.objects.raw('SELECT * FROM book WHERE price > %s', [50])

# Direct cursor (no models)
from django.db import connection
with connection.cursor() as cursor:
    cursor.execute("SELECT title FROM book")
    rows = cursor.fetchall()

⚠️ SQL Injection Warning

ALWAYS use %s placeholders. NEVER use string formatting!

3.8Common Table Expressions (CTEs)

▼

SQL

WITH expensive AS (SELECT * FROM book WHERE price > 50)
SELECT author_id, COUNT(*) FROM expensive GROUP BY author_id;

Django ORM

# Django doesn't have native CTE, use subqueries
expensive = Book.objects.filter(price__gt=50)
Author.objects.filter(books__in=expensive).annotate(cnt=Count('books'))

3.9Database Views

▼

SQL

CREATE VIEW available_books AS SELECT * FROM book WHERE is_available = TRUE;

Django ORM

# Unmanaged model for view
class AvailableBooks(models.Model):
    title = models.CharField(max_length=200)
    class Meta:
        managed = False  # Django won't create table
        db_table = 'available_books'

3.10Bulk Operations

▼

Django ORM

# bulk_create - ~10x faster than individual saves
Book.objects.bulk_create([
    Book(title='A', price=10),
    Book(title='B', price=20),
], batch_size=1000)

# bulk_update
books = list(Book.objects.all())
for b in books: b.price *= 1.1
Book.objects.bulk_update(books, ['price'])

# update() - single query, fastest
Book.objects.filter(is_available=True).update(price=F('price') * 1.1)

🐘

PostgreSQL-Specific Features

4.1JSONB Fields

▼

SQL

SELECT * FROM product WHERE metadata->>'color' = 'red';
SELECT * FROM product WHERE metadata @> '{"size": "large"}';

Django ORM

class Product(models.Model):
    metadata = models.JSONField(default=dict)

Product.objects.filter(metadata__color='red')
Product.objects.filter(metadata__contains={'size': 'large'})

4.2Array Fields

▼

Django ORM

from django.contrib.postgres.fields import ArrayField

class Book(models.Model):
    tags = ArrayField(models.CharField(max_length=50))

Book.objects.filter(tags__contains=['python'])
Book.objects.filter(tags__overlap=['python', 'django'])
Book.objects.filter(tags__len=3)

4.3Full-Text Search

▼

Django ORM

from django.contrib.postgres.search import SearchVector, SearchQuery, SearchRank

# Basic search
Book.objects.annotate(search=SearchVector('title', 'description')).filter(search=SearchQuery('python'))

# Ranked search
Book.objects.annotate(
    rank=SearchRank(SearchVector('title'), SearchQuery('python'))
).order_by('-rank')

4.4Range Types

▼

Django ORM

from django.contrib.postgres.fields import DateRangeField

class Event(models.Model):
    dates = DateRangeField()

Event.objects.filter(dates__contains=date.today())

🎤

Interview Questions & Answers (50+)

Q1What is the difference between DBMS and RDBMS?▼

DBMS stores data as files with no relationships. RDBMS stores data in related tables with rows/columns, supports ACID, foreign keys, and SQL.

Q2What is a Primary Key?▼

A column (or set) that uniquely identifies each row. Must be unique and NOT NULL. Example: id SERIAL PRIMARY KEY

Q3What is a Foreign Key?▼

A column that references the primary key of another table, creating a relationship. Example: author_id INTEGER REFERENCES author(id)

Q4What is Normalization?▼

Process of organizing data to reduce redundancy. 1NF: Atomic values. 2NF: No partial dependencies. 3NF: No transitive dependencies.

Q5Difference between WHERE and HAVING?▼

WHERE filters rows BEFORE aggregation. HAVING filters groups AFTER aggregation.
SELECT author_id, COUNT(*) FROM book GROUP BY author_id HAVING COUNT(*) > 2

Q6Explain INNER JOIN vs LEFT JOIN▼

INNER: Only matching rows from both tables. LEFT: All rows from left table + matching from right (NULL if no match).

Q7What is an Index? Why use it?▼

A data structure that speeds up queries on specific columns. Like a book's index. Trade-off: slower writes, more storage.

Q8What are ACID properties?▼

Atomicity: All or nothing. Consistency: Valid state to valid state. Isolation: Transactions don't interfere. Durability: Committed = permanent.

Q9What is a Transaction?▼

A unit of work that either completes entirely or rolls back. BEGIN → operations → COMMIT/ROLLBACK.

Q10What is N+1 Query Problem?▼

1 query to get N items, then N queries for related data. Fix with select_related() or prefetch_related().

Q11select_related vs prefetch_related?▼

select_related: SQL JOIN, single query (ForeignKey/OneToOne). prefetch_related: Separate queries (ManyToMany/reverse FK).

Q12What is F() expression in Django?▼

References field values in database operations. Enables atomic updates: update(stock=F('stock')+1) prevents race conditions.

Q13What is Q() object in Django?▼

Encapsulates filter conditions for complex queries (OR, NOT). Q(a=1) | Q(b=2) for OR, ~Q(a=1) for NOT.

Q14Difference between filter() and exclude()?▼

filter(): Include matching. exclude(): Exclude matching. exclude(price=50) = WHERE price != 50

Q15What is aggregate() vs annotate()?▼

aggregate(): Returns single dict for whole queryset {'avg': 50}. annotate(): Adds field to each object.

Q16What is a QuerySet in Django?▼

A lazy collection of database rows. Doesn't hit DB until evaluated (iteration, len, list, slicing).

Q17What is lazy evaluation?▼

QuerySets don't execute SQL until needed. Book.objects.all() doesn't query DB. list(qs) or for x in qs does.

Q18How to prevent SQL injection?▼

Use parameterized queries: cursor.execute("SELECT * WHERE id=%s", [user_id]). Never string format user input!

Q19What is CASCADE in on_delete?▼

When parent deleted, delete all children. Other options: SET_NULL, SET_DEFAULT, PROTECT, DO_NOTHING.

Q20What is a Subquery?▼

A query inside another query. WHERE price > (SELECT AVG(price) FROM book)

Q21Clustered vs Non-clustered Index?▼

Clustered: Physically reorders data, one per table. Non-clustered: Logical ordering, multiple allowed.

Q22When NOT to use an index?▼

Small tables, rarely queried columns, frequently updated columns, low cardinality columns, heavy write operations.

Q23What is EXPLAIN ANALYZE?▼

Shows query execution plan - execution time, rows examined, index usage. Helps identify performance issues.

Q24What is a Deadlock?▼

Two transactions waiting for each other to release locks. Prevent with: consistent lock ordering, timeouts, short transactions.

Q25What are Isolation Levels?▼

Read Uncommitted → Read Committed → Repeatable Read → Serializable. Higher = more isolation, less concurrency.

Q26filter() vs get() in Django?▼

filter(): Returns QuerySet, empty if no match. get(): Returns single object, raises DoesNotExist or MultipleObjectsReturned.

Q27What is bulk_create()?▼

Creates multiple objects in single query. ~10x faster than individual saves. Use batch_size for large datasets.

Q28What is a View in SQL?▼

Virtual table based on a query. Doesn't store data. CREATE VIEW active_books AS SELECT * FROM book WHERE available=TRUE

Q29What is a Stored Procedure?▼

Precompiled SQL code that can be executed multiple times. Benefits: reduced network traffic, reusability, security.

Q30What is a Trigger?▼

Special procedure that executes automatically on INSERT, UPDATE, DELETE events.

Q31What is Normalization?▼

Organizing data to reduce redundancy. 1NF: Atomic values. 2NF: No partial dependencies. 3NF: No transitive dependencies.

Q32What is Denormalization?▼

Intentionally adding redundancy for read performance. Use for read-heavy apps with rarely changing data.

Q33What is a Self-Join?▼

Table joined with itself. Used for hierarchical data like employees/managers.

Q34What is a Cross Join?▼

Cartesian product - every row from A combined with every row from B. Result: A_rows × B_rows.

Q35DELETE vs TRUNCATE?▼

DELETE: DML, can use WHERE, triggers fire, can rollback. TRUNCATE: DDL, removes all, faster, no rollback.

Q36UNION vs UNION ALL?▼

UNION: Removes duplicates (slower). UNION ALL: Keeps all rows (faster).

Q37What is JSONB in PostgreSQL?▼

Binary JSON with indexing support. Query: filter(metadata__color='red')

Q38Why PostgreSQL over MySQL?▼

Better ACID, advanced types (JSONB, Arrays), built-in full-text search, window functions, complex query support.

Q39What is Database Sharding?▼

Splitting database into smaller pieces across servers. Horizontal: split rows. Vertical: split columns.

Q40What is Database Replication?▼

Copying data to multiple servers. Master-Slave: one write, many read. Master-Master: multiple write nodes.

Q41What is Connection Pooling?▼

Reusing DB connections instead of creating new ones. Django: CONN_MAX_AGE = 60

Q42What is a Materialized View?▼

A view that stores results physically. Must REFRESH to update data. Faster reads, stale data risk.

Q43What is a CTE?▼

Common Table Expression - temporary named result. WITH expensive AS (SELECT..) SELECT FROM expensive

Q44EXISTS vs IN?▼

EXISTS: Returns TRUE/FALSE, stops at first match. IN: Compares values, checks all. EXISTS better for large subqueries.

Q45How to optimize slow queries?▼

1. EXPLAIN ANALYZE 2. Add indexes 3. Avoid SELECT * 4. Use LIMIT 5. Optimize JOINs 6. Connection pooling 7. Caching 8. Denormalize if needed

Q46Django Migrations?▼

makemigrations = create. migrate = apply. showmigrations = status. migrate app_name 0001 = rollback.

Q47What is only() and defer()?▼

only(): Load only specified fields. defer(): Load all except specified. For performance when you don't need all fields.

Q48What is select_for_update()?▼

Locks rows until transaction completes. Prevents concurrent modifications. Use with transaction.atomic().

Q49What is __contains vs __icontains?▼

contains: Case-sensitive LIKE '%x%'. icontains: Case-insensitive. Always prefer icontains for user search.

Q50What is update_or_create()?▼

Updates if exists, creates if not. Returns (object, created_boolean). Atomic operation.

📋

Quick Reference Cheat Sheet

🔍 Basic Queries

SQL	Django ORM
SELECT *	.all()
SELECT cols	.values() / .values_list()
WHERE	.filter()
WHERE NOT	.exclude()
ORDER BY	.order_by()
LIMIT	[:n]
OFFSET	[n:m]

➕ CRUD Operations

SQL	Django ORM
INSERT	.create() / .save()
UPDATE	.update() / .save()
DELETE	.delete()
BULK INSERT	.bulk_create()
BULK UPDATE	.bulk_update()

📊 Aggregation

SQL	Django ORM
COUNT(*)	Count()
SUM(col)	Sum('col')
AVG(col)	Avg('col')
MIN(col)	Min('col')
MAX(col)	Max('col')
GROUP BY	.values().annotate()
HAVING	.annotate().filter()

🔗 Joins & Relations

SQL	Django ORM
INNER JOIN	select_related()
LEFT JOIN	annotate() with Count
Eager Load FK	select_related()
Eager Load M2M	prefetch_related()

🔠 Lookups

Purpose	Django Lookup
Equals	field=val
Less than	__lt
Greater than	__gt
Contains	__contains / __icontains
Starts with	__startswith
In list	__in
Is null	__isnull
Range	__range

⚡ Advanced

Concept	Django
Field reference	F('field')
Complex conditions	Q() objects
Conditional	Case/When
Transactions	@transaction.atomic
Row locking	select_for_update()
Raw SQL	.raw() / cursor