A Primer on SQL

SQL (Structured Query Language) is the language used to define, manipulate, and query data stored in a relational database. Statements that create or change the structure of a database (tables, columns, constraints…) belong to the Data Definition Language (DDL), while statements that insert, update, delete, or read rows belong to the Data Manipulation Language (DML).

Every relational database implements SQL with its own set of extensions and quirks, usually called a dialect. Since we will be running our Spring Boot application against an in-memory H2 database, the statements in this section use H2's dialect. At the end of this section, we will see what needs to change to run the same statements against MySQL or MariaDB.

Trying it out

To follow along, enable the H2 web console in your Spring Boot application and give the in-memory database a stable name, so that it can be reopened between two requests to the console.

1spring.h2.console.enabled=true
2spring.datasource.url=jdbc:h2:mem:testdb

With the application running, open http://localhost:8080/h2-console in your browser, and connect using the JDBC URL jdbc:h2:mem:testdb, the user name sa, and an empty password. You can then type any of the statements below in the console and execute them.

Note	Being in-memory, this database is created empty every time the application restarts. Everything we are about to create and insert will be lost on the next restart, which is perfectly fine for this exercise.

Modeling a small bookstore

Let’s imagine a tiny bookstore that tracks its authors and the books it sells. An author can have written several books, and a book is written by a single author: a classic one-to-many relationship.

To uniquely identify a row in a table, we need a primary key. It can be a natural key, an existing attribute with a business meaning, such as a book’s ISBN, or a surrogate key, an artificial identifier with no business meaning, generated solely to uniquely identify rows. Natural keys can turn out to be not as unique or as stable as they first appeared, so we will use surrogate keys here: author_id and book_id, both auto-generated by H2.

The database starts empty: no table exists yet.

Creating tables

Let’s create the authors table first, since books will depend on it.

1CREATE TABLE authors (
2    author_id  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3    first_name VARCHAR(100) NOT NULL,
4    last_name  VARCHAR(100) NOT NULL,
5    birth_date DATE
6);

author_id is our surrogate key: GENERATED BY DEFAULT AS IDENTITY tells H2 to generate a new, ever-increasing value whenever a row is inserted without specifying one.
first_name and last_name are character strings (VARCHAR), with a maximum length and a NOT NULL constraint, since every author must have a name.
birth_date is a DATE, and may be left unknown, hence no NOT NULL constraint.

Now let’s create the books table.

1CREATE TABLE books (
2    book_id      BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3    title        VARCHAR(200) NOT NULL,
4    price        NUMERIC(6, 2) NOT NULL,
5    published_at DATE,
6    author_id    BIGINT NOT NULL REFERENCES authors (author_id)
7);

price is a numeric type with a precision of 6 digits in total (the first argument), 2 of which after the decimal point (the second argument), well suited to currency amounts.
author_id is a foreign key: it references the author_id surrogate key of authors, materializing the relation between the two tables.

Because of this foreign key, authors had to exist before books could be created, and, as we are about to see, authors will also have to be inserted before the books that reference them.

Inserting data

Let’s insert a few authors first.

1INSERT INTO authors (first_name, last_name, birth_date) VALUES ('George', 'Orwell', DATE '1903-06-25');
2INSERT INTO authors (first_name, last_name, birth_date) VALUES ('Jane', 'Austen', DATE '1775-12-16');
3INSERT INTO authors (first_name, last_name, birth_date) VALUES ('Agatha', 'Christie', DATE '1890-09-15');
4INSERT INTO authors (first_name, last_name, birth_date) VALUES ('Nicolas', 'Bourbaki', NULL);

We never provide a value for author_id: H2 generates 1, 2, 3, and 4 for us, in that order. Nicolas Bourbaki is, in fact, a pseudonym collectively used by a group of mathematicians, so it genuinely has no single birth date, hence the NULL.

Now let’s insert books, each referencing the author_id of the author who wrote it.

1INSERT INTO books (title, price, published_at, author_id) VALUES ('1984', 9.99, DATE '1949-06-08', 1);
2INSERT INTO books (title, price, published_at, author_id) VALUES ('Animal Farm', 10.99, DATE '1945-08-17', 1);
3INSERT INTO books (title, price, published_at, author_id) VALUES ('Pride and Prejudice', 7.50, DATE '1813-01-28', 2);
4INSERT INTO books (title, price, published_at, author_id) VALUES ('Murder on the Orient Express', 12.00, DATE '1934-01-01', 3);
5INSERT INTO books (title, price, published_at, author_id) VALUES ('Éléments de mathématique', 25.00, DATE '1939-01-01', 4);

book_id is generated the same way, yielding 1 to 5.

Warning

Order matters here. Had we tried to insert a book referencing, say, author_id 5, or run the INSERT INTO books statements before the INSERT INTO authors ones, H2 would have rejected the statement with a referential integrity constraint violation, since the referenced author would not exist yet.

Updating data

We realize the price of "Animal Farm" (book_id 2) was entered incorrectly.

1UPDATE books SET price = 11.99 WHERE book_id = 2;

Without a WHERE clause, an UPDATE statement would apply to every row of the table, so always double-check that clause before running one.

Deleting data

The bookstore decides to stop selling "Éléments de mathématique" (book_id 5).

1DELETE FROM books WHERE book_id = 5;

The same warning as for UPDATE applies: a DELETE statement without a WHERE clause empties the whole table.

Querying data

SQL’s SELECT statement is what makes it so expressive. Let’s go through a few variations.

Selecting every column of every row:

1SELECT * FROM authors;

Selecting only the columns we are interested in:

1SELECT title, price FROM books;

Filtering rows with WHERE, combining conditions with AND and OR:

1SELECT title, price FROM books WHERE price > 10 AND published_at > DATE '1900-01-01';
2
3SELECT title, price FROM books WHERE price < 8 OR published_at < DATE '1800-01-01';

Testing for the presence or absence of a value with IS NULL and IS NOT NULL (recall that = can never match NULL):

1SELECT first_name, last_name FROM authors WHERE birth_date IS NULL;
2
3SELECT first_name, last_name FROM authors WHERE birth_date IS NOT NULL;

Sorting rows with ORDER BY:

1SELECT title, price FROM books ORDER BY price DESC;

Combining rows from both tables with JOIN … ON, matching each book to its author through the foreign key:

1SELECT a.first_name, a.last_name, b.title
2FROM authors a
3JOIN books b ON b.author_id = a.author_id
4ORDER BY a.last_name, b.title;

Aggregating rows with GROUP BY and COUNT, keeping only the groups that match a condition with HAVING (unlike WHERE, HAVING filters after aggregation):

1SELECT author_id, COUNT(*) AS book_count
2FROM books
3GROUP BY author_id
4HAVING COUNT(*) > 1;

Only George Orwell’s author_id is returned, since he is the only author with more than one book left in the table.

Dropping tables

Once we are done, tables can be removed with DROP TABLE. Because books has a foreign key referencing authors, it must be dropped first.

1DROP TABLE books;
2DROP TABLE authors;

From H2 to MySQL/MariaDB

Most of the statements above are standard enough to run unchanged on MySQL or MariaDB. There is one notable exception: MySQL and MariaDB do not support H2’s GENERATED BY DEFAULT AS IDENTITY clause. Instead, a surrogate key is generated using the AUTO_INCREMENT column attribute. The authors table from above would be created like this on MySQL/MariaDB:

1CREATE TABLE authors (
2    author_id  BIGINT AUTO_INCREMENT PRIMARY KEY,
3    first_name VARCHAR(100) NOT NULL,
4    last_name  VARCHAR(100) NOT NULL,
5    birth_date DATE
6);

Every other statement in this section, including every INSERT, UPDATE, DELETE, SELECT, and DROP TABLE, runs as is on MySQL/MariaDB.