Practice — Day 6 · Python, Java, JavaScript & SQL

List Comprehension Masterclass 🧩

LIST COMPEASY~20 min

📖 Scenario

You're a data analyst cleaning a messy dataset of student scores. List comprehensions let you filter, transform, and build lists in one clean line — no for-loop boilerplate. They're faster to write, faster to read, and actually run faster than regular loops in Python.

Starter Code

scores = [45, 82, 91, 33, 67, 55, 78, 29, 95, 61]
names  = ["Rahul", "priya", "ARJUN", "sneha", "Vikram"]

# Write each answer as a ONE-LINE list comprehension

Your Tasks

Get all scores that are 60 or above (passing scores) [x for x in scores if x >= 60]
Get the square of every score — [x**2 for x in scores]
Convert all scores to grades: "A" if ≥80, "B" if ≥60, else "C" ["A" if x>=80 else "B" if x>=60 else "C" for x in scores]
Normalize all names to Title Case — first letter capital, rest lowercase Use .title() method — one line comprehension over names list
Get scores that are both above 50 AND even numbers

Expected Output

Passing: [82, 91, 67, 55, 78, 95, 61]
Grades: ['C','A','A','C','B','C','B','C','A','B']
Names: ['Rahul', 'Priya', 'Arjun', 'Sneha', 'Vikram']
Above 50 & even: [82, 78]

💡 One Hint

For Task ⑤, two conditions in a comprehension: [x for x in scores if x > 50 and x % 2 == 0]. The and works exactly like in an if statement — both must be true.

FizzBuzz — The Classic 🎯

LOOPSFUNCTIONSEASY~15 min

📖 Scenario

FizzBuzz is the most famous beginner coding interview question — Google, Microsoft, and Amazon have all used it. Print numbers 1 to N, but replace multiples of 3 with "Fizz", multiples of 5 with "Buzz", and multiples of both with "FizzBuzz". Sounds simple — the trick is getting the order of conditions right!

Starter Code

def fizzbuzz(n):
    for i in range(1, n + 1):
        pass  # check divisibility and print

fizzbuzz(20)

Your Tasks

Check if i is divisible by both 3 and 5 → print "FizzBuzz" ⚠️ This check MUST come first — otherwise 15 will wrongly print just "Fizz"
Else if divisible by 3 → print "Fizz"
Else if divisible by 5 → print "Buzz"
Else → print the number itself
Rewrite it as a one-liner list comprehension — the whole thing in one line! ["FizzBuzz" if i%15==0 else "Fizz" if i%3==0 else "Buzz" if i%5==0 else str(i) for i in range(1, n+1)]

Expected Output (1–20)

1 2 Fizz 4 Buzz Fizz 7 8 Fizz Buzz 11 Fizz 13 14 FizzBuzz 16 17 Fizz 19 Buzz

💡 One Hint

Use i % 3 == 0 to check divisibility. Why check 15 first? Because 15 % 3 == 0 is also true, so if you check "divisible by 3" first, 15 would print "Fizz" and never reach the FizzBuzz check.

Caesar Cipher 🔑

STRINGASCIIEASY~25 min

📖 Scenario

Julius Caesar used this cipher to send secret military messages — shift every letter by a fixed number. A→D, B→E, C→F (shift of 3). Python's ord() converts a character to its ASCII number, and chr() converts back. This teaches you how characters are really just numbers under the hood.

🧠 ASCII Trick

ord('A') = 65, ord('Z') = 90, ord('a') = 97, ord('z') = 122
chr(65) = 'A', chr(66) = 'B'
Shift formula: chr((ord(c) - ord('A') + shift) % 26 + ord('A'))
The % 26 wraps Z back to A — so Z + 3 = C, not ']'

Starter Code

def encrypt(text, shift):
    result = ""
    for c in text:
        if c.isupper():
            pass  # shift uppercase letter, wrap with % 26
        elif c.islower():
            pass  # shift lowercase letter
        else:
            result += c   # spaces and punctuation stay the same
    return result

def decrypt(text, shift):
    pass   # hint: decrypting = encrypting with (26 - shift)

print(encrypt("Hello World", 3))
print(decrypt("Khoor Zruog", 3))

Your Tasks

Fill uppercase encryption: chr((ord(c) - ord('A') + shift) % 26 + ord('A'))
Fill lowercase encryption: same formula but use ord('a') as base
Fill decrypt() — just call encrypt(text, 26 - shift)
Test: encrypt "Hello World" with shift 3 → should give "Khoor Zruog"

Expected Output

Khoor Zruog
Hello World

💡 One Hint

Why % 26? Letters loop — after Z comes A. Without modulo, shifting 'Y' by 3 would give ord 91 which is '[', not 'B'. The % 26 keeps the number in the range 0–25, then we add the base (ord('A') or ord('a')) to get back to the right ASCII value.

Number to Words 🔢➡️📝

DICTLOGICEASY~25 min

📖 Scenario

Bank cheques, bill generators, and screen readers all need to convert numbers into words — "1234" → "One Thousand Two Hundred Thirty Four". You'll build a simplified version that handles 0–999. This tests your ability to break a problem into smaller pieces and use a dictionary as a lookup table.

Starter Code

ones  = ["","One","Two","Three","Four","Five",
         "Six","Seven","Eight","Nine","Ten",
         "Eleven","Twelve","Thirteen","Fourteen","Fifteen",
         "Sixteen","Seventeen","Eighteen","Nineteen"]
tens  = ["","","Twenty","Thirty","Forty","Fifty",
         "Sixty","Seventy","Eighty","Ninety"]

def to_words(n):
    if n == 0: return "Zero"
    if n < 20:  return ones[n]
    if n < 100:
        pass  # tens[n//10] + " " + ones[n%10], strip extra space
    if n < 1000:
        pass  # ones[n//100] + " Hundred " + to_words(n%100)

for num in [0, 7, 15, 42, 100, 305, 999]:
    print(f"{num} → {to_words(num)}")

Your Tasks

Fill the 20–99 case: combine tens[n//10] and ones[n%10], strip trailing/extra spaces
Fill the 100–999 case: use ones[n//100] + " Hundred " + recursive call to_words(n%100)
Handle the edge case: if n%100 == 0 (like 100, 200), don't add " " at the end
Test all 7 numbers — verify the output matches exactly

Expected Output

0 → Zero | 7 → Seven | 15 → Fifteen
42 → Forty Two | 100 → One Hundred
305 → Three Hundred Five | 999 → Nine Hundred Ninety Nine

💡 One Hint

For 42: tens[4] = "Forty", ones[2] = "Two" → join with a space. But for 40: ones[0] = "" — so you'd get "Forty " with a trailing space. Use .strip() to clean it: (tens[n//10] + " " + ones[n%10]).strip()

Bank Account — Full OOP 🏦

OOPEXCEPTIONMEDIUM~30 min

📖 Scenario

A real bank account tracks a balance and keeps a full transaction history. You'll build a class that supports deposit, withdrawal (with validation), and prints a formatted statement. This combines OOP + exception handling + list operations — the three big topics from Days 1–5 all at once.

Starter Code

class BankAccount:
    def __init__(self, owner, balance=0):
        self.owner    = owner
        self.balance  = balance
        self.history  = []   # list of transaction strings

    def deposit(self, amount):
        pass   # add to balance, append "+ ₹X" to history

    def withdraw(self, amount):
        pass   # check amount valid & sufficient, deduct, log "- ₹X"

    def statement(self):
        pass   # print owner, current balance, and full history

acc = BankAccount("Rahul", 1000)
acc.deposit(500)
acc.withdraw(200)
acc.withdraw(5000)   # should fail
acc.statement()

Your Tasks

Fill deposit() — add to balance, append f"+ ₹{amount}" to self.history
Fill withdraw() — raise ValueError if amount ≤ 0 or amount > balance. On success, deduct and log f"- ₹{amount}"
Wrap the withdraw() call in try/except in the test code so the ₹5000 attempt prints a clean error
Fill statement() — print owner name, balance, and loop through history to print each transaction

Expected Output

Insufficient funds!
── Statement: Rahul ──
Balance: ₹1300
+ ₹500
- ₹200

💡 One Hint

For history, use a list of strings: self.history.append(f"+ ₹{amount}"). In statement(), loop with for t in self.history: print(t). The starting balance of 1000 is set in __init__ so it doesn't appear in history — only changes do.

List Comprehension → Java Streams 🧩

STREAMSLAMBDAEASY~20 min

📖 Scenario

Java doesn't have list comprehensions, but it has Streams — a pipeline approach. stream().filter().map().collect() is Java's equivalent of Python's [x for x in list if condition]. You'll do the same data-cleaning tasks but the Java way.

Starter Code

import java.util.*;
import java.util.stream.*;

public class DataClean {
  public static void main(String[] args) {
    List<Integer> scores = Arrays.asList(45,82,91,33,67,55,78,29,95,61);
    List<String>  names  = Arrays.asList("rahul","PRIYA","Arjun","sneha","VIKRAM");

    // Task 1: passing scores (>= 60)
    List<Integer> passing = scores.stream()
        .filter(x -> x >= 60)
        .collect(Collectors.toList());
    System.out.println("Passing: " + passing);

    // Write Tasks 2–5 below
  }
}

Your Tasks

Task 1 is done — study how .filter() + .collect() works
Use .map(x -> x * x) to get squares of all scores
Map each score to a grade string: "A" / "B" / "C" — use a ternary inside the lambda
Normalize all names to lowercase then capitalize first letter s -> s.substring(0,1).toUpperCase() + s.substring(1).toLowerCase()
Filter scores that are above 50 AND even (x % 2 == 0)

Expected Output

Passing: [82, 91, 67, 55, 78, 95, 61]
Grades: [C, A, A, C, B, C, B, C, A, B]
Names: [Rahul, Priya, Arjun, Sneha, Vikram]
Above 50 & even: [82, 78]

💡 One Hint

Two filters can be chained: .filter(x -> x > 50).filter(x -> x % 2 == 0). Or combine in one: .filter(x -> x > 50 && x % 2 == 0). Both work — the chained version is easier to read.

FizzBuzz — The Classic 🎯

LOOPSMODULOEASY~15 min

📖 Scenario

FizzBuzz is used by real interviewers at top companies to filter out people who can't code at all. Print 1 to N, replace multiples of 3 with "Fizz", multiples of 5 with "Buzz", both with "FizzBuzz". Simple — but you have to get the order right!

Starter Code

public class FizzBuzz {
  static String fizzBuzz(int i) {
      // TODO: return "FizzBuzz", "Fizz", "Buzz", or String.valueOf(i)
      return "";
  }
  public static void main(String[] args) {
      for (int i = 1; i <= 20; i++)
          System.out.print(fizzBuzz(i) + " ");
  }
}

Your Tasks

Check divisible by 15 first → return "FizzBuzz"
Else if by 3 → "Fizz", else if by 5 → "Buzz"
Else → String.valueOf(i) — converts int to String in Java
Bonus: use a StringBuilder to build the full result string and return it from a method instead of printing inside the loop

Expected Output

1 2 Fizz 4 Buzz Fizz 7 8 Fizz Buzz 11 Fizz 13 14 FizzBuzz 16 17 Fizz 19 Buzz

💡 One Hint

i % 15 == 0 is the same as i % 3 == 0 && i % 5 == 0. Using 15 is cleaner. String.valueOf(i) converts int 7 to the String "7" — Java won't let you return an int where a String is expected.

Caesar Cipher 🔑

STRINGCHAR MATHEASY~25 min

📖 Scenario

Same Caesar Cipher as Python but now in Java. Key difference: Java uses char (a primitive type) and you can do arithmetic directly on characters — 'A' + 1 = 'B'. Cast back with (char). This shows how Java's type system works at the character level.

🧠 Java Char Arithmetic

char c = 'A'; → stored as number 65
(char)(c + 1) → 'B' (cast int back to char)
(c - 'A') → 0 (distance from 'A')
('A' + (c - 'A' + shift) % 26) → shifted char

Starter Code

public class Caesar {
  static String encrypt(String text, int shift) {
      StringBuilder sb = new StringBuilder();
      for (char c : text.toCharArray()) {
          if (Character.isUpperCase(c))
              sb.append((char)('A' + (c - 'A' + shift) % 26));
          else if (Character.isLowerCase(c))
              sb.append(/* TODO: same formula but with 'a' */);
          else
              sb.append(c);
      }
      return sb.toString();
  }
  static String decrypt(String text, int shift) {
      return encrypt(text, 26 - shift); // decrypt = shift backwards
  }
  public static void main(String[] args) {
      System.out.println(encrypt("Hello World", 3));
      System.out.println(decrypt("Khoor Zruog", 3));
  }
}

Your Tasks

Fill the lowercase case — same formula as uppercase but replace 'A' with 'a'
The uppercase case is already provided — trace through it with 'H' and shift=3 to verify you understand it
Test encrypt and decrypt — they should be exact opposites
Try encrypting with shift=0 — output should equal the input

Expected Output

Khoor Zruog
Hello World

💡 One Hint

Trace 'H' (uppercase, shift=3): c - 'A' = 72 - 65 = 7 → add shift: 7+3=10 → mod 26: 10 → add 'A': 65+10 = 75 = 'K'. That's why "Hello" starts with 'K'. Lowercase is identical, just replace 65 with 97 (which is 'a').

Number to Words 🔢➡️📝

ARRAYSLOGICEASY~25 min

📖 Scenario

Same number-to-words converter — 0 to 999. Java version uses String arrays as lookup tables and integer division/modulo to break the number apart. The logic is identical to Python; only the syntax changes.

Starter Code

public class NumWords {
  static String[] ones = {"","One","Two","Three","Four","Five",
      "Six","Seven","Eight","Nine","Ten","Eleven","Twelve",
      "Thirteen","Fourteen","Fifteen","Sixteen",
      "Seventeen","Eighteen","Nineteen"};
  static String[] tens = {"","","Twenty","Thirty","Forty",
      "Fifty","Sixty","Seventy","Eighty","Ninety"};

  static String toWords(int n) {
      if (n == 0) return "Zero";
      if (n < 20)  return ones[n];
      if (n < 100)
          return (tens[n/10] + " " + ones[n%10]).trim();
      // TODO: handle 100–999
      return "";
  }
  public static void main(String[] args) {
      int[] nums = {0,7,15,42,100,305,999};
      for (int n : nums)
          System.out.println(n + " → " + toWords(n));
  }
}

Your Tasks

The 0–99 cases are provided — read and understand them
Fill 100–999: ones[n/100] + " Hundred " + toWords(n%100) Use .trim() to clean trailing spaces when n%100 == 0
Test all 7 values match expected output exactly
What does (tens[n/10] + " " + ones[n%10]).trim() do for n=40? Trace it.

Expected Output

💡 One Hint

For 305: n/100=3 → "Three", then recursive toWords(5) → "Five". For 300: toWords(0) → "Zero" — but we don't want "Three Hundred Zero"! Add a check: if (n%100 == 0) return ones[n/100] + " Hundred"; as the first case in the hundreds branch.

Bank Account — Full OOP 🏦

OOPEXCEPTIONMEDIUM~30 min

📖 Scenario

A bank account with deposit, withdrawal (validated), and a transaction history statement. Same concept as Python but now in Java — use ArrayList<String> for history, throw new IllegalArgumentException() for errors, and toString() for display.

Starter Code

import java.util.*;

class BankAccount {
    String owner;
    double balance;
    ArrayList<String> history = new ArrayList<>();

    BankAccount(String owner, double balance) {
        this.owner   = owner;
        this.balance = balance;
    }
    void deposit(double amount)  { /* TODO */ }
    void withdraw(double amount) { /* TODO — throw on bad input */ }
    void statement() { /* TODO — print owner, balance, history */ }
}

public class Main {
    public static void main(String[] args) {
        BankAccount acc = new BankAccount("Rahul", 1000);
        acc.deposit(500);
        try { acc.withdraw(200); } catch (Exception e) { System.out.println(e.getMessage()); }
        try { acc.withdraw(5000); } catch (Exception e) { System.out.println(e.getMessage()); }
        acc.statement();
    }
}

Your Tasks

Fill deposit() — add to balance, add "+ ₹" + amount to history
Fill withdraw() — throw IllegalArgumentException("Insufficient funds!") if amount > balance
Fill statement() — print owner, balance, then loop through history
Verify the ₹5000 withdrawal prints the error and the statement shows correct balance ₹1300

Expected Output

Insufficient funds!
── Statement: Rahul ──
Balance: ₹1300.0
+ ₹500.0
- ₹200.0

💡 One Hint

history.add("+ ₹" + amount) works because Java auto-converts double to String when concatenating with "+". For statement: for (String t : history) System.out.println(t);

Array Method Masterclass 🧩

MAPFILTERREDUCEEASY~20 min

📖 Scenario

JavaScript's .map(), .filter(), and .reduce() are the bread-and-butter of modern web development. Every React component, every API response handler, every dashboard uses these. Master them and 80% of real-world JS data work becomes easy.

Starter Code

const scores = [45,82,91,33,67,55,78,29,95,61];
const names  = ["rahul","PRIYA","Arjun","sneha","VIKRAM"];

// Write your code below

Your Tasks

.filter() scores ≥ 60
.map() each score to a grade string "A"/"B"/"C"
Normalize names using .map(n => n.charAt(0).toUpperCase() + n.slice(1).toLowerCase())
Use .reduce() to find the total and average of all scores
Chain: filter above 50 AND even, then map to "Score: X" strings

Expected Output

Passing: [82,91,67,55,78,95,61]
Grades: ['C','A','A','C','B','C','B','C','A','B']
Names: ['Rahul','Priya','Arjun','Sneha','Vikram']
Total: 636 Average: 63.6
Above50&Even: ['Score: 82','Score: 78']

💡 One Hint

.reduce((sum, x) => sum + x, 0) totals the array — the 0 is the starting value. Chain it: scores.filter(x => x > 50 && x % 2 === 0).map(x => "Score: " + x). In JS, use === (strict equality) not ==.

FizzBuzz + DOM Output 🎯

DOMLOOPSEASY~15 min

📖 Scenario

Same FizzBuzz logic — but this time output it in the browser as colored HTML, not just console.log. "Fizz" in blue, "Buzz" in red, "FizzBuzz" in green, numbers in grey. This shows how JS connects logic to visual output — the unique superpower of JavaScript.

Starter Code (HTML file)

<div id="output"></div>

<script>
function fizzBuzz(n) {
    let html = "";
    for (let i = 1; i <= n; i++) {
        // TODO: build colored span for each number
        // hint: html += `<span style="color:...">TEXT</span> `
    }
    document.getElementById("output").innerHTML = html;
}
fizzBuzz(30);
</script>

Your Tasks

Build the FizzBuzz logic inside the loop (same if/else as before)
Wrap each output in a <span> with a color style — blue for Fizz, red for Buzz, green for FizzBuzz, gray for numbers
Set the full HTML string into the div using .innerHTML
Add a <button> that runs fizzBuzz() with a different N from an <input>

Expected Behaviour

Browser shows: 1 2 Fizz 4 Buzz Fizz ... FizzBuzz ...
Each word/number is colored according to its type

💡 One Hint

Template literal with style: html += `<span style="color:#58a6ff;font-weight:bold">Fizz</span> `. Build the whole string first in the loop, then do ONE innerHTML assignment at the end — this is faster than updating the DOM on every iteration.

Caesar Cipher 🔑

STRINGCHARCODEEASY~25 min

📖 Scenario

Same Caesar Cipher in JavaScript. JS uses charCodeAt() instead of ord(), and String.fromCharCode() instead of chr(). The math is identical — just different method names. After writing it, make it interactive with an HTML input!

Starter Code

function encrypt(text, shift) {
    return text.split("").map(c => {
        if (c >= 'A' && c <= 'Z')
            return String.fromCharCode(
                'A'.charCodeAt(0) + (c.charCodeAt(0) - 'A'.charCodeAt(0) + shift) % 26
            );
        if (c >= 'a' && c <= 'z')
            return /* TODO: same but with 'a' */;
        return c;
    }).join("");
}

const decrypt = (text, shift) => encrypt(text, 26 - shift);

console.log(encrypt("Hello World", 3));
console.log(decrypt("Khoor Zruog", 3));

Your Tasks

Fill the lowercase case — same formula but with 'a'.charCodeAt(0) as base
Study the .split("").map(...).join("") pattern — this is the JS idiom for processing each character of a string
Add an HTML UI: text input for the message, number input for shift, buttons for Encrypt and Decrypt, output div
Wire the buttons to call encrypt/decrypt and display result in the output div

Expected Output

Khoor Zruog
Hello World

💡 One Hint

text.split("") turns "Hello" into ['H','e','l','l','o']. .map() transforms each character. .join("") puts them back together into a string. This is the standard JS pattern for character-by-character string processing.

Number to Words 🔢➡️📝

ARRAYSLOGICEASY~25 min

📖 Scenario

Same number converter in JavaScript. Identical logic to Python and Java — just JS syntax. After building it, create a live input field: type a number and the words appear instantly using the oninput event. This is a real feature you'd build for a fintech or banking web app.

Starter Code

const ones = ["","One","Two","Three","Four","Five",
    "Six","Seven","Eight","Nine","Ten","Eleven","Twelve",
    "Thirteen","Fourteen","Fifteen","Sixteen",
    "Seventeen","Eighteen","Nineteen"];
const tens = ["","","Twenty","Thirty","Forty",
    "Fifty","Sixty","Seventy","Eighty","Ninety"];

function toWords(n) {
    if (n === 0) return "Zero";
    if (n < 20)  return ones[n];
    if (n < 100) return (tens[Math.floor(n/10)] + " " + ones[n%10]).trim();
    // TODO: handle 100–999
}

Your Tasks

Fill 100–999: same logic as Java/Python. Use Math.floor(n/100) for the hundreds digit
Test with [0, 7, 15, 42, 100, 305, 999]
Add a live HTML input: <input oninput="document.getElementById('out').textContent = toWords(+this.value)"> The + before this.value converts the string "42" to the number 42
What happens if someone types a decimal like 3.7? Add a guard: if (!Number.isInteger(+n)) return "Integers only!"

Expected Output

0→Zero | 7→Seven | 42→Forty Two | 100→One Hundred | 999→Nine Hundred Ninety Nine

💡 One Hint

JS doesn't have integer division like Python's // or Java's int division. Use Math.floor(n / 100) to get the hundreds digit. n % 100 works the same as in Python and Java.

Bank Account — Full OOP 🏦

CLASSESDOMMEDIUM~30 min

📖 Scenario

Build the same BankAccount class in JS, then go one step further — render the statement as live HTML. Every deposit and withdrawal updates the screen in real time. This is exactly the kind of mini-app a junior frontend dev would build as a portfolio project.

Starter Code

class BankAccount {
    constructor(owner, balance = 0) {
        this.owner   = owner;
        this.balance = balance;
        this.history = [];
    }
    deposit(amount)  { /* TODO */ }
    withdraw(amount) { /* TODO — throw Error on bad input */ }
    render() {
        // TODO: update #owner, #balance, #history divs in the HTML
    }
}

const acc = new BankAccount("Rahul", 1000);
acc.deposit(500);
try { acc.withdraw(200); } catch(e) { console.log(e.message); }
try { acc.withdraw(5000); } catch(e) { console.log(e.message); }
acc.render();

Your Tasks

Fill deposit() and withdraw() — same logic, throw new Error("Insufficient funds!")
Fill render() — update DOM elements for owner name, balance, and history list
Add HTML: <input id="amt">, deposit and withdraw buttons that call the methods and re-render
Show errors in a <p id="err" style="color:red"> when withdraw fails, clear it on success

Expected Behaviour

Page shows owner name, live balance, and growing history list
Deposit → balance goes up, "+ ₹X" added to history
Bad withdrawal → red error message appears, balance unchanged

💡 One Hint

In render(): document.getElementById("balance").textContent = "₹" + this.balance. For history: build an HTML string inside this.history.map(t => "<li>" + t + "</li>").join("") and set it as innerHTML of a <ul>.

Basic SELECT & WHERE 🔍

SELECTWHEREEASY~15 min

📖 Scenario

You're a data analyst at a school. The database has a students table. Your job: write queries to answer the principal's questions — who passed, who failed, who's from a specific city. SELECT picks columns, WHERE filters rows.

🗄️ Concept: SELECT & WHERE

A SQL query has three main parts: what you want to see, where it lives, and a condition to filter rows.

You can ask for specific columns by name, or use a shortcut to get everything.
When comparing text values, wrap them in single quotes. Numbers don't need quotes.
The condition after WHERE works like an if statement — only rows that pass it are returned.

📋 The Table — students

TABLE: students

id	name	city	marks	grade
1	'Rahul'	'Delhi'	72	'B'
2	'Priya'	'Mumbai'	91	'A'
3	'Arjun'	'Delhi'	38	'F'
4	'Sneha'	'Pune'	85	'A'
5	'Vikram'	'Mumbai'	55	'C'
6	'Anita'	'Delhi'	94	'A'
7	'Rohan'	'Pune'	43	'F'

Your Tasks — Write one query per task

Select all columns from every student
Select only name and marks of all students
Get all students from Delhi Use a condition that checks if city matches a specific text value
Get students with marks above 80
Get students who failed (grade = 'F')

① Expected — SELECT *

id	name	city	marks	grade
1	Rahul	Delhi	72	B
... all 7 rows

③ Expected — Delhi students

id	name	city	marks	grade
1	Rahul	Delhi	72	B
3	Arjun	Delhi	38	F
6	Anita	Delhi	94	A

💡 One Hint

A SQL query always needs at minimum two things: which columns (SELECT) and which table (FROM). The filter (WHERE) is optional. Try writing your first query with just those two parts and then add the condition for Task ③ onwards. Don't forget — SQL keywords are not case sensitive, but the convention is to write them in UPPERCASE so they stand out from your column/table names.

ORDER BY & LIMIT 📊

ORDER BYLIMITEASY~15 min

📖 Scenario

The principal wants a ranked list — top scorers first. ORDER BY sorts your results (like Python's sorted()). LIMIT restricts how many rows come back — useful for "top 3" or "latest 5" queries.

🗄️ Concept: Sorting & Limiting

SQL lets you control the order of results — ascending means smallest to largest (or A to Z), descending is the opposite.

You can also tell SQL to give you only a certain number of rows instead of everything — useful when you just want the "top N".
These two features can be used together or separately.

📋 Same Table — students

TABLE: students

id	name	city	marks	grade
1	'Rahul'	'Delhi'	72	'B'
2	'Priya'	'Mumbai'	91	'A'
3	'Arjun'	'Delhi'	38	'F'
4	'Sneha'	'Pune'	85	'A'
5	'Vikram'	'Mumbai'	55	'C'
6	'Anita'	'Delhi'	94	'A'
7	'Rohan'	'Pune'	43	'F'

Your Tasks

Get all students sorted by marks highest first
Get top 3 scorers — highest marks, only 3 rows You'll need two keywords here — one for ordering, one for restricting the row count
Get students sorted by name A→Z
Get the bottom 2 scorers — lowest marks first, only 2 rows
Get Delhi students sorted by marks highest first — combine two of the concepts from this question

② Expected — Top 3 Scorers

id	name	city	marks	grade
6	Anita	Delhi	94	A
2	Priya	Mumbai	91	A
4	Sneha	Pune	85	A

⑤ Expected — Delhi sorted by marks

id	name	marks
6	Anita	94
1	Rahul	72
3	Arjun	38

💡 One Hint

The fixed order of clauses in a full query is: SELECT → FROM → WHERE → ORDER BY → LIMIT. You can't swap them around. Also — when sorting text alphabetically, ascending order is the default, so you can actually leave that keyword out and it still works the same way.

Aggregate Functions 🔢

COUNTSUMAVGMAX/MINEASY~20 min

📖 Scenario

SQL can calculate totals, averages, and counts directly — no Python loop needed! These are called aggregate functions and they collapse multiple rows into a single answer. The principal needs: how many students? What's the class average? Who scored highest?

🗄️ Concept: Aggregate Functions

Instead of fetching all rows and doing math in your code, SQL can do the math inside the database.

There are functions to count rows, add up a column, find the average, and find the highest or lowest value in a column.
These collapse many rows into a single answer — the result is always just one row (or one row per group).

📋 Same Table — students

TABLE: students

id	name	city	marks	grade
1	'Rahul'	'Delhi'	72	'B'
2	'Priya'	'Mumbai'	91	'A'
3	'Arjun'	'Delhi'	38	'F'
4	'Sneha'	'Pune'	85	'A'
5	'Vikram'	'Mumbai'	55	'C'
6	'Anita'	'Delhi'	94	'A'
7	'Rohan'	'Pune'	43	'F'

Your Tasks

Count the total number of students
Find the average marks of all students
Find the highest and lowest marks in one query You can call two aggregate functions in the same SELECT
Count how many students are from Delhi Filter the rows first, then count — think about what order these clauses go in
Find the average marks of only Grade A students

③ Expected — Max & Min

MAX(marks)	MIN(marks)
94	38

⑤ Expected — Avg of Grade A

AVG(marks)
90.0

💡 One Hint

For Task ⑤ think about the order of operations: SQL first picks the rows that match your condition, then runs the aggregate on just those rows. So you don't need anything special — just combine the filter and the aggregate the same way you would for any other query. Verify your answer: Priya (91) + Sneha (85) + Anita (94) = 270 ÷ 3 = 90.0 ✓

GROUP BY — Count per City 🏙️

GROUP BYHAVINGMEDIUM~25 min

📖 Scenario

GROUP BY is like Python's dictionary counting — instead of looping and counting manually, SQL does it in one query. "How many students per city?" "What's the average marks per grade?" GROUP BY groups rows with the same value and lets you aggregate each group. This is one of the most powerful SQL features.

🗄️ Concept: Grouping Rows

GROUP BY lets you split rows into buckets based on a column, then run an aggregate function on each bucket separately — like Python's word frequency counter but in one SQL line.

Important distinction: WHERE filters individual rows before any grouping happens. HAVING filters the groups themselves after grouping is done.
Rule of thumb: if your condition mentions a COUNT or AVG, it belongs in HAVING, not WHERE.

📋 Same Table — students

TABLE: students

id	name	city	marks	grade
1	'Rahul'	'Delhi'	72	'B'
2	'Priya'	'Mumbai'	91	'A'
3	'Arjun'	'Delhi'	38	'F'
4	'Sneha'	'Pune'	85	'A'
5	'Vikram'	'Mumbai'	55	'C'
6	'Anita'	'Delhi'	94	'A'
7	'Rohan'	'Pune'	43	'F'

Your Tasks

Count the number of students in each city You need to select city, count the rows, and group by city
Find the average marks per grade
Find cities that have more than 2 students You can't filter on a COUNT with WHERE — there's a different keyword for filtering after grouping
Find the highest marks per city

① Expected — Students per City

city	COUNT(*)
Delhi	3
Mumbai	2
Pune	2

② Expected — Avg marks per Grade

grade	AVG(marks)
A	90.0
B	72.0
C	55.0
F	40.5

💡 One Hint

The WHERE vs HAVING confusion trips up almost everyone. Here's the mental model: WHERE is a bouncer at the door — it decides which rows even get into the room before any grouping happens. HAVING is a judge at the end — it looks at each finished group and decides whether to keep it. If your condition is checking a COUNT or AVG, you're judging a group, so it goes in HAVING.

INSERT, UPDATE & DELETE ✏️

INSERTUPDATEDELETEMEDIUM~25 min

📖 Scenario

Reading data is only half the story — you also need to write, change, and remove it. INSERT adds new rows, UPDATE changes existing ones, and DELETE removes them. These are the core write operations and every real application uses all four: SELECT + INSERT + UPDATE + DELETE (often called CRUD).

🗄️ Concept: Writing Data (CRUD)

SELECT only reads data. To actually change the database you need three more operations:

Adding a new row requires you to name the columns and provide a matching list of values.
Changing an existing row lets you set one or more columns to new values — always narrow it down with a condition or you'll change every single row.
Removing rows works the same way — the condition decides which rows get deleted. No condition = all rows gone.

Together with SELECT, these four form what developers call CRUD: Create, Read, Update, Delete.

📋 Starting Table — students

TABLE: students (starting state)

id	name	city	marks	grade
1	'Rahul'	'Delhi'	72	'B'
2	'Priya'	'Mumbai'	91	'A'
3	'Arjun'	'Delhi'	38	'F'
4	'Sneha'	'Pune'	85	'A'
5	'Vikram'	'Mumbai'	55	'C'
6	'Anita'	'Delhi'	94	'A'
7	'Rohan'	'Pune'	43	'F'

Your Tasks — Run these queries in order

INSERT a new student: Karan from Bangalore, marks 78, grade B
UPDATE Arjun's marks to 65 and grade to 'C' You can change multiple columns at once — separate them with a comma in the SET clause
DELETE all students with grade 'F' ⚠️ After Task ②, Arjun is no longer grade F — think about who this will actually affect
Write a SELECT * to verify the final state of the table — should have 7 rows (original 7 + 1 inserted - 1 deleted)

④ Expected — Final Table State

id	name	city	marks	grade
1	Rahul	Delhi	72	B
2	Priya	Mumbai	91	A
3	Arjun	Delhi	65	C
4	Sneha	Pune	85	A
5	Vikram	Mumbai	55	C
6	Anita	Delhi	94	A
8	Karan	Bangalore	78	B

💡 One Hint

When updating multiple columns, you separate them with a comma inside the SET clause — you only write SET once. Notice in the final expected table that Rohan (id=7) is missing and the new student gets id=8 — databases don't reuse deleted IDs, they keep incrementing. This is why you should never assume IDs are consecutive.

5 Questions — Query & Conquer 🗄️