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

Anagram Checker 🔤

STRINGSORTINGEASY~20 min

📖 Scenario

Two words are anagrams of each other if they contain exactly the same letters in any order — "listen" and "silent" are anagrams, "hello" and "world" are not. Your function must ignore spaces and treat uppercase and lowercase as the same letter. This kind of check powers word games, plagiarism detectors, and puzzle solvers.

🧠 Key Idea If two strings are anagrams, their characters — when sorted alphabetically — will produce an identical result. Think about what Python's sorted() function does to a string, and what you get when you compare two sorted results.

Starter Code

def is_anagram(s1, s2):
    # TODO: remove spaces and convert both strings to the same case
    # TODO: sort the characters of each and compare
    # TODO: return True or False
    pass

print(is_anagram("listen",     "silent"))
print(is_anagram("hello",      "world"))
print(is_anagram("Astronomer", "Moon starer"))
print(is_anagram("abc",        "ab"))
print(is_anagram("",           ""))

Your Tasks

Strip all spaces from both strings and normalise their case so that uppercase and lowercase letters are treated identically
Sort the characters of each cleaned string and compare the two results — if they are equal, the strings are anagrams
Verify that "Astronomer" and "Moon starer" return True — trace through your logic manually to confirm why

Expected OutputTrue False True False True

💡 One Hint Think about what happens when you sort the letters of two anagrams — they must end up in the exact same order. The tricky part is normalisation: spaces and capital letters would throw off a direct comparison. Consider what string methods handle case and what handles whitespace, and apply both before sorting.

Grade Calculator 📝

DICTFUNCTIONSEASY~20 min

📖 Scenario

A school stores student names and their scores in a dictionary. You need to build a reporting system that assigns a letter grade to each score, calculates the class average, and identifies the top performer. Grading scale: 90+ → A, 80+ → B, 70+ → C, 60+ → D, below 60 → F.

Starter Code

students = {
    "Rahul":  85,
    "Priya":  92,
    "Arjun":  58,
    "Sneha":  74,
    "Vikram": 67,
}

def get_grade(score):
    # TODO: implement the grading scale with if/elif/else
    pass

def class_report(students):
    # TODO: loop and print each student's name, score, and grade
    # TODO: calculate and print the class average
    # TODO: find and print the top student
    pass

class_report(students)

Your Tasks

Implement get_grade(score) — it receives a number and must return the correct letter based on the grading scale described above
In class_report(), iterate through the dictionary and print each student's name, score, and the grade returned by get_grade()
Still inside class_report(), compute the average of all scores and identify the student with the highest score — print both

Expected OutputRahul: 85 -> B Priya: 92 -> A Arjun: 58 -> F Sneha: 74 -> C Vikram: 67 -> D Class average: 75.2 Top student: Priya (92)

💡 One Hint When iterating a dictionary you get keys by default — remember that .items() gives you both the key and the value at once. For finding the top student, think about which built-in function finds the maximum value in a collection, and how you can tell it which property of each item to compare.

Flatten a Nested List 📦

LISTLOOPEASY~20 min

📖 Scenario

Sometimes data arrives in a messy nested format — [1, [2, 3], 4, [5, 6]] — and you need it flat: [1, 2, 3, 4, 5, 6]. This is a common data-cleaning task in analytics, APIs, and file parsing. Your function handles one level of nesting only — a sub-item is either a plain value or a list of plain values.

Starter Code

def flatten(nested):
    result = []
    # TODO: loop through each item in nested
    #   if the item is itself a list, add its elements one by one
    #   if the item is a plain value, add it directly
    return result

print(flatten([1, [2, 3], 4, [5, 6]]))
print(flatten([[1, 2], [3, 4], [5, 6]]))
print(flatten([1, 2, 3]))
print(flatten([]))

Your Tasks

Write the loop body — for each item, decide whether it is a list or a plain value, and handle each case separately
For the case where the item is itself a list, you need an inner loop to unpack its elements one by one into result
Confirm your function handles all four test cases correctly, including the edge cases of an already-flat list and an empty list

Expected Output[1, 2, 3, 4, 5, 6] [1, 2, 3, 4, 5, 6] [1, 2, 3] []

💡 One Hint Python has a built-in way to check what type a value is — think about how you would ask "is this item a list?" Once you can answer that question, the rest is two straightforward append operations. An empty list passed in means your outer loop simply has nothing to iterate over, so result stays empty automatically.

Fibonacci — Iterative 🌀

LOOPLISTEASY~20 min

📖 Scenario

The Fibonacci sequence starts with 0 and 1, and every subsequent number is the sum of the two before it: 0, 1, 1, 2, 3, 5, 8, 13 … Your function takes a count n and returns a list of the first n Fibonacci numbers. You must solve this with a loop — no recursion.

🧠 Key Idea Start with the first two values. At each step the next value is the sum of the last two values already in your list. You already have those two values — they are the last two elements. Repeat this until your list has n elements.

Starter Code

def fibonacci(n):
    if n <= 0: return []
    if n == 1: return [0]
    result = [0, 1]
    # TODO: loop from 2 up to n, appending the next number each time
    return result

print(fibonacci(1))
print(fibonacci(5))
print(fibonacci(8))
print(fibonacci(10))

Your Tasks

Write the loop — it should run exactly enough times to bring the list up to n elements total
Inside the loop, calculate the next Fibonacci number from the last two elements already in result and append it
Verify n=1 returns only [0] and n=5 returns [0, 1, 1, 2, 3] — trace through your loop manually for n=5 to check

Expected Output[0] [0, 1, 1, 2, 3] [0, 1, 1, 2, 3, 5, 8, 13] [0, 1, 1, 2, 3, 5, 8, 13, 21, 34]

💡 One Hint Your list already starts with two elements. You need the loop to add the remaining n - 2 elements. Each new element is the sum of the element before it and the element before that — think about negative indexing to access the last and second-to-last items without knowing their positions explicitly.

Student Report Card — OOP 📋

OOPDICTMEDIUM~30 min

📖 Scenario

You are building a student management system. Each student has a name and a dictionary of subject scores. The Student class must be able to calculate its own average, identify its own best and weakest subjects, and print a formatted report — all as methods. This is the classic OOP principle of keeping data and the logic that operates on it in the same place.

Starter Code

class Student:
    def __init__(self, name, scores):
        # scores is a dict: {"Maths": 88, "Science": 74, ...}
        # TODO: store name and scores on the object
        pass

    def average(self):
        # TODO: return the average of all scores
        pass

    def highest_subject(self):
        # TODO: return the subject name with the highest score
        pass

    def lowest_subject(self):
        # TODO: return the subject name with the lowest score
        pass

    def report(self):
        # TODO: print the student's name as a header, then each subject
        # and score, then the average, best, and weakest subject
        pass

s1 = Student("Rahul", {"Maths": 88, "Science": 74, "English": 91})
s2 = Student("Priya", {"Maths": 95, "Science": 89, "English": 78})
s1.report()
s2.report()

Your Tasks

Fill __init__ — store both arguments on the object so all other methods can access them via self
Fill average(), highest_subject(), and lowest_subject() — each method should operate on self.scores
Fill report() — call the other three methods from inside it and format the output to match the expected output exactly

Expected Output--- Rahul's Report --- Maths: 88 Science: 74 English: 91 Average: 84.3 Best: English Weakest: Science --- Priya's Report --- Maths: 95 Science: 89 English: 78 Average: 87.3 Best: Maths Weakest: English

💡 One Hint A method inside a class can call another method on the same object using self — report() does not need to re-implement the average calculation, it can simply call self.average(). For the highest and lowest subjects, think about how you find a maximum or minimum value from a dictionary where the values are the scores and the keys are the subject names.

Anagram Checker 🔤

STRINGARRAYSEASY~20 min

📖 Scenario

Two strings are anagrams when they contain exactly the same characters in any order — "listen" and "silent" are anagrams, "hello" and "world" are not. Your method must be case-insensitive and must ignore spaces. Word games, spell-checkers, and cryptographic tools all use this kind of check.

🧠 Key Idea If you sort the characters of two anagram strings, the results will be identical. In Java, a String cannot be sorted directly — you need to convert it to an array of characters first, sort that array, then compare. Arrays.sort() and Arrays.equals() are your tools here.

Starter Code

import java.util.Arrays;

public class AnagramChecker {

    static boolean isAnagram(String s1, String s2) {
        // TODO: remove spaces and convert both to the same case
        // TODO: convert each String to a char[] and sort it
        // TODO: compare the two sorted arrays and return the result
        return false;
    }

    public static void main(String[] args) {
        System.out.println(isAnagram("listen",     "silent"));
        System.out.println(isAnagram("hello",      "world"));
        System.out.println(isAnagram("Astronomer", "Moon starer"));
        System.out.println(isAnagram("abc",        "ab"));
        System.out.println(isAnagram("",           ""));
    }
}

Your Tasks

Clean both strings — remove spaces and normalise the case to lowercase, so that capital and lowercase letters are treated as identical
Convert each cleaned string to a character array, sort both arrays, then compare them for equality
Test with "Astronomer" / "Moon starer" — trace each step manually to confirm your cleaning and sorting logic handles spaces and mixed case correctly

Expected Outputtrue false true false true

💡 One Hint Java Strings have methods for replacing characters and changing case — think about which method removes all occurrences of a specific character and which method produces an entirely lowercase copy. Once you have two clean char arrays that are sorted, remember that you cannot compare arrays with == in Java — there is a dedicated method in the Arrays class for comparing array contents.

Grade Calculator 📝

HASHMAPMETHODSEASY~20 min

📖 Scenario

A school stores student records as a HashMap where each key is a student's name and the value is their numeric score. Your program must assign a letter grade to each score, compute the class average, and identify the top student. Grading scale: 90+ → A, 80+ → B, 70+ → C, 60+ → D, below 60 → F.

Starter Code

import java.util.*;

public class GradeCalculator {

    static String getGrade(int score) {
        // TODO: implement if/else if chain for the grading scale
        return "";
    }

    static void classReport(HashMap<String, Integer> students) {
        // TODO: loop through entrySet(), print name, score, grade
        // TODO: calculate and print the average
        // TODO: find and print the top student
    }

    public static void main(String[] args) {
        HashMap<String, Integer> students = new HashMap<>();
        students.put("Rahul",  85);
        students.put("Priya",  92);
        students.put("Arjun",  58);
        students.put("Sneha",  74);
        students.put("Vikram", 67);
        classReport(students);
    }
}

Your Tasks

Implement getGrade() — use an if/else if chain that checks the score against each threshold in the right order and returns the appropriate letter
In classReport(), iterate through the map using entrySet() and print each student's name, score, and grade
After the loop, compute the average from all values and identify the student with the highest score — print both

Expected OutputRahul: 85 -> B Priya: 92 -> A Arjun: 58 -> F Sneha: 74 -> C Vikram: 67 -> D Class average: 75.2 Top student: Priya (92)

💡 One Hint HashMap does not guarantee any particular iteration order — if your output shows students in a different sequence that is acceptable, as long as every name/score/grade is correct. For finding the top student, a tracking variable pattern works well: keep track of the highest score seen so far and the name that goes with it, updating both whenever you encounter a higher score.

Flatten a Nested Structure 📦

ARRAYLISTGENERICSEASY~20 min

📖 Scenario

Data sometimes arrives in a nested structure — a list where some elements are plain integers and others are sub-lists of integers. Your job is to produce a single flat list containing all integers in order. You must handle one level of nesting only. This pattern appears constantly in data pipelines and API response handling.

Starter Code

import java.util.*;

public class Flatten {

    static List<Integer> flatten(List<Object> nested) {
        List<Integer> result = new ArrayList<>();
        // TODO: loop through nested
        //   if item is a List, loop through its elements and add each
        //   if item is an Integer, add it directly
        return result;
    }

    public static void main(String[] args) {
        System.out.println(flatten(Arrays.asList(1, Arrays.asList(2,3), 4, Arrays.asList(5,6))));
        System.out.println(flatten(Arrays.asList(Arrays.asList(1,2), Arrays.asList(3,4), Arrays.asList(5,6))));
        System.out.println(flatten(Arrays.asList(1, 2, 3)));
        System.out.println(flatten(Collections.emptyList()));
    }
}

Your Tasks

Write the outer loop — iterate over each item in nested
Use an instanceof check to determine whether the current item is a List or a plain Integer, and handle each case — sub-lists require an inner loop, plain values go straight into result
Verify all four test cases produce the correct output, including the empty list which should produce an empty result with no errors

Expected Output[1, 2, 3, 4, 5, 6] [1, 2, 3, 4, 5, 6] [1, 2, 3] []

💡 One Hint The instanceof keyword in Java lets you ask "is this object an instance of a particular class?" — use it to branch between the two cases. When the item is a List, you will need to cast it before you can iterate it, since the outer list holds Object references. An empty input list simply means the outer loop body never executes, so result stays as an empty ArrayList.

Fibonacci — Iterative 🌀

LOOPARRAYLISTEASY~20 min

📖 Scenario

The Fibonacci sequence starts with 0 and 1. Every number after that is the sum of the two numbers that precede it: 0, 1, 1, 2, 3, 5, 8, 13 … Your method receives a count n and returns an ArrayList containing the first n Fibonacci numbers. Implement this with a loop — recursion is not needed here.

Starter Code

import java.util.*;

public class Fibonacci {

    static List<Integer> fibonacci(int n) {
        if (n <= 0) return new ArrayList<>();
        List<Integer> result = new ArrayList<>();
        result.add(0);
        if (n == 1) return result;
        result.add(1);
        // TODO: loop to generate the remaining n-2 numbers
        return result;
    }

    public static void main(String[] args) {
        System.out.println(fibonacci(1));
        System.out.println(fibonacci(5));
        System.out.println(fibonacci(8));
        System.out.println(fibonacci(10));
    }
}

Your Tasks

Write the loop — it needs to run exactly n - 2 times, once for each number still to be added
Each iteration should calculate the next Fibonacci number from the last two elements already in the list and add it
Trace the loop manually for n=5 — what are the two values used at each step, and what number gets added each time?

Expected Output[0] [0, 1, 1, 2, 3] [0, 1, 1, 2, 3, 5, 8, 13] [0, 1, 1, 2, 3, 5, 8, 13, 21, 34]

💡 One Hint The list already contains two elements before your loop starts. To get the last element of an ArrayList, use its size to calculate the correct index. The second-to-last element is one position before that. At each step, add those two together — that is your next Fibonacci number.

Student Report Card — OOP 📋

OOPHASHMAPMEDIUM~30 min

📖 Scenario

You are building a student management system. Each Student object holds a name and a HashMap mapping subject names to scores. The class must expose methods to compute the average, find the best subject, find the weakest subject, and print a full formatted report. The report method should call the other methods — not reimplement their logic.

Starter Code

import java.util.*;

class Student {
    String name;
    HashMap<String, Integer> scores;

    Student(String name, HashMap<String, Integer> scores) {
        // TODO: store name and scores
    }

    double average() {
        // TODO: return average of all scores
        return 0;
    }

    String highestSubject() {
        // TODO: return subject name with the highest score
        return "";
    }

    String lowestSubject() {
        // TODO: return subject name with the lowest score
        return "";
    }

    void report() {
        // TODO: print header, each subject and score, average, best, weakest
    }
}

public class Main {
    public static void main(String[] args) {
        HashMap<String, Integer> r = new HashMap<>();
        r.put("Maths", 88); r.put("Science", 74); r.put("English", 91);
        HashMap<String, Integer> p = new HashMap<>();
        p.put("Maths", 95); p.put("Science", 89); p.put("English", 78);
        new Student("Rahul", r).report();
        new Student("Priya", p).report();
    }
}

Your Tasks

Fill the constructor — assign both parameters to the instance fields
Implement average(), highestSubject(), and lowestSubject() — each should iterate over the scores map using entrySet() to access both the key (subject name) and the value (score)
Implement report() — it should call the other three methods rather than recalculating anything itself

💡 One Hint For the highest and lowest subject, the tracking-variable pattern is reliable — start with the first entry as your initial candidate, then compare every subsequent entry against it. For the average, accumulate the total by looping over values, then divide — remember that dividing two integers in Java gives an integer result, so consider how to force a decimal division.

Anagram Checker 🔤

STRINGARRAY METHODSEASY~20 min

📖 Scenario

Two strings are anagrams when they contain exactly the same characters regardless of order — "listen" and "silent" are anagrams, "hello" and "world" are not. Your function must ignore spaces and be case-insensitive. This logic powers word games, duplicate detection tools, and natural language processing applications.

🧠 Key Idea Sorting the characters of two anagram strings will always produce the same result. In JavaScript, strings have a split method that converts them to arrays, arrays have a sort method, and arrays can be joined back to strings for easy comparison with ===.

Starter Code

function isAnagram(s1, s2) {
    // TODO: remove spaces and convert both strings to the same case
    // TODO: split each into an array of characters, sort, and join back
    // TODO: compare the two resulting strings and return true or false
}

console.log(isAnagram("listen",     "silent"));
console.log(isAnagram("hello",      "world"));
console.log(isAnagram("Astronomer", "Moon starer"));
console.log(isAnagram("abc",        "ab"));
console.log(isAnagram("",           ""));

Your Tasks

Clean both strings — remove all space characters and convert to a consistent case so that capital letters do not affect the comparison
Sort the characters of each cleaned string alphabetically and produce a sorted string from each one
Return whether the two sorted strings are strictly equal — test with the five cases and verify your output matches

Expected Outputtrue false true false true

💡 One Hint Think about the chain of transformations a string needs to go through: first remove what you don't want, then change the case, then break it into individual characters, then sort those characters, then reassemble them into a string for comparison. JavaScript strings and arrays each have methods for each of those steps — look for them before writing any loops.

Grade Calculator 📝

OBJECTFUNCTIONSEASY~20 min

📖 Scenario

A school stores its student records as a JavaScript object where keys are student names and values are numeric scores. Your program must assign a letter grade to each score, compute the class average, and identify the top student. Grading scale: 90+ → A, 80+ → B, 70+ → C, 60+ → D, below 60 → F.

Starter Code

const students = {
    Rahul:  85,
    Priya:  92,
    Arjun:  58,
    Sneha:  74,
    Vikram: 67,
};

function getGrade(score) {
    // TODO: return the correct letter grade using if/else if/else
}

function classReport(students) {
    // TODO: iterate over each student and log name, score, and grade
    // TODO: compute and log the class average
    // TODO: find and log the top student
}

classReport(students);

Your Tasks

Implement getGrade(score) using a chain of conditionals that checks thresholds from highest to lowest and returns the correct letter
In classReport(), loop over the object's entries and log each student's name, score, and the grade returned by calling getGrade()
Compute the average from all score values and identify the student whose score is the highest — log both

Expected OutputRahul: 85 -> B Priya: 92 -> A Arjun: 58 -> F Sneha: 74 -> C Vikram: 67 -> D Class average: 75.2 Top student: Priya (92)

💡 One Hint To iterate over a JavaScript object and get both the key and the value at the same time, think about which Object method gives you an array of key-value pairs that you can then loop over. For computing the average, you already need to visit every score once — combine that with finding the maximum so you only loop through the data one time.

Flatten a Nested Array 📦

ARRAYREDUCEEASY~20 min

📖 Scenario

An array arrives where some elements are plain numbers and others are nested arrays of numbers: [1, [2, 3], 4, [5, 6]]. Your task is to produce a flat array with all the values in order. You must handle one level of nesting only. This data-cleaning step is extremely common when merging API responses or combining data from multiple sources.

Starter Code

function flatten(nested) {
    const result = [];
    // TODO: loop through nested
    //   if item is an Array, push each of its elements into result
    //   if item is a plain value, push it directly
    return result;
}

console.log(flatten([1, [2,3], 4, [5,6]]));
console.log(flatten([[1,2], [3,4], [5,6]]));
console.log(flatten([1, 2, 3]));
console.log(flatten([]));

Your Tasks

Write the loop body — for each element, determine whether it is an Array or a plain value
Handle the Array case by pushing each sub-element individually into result, and the plain value case by pushing it directly
Once your loop version works, try rewriting the entire function body as a single expression using reduce and see if you can produce the same result

Expected Output[1, 2, 3, 4, 5, 6] [1, 2, 3, 4, 5, 6] [1, 2, 3] []

💡 One Hint JavaScript has a built-in way to check whether a value is an array — it is a static method on the Array object, not an instanceof check. When the item is an array, think about which array method adds multiple elements to another array at once, versus which method adds a single element. The reduce version in Task ③ works by accumulating — starting from an empty array and merging each item into it.

Fibonacci — Iterative 🌀

LOOPARRAYEASY~20 min

📖 Scenario

The Fibonacci sequence begins with 0 and 1. Every number that follows is the sum of the two numbers immediately before it: 0, 1, 1, 2, 3, 5, 8, 13 … Your function receives a count n and must return an array of the first n Fibonacci numbers. Build this with a regular loop — no recursion required.

Starter Code

function fibonacci(n) {
    if (n <= 0) return [];
    if (n === 1) return [0];
    const result = [0, 1];
    // TODO: loop and push the next number until result has n elements
    return result;
}

console.log(fibonacci(1));
console.log(fibonacci(5));
console.log(fibonacci(8));
console.log(fibonacci(10));

Your Tasks

Write the loop — decide how many iterations are needed to bring result from 2 elements up to n elements
Each iteration must read the last two elements already in result, compute their sum, and push it
Trace the loop for n=5 step by step — write down what result looks like after each push until you reach 5 elements

Expected Output[0] [0, 1, 1, 2, 3] [0, 1, 1, 2, 3, 5, 8, 13] [0, 1, 1, 2, 3, 5, 8, 13, 21, 34]

💡 One Hint Your array starts with two values already in it. The loop needs to run exactly as many times as there are values still missing. To access the last element of an array without knowing its length explicitly, think about how negative indices work in Python — JavaScript does not support that syntax, but it does have a method that returns the last element of an array without removing it.

Student Report Card — OOP 📋

CLASSOBJECTMEDIUM~30 min

📖 Scenario

You are building a student management system in JavaScript. Each Student instance stores a name and an object mapping subject names to scores. The class must provide methods to calculate the average score, find the best subject, find the weakest subject, and print a formatted report. The report method must delegate to the other methods rather than duplicate their logic.

Starter Code

class Student {
    constructor(name, scores) {
        // TODO: store name and scores on the instance
    }

    average() {
        // TODO: return the average of all values in this.scores
    }

    highestSubject() {
        // TODO: return the key with the highest value in this.scores
    }

    lowestSubject() {
        // TODO: return the key with the lowest value in this.scores
    }

    report() {
        // TODO: log the header, each subject and score, then call
        // this.average(), this.highestSubject(), this.lowestSubject()
    }
}

const s1 = new Student("Rahul", { Maths: 88, Science: 74, English: 91 });
const s2 = new Student("Priya", { Maths: 95, Science: 89, English: 78 });
s1.report();
s2.report();

Your Tasks

Fill the constructor — store both parameters so that all methods can access them through this
Implement average(), highestSubject(), and lowestSubject() — each must work from this.scores using the keys as subject names and values as scores
Implement report() — iterate over the scores object to print each subject, then call the three helper methods to print the summary

💡 One Hint To iterate over an object and access both keys and values, think about which Object method produces an array of pairs that you can then loop or reduce over. For finding the subject with the highest or lowest score, consider reducing the entries down to a single winning entry by comparing values at each step.

SELECT & WHERE — Employee Lookup 🔍

SELECTWHEREEASY~15 min

📖 Scenario

You have been given access to a company's employees table. The HR team needs you to answer three specific lookup questions. Each question requires you to decide which columns to display and which condition to filter by — two fundamental decisions in every SQL query.

TABLE: employees

id	name	department	salary	years
1	'Rahul'	'Engineering'	85000	5
2	'Priya'	'Marketing'	62000	3
3	'Arjun'	'Engineering'	92000	8
4	'Sneha'	'HR'	54000	2
5	'Vikram'	'Marketing'	71000	6
6	'Anita'	'Engineering'	78000	4
7	'Rohan'	'HR'	48000	1
8	'Divya'	'Marketing'	68000	5

Your Tasks

Retrieve the name and salary of every employee in the Engineering department
Retrieve all columns for every employee earning more than 70,000
Retrieve only the name column for every employee in the HR department

① Engineering — name and salary

name	salary
Rahul	85000
Arjun	92000
Anita	78000

② Salary > 70000 — all columns

id	name	department	salary	years
1	Rahul	Engineering	85000	5
3	Arjun	Engineering	92000	8
5	Vikram	Marketing	71000	6
6	Anita	Engineering	78000	4

③ HR — name only

name
Sneha
Rohan

💡 One Hint Every query needs a SELECT clause (what to show) and a FROM clause (which table). When you only want some rows, add a WHERE clause with a condition. Text values in a condition require quotes around them; numeric values do not. Think carefully about Task ②: "more than 70,000" means strictly greater than — Vikram at exactly 71,000 qualifies, but would someone at exactly 70,000 qualify?

ORDER BY & LIMIT — Rankings 📊

ORDER BYLIMITEASY~15 min

📖 Scenario

The finance team needs ranked views of the data — who are the top earners, who are the newest employees, and how does Marketing rank internally by salary. Sorting and limiting are two of the most frequently used SQL operations in dashboards and reports.

TABLE: employees (same table)

id	name	department	salary	years
1	'Rahul'	'Engineering'	85000	5
2	'Priya'	'Marketing'	62000	3
3	'Arjun'	'Engineering'	92000	8
4	'Sneha'	'HR'	54000	2
5	'Vikram'	'Marketing'	71000	6
6	'Anita'	'Engineering'	78000	4
7	'Rohan'	'HR'	48000	1
8	'Divya'	'Marketing'	68000	5

Your Tasks

Return the name and salary of the 3 highest-paid employees, with the highest salary first
Return the name and years of the 2 least experienced employees, with the fewest years first
Return the name and salary of all Marketing employees, ordered from highest to lowest salary

① Top 3 earners

name	salary
Arjun	92000
Rahul	85000
Anita	78000

② 2 least experienced

name	years
Rohan	1
Sneha	2

③ Marketing by salary DESC

name	salary
Vikram	71000
Divya	68000
Priya	62000

💡 One Hint When you need both a filter and a sort, the WHERE clause must come before ORDER BY — SQL requires a specific clause order. For Tasks ① and ②, you need to sort the entire table first and then restrict how many rows come back. Think about which keyword restricts the number of rows returned and where in the clause order it belongs.

Aggregate Functions — Company Stats 🔢

COUNTSUMAVGMAX MINEASY~15 min

📖 Scenario

The CFO needs a quick statistical overview of company compensation. Rather than pulling all rows and computing in a spreadsheet, you can ask SQL to calculate these numbers directly in the query. Each aggregate function collapses all matching rows into a single summary value.

TABLE: employees (same table)

id	name	department	salary	years
1	'Rahul'	'Engineering'	85000	5
2	'Priya'	'Marketing'	62000	3
3	'Arjun'	'Engineering'	92000	8
4	'Sneha'	'HR'	54000	2
5	'Vikram'	'Marketing'	71000	6
6	'Anita'	'Engineering'	78000	4
7	'Rohan'	'HR'	48000	1
8	'Divya'	'Marketing'	68000	5

Your Tasks

In a single query, retrieve: total headcount, total salary spend, average salary, highest salary, and lowest salary across the entire company
Write a separate query for Engineering employees only — retrieve their headcount and average salary
Before running ①, add up all 8 salaries by hand and divide by 8 — verify that your SQL result matches your manual calculation

① Company-wide stats

COUNT(*)	SUM(salary)	AVG(salary)	MAX(salary)	MIN(salary)
8	558000	69750.00	92000	48000

② Engineering stats

COUNT(*)	AVG(salary)
3	85000.00

💡 One Hint Multiple aggregate functions can appear in the same SELECT clause — you do not need a separate query for each statistic. For Task ②, combine an aggregate with a WHERE clause — the filter narrows which rows the aggregate operates on. Verify Task ③ manually: add 85000 + 62000 + 92000 + 54000 + 71000 + 78000 + 48000 + 68000, then divide by 8.

GROUP BY — Department Breakdown 🏷️

GROUP BYCOUNTAVGMEDIUM~20 min

📖 Scenario

The HR team wants a breakdown of statistics per department rather than across the whole company. GROUP BY is the SQL feature that splits a table into buckets based on a column's value and then lets aggregate functions operate independently on each bucket — producing one summary row per department.

🗄️ Concept: GROUP BY

When you add GROUP BY to a query, SQL creates one group per unique value in that column and applies aggregates to each group separately. Any column in your SELECT that is not wrapped in an aggregate must appear in the GROUP BY clause — otherwise the database does not know which row's value to display for the group.

TABLE: employees (same table)

id	name	department	salary	years
1	'Rahul'	'Engineering'	85000	5
2	'Priya'	'Marketing'	62000	3
3	'Arjun'	'Engineering'	92000	8
4	'Sneha'	'HR'	54000	2
5	'Vikram'	'Marketing'	71000	6
6	'Anita'	'Engineering'	78000	4
7	'Rohan'	'HR'	48000	1
8	'Divya'	'Marketing'	68000	5

Your Tasks

For each department, show its name, the number of employees in it, and the average salary — one row per department
For each department, show its name and the total salary spend — one row per department
Verify your ① results manually: calculate the average salary for Engineering (85000, 92000, 78000), Marketing (62000, 71000, 68000), and HR (54000, 48000) by hand

① Count and average salary per department

department	COUNT(*)	AVG(salary)
Engineering	3	85000.00
HR	2	51000.00
Marketing	3	67000.00

② Total salary spend per department

department	SUM(salary)
Engineering	255000
HR	102000
Marketing	201000

💡 One Hint The department column must appear in both the SELECT list and the GROUP BY clause — it is the grouping key. The aggregate functions (COUNT, AVG, SUM) do not need to appear in GROUP BY because they summarise the group rather than identify it. If your rows come out in a different order than the expected output, that is acceptable — add an ORDER BY department to make it consistent.

HAVING — Filtering Groups 🎯

HAVINGGROUP BYMEDIUM~20 min

📖 Scenario

You now need to filter groups themselves — not individual rows. For example: "which departments have an average salary above 65,000?" or "which departments have more than two employees?" These conditions apply to the group as a whole, not to any single row, which is why a regular WHERE clause cannot handle them. SQL provides HAVING for exactly this purpose.

🗄️ Concept: WHERE vs HAVING

WHERE filters individual rows before any grouping takes place. HAVING filters entire groups after grouping is complete. The key rule: if your condition refers to an aggregate function such as AVG, COUNT, or SUM, it must go in HAVING — WHERE cannot see aggregate results.

TABLE: employees (same table)

id	name	department	salary	years
1	'Rahul'	'Engineering'	85000	5
2	'Priya'	'Marketing'	62000	3
3	'Arjun'	'Engineering'	92000	8
4	'Sneha'	'HR'	54000	2
5	'Vikram'	'Marketing'	71000	6
6	'Anita'	'Engineering'	78000	4
7	'Rohan'	'HR'	48000	1
8	'Divya'	'Marketing'	68000	5

Your Tasks

Return the department name and average salary for every department whose average salary exceeds 65,000 — you will need GROUP BY, an aggregate function, and HAVING
Return the department name and employee count for every department with more than 2 employees
Explain in writing why you cannot use WHERE instead of HAVING for these two tasks — what would go wrong?

① Departments with AVG salary > 65000

department	AVG(salary)
Engineering	85000.00
Marketing	67000.00

② Departments with more than 2 employees

department	COUNT(*)
Engineering	3
Marketing	3

💡 One Hint The full clause order for a grouped query with filtering on groups is: SELECT → FROM → WHERE (optional) → GROUP BY → HAVING → ORDER BY (optional). Notice that HAVING always comes after GROUP BY — it cannot appear before it. For Task ③, think about when in the process each clause runs — WHERE runs on individual rows before groups exist, so it cannot see a value that only exists after grouping is complete.

Python · Java · JavaScript · SQL