SQL & Database Tutorials
SQL queries, self joins, aggregations, H2 troubleshooting, and JPA/Hibernate with Spring Boot — practical guides for Java devs.
Advertisement
SQL Tutorials 4 posts
Joins, aggregations, subqueries, and advanced SQL patterns
Concatenating multiple rows into a single column dynamically
How to concatenate multiple rows of a column in a table into a single column? I have to concatenate multiple rows to a single column. For ex…
SQLDifferent Ways (How) to Delete Duplicate Rows in Table
In this article, I am going to show different ways of deleting duplicating records from the table. It is a common question in interviews whi…
SQLSQL Query to Group / Aggregate N Consecutive Rows
Interview Question: In one of my project, I got a requirement to group N consecutive rows and find the min, max and sum of values in each gr…
SQLSelf Join – How to Write Self Join Queries Easily
Self Join: By definition, a self join query is a query in which the table is joined to itself. Self joins are written in cases where there i…
H2 & JPA Exceptions 4 posts
Fix common H2 setup errors and Spring Boot JPA issues
Java Exception Handling
Understanding Exception Handling in Java: Types of Exceptions and Creating Custom Exceptions Exception handling is a critical aspect of Java…
FixH2 Schema Initialisation Syntax Error in Sql Statement
Application Startup error: But I got ‘Syntax error in SQL statement’ on application startup: Error: 19:08:45.642 6474 [main] INF…
FixHow to fix org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement expected “identifier” (2023)
GenerationTarget encountered exception accepting command : Error executing DDL Issue I am using H₂ in-memory database for my Spring Boot app…
FixGenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement (2023)
GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement You should try different dialects likeorg.…
Quick SQL Reference
| Query Type | Use Case | Example |
|---|---|---|
| SELF JOIN | Hierarchical data (manager-employee) | FROM emp e1 JOIN emp e2 ON e1.mgr=e2.id |
| GROUP BY + HAVING | Filter aggregated results | GROUP BY dept HAVING COUNT(*)>5 |
| Consecutive Rows | N-row aggregation | ROW_NUMBER() OVER (ORDER BY id) |
| Delete Duplicates | Keep unique rows | DELETE WHERE id NOT IN (SELECT MIN(id)...) |
| Dynamic Pivot | Rows to columns | GROUP_CONCAT / STRING_AGG |