How do I create my first database and table?

By Wayan in SQL 0 Comment

Now that you have a database server installed, let’s walk through creating your first database and table. I’ll show you how to do this both directly with SQL (recommended for learning) and briefly mention the programmatic approach.

Part 1: Create Your First Database

Step 1: Connect to Your Database Server

Open a terminal and connect using the CLI:

MySQL:

mysql -u root -p

PostgreSQL:

psql -U postgres

Enter the password you set during installation.

Step 2: Create the Database

Once connected, run:

CREATE DATABASE bookstore;

You should see a confirmation like Query OK, 1 row affected.

Step 3: Verify It Was Created

SHOW DATABASES;   -- MySQL
-- \l            -- PostgreSQL

You should see bookstore in the list.

Step 4: Switch to Your New Database

Before creating tables, tell the server which database to work with:

USE bookstore;   -- MySQL
-- \c bookstore  -- PostgreSQL

Part 2: Create Your First Table

A table is where actual data lives. It has columns (fields) and rows (records).

Step 1: Design Your Table

Let’s create a simple book table. Before writing SQL, think about:

  • What data do you want to store? (title, author, price, etc.)
  • What type is each field? (text, number, date)
  • Which field uniquely identifies a row? (the primary key)

Step 2: Write the CREATE TABLE Statement

CREATE TABLE book (
    id             BIGINT       NOT NULL AUTO_INCREMENT,
    isbn           VARCHAR(50)  NOT NULL,
    title          VARCHAR(100) NOT NULL,
    author         VARCHAR(100) NOT NULL,
    published_year INT,
    price          DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id)
);

Understanding Each Part

Element Meaning
id Column name
BIGINT Data type — a large integer
AUTO_INCREMENT Database auto-generates the next number (MySQL syntax)
NOT NULL This field is required
VARCHAR(100) Variable-length text, up to 100 characters
DECIMAL(10, 2) Number with 10 digits total, 2 after the decimal point
DEFAULT 0.00 Default value if none is provided
PRIMARY KEY (id) Marks id as the unique identifier for each row

PostgreSQL note: Replace BIGINT ... AUTO_INCREMENT with BIGSERIAL or BIGINT GENERATED ALWAYS AS IDENTITY.

Step 3: Verify the Table Was Created

SHOW TABLES;              -- MySQL
DESCRIBE book;            -- MySQL — shows column details

-- \dt                    -- PostgreSQL — list tables
-- \d book                -- PostgreSQL — describe a table

Part 3: Add Some Data

Insert Rows

INSERT INTO book (isbn, title, author, published_year, price)
VALUES
    ('978-0134685991', 'Effective Java',        'Joshua Bloch',    2018, 45.00),
    ('978-0132350884', 'Clean Code',            'Robert C. Martin', 2008, 39.99),
    ('978-0596009205', 'Head First Design Patterns', 'Eric Freeman', 2004, 49.95);

Query Your Data

SELECT * FROM book;

You should see all three rows with their auto-generated id values.

Part 4: Common Beginner Operations

Filter with WHERE

SELECT title, price FROM book WHERE price < 45.00;

Sort with ORDER BY

SELECT title, published_year FROM book ORDER BY published_year DESC;

Update a Row

UPDATE book SET price = 42.00 WHERE isbn = '978-0134685991';

Delete a Row

DELETE FROM book WHERE id = 3;

Part 5: Doing This from Java (Optional)

Since your project uses Java, you can also create databases and tables programmatically through JDBC. The general pattern is:

  1. Get a Connection via DriverManager.getConnection(url, user, password)
  2. Create a Statement or PreparedStatement
  3. Execute your CREATE DATABASE / CREATE TABLE SQL
  4. Use try-with-resources so connections close automatically

However, in a modern Spring Data JPA project, you usually don’t create tables manually with JDBC. Instead:

  • Hibernate/JPA can auto-generate tables from your @Entity classes (via spring.jpa.hibernate.ddl-auto)
  • Flyway or Liquibase manage schema migrations with versioned SQL scripts

But learning the raw SQL first (as shown above) gives you the foundation to understand what these tools do under the hood.

Recommended Next Steps

  1. Create the bookstore database and book table using the SQL above
  2. Insert 5–10 sample rows
  3. Practice SELECT with different WHERE conditions
  4. Learn about relationships — create a second table (e.g., author) and link it with a foreign key
  5. Explore JOINs to combine data from multiple tables

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

By Wayan in SQL 0 Comment

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?

By Wayan in SQL 0 Comment

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?

By Wayan in JUnit 0 Comment

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?

By Wayan in JUnit 0 Comment

@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.