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

Count Vowels in a Sentence 🔤

STRINGLOOPEASY~15 min

📖 Scenario

A simple text analyser that counts how many vowels appear in any sentence. This logic is commonly used in word games, typing tests, and linguistics tools.

Starter Code

def count_vowels(sentence):
    vowels = "aeiouAEIOU"
    count  = 0
    for char in sentence:
        if char in vowels:
            # TODO: increment the counter
            pass
            
    # TODO: return the final count

sentences = ["Hello World", "Python is fun", "aeiou", "rhythm"]
# TODO: Loop through the 'sentences' list and call the function

Your Tasks

Fill in the missing logic inside the function to increment the counter and return the final total.
Loop through the sentences list and print each sentence alongside its vowel count.
Why does "rhythm" return 0? List all its characters and verify the condition manually.
Modify the function to also count consonants (letters that are not vowels) and print both counts for "Hello World". Use .isalpha() to ensure you skip spaces.

Expected Output (Task ①)"Hello World" → 3 vowels "Python is fun" → 3 vowels "aeiou" → 5 vowels "rhythm" → 0 vowels

💡 One Hint How do you check if a character is NOT inside a specific string? Also, check out string methods that verify if a character is an alphabet letter so you can safely ignore spaces and punctuation when counting consonants.

Reverse a List Without Slicing 🔄

LISTLOOPEASY~15 min

📖 Scenario

Python has [::-1] as a brilliant shortcut to reverse a list, but understanding how to do it manually — walking backwards using an index — teaches you how array indexing really works under the hood.

Starter Code

def reverse_list(lst):
    result = []
    for i in range(len(lst) - 1, -1, -1):
        # TODO: append the item at the current index to 'result'
        pass
        
    return result

print(reverse_list([1, 2, 3, 4, 5]))
print(reverse_list(["a", "b", "c"]))
print(reverse_list([]))

Your Tasks

Fill in the missing line inside the loop to extract the value at index i and add it to the new list. Run the code to confirm the output matches.
Write out exactly what sequence of numbers range(4, -1, -1) produces.
Write a completely new function reverse_string(s) that does the exact same thing for a string. Remember, strings are joined, not appended.

Expected Output[5, 4, 3, 2, 1] ['c', 'b', 'a'] []

💡 One Hint Remember that strings can be concatenated just like lists can be appended. Set up an empty string and add to it inside a loop that steps backward through the indices.

Find Duplicates in a List 🔍

LISTLOOPEASY~20 min

📖 Scenario

Detecting duplicates is a core task in fraud detection, finding repeated orders, and cleaning up raw datasets. This algorithm uses two lists working together — one tracks everything seen so far, and the other tracks what appeared more than once.

Starter Code

def find_duplicates(lst):
    seen       = []
    duplicates = []
    for item in lst:
        if item in seen and item not in duplicates:
            # TODO: add item to the duplicates list
            pass
            
        # TODO: ensure the item is always added to the 'seen' list
        pass
        
    return duplicates

print(find_duplicates([1, 2, 3, 2, 4, 3, 5]))
print(find_duplicates(["apple", "banana", "apple", "cherry", "banana"]))
print(find_duplicates([1, 2, 3]))
print(find_duplicates([5, 5, 5, 5]))

Your Tasks

Fill in the two TODOs to manage the internal tracking lists. Run the code and confirm all 4 outputs are correct.
Trace through the array [1, 2, 3, 2, 4, 3, 5] step by step on paper — after each item is processed, write down what both the seen and duplicates lists look like.
Why does the input [5, 5, 5, 5] return [5] and not [5, 5, 5]? Which specific part of the if condition prevents it from firing multiple times?

Expected Output[2, 3] ['apple', 'banana'] [] [5]

💡 One Hint Walk through the array manually. When the loop hits the second '5', what are the exact conditions it checks? Why does it skip the append operation the third and fourth time?

Simple Calculator 🧮

FUNCTIONSIF/ELSEEASY~15 min

📖 Scenario

A functional calculator program takes two numbers and an operator symbol, parses the intent, and returns the result. The edge case of dividing by zero is already handled to prevent a crash.

Starter Code

def calculator(a, op, b):
    if   op == '+': 
        # TODO: return addition
        pass
    elif op == '-': 
        # TODO: return subtraction
        pass
    elif op == '*': 
        # TODO: return multiplication
        pass
    elif op == '/':
        if b == 0:
            return "Error: division by zero"
        # TODO: return division
        pass

print(calculator(10, '+', 5))
print(calculator(10, '-', 3))
print(calculator( 6, '*', 7))
print(calculator(15, '/', 4))
print(calculator( 9, '/', 0))

Your Tasks

Fill in the mathematical operations for each branch and verify all 5 outputs are correct.
Why does 15 / 4 give 3.75 and not 3? What specific Python operator would give you a flat 3?
Add a 5th operation: if the operator is %, return the remainder of the division. Test it with calculator(10, '%', 3) — the expected answer is 1.

Expected Output15 7 42 3.75 Error: division by zero

💡 One Hint The modulo operator (`%`) gives you the remainder of a division. You just need to add one more `elif` branch to handle this specific string symbol.

Temperature Converter 🌡️

FUNCTIONSMATHSEASY~15 min

📖 Scenario

Weather apps, medical equipment, and cooking timers all rely on background functions to convert between Celsius and Fahrenheit seamlessly. This is excellent practice for writing single-purpose, highly reliable utility functions.

Starter Code

def celsius_to_fahrenheit(c):
    # formula: (c * 9/5) + 32
    # TODO: return the calculated value
    pass

def fahrenheit_to_celsius(f):
    # formula: (f - 32) * 5/9
    # TODO: return the calculated value
    pass

for c in [0, 100, 37, -40]:
    print(f"{c}°C → {celsius_to_fahrenheit(c):.2f}°F")

print()

for f in [32, 212, 98.6, -40]:
    print(f"{f}°F → {fahrenheit_to_celsius(f):.2f}°C")

Your Tasks

Implement the mathematical logic inside the functions based on the comments. Run the code and confirm every output matches perfectly.
Notice that -40 converts to -40 in both directions — verify this by hand using the formula on paper to see the math in action.
Write a completely new function celsius_to_kelvin(c). The formula for Kelvin is simply the Celsius value plus the constant 273.15. Run a test case ensuring 0°C maps to 273.15K.

Expected Output0°C → 32.00°F 100°C → 212.00°F 37°C → 98.60°F -40°C → -40.00°F 32°F → 0.00°C 212°F → 100.00°C 98.6°F → 37.00°C -40°F → -40.00°C

💡 One Hint You don't need a complex formula for Kelvin—it's just a flat mathematical addition to the Celsius value. Create a new function that takes `c` and adds the constant.

Count Vowels in a Sentence 🔤

STRINGLOOPEASY~15 min

📖 Scenario

A simple text analyser that counts how many vowels appear in any sentence. In Java, you can use String.valueOf(ch) to convert characters and then vowels.contains() to check membership safely.

Starter Code

public class VowelCounter {
    static int countVowels(String sentence) {
        String vowels = "aeiouAEIOU";
        int count = 0;
        for (char ch : sentence.toCharArray()) {
            if (vowels.contains(String.valueOf(ch))) {
                // TODO: increment count
            }
        }
        // TODO: return count
        return 0;
    }

    public static void main(String[] args) {
        String[] sentences = {"Hello World", "Python is fun", "aeiou", "rhythm"};
        // TODO: loop through sentences and print results
    }
}

Your Tasks

Implement the logic inside the function and execute the loop in the main method to run all tests. Confirm all 4 outputs match.
Trace what sentence.toCharArray() does for "Hello" — what exact primitive array does it produce?
Add a secondary countConsonants(String s) method and print both counts for "Hello World". Use Character.isLetter(ch) to ensure you skip spaces and punctuation.

Expected Output"Hello World" → 3 vowels "Python is fun" → 3 vowels "aeiou" → 5 vowels "rhythm" → 0 vowels

💡 One Hint To count consonants, you need to verify two conditions simultaneously: is the character actually a letter, AND is it completely absent from the vowels string? Look up `Character.isLetter()`.

Reverse an Array 🔄

ARRAYLOOPEASY~15 min

📖 Scenario

Reversing arrays is a fundamental algorithmic skill. Java doesn't have a rapid built-in one-liner for primitive arrays, so you must loop backwards and collect the items manually into a brand new array. The index arithmetic is the only tricky part.

Starter Code

import java.util.Arrays;

public class ReverseArray {
    static int[] reverseArray(int[] arr) {
        int[] result = new int[arr.length];
        for (int i = 0; i < arr.length; i++) {
            // TODO: map the position backwards
        }
        return result;
    }

    public static void main(String[] args) {
        System.out.println(Arrays.toString(reverseArray(new int[]{1,2,3,4,5})));
        System.out.println(Arrays.toString(reverseArray(new int[]{10,20,30})));
        System.out.println(Arrays.toString(reverseArray(new int[]{7})));
    }
}

Your Tasks

Figure out the mathematical relationship between the current index i and the end of the array to populate the result array. Run the code and confirm the output.
Trace through {1,2,3,4,5} on paper. For each value of i (0 to 4), mathematically prove which element from the original array gets picked.
Write a secondary method reverseString(String s) that reverses a String using the exact same logic structure, utilizing s.charAt() and a StringBuilder.

Expected Output[5, 4, 3, 2, 1] [30, 20, 10] [7]

💡 One Hint A `StringBuilder` is perfect for building a string incrementally inside a loop. Run a loop starting from the last index of the string down to 0, appending each character one by one.

Find Duplicates in an Array 🔍

ARRAYLISTLOOPEASY~20 min

📖 Scenario

Detecting duplicates is used in fraud detection, finding repeated orders, and cleaning up datasets. Java leverages the ArrayList structure and the .contains() method to track items securely as the algorithm scans the array.

Starter Code

import java.util.*;

public class FindDuplicates {
    static List<Integer> findDuplicates(int[] arr) {
        List<Integer> seen       = new ArrayList<>();
        List<Integer> duplicates = new ArrayList<>();
        for (int item : arr) {
            if (seen.contains(item) && !duplicates.contains(item)) {
                // TODO: add to duplicates
            }
            // TODO: add to seen
        }
        return duplicates;
    }

    public static void main(String[] args) {
        System.out.println(findDuplicates(new int[]{1,2,3,2,4,3,5}));
        System.out.println(findDuplicates(new int[]{1,2,3}));
        System.out.println(findDuplicates(new int[]{5,5,5,5}));
    }
}

Your Tasks

Fill in the tracking array methods. Run and confirm all 3 outputs match perfectly.
Java uses .contains() extensively here. What is the fundamental difference between an ArrayList and a primitive int[] array regarding built-in utility methods?
What does Java print automatically when you pass an ArrayList directly into a System.out.println() statement compared to a primitive array?

Expected Output[2, 3] [] [5]

💡 One Hint If you print a List directly in Java, it implicitly calls its own `toString()` method to format the output with square brackets, unlike raw arrays which just output a memory hash.

Simple Calculator 🧮

METHODSSWITCHEASY~15 min

📖 Scenario

A function that takes two numbers and an operator symbol and returns the result. This implementation utilizes Java's switch statement targeting a String to handle the logic cleanly, returning a double.

Starter Code

public class Calculator {
    static double calculate(double a, String op, double b) {
        switch (op) {
            case "+": // TODO: return addition
            case "-": // TODO: return subtraction
            case "*": // TODO: return multiplication
            case "/": return b == 0 ? Double.NaN : a / b;
            default:  return Double.NaN;
        }
    }

    public static void main(String[] args) {
        System.out.println(calculate(10, "+", 5));
        System.out.println(calculate(10, "-", 3));
        System.out.println(calculate( 6, "*", 7));
        System.out.println(calculate(15, "/", 4));
        System.out.println(calculate( 9, "/", 0));
    }
}

Your Tasks

Provide the mathematical operations for the open switch cases. Run and confirm the outputs — note that division by zero gracefully handles an error state.
What exactly is Double.NaN? Look at what Java prints for it and explain in one sentence why using it is safer than allowing an unhandled exception to crash the application.
Add a new case "%" to the switch block to handle modulus logic. Test with calculate(10, "%", 3) — expected output is 1.0.

Expected Output15.0 7.0 42.0 3.75 NaN

💡 One Hint The `switch` statement evaluates the operator string. You just need to add a new `case` for the modulo symbol before the default branch and return the calculation for the remainder. Don't forget that in Java, `switch` blocks generally require `return` or `break` to avoid cascading into the next line.

Temperature Converter 🌡️

METHODSMATHSEASY~15 min

📖 Scenario

Weather apps, medical equipment, and cooking timers all rely on background functions to convert between Celsius and Fahrenheit. Java uses String.format() heavily in these situations to ensure mathematical outputs map cleanly to a user interface.

Starter Code

public class TempConverter {
    static double celsiusToFahrenheit(double c) {
        // formula: (c * 9/5) + 32
        // TODO: return calculation
        return 0;
    }
    static double fahrenheitToCelsius(double f) {
        // formula: (f - 32) * 5/9
        // TODO: return calculation
        return 0;
    }

    public static void main(String[] args) {
        double[] cTemps = {0, 100, 37, -40};
        for (double c : cTemps)
            System.out.println(String.format("%.0f°C → %.2f°F", c, celsiusToFahrenheit(c)));
        System.out.println();
        double[] fTemps = {32, 212, 98.6, -40};
        for (double f : fTemps)
            System.out.println(String.format("%.1f°F → %.2f°C", f, fahrenheitToCelsius(f)));
    }
}

Your Tasks

Implement the mathematical logic inside the methods. Run and confirm all outputs match. Be very careful with how Java handles division.
If you mathematically implemented the formula exactly as written in the comment using integers like 9/5, Java will return bad data. Why must you type 9.0 / 5.0 to get the correct output?
Add a new celsiusToKelvin(double c) method. The formula is simply the Celsius value plus the constant 273.15. Print an output verifying 100°C maps to 373.15K.

Expected Output0°C → 32.00°F 100°C → 212.00°F 37°C → 98.60°F -40°C → -40.00°F 32.0°F → 0.00°C 212.0°F → 100.00°C 98.6°F → 37.00°C -40.0°F → -40.00°C

💡 One Hint Remember that `9/5` performs strict integer division in Java and drops the decimal entirely, resulting in `1`. Always append `.0` to force floating-point math across the operation. For Kelvin, just write a method that adds the constant offset to the passed Celsius value.

Count Vowels in a Sentence 🔤

STRINGLOOPEASY~15 min

📖 Scenario

A simple text analyser that counts how many vowels appear in any sentence. Modern JavaScript utilizes the includes() string method directly against iterable variables for fast validation checks.

Starter Code

function countVowels(sentence) {
    const vowels = "aeiouAEIOU";
    let count = 0;
    for (const char of sentence) {
        if (vowels.includes(char)) {
            // TODO: increment tracking variable
        }
    }
    // TODO: return data
}

const sentences = ["Hello World", "Python is fun", "aeiou", "rhythm"];
sentences.forEach(s => console.log(`"${s}" → ${countVowels(s)} vowels`));

Your Tasks

Fill out the operational logic inside the function. Run it and confirm all 4 outputs match.
Analyze the for...of loop. How does this iterate over the string compared to a traditional index-based for(let i=0) loop?
Rewrite the entire function as an advanced ES6 one-liner utilizing chained array methods: .split().filter().length.

Expected Output"Hello World" → 3 vowels "Python is fun" → 3 vowels "aeiou" → 5 vowels "rhythm" → 0 vowels

💡 One Hint To make it a one-liner, string methods are your friend. First, break the string into an array of characters. Then, filter that array based on your condition. Finally, isolate the size property of that filtered array.

Reverse an Array (The Safe Way) 🔄

ARRAYMETHODSEASY~15 min

📖 Scenario

Reversing arrays is a fundamental algorithmic skill. JavaScript has a built-in .reverse() method, but it possesses a dangerous quirk: it mutates (permanently alters) the original array. To safely use the built-in tool, you must spread the array into an independent copy first. Today, you will write a safe manual loop, and then write the safe modern ES6 one-liner.

Starter Code

// Manual loop version
function reverseManual(arr) {
    const result = [];
    // TODO: Write a for-loop that starts at the end of 'arr' and counts down to 0
    // TODO: Push each item into the 'result' bucket
    return result;
}

// Modern JS one-liner version
const reverseOneliner = arr => {
    // TODO: Use the spread operator [...] alongside the built-in method safely
};

const nums = [1, 2, 3, 4, 5];
console.log(reverseManual(nums));
console.log(reverseOneliner(nums));
console.log(nums);   // Safety Check: The original array MUST still be 1,2,3,4,5!

Your Tasks

Complete the `reverseManual` function utilizing a decrementing loop block. Run it and ensure the array flips.
Complete the `reverseOneliner` using the spread operator [...arr] attached to the built-in JS reverse function. Verify the safety check at the bottom still prints 1,2,3,4,5.
What would happen if you wrote the one-liner strictly as arr.reverse() without wrapping it in a spread block? Test it and observe what happens to your final safety check log.
Write a completely independent `reverseString(s)` function that reverses a String utilizing the .split().reverse().join() method chain.

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

💡 One Hint JavaScript arrays are passed by reference. If you mutate an array with `.reverse()`, the original is permanently changed across the entire program. Spreading `[...arr]` generates a temporary, safe replica before the flip occurs.

Find Duplicates in an Array 🔍

ARRAYFILTEREASY~20 min

📖 Scenario

Detecting duplicates is a core task in fraud detection, finding repeated orders, and cleaning up datasets. JavaScript allows you to achieve this through traditional array looping techniques or by leveraging index-searching filters.

Starter Code

// Traditional loop version
function findDuplicates(arr) {
    const seen = [], duplicates = [];
    for (const item of arr) {
        if (seen.includes(item) && !duplicates.includes(item)) {
            // TODO: push to duplicates bucket
        }
        // TODO: push to seen bucket
    }
    return duplicates;
}

// JS advanced one-liner version
const findDupsOneliner = arr =>
    arr.filter((item, index) => arr.indexOf(item) !== index && arr.lastIndexOf(item) === index);

console.log(findDuplicates([1,2,3,2,4,3,5]));
console.log(findDupsOneliner([1,2,3,2,4,3,5]));
console.log(findDuplicates([5,5,5,5]));

Your Tasks

Implement the array pushes inside the manual function block. Run and confirm both versions give the exact same mathematical result.
Analyze the advanced one-liner. Explain in plain English what the comparative logic arr.indexOf(item) !== index is fundamentally checking against. What exact value does an indexOf call return?
Test your manual loop against the string array ["cat","dog","cat","bird","dog"]. Ensure it correctly returns the duplicated strings.

Expected Output[2, 3] [2, 3] [5]

💡 One Hint `indexOf()` only ever returns the *first* time it sees an item. If your current loop index is 3, but `indexOf()` says the item first appeared at index 1, you immediately know you've found a duplicate item further down the line!

Simple Calculator 🧮

FUNCTIONSSWITCHEASY~15 min

📖 Scenario

A functional calculator program takes two numbers and an operator symbol, parses the intent, and returns the result. This block leverages a clean `switch` statement to replace bulky `if/else` logic trees.

Starter Code

function calculator(a, op, b) {
    switch (op) {
        case '+': // TODO: return addition block
        case '-': // TODO: return subtraction block
        case '*': // TODO: return multiplication block
        case '/': return b === 0 ? "Error: division by zero" : a / b;
        default:  return "Error: unknown operator";
    }
}

console.log(calculator(10, '+', 5));
console.log(calculator(10, '-', 3));
console.log(calculator( 6, '*', 7));
console.log(calculator(15, '/', 4));
console.log(calculator( 9, '/', 0));

Your Tasks

Supply the appropriate mathematical returns for the open switch branches. Run and confirm all 5 outputs generate properly.
Analyze the line b === 0 ? "Error" : a / b. If you translated this ternary operator back into standard code, what would the `if` and `else` branches look like?
Add an entirely new case '%' to evaluate a modulus request. Test it via calculator(10, '%', 3) — expect an exact return of 1.

Expected Output15 7 42 3.75 Error: division by zero

💡 One Hint The ternary operator `? :` is just a compressed `if/else`. Notice how JS uses `===` (strict equals) not `==`. The difference: `0 == "0"` is true in JS (loose), but `0 === "0"` is false (strict, checks type too). Always prefer `===` in your logic flows.

Temperature Converter 🌡️

FUNCTIONSMATHSEASY~15 min

📖 Scenario

JavaScript powers background temperature conversions for massive weather platforms and live tracking apps. These utility math functions are an ideal use case for the hyper-concise ES6 arrow function syntax.

Starter Code

// Formula for C -> F: (c * 9/5) + 32
const celsiusToFahrenheit = c => /* TODO: return operation */;

// Formula for F -> C: (f - 32) * 5/9
const fahrenheitToCelsius = f => /* TODO: return operation */;

// Execution tests
[0, 100, 37, -40].forEach(c =>
    console.log(`${c}°C → ${celsiusToFahrenheit(c).toFixed(2)}°F`));

[32, 212, 98.6, -40].forEach(f =>
    console.log(`${f}°F → ${fahrenheitToCelsius(f).toFixed(2)}°C`));

Your Tasks

Complete the mathematical translations utilizing the implicit returns inherent to inline arrow functions. Run and confirm all 8 outputs perfectly align.
Analyze the `.toFixed(2)` call dynamically attached to the function execution within the log. Execute `(3.14159).toFixed(2)` standalone in your console to verify exactly what data format it generates.
Write a completely independent `celsiusToKelvin(c)` arrow function. The mathematical constant for Kelvin conversion requires adding `273.15`.

Expected Output0°C → 32.00°F 100°C → 212.00°F 37°C → 98.60°F -40°C → -40.00°F 32°F → 0.00°C 212°F → 100.00°C 98.6°F → 37.00°C -40°F → -40.00°C

💡 One Hint `.toFixed(2)` is universally used to format float values cleanly before presenting them to users. Note that the output of this specific method isn't actually a number type anymore—it returns the rounded value directly as a string format!

Basic SELECT & WHERE 🔍

SELECTWHEREEASY~15 min

📖 Scenario

You're querying a product database for an e-commerce store. Same skills as before — SELECT and WHERE — just a fresh table to practice on.

TABLE: products

id	name	price	category	stock
1	'Laptop'	45000	'Electronics'	10
2	'T-Shirt'	799	'Clothing'	50
3	'Headphones'	2500	'Electronics'	30
4	'Jeans'	1499	'Clothing'	20
5	'Keyboard'	1800	'Electronics'	15
6	'Jacket'	3200	'Clothing'	8

Your Tasks

Select the name and price of all Electronics products
Select all columns for products with price less than 2000
Select name, price, and stock for all products, sorted by price lowest first

① Electronics — name and price

name	price
Laptop	45000
Headphones	2500
Keyboard	1800

② Price < 2000

id	name	price	category	stock
2	T-Shirt	799	Clothing	50
4	Jeans	1499	Clothing	20
5	Keyboard	1800	Electronics	15

③ All products sorted by price ASC

name	price	stock
T-Shirt	799	50
Jeans	1499	20
Keyboard	1800	15
Headphones	2500	30
Jacket	3200	8
Laptop	45000	10

💡 One Hint Strings in SQL need to be wrapped in single quotes. To sort your final results from lowest to highest, look into the `ORDER BY` clause and the keyword that represents ascending order.

ORDER BY & LIMIT 📊

ORDER BYLIMITEASY~15 min

📖 Scenario

The store manager wants quick answers — "what are the top 3 most expensive items?", "what are the 2 cheapest?" Sort and slice with ORDER BY and LIMIT.

TABLE: products (same table)

id	name	price	category	stock
1	'Laptop'	45000	'Electronics'	10
2	'T-Shirt'	799	'Clothing'	50
3	'Headphones'	2500	'Electronics'	30
4	'Jeans'	1499	'Clothing'	20
5	'Keyboard'	1800	'Electronics'	15
6	'Jacket'	3200	'Clothing'	8

Your Tasks

Get the top 3 most expensive products — name and price only
Get the 2 cheapest products — name and price only
Get all Electronics products sorted by price highest first

① Top 3 most expensive

name	price
Laptop	45000
Jacket	3200
Headphones	2500

② 2 cheapest

name	price
T-Shirt	799
Jeans	1499

③ Electronics by price DESC

name	price
Laptop	45000
Headphones	2500
Keyboard	1800

💡 One Hint The order of operations in a SQL query is strict: SELECT -> FROM -> WHERE -> ORDER BY -> LIMIT. Build your query sequentially following that exact structure.

Aggregate Functions 🔢

COUNTSUMAVGMAX MINEASY~15 min

📖 Scenario

The store owner wants quick stats — how many products do we have? What's the total value of our inventory? What's the average price? SQL calculates all of this in a single query.

TABLE: products (same table)

id	name	price	category	stock
1	'Laptop'	45000	'Electronics'	10
2	'T-Shirt'	799	'Clothing'	50
3	'Headphones'	2500	'Electronics'	30
4	'Jeans'	1499	'Clothing'	20
5	'Keyboard'	1800	'Electronics'	15
6	'Jacket'	3200	'Clothing'	8

Your Tasks

In one query: find the total number of products, the sum of all prices, the average price, the highest price, and the lowest price
Find the count and average price of Clothing products only
Calculate the average by hand — add up all 6 prices and divide by 6. Does your SQL answer match?

① Overall stats

COUNT(*)	SUM(price)	AVG(price)	MAX(price)	MIN(price)
6	54798	9133.00	45000	799

② Clothing stats

COUNT(*)	AVG(price)
3	1832.67

💡 One Hint You don't need to write separate queries for multiple stats. You can place COUNT(), SUM(), and AVG() right next to each other in the exact same SELECT statement, separated by commas.

GROUP BY — Stats Per Category 🏷️

GROUP BYCOUNTAVGEASY~20 min

📖 Scenario

The store wants a breakdown per category — how many products in each, what's the average price in each. GROUP BY splits the table into groups and then you can aggregate each group separately.

🗄️ Reminder — GROUP BY

GROUP BY splits rows into buckets based on a column. Then COUNT, AVG, SUM etc. work on each bucket separately — giving one result row per group instead of one result for the whole table.

TABLE: products (same table)

id	name	price	category	stock
1	'Laptop'	45000	'Electronics'	10
2	'T-Shirt'	799	'Clothing'	50
3	'Headphones'	2500	'Electronics'	30
4	'Jeans'	1499	'Clothing'	20
5	'Keyboard'	1800	'Electronics'	15
6	'Jacket'	3200	'Clothing'	8

Your Tasks

For each category, show the number of products and the average price
For each category, show the highest price in that category
Verify your ① results by hand — count and average Clothing (799, 1499, 3200) and Electronics (45000, 2500, 1800) manually

① Count and avg per category

category	COUNT(*)	AVG(price)
Clothing	3	1832.67
Electronics	3	16433.33

② Max price per category

category	MAX(price)
Clothing	3200
Electronics	45000

💡 One Hint The golden rule of GROUP BY: If a column is in your SELECT statement (like 'category'), and it's not wrapped in an aggregate function, it must be included in your GROUP BY clause.

WHERE with Multiple Conditions 🎯

WHEREANDOREASY~15 min

📖 Scenario

Real queries almost always have more than one condition. Combine conditions with AND (both must be true) and OR (either can be true) — just like if statements in Python, Java, and JavaScript.

TABLE: products (same table)

id	name	price	category	stock
1	'Laptop'	45000	'Electronics'	10
2	'T-Shirt'	799	'Clothing'	50
3	'Headphones'	2500	'Electronics'	30
4	'Jeans'	1499	'Clothing'	20
5	'Keyboard'	1800	'Electronics'	15
6	'Jacket'	3200	'Clothing'	8

Your Tasks

Get Electronics products with stock greater than 10 — name and stock only
Get all Clothing products — sorted by price lowest first
Get products that are either Electronics OR have price below 1000 — think carefully about which rows qualify

① Electronics with stock > 10

name	stock
Headphones	30
Keyboard	15

② Clothing sorted by price ASC

name	price
T-Shirt	799
Jeans	1499
Jacket	3200

③ Electronics OR price < 1000

name	price	category
Laptop	45000	Electronics
T-Shirt	799	Clothing
Headphones	2500	Electronics
Keyboard	1800	Electronics

💡 One Hint When combining multiple requirements, AND requires both sides to be true, while OR requires only one side to be true to pass the filter. Use parentheses if you ever need to group logical checks.

Python · Java · JavaScript · SQL