All the reading

SQL

8 lessons, about 34 minutes. The check's questions come from these.

Modeling tables and keys

1. Model one real feature

4 min

Start with a feature

A database project is easier when you model one real feature, not a full app. Pick something a user can do and a recruiter can understand in 30 seconds.

Good examples: track mess payments, save internship applications, list hostel repair requests, manage club event registrations, or record UPI expense splits.

Key point

One idea

Model the data your feature must remember. Do not start with screens, buttons, or a full startup idea.

A table stores one kind of thing. A row stores one example of that thing. A column stores one fact about it.

Your to-do

Choose the scope

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Name one user action, like “student submits a repair request”.
Write the facts you must store for that action.
Remove anything not needed for a first demo.
Keep 1 to 3 tables for now.
Add sample data you can show in your README later.

0 of 5 done

Example

Worked example

Feature: hostel students submit repair requests. The first version only needs one table.

You are not solving login, admin dashboards, or notifications yet. You are modelling the core data.

Feature: Hostel repair requests

User action:
A student reports a problem in their room.

Facts to store:
- student_name
- hostel_name
- room_number
- issue_title
- issue_description
- status
- created_at

Table:
repair_requests

Sample row:
Asha Nair | Ganga Hostel | B-204 | Fan not working | Fan stopped last night | open | 2026-09-20

2. Use primary and foreign keys

4 min

Why keys matter

A table without keys is easy to fill and hard to trust. You can insert duplicate users, orphan orders, or reviews for products that do not exist.

Keys turn your feature idea from loose rows into rules the database can enforce. This is what makes your repo look serious.

Key point

Remember this

A primary key identifies one row in its own table. A foreign key stores the primary key of another table to show a real relationship.

Your to-do

Pick primary keys

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Use one primary key per table.
Prefer a simple id column for app-built features.
Make it stable. Do not use values that can change, like phone number or email.
Keep display fields separate from keys, like name, title, or roll number.
Name keys clearly, such as user_id, event_id, or order_id.

0 of 5 done

Querying and combining rows

3. Read data with SELECT

4 min

Key point

One idea

A SELECT query is the read side of your feature. It should return the exact rows and columns your screen, API route, or CLI command needs.

Start from the screen

Before writing SQL, write the question your feature must answer. For a campus event app, it might be: “Show upcoming events for this college, newest first.”

That question tells you the table, columns, filters, and order. Do not start with SELECT * unless you are quickly exploring data.

Example

Basic read query

This query reads only what an event list card needs. It skips internal columns like created_at if the screen does not show them.

-- queries/list_upcoming_events.sql
SELECT
  id,
  title,
  venue,
  starts_at
FROM events
WHERE college_id = 1
  AND starts_at >= '2026-01-01 00:00:00'
ORDER BY starts_at ASC
LIMIT 10;

Fixing query safety and speed

6. Find slow queries

5 min

Key point

One idea

A slow query usually reads too many rows. Your job is to prove what it reads, then reduce the work.

Use EXPLAIN before and after a change. Do not guess from vibes or one lucky run.

What slow means

In a hackathon project, slow often means a page feels stuck when seed data grows. Your laptop may hide the issue when you have only 20 rows.

Test with enough seed data to make bad queries visible. Even a few thousand rows is useful for a student project.

Your to-do

Find the query

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Open the page or API route that feels slow.
Copy the exact SQL used by that feature.
Replace user input with safe sample values.
Run the query in your database shell or DB client.
Save the query in a notes file so you can compare later.

0 of 5 done

10 questions, 7 to pass.

Example

First schema file

Create a folder named sql. Put your first table design in sql/schema.sql.

This is only the model. Later lessons will improve it with keys and relationships.

CREATE TABLE repair_requests (
  student_name TEXT,
  hostel_name TEXT,
  room_number TEXT,
  issue_title TEXT,
  issue_description TEXT,
  status TEXT,
  created_at TEXT
);

Example

Seed data file

Seed data makes your repo easy to run and inspect. Add a few realistic rows, not private data from real people.

Use simple names and Indian campus examples if they fit your feature.

INSERT INTO repair_requests (
  student_name,
  hostel_name,
  room_number,
  issue_title,
  issue_description,
  status,
  created_at
) VALUES
('Asha Nair', 'Ganga Hostel', 'B-204', 'Fan not working', 'Fan stopped last night', 'open', '2026-09-20'),
('Rohan Mehta', 'Kaveri Hostel', 'A-118', 'Leaking tap', 'Water leaking near washbasin', 'open', '2026-09-20'),
('Iqbal Khan', 'Narmada Hostel', 'C-310', 'Tube light flickers', 'Light flickers during evening study time', 'closed', '2026-09-19');

Example

Feature example

Say you are building a campus event RSVP feature. One user can create many RSVPs. One event can have many RSVPs.

The rsvps table should not copy the full user and event details. It should store user_id and event_id, then foreign keys check that both rows exist.

-- schema.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  venue TEXT NOT NULL,
  starts_at TEXT NOT NULL
);

CREATE TABLE rsvps (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  event_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (event_id) REFERENCES events(id)
);

Tip

Seed in order

Insert parent rows before child rows. For this example, insert users and events before rsvps.

If your seed file fails, check whether a foreign key points to a row you have not inserted yet.

-- seed.sql
INSERT INTO users (id, name, email) VALUES
  (1, 'Asha Rao', '[email protected]'),
  (2, 'Kabir Mehta', '[email protected]');

INSERT INTO events (id, title, venue, starts_at) VALUES
  (1, 'AI Project Night', 'Lab 204', '2026-02-10 17:00');

INSERT INTO rsvps (id, user_id, event_id, status, created_at) VALUES
  (1, 1, 1, 'going', '2026-02-01 12:30');

Example

Search with filters

A search query usually has a text filter, a stable sort, and a limit. Later in this track you will make inputs safe with parameters. For today, focus on the shape of the read.

-- queries/search_events_by_title.sql
SELECT
  id,
  title,
  venue,
  starts_at
FROM events
WHERE college_id = 1
  AND title LIKE '%hack%'
ORDER BY starts_at ASC, id ASC
LIMIT 20;

4. Combine data with JOINs

4 min

Key point

One idea

A JOIN connects rows using a relationship, usually a foreign key pointing to a primary key. You use it when the data you need is split across tables.

Why JOINs matter

Your feature should not store the same detail in many places. For example, an orders table can store user_id, while the users table stores the user name and phone.

When you need to show an order list with buyer names, you combine orders and users in the query. You do not copy the user name into every order row.

Example

Basic INNER JOIN

Use INNER JOIN when you only want rows that have a match in both tables. This is good for required relationships, like an order that must belong to a user.

-- List orders with buyer details
SELECT
  orders.id AS order_id,
  orders.total_rupees,
  orders.status,
  users.name AS buyer_name,
  users.email AS buyer_email
FROM orders
INNER JOIN users
  ON orders.user_id = users.id
ORDER BY orders.id DESC;

Read the query

FROM says the main table you are reading from. INNER JOIN names the second table. ON explains how the two tables match.

Use table names before column names when both tables may have the same column, like id or created_at. Use AS to give readable names to the output.

Example

LEFT JOIN for missing data

Use LEFT JOIN when you want every row from the left table, even if the joined table has no match. This helps with optional data.

For example, not every student profile may have a submitted project yet. A LEFT JOIN can still show all students.

-- Show all students, with project title if submitted
SELECT
  students.id AS student_id,
  students.name,
  projects.title AS project_title
FROM students
LEFT JOIN projects
  ON projects.student_id = students.id
ORDER BY students.name;

Common mistake

Do not join tables without an ON condition. You can accidentally create too many rows, where every row from one table is paired with every row from the other.

If your result suddenly has hundreds of rows for a small seed file, check your ON line first.

Tip

Use AI safely

You can ask ChatGPT or Gemini to draft a JOIN query from your schema. Paste only your table definitions, not private data.

Then check three things yourself: the ON condition uses the right keys, the JOIN type matches your need, and the selected columns have clear aliases.

Your to-do

Do this now

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Pick one screen or API response in your feature that needs data from two tables.
Write one INNER JOIN query using your foreign key relationship.
Write one LEFT JOIN query if your feature has optional related data.
Run both queries on your seed data and save the useful one in your repo.
Add the query and a short sample output to your README or queries file.

0 of 5 done

5. Summarise with GROUP BY

4 min

Why summaries matter

Most useful features need summary numbers. A canteen app shows orders per day. A fest app shows registrations per event. A placement tracker shows applications by status.

GROUP BY is how SQL makes these numbers from normal rows. It helps your repo look like a real product, not only a list page.

Key point

One idea

GROUP BY makes one result row for each value in a column, then aggregate functions calculate numbers inside each group.

Every selected column must either be grouped or aggregated. If it is not, the result is unclear.

Example

Start with rows

Imagine your feature stores orders for a college canteen. Each row is one order. You want totals per status for an admin screen.

-- seed data idea
orders
id | student_name | status     | amount_rupees
1  | Asha         | paid       | 80
2  | Kabir        | paid       | 120
3  | Meera        | pending    | 60
4  | Rohan        | cancelled  | 100
5  | Iqra         | paid       | 50

Example

Count each group

Use COUNT(*) when you want to count rows. This is good for badges, reports, and simple admin dashboards.

SELECT
  status,
  COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

-- possible result
status    | order_count
paid      | 3
pending   | 1
cancelled | 1

Example

Add useful totals

Use SUM for money, AVG for averages, and MIN or MAX for ranges. Name your output columns clearly so your README is easy to read.

SELECT
  status,
  COUNT(*) AS order_count,
  SUM(amount_rupees) AS total_rupees,
  AVG(amount_rupees) AS average_rupees
FROM orders
GROUP BY status
ORDER BY total_rupees DESC;

-- possible result
status    | order_count | total_rupees | average_rupees
paid      | 3           | 250          | 83.333333
cancelled | 1           | 100          | 100
pending   | 1           | 60           | 60

Common mistake

Do not select a normal column that is not in GROUP BY. For example, selecting student_name while grouping only by status does not make sense. There are many students inside one status.

If you need student-level rows, do not group. If you need status-level summaries, group by status and aggregate the rest.

-- Avoid this
SELECT
  status,
  student_name,
  COUNT(*) AS order_count
FROM orders
GROUP BY status;

Tip

Filter in the right place

Use WHERE before GROUP BY to filter raw rows. For example, only orders from this month.

Use HAVING after GROUP BY to filter summary rows. For example, only statuses with at least 2 orders.

SELECT
  status,
  COUNT(*) AS order_count,
  SUM(amount_rupees) AS total_rupees
FROM orders
WHERE amount_rupees > 0
GROUP BY status
HAVING COUNT(*) >= 2;

Your to-do

Do this now

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Pick one table in your feature that has repeated values, such as status, event_id, category, or payment_mode.
Write one GROUP BY query that returns COUNT(*) per group.
Add one more aggregate, such as SUM(amount_rupees) or AVG(rating).
Save the query in your repo, for example in queries/summary.sql.
Add the query and one sample result table to your README.

0 of 5 done

Example

Use EXPLAIN

SQLite is common for small local projects. It has EXPLAIN QUERY PLAN, which shows if the database scans a table or uses an index.

This example finds orders for one user. Without an index on user_id, SQLite may scan the whole orders table.

EXPLAIN QUERY PLAN
SELECT id, total_amount, status, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

-- Possible output before an index:
-- SCAN orders
-- USE TEMP B-TREE FOR ORDER BY

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

EXPLAIN QUERY PLAN
SELECT id, total_amount, status, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

-- Better output after the index:
-- SEARCH orders USING INDEX idx_orders_user_created (user_id=?)

Example

Record your fix

Recruiters cannot see your thinking unless you leave evidence. Add a short performance note to your README or docs folder.

Keep it simple. Show the query, the problem, the index, and the changed plan.

Performance note: orders list

Feature: Show a user's recent orders.
Problem: Query scanned the full orders table.
Fix: Added idx_orders_user_created on orders(user_id, created_at DESC).
Before plan: SCAN orders, USE TEMP B-TREE FOR ORDER BY.
After plan: SEARCH orders USING INDEX idx_orders_user_created (user_id=?).

7. Stop SQL injection

4 min

Key point

One rule

Never build SQL by joining strings with user input. Use placeholders and pass values separately.

The database driver sends the SQL shape and the user values apart. So a name, email, UPI handle, or search term stays data.

Why this matters

SQL injection happens when user input changes the meaning of your query. A login box, search box, filter, or admin route can all become risky.

Your internship repo should show that you know the safe habit. Recruiters may not run attacks, but they can read your query code.

Example

Unsafe query

This is the pattern to remove from your project. It looks simple, but the input becomes part of the SQL command.

// Bad: Node.js + pg
const email = req.body.email;
const sql = "SELECT id, name FROM users WHERE email = '" + email + "'";
const result = await db.query(sql);

Example

Safe query

Use placeholders. In PostgreSQL with the pg package, placeholders are $1, $2, and so on. Values go in an array.

// Good: Node.js + pg
const email = req.body.email;
const sql = "SELECT id, name FROM users WHERE email = $1";
const result = await db.query(sql, [email]);

Your to-do

Fix your feature

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Search your code for db.query, execute, raw, SELECT, INSERT, UPDATE, and DELETE.
Find any query built with +, template strings, format strings, or f-strings.
Replace user values with placeholders supported by your driver.
Pass values through the driver’s parameter list, not inside the SQL string.
Run your feature with normal input and with a weird value like test' OR '1'='1.

0 of 5 done

Common mistake

Do not trust client-side checks. A user can skip your form and call your API directly with Postman, curl, or browser dev tools.

Validation is still useful for clean data. But validation is not a replacement for parameters.

Tip

Use AI carefully

Ask ChatGPT or Gemini to review one file for SQL injection. Paste the file and ask: “Find SQL queries where user input is joined into the SQL string. Suggest parameterised replacements.”

Check the answer yourself. Make sure every user-controlled value is outside the SQL string and inside the values array or parameter object.

Your to-do

Do this now

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Open the feature code you will submit in your public repo.
Convert at least one SELECT and one INSERT or UPDATE to parameterised queries.
Add a short README note: “Queries use parameters to avoid SQL injection.”
Commit the change with a clear message like safe-parameterised-queries.

0 of 4 done

8. Ship the SQL feature repo

5 min

Key point

Repo proves the feature

Your project is not finished when it works on your laptop. It is finished when a reviewer can clone it, set up the database, run the app, and see the feature without asking you on WhatsApp.

For this track, your repo must show four things: schema files, seed data, parameterised queries, and a README with local run steps.

What to ship

Keep the feature small. A campus canteen order list, club event RSVP, hostel complaint tracker, or internship application tracker is enough.

Recruiters and hackathon judges look for proof that you can model data, query it, and protect inputs. A neat repo beats a half-working large app.

Your to-do

Use this structure

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Create a public GitHub repository for one SQL-backed feature.
Add a db folder with schema.sql and seed.sql.
Keep parameterised queries in your server code or a separate queries file.
Add a README with setup steps, screenshots or sample output, and sample queries.
Commit small changes with clear messages.

0 of 5 done

Example

Simple repo layout

Use names that make sense without explanation. If someone opens your repo on a phone, they should know where the database files are.

canteen-orders/
  README.md
  package.json
  src/
    server.js
    db.js
    orders.js
  db/
    schema.sql
    seed.sql
  .env.example

Example

README starter

Your README is the demo guide. Write it for a classmate who has Node.js and a database installed, but has never seen your code.

# Canteen Orders

A small SQL-backed feature to create and view canteen orders.

## Feature
Students can place an order with item name, quantity, and phone number.
The app stores orders in SQL and lists recent orders.

## Tech
Node.js, Express, PostgreSQL

## Run locally
1. Clone the repo
2. Run npm install
3. Create a local database named canteen_orders
4. Run psql -d canteen_orders -f db/schema.sql
5. Run psql -d canteen_orders -f db/seed.sql
6. Copy .env.example to .env and set DATABASE_URL
7. Run npm start

## Sample query
SELECT id, item_name, quantity, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

## Safety
User input is sent using parameterised queries, not string joining.

Common mistake

Do not hide SQL

A common mistake is shipping only screenshots and app code. That does not prove you can design the database.

Do not make reviewers guess your tables. Put CREATE TABLE statements in schema.sql. Put sample INSERT statements in seed.sql. Keep them runnable from a clean database.

Tip

Use AI carefully

You can ask ChatGPT or Gemini to review your README and setup steps. Paste your repo layout and ask: “Can a new developer run this locally? What is missing?”

Check the answer. AI may invent commands, database names, or files you do not have. Only keep steps you have tested yourself.

Your to-do

Do this now

Do each one yourself, then tap it to tick it off. The ticks are only a checklist for you: they are not marked or scored.

Open your project and create db/schema.sql and db/seed.sql if they are missing.
Move at least one unsafe or hidden SQL query into clear parameterised code.
Write a README with feature, tech stack, run steps, and one sample query.
Test the setup from a fresh database using your own README.
Push to a public repo and submit the link on HackIndia.

0 of 5 done

SQL

1. Model one real feature

Start with a feature

One idea

Choose the scope

Worked example

2. Use primary and foreign keys

Why keys matter

Remember this

Pick primary keys

3. Read data with SELECT

One idea

Start from the screen

Basic read query

6. Find slow queries

One idea

What slow means

Find the query

First schema file

Seed data file

Common mistake

Do this now

Feature example

What this prevents

Common mistake

Seed in order

Do this now

Build a SELECT

Name saved queries

Avoid SELECT star

Search with filters

Do this now

4. Combine data with JOINs

One idea

Why JOINs matter

Basic INNER JOIN

Read the query

LEFT JOIN for missing data

Common mistake

Use AI safely

Do this now

5. Summarise with GROUP BY

Why summaries matter

One idea

Start with rows

Count each group

Add useful totals

Common mistake

Filter in the right place

Do this now

Use EXPLAIN

Read the plan

Do not index everything

Record your fix

Do this now

7. Stop SQL injection

One rule

Why this matters

Unsafe query

Safe query

Fix your feature

Common mistake

Use AI carefully

Do this now

8. Ship the SQL feature repo

Repo proves the feature

What to ship

Use this structure

Simple repo layout

README starter

Do not hide SQL

Use AI carefully

Do this now