How do I install and set up a database for learning SQL?

Getting a local database up and running is the first step toward learning SQL hands-on. Here’s a practical, beginner-friendly guide.

Step 1: Choose a Database System

For learning SQL, I recommend one of these free options:

Database Best For
SQLite Absolute beginners, no setup
MySQL Web development, widely used
PostgreSQL Modern SQL features, professional use
H2 Java developers, embedded testing

Step 2: Install the Database

Option A: MySQL (Recommended for Beginners)

Windows / macOS:
1. Download the MySQL Community Server from dev.mysql.com/downloads
2. Run the installer and choose “Developer Default”
3. During setup, set a root password (remember it!)
4. Accept default port 3306

macOS (via Homebrew):

brew install mysql
brew services start mysql
mysql_secure_installation

Linux (Ubuntu/Debian):

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql_secure_installation

Option B: PostgreSQL

Windows / macOS: Download from postgresql.org/download

macOS:

brew install postgresql@16
brew services start postgresql@16

Linux:

sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Option C: Docker (Cleanest Setup)

If you have Docker, this is the fastest way:

# MySQL
docker run --name mysql-learn -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 -d mysql:8

# PostgreSQL
docker run --name pg-learn -e POSTGRES_PASSWORD=root -p 5432:5432 -d postgres:16

Step 3: Install a Database Client

You’ll want a GUI to browse tables and run queries visually:

  • DataGrip (JetBrains, paid but excellent) — or use the Database tool window built into IntelliJ IDEA Ultimate
  • DBeaver (free, universal) — dbeaver.io
  • MySQL Workbench (free, MySQL-specific)
  • pgAdmin (free, PostgreSQL-specific)

Step 4: Create Your First Database

Connect to your server via CLI or GUI and run:

CREATE DATABASE learning_sql;
USE learning_sql;  -- MySQL syntax
-- \c learning_sql  -- PostgreSQL syntax

Step 5: Load Sample Data

Learning SQL is much easier with realistic data. Try one of these well-known sample databases:

Import via CLI, for example:

mysql -u root -p learning_sql < sakila-schema.sql
mysql -u root -p learning_sql < sakila-data.sql

Step 6: Practice SQL Basics

Start with these fundamentals in order:

  1. SELECT, FROM, WHERE — retrieving data
  2. ORDER BY, LIMIT — sorting and paging
  3. JOIN — combining tables (INNER, LEFT, RIGHT)
  4. GROUP BY, aggregate functions (COUNT, SUM, AVG)
  5. Subqueries and CTEs (WITH clauses)
  6. INSERT, UPDATE, DELETE — modifying data
  7. CREATE TABLE, constraints, indexes — schema design

Great Practice Resources

Step 7: Verify Your Setup

Run a quick sanity check in your SQL client:

SELECT VERSION();
SHOW DATABASES;   -- MySQL
-- \l              -- PostgreSQL

If you see version info and your learning_sql database listed, you’re all set!

Quick Recommendation

If you want the fastest path with minimal friction:

  1. Install MySQL (or run it via Docker)
  2. DBeaver as your client
  3. Import the Sakila sample database
  4. Work through SQLZoo tutorials alongside it

How do I understand what SQL is and why it is used?

What is SQL?

SQL (Structured Query Language, pronounced “sequel” or “S-Q-L”) is a domain-specific programming language designed for managing and manipulating data stored in relational databases.

Think of it as the standard “language” you use to talk to a database — to ask it questions, store information, update records, or delete data.

The Core Idea

Imagine a giant, organized filing cabinet (the database) with many labeled drawers (tables). Each drawer contains index cards (rows) with specific fields (columns) like name, age, email, etc.

SQL is the set of commands you use to:

  • Put cards in (INSERT)
  • Find specific cards (SELECT)
  • Change information on cards (UPDATE)
  • Throw cards away (DELETE)

Basic SQL Examples

1. Querying Data (SELECT)

SELECT name, email
FROM users
WHERE age > 18;

“Give me the name and email of all users older than 18.”

2. Inserting Data (INSERT)

INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 25);

3. Updating Data (UPDATE)

UPDATE users
SET email = '[email protected]'
WHERE name = 'Alice';

4. Deleting Data (DELETE)

DELETE FROM users
WHERE age < 18;

Why is SQL Used?

1. Universal Standard

SQL works across most relational databases: MySQL, PostgreSQL, Oracle, SQL Server, SQLite, etc. Learn it once, use it almost anywhere.

2. Declarative, Not Procedural

You describe WHAT you want, not HOW to get it. The database engine figures out the most efficient way to fetch the data.

-- You just say what you want:
SELECT * FROM orders WHERE total > 1000;
-- You don't write loops or index lookups yourself

3. Handles Massive Data Efficiently

SQL databases are optimized to handle millions or billions of records with speed, using indexes, query optimizers, and caching.

4. Data Integrity & Relationships

SQL enforces rules (constraints, foreign keys) that keep data consistent and reliable. For example, you can’t have an order that references a non-existent customer.

5. Powerful for Analysis

SQL can aggregate, group, and analyze data:

SELECT country, COUNT(*) AS user_count, AVG(age) AS avg_age
FROM users
GROUP BY country
ORDER BY user_count DESC;

6. Transactions & Safety

SQL supports ACID transactions (Atomicity, Consistency, Isolation, Durability) — critical for banks, e-commerce, and any system where correctness matters.

Where Is SQL Used?

  • Web Applications — user accounts, posts, comments (e.g., stored via Spring Data JPA in Java apps)
  • Mobile Apps — local storage (SQLite)
  • Business Systems — CRM, ERP, HR platforms
  • Analytics & Data Science — reporting, dashboards, BI tools
  • Banking & Finance — transactions, ledgers
  • E-commerce — products, orders, inventory

How to Start Learning SQL

  1. Install a database — SQLite (easiest) or PostgreSQL
  2. Try interactive tutorials — SQLBolt, Mode Analytics SQL Tutorial, LeetCode SQL problems
  3. Practice on real data — download sample databases like Chinook or Sakila
  4. Master the “Big 6”: SELECT, FROM, WHERE, GROUP BY, ORDER BY, JOIN

Summary

Aspect Description
What A language for talking to relational databases
Why Efficient, standardized, safe, and powerful data management
Where Nearly every application that stores structured data
How Declarative statements like SELECT, INSERT, UPDATE, DELETE

SQL is one of the most valuable and enduring skills in software development — it has been around since the 1970s and remains the backbone of data-driven applications today.

How do I use test templates in JUnit?

Test templates in JUnit 5 provide a powerful way to run the same test multiple times with different contexts or invocation strategies. Unlike @ParameterizedTest (which is a specialized form of test template), @TestTemplate gives you full control over how tests are invoked by requiring you to register a custom TestTemplateInvocationContextProvider.

When to Use @TestTemplate

Use test templates when you need to:

  • Run a test with different environments (e.g., different databases, browsers, or configurations).
  • Provide custom test invocation logic beyond what @ParameterizedTest or @RepeatedTest offers.
  • Inject different parameter sets and extensions per invocation.

Basic Structure

A @TestTemplate method requires at least one TestTemplateInvocationContextProvider registered via @ExtendWith.

Step 1: Define the Test Template Method

import org.junit.jupiter.api.TestTemplate;
import org.junit.jupiter.api.extension.ExtendWith;

@ExtendWith(MyTestTemplateProvider.class)
class UserServiceTest {

    @TestTemplate
    void testUserCreation(String environment) {
        System.out.println("Running test in environment: " + environment);
        // Your test logic here
    }
}

Step 2: Create the Invocation Context Provider

import org.junit.jupiter.api.extension.*;
import java.util.stream.Stream;
import java.util.List;

public class MyTestTemplateProvider implements TestTemplateInvocationContextProvider {

    @Override
    public boolean supportsTestTemplate(ExtensionContext context) {
        return true;
    }

    @Override
    public Stream<TestTemplateInvocationContext> provideTestTemplateInvocationContexts(
            ExtensionContext context) {
        return Stream.of(
                invocationContext("DEV"),
                invocationContext("STAGING"),
                invocationContext("PRODUCTION")
        );
    }

    private TestTemplateInvocationContext invocationContext(String environment) {
        return new TestTemplateInvocationContext() {
            @Override
            public String getDisplayName(int invocationIndex) {
                return "Environment: " + environment;
            }

            @Override
            public List<Extension> getAdditionalExtensions() {
                return List.of(new EnvironmentParameterResolver(environment));
            }
        };
    }
}

Step 3: Provide a ParameterResolver

import org.junit.jupiter.api.extension.*;

public class EnvironmentParameterResolver implements ParameterResolver {

    private final String environment;

    public EnvironmentParameterResolver(String environment) {
        this.environment = environment;
    }

    @Override
    public boolean supportsParameter(ParameterContext parameterContext,
                                     ExtensionContext extensionContext) {
        return parameterContext.getParameter().getType() == String.class;
    }

    @Override
    public Object resolveParameter(ParameterContext parameterContext,
                                   ExtensionContext extensionContext) {
        return environment;
    }
}

Real-World Example: Testing Against Multiple Configurations

Imagine testing a service against different database configurations:

@ExtendWith(DatabaseTestTemplateProvider.class)
class RepositoryTest {

    @TestTemplate
    void shouldSaveEntity(DatabaseConfig config) {
        // Test runs once for each configuration provided
        Repository repo = new Repository(config);
        assertTrue(repo.save(new Entity("test")));
    }
}

The provider can supply DatabaseConfig objects for H2, PostgreSQL, MySQL, etc.

Key Points to Remember

Feature Description
Annotation @TestTemplate
Required Provider TestTemplateInvocationContextProvider
Registration Via @ExtendWith or ServiceLoader
Invocation Count Determined by the number of contexts returned
Parameter Injection Through ParameterResolver in each context

@TestTemplate vs Other Test Annotations

  • @Test → Runs once.
  • @RepeatedTest → Runs a fixed number of times with the same context.
  • @ParameterizedTest → Runs with different arguments (built-in template).
  • @TestTemplate → Full custom control over invocation contexts and extensions.

Best Practices

  1. Use @TestTemplate only when built-in options are insufficient@ParameterizedTest covers most cases.
  2. Give meaningful display names via getDisplayName(int invocationIndex) for clear test reports.
  3. Keep providers reusable — a good provider can be shared across many test classes.
  4. Combine with other extensions to inject mocks, configurations, or lifecycle hooks per invocation.

Test templates unlock advanced testing scenarios where you need dynamic, context-aware test invocation — perfect for integration testing across multiple environments or configurations. 🚀

How do I write dynamic tests with @TestFactory?

@TestFactory is a JUnit Jupiter feature that lets you generate tests at runtime rather than declaring them statically with @Test. This is useful when the number or nature of tests depends on data that’s only known at execution time.

Key Rules

  • A @TestFactory method must return one of:
    • DynamicNode (or a subtype like DynamicTest / DynamicContainer)
    • Stream, Collection, Iterable, Iterator, or an array of DynamicNode
  • It must not be private or static.
  • Each generated DynamicTest consists of a display name and an Executable (lambda with the assertion logic).

1. Basic Example — Collection of Tests

import org.junit.jupiter.api.DynamicTest;
import org.junit.jupiter.api.TestFactory;

import java.util.List;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.DynamicTest.dynamicTest;

class CalculatorDynamicTests {

    @TestFactory
    List<DynamicTest> additionTests() {
        return List.of(
            dynamicTest("1 + 1 = 2", () -> assertEquals(2, 1 + 1)),
            dynamicTest("2 + 3 = 5", () -> assertEquals(5, 2 + 3)),
            dynamicTest("10 + 5 = 15", () -> assertEquals(15, 10 + 5))
        );
    }
}

2. Generating Tests from a Stream

Great for data-driven scenarios:

import java.util.stream.Stream;

@TestFactory
Stream<DynamicTest> squareTests() {
    return Stream.of(1, 2, 3, 4, 5)
        .map(n -> dynamicTest(
            "square of " + n + " is " + (n * n),
            () -> assertEquals(n * n, n * n)
        ));
}

3. Using an Input Generator, Display-Name Generator, and Test Executor

The DynamicTest.stream(...) helper simplifies iterator-based generation:

import org.junit.jupiter.api.DynamicTest;
import org.junit.jupiter.api.TestFactory;

import java.util.Iterator;
import java.util.List;
import java.util.stream.Stream;

import static org.junit.jupiter.api.Assertions.assertTrue;

class PalindromeDynamicTests {

    @TestFactory
    Stream<DynamicTest> palindromeTests() {
        Iterator<String> inputs = List.of("racecar", "level", "madam").iterator();

        return DynamicTest.stream(
            inputs,
            input -> "isPalindrome('" + input + "')",
            input -> assertTrue(isPalindrome(input))
        );
    }

    private boolean isPalindrome(String s) {
        return new StringBuilder(s).reverse().toString().equals(s);
    }
}

4. Grouping Tests with DynamicContainer

You can nest dynamic tests into containers to build a hierarchy:

import org.junit.jupiter.api.DynamicContainer;
import org.junit.jupiter.api.DynamicNode;
import org.junit.jupiter.api.DynamicTest;
import org.junit.jupiter.api.TestFactory;

import java.util.List;
import java.util.stream.Stream;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.DynamicContainer.dynamicContainer;
import static org.junit.jupiter.api.DynamicTest.dynamicTest;

class MathDynamicTests {

    @TestFactory
    Stream<DynamicNode> mathOperations() {
        return Stream.of(
            dynamicContainer("Addition", List.of(
                dynamicTest("1+1", () -> assertEquals(2, 1 + 1)),
                dynamicTest("2+2", () -> assertEquals(4, 2 + 2))
            )),
            dynamicContainer("Multiplication", List.of(
                dynamicTest("2*3", () -> assertEquals(6, 2 * 3)),
                dynamicTest("4*5", () -> assertEquals(20, 4 * 5))
            ))
        );
    }
}

5. Reading Test Data from a File

Perfect for parameterized-style tests where inputs live outside code:

import java.nio.file.Files;
import java.nio.file.Path;
import java.util.stream.Stream;

@TestFactory
Stream<DynamicTest> testsFromFile() throws Exception {
    return Files.lines(Path.of("src/test/resources/cases.csv"))
        .map(line -> line.split(","))
        .map(parts -> dynamicTest(
            "Case: " + parts[0],
            () -> assertEquals(Integer.parseInt(parts[2]),
                               Integer.parseInt(parts[0]) + Integer.parseInt(parts[1]))
        ));
}

When to Use @TestFactory vs @ParameterizedTest

Use @ParameterizedTest Use @TestFactory
Fixed set of arguments, single test body Fully dynamic generation (count, names, logic can all vary)
Simple data variations Hierarchical / conditional / streamed test generation
Compile-time known inputs Runtime-computed inputs

Important Lifecycle Note

⚠️ Standard lifecycle callbacks like @BeforeEach and @AfterEach do not run around each generated dynamic test — only around the factory method itself. If you need per-test setup, do it inside each Executable.

How do I use conditional test execution in JUnit?

JUnit 5 provides several ways to conditionally execute tests based on various runtime conditions. This is useful when tests should only run under specific circumstances — like a particular operating system, JRE version, environment variable, or system property.

1. Operating System Conditions

Use @EnabledOnOs and @DisabledOnOs to run tests only on specific operating systems.

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.EnabledOnOs;
import org.junit.jupiter.api.condition.DisabledOnOs;
import org.junit.jupiter.api.condition.OS;

class OsConditionalTest {

    @Test
    @EnabledOnOs(OS.WINDOWS)
    void onlyOnWindows() {
        // Runs only on Windows
    }

    @Test
    @EnabledOnOs({OS.LINUX, OS.MAC})
    void onlyOnLinuxOrMac() {
        // Runs only on Linux or macOS
    }

    @Test
    @DisabledOnOs(OS.WINDOWS)
    void notOnWindows() {
        // Skipped on Windows
    }
}

2. JRE Version Conditions

Use @EnabledOnJre, @DisabledOnJre, or @EnabledForJreRange to control tests based on the Java version.

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.EnabledOnJre;
import org.junit.jupiter.api.condition.EnabledForJreRange;
import org.junit.jupiter.api.condition.JRE;

class JreConditionalTest {

    @Test
    @EnabledOnJre(JRE.JAVA_21)
    void onlyOnJava21() {
        // Runs only on Java 21
    }

    @Test
    @EnabledForJreRange(min = JRE.JAVA_17, max = JRE.JAVA_25)
    void betweenJava17AndJava25() {
        // Runs on Java 17 through 25
    }
}

3. System Property Conditions

Enable or disable tests based on JVM system properties.

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.EnabledIfSystemProperty;
import org.junit.jupiter.api.condition.DisabledIfSystemProperty;

class SystemPropertyTest {

    @Test
    @EnabledIfSystemProperty(named = "env", matches = "ci")
    void onlyInCiEnvironment() {
        // Runs only when -Denv=ci
    }

    @Test
    @DisabledIfSystemProperty(named = "os.arch", matches = ".*32.*")
    void notOn32BitArch() {
        // Skipped on 32-bit architectures
    }
}

4. Environment Variable Conditions

Similar to system properties, but for OS-level environment variables.

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.EnabledIfEnvironmentVariable;
import org.junit.jupiter.api.condition.DisabledIfEnvironmentVariable;

class EnvVarTest {

    @Test
    @EnabledIfEnvironmentVariable(named = "CI", matches = "true")
    void onlyInCi() {
        // Runs only when CI=true in environment
    }

    @Test
    @DisabledIfEnvironmentVariable(named = "ENV", matches = "prod")
    void skipInProduction() {
        // Skipped when ENV=prod
    }
}

5. Custom Conditions with @EnabledIf and @DisabledIf

For more complex logic, delegate to a static method that returns a boolean.

import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.condition.EnabledIf;

class CustomConditionTest {

    @Test
    @EnabledIf("isDatabaseAvailable")
    void runOnlyIfDatabaseIsUp() {
        // Runs only if the referenced method returns true
    }

    static boolean isDatabaseAvailable() {
        // Perform a real check (ping DB, check port, etc.)
        return "true".equalsIgnoreCase(System.getenv("DB_UP"));
    }
}

The method must:

  • Be static (unless the test class is @TestInstance(PER_CLASS))
  • Return boolean
  • Take no arguments (or accept ExtensionContext)

6. Programmatic Conditions with Assumptions

Sometimes you don’t want a test skipped by annotation but rather aborted mid-execution based on runtime state. Use Assumptions:

import org.junit.jupiter.api.Test;
import static org.junit.jupiter.api.Assumptions.assumeTrue;
import static org.junit.jupiter.api.Assumptions.assumingThat;

class AssumptionTest {

    @Test
    void runOnlyIfOnDeveloperMachine() {
        assumeTrue("DEV".equals(System.getenv("PROFILE")),
                   "Skipping: not on developer profile");
        // rest of test logic
    }

    @Test
    void partiallyConditional() {
        assumingThat("CI".equals(System.getenv("PROFILE")), () -> {
            // Only executed in CI, but the outer test still runs
        });
        // Always executed
    }
}

Difference:

  • @Disabled* / @Enabled* → skipped at discovery time (test shows as skipped).
  • Assumptions → aborted at execution time (test shows as aborted).

Which One Should You Use?

Scenario Recommended Approach
Skip based on OS / JRE @EnabledOnOs, @EnabledOnJre
Skip based on env variable or system property @EnabledIfEnvironmentVariable, @EnabledIfSystemProperty
Complex, dynamic condition @EnabledIf("methodName")
Runtime state check inside the test Assumptions.assumeTrue(...)

Key Takeaways

  • Use annotation-based conditions for static, predictable rules (OS, JRE, env).
  • Use @EnabledIf / @DisabledIf for custom logic that can’t be expressed with the built-in annotations.
  • Use Assumptions when you need to abort a test at runtime based on data available only during execution.
  • Always provide a reason/message (e.g., disabledReason = "...") — future you (and your teammates) will thank you when reading test reports.