SQL Course - Part 4. Selecting in SELECT queries

In the previous post, we've learned how to select all rows from a table. However, most of the time you want to really select data in an SQL query. This means you have to be able to specify some criteria.

I assume you already have a handle on running SQL queries via HSQLDB client and via OneWebSQL. From now on I will skip the instructions how to run them. If you need a reminder, see the Environment and Basic Select posts.

The WHERE clause

If you want to get the list of books published after the year 2005, write this:

SELECT * FROM book
WHERE publication_year > 2005;

You specify the select conditions using the WHERE clause in SQL query.

Which conditions can you specify?

Arithmetic operators

First, there are arithmetic conditions. SQL uses arithmetic operators similar to other programming languages.

Operator      Description
= equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
<>, != not equal to

 

String operators

There are also string operators. Obviously, you can use the equality sign '=' to compare two strings:

SELECT * FROM book
WHERE title = 'Java Puzzlers';

Use single quotes (the ' sign) to mark the beginning and end of a string in SQL.

There is one more string operator, LIKE, which is used to search for a specific pattern in a string. The "%" sign is used to define wildcards (missing letters in the pattern) both before and after the pattern.

Let's say that you want to get books with titles beginning with 'A'. Well, here you go:

SELECT * FROM book
WHERE title LIKE 'A%';

Logical operators

Of course, you can combine various conditions to get more complex conditions.

SELECT * FROM book
WHERE title LIKE 'A%' AND publication_year > 2005;
SELECT * FROM book
WHERE NOT title LIKE 'A%';

You have AND, OR and NOT operators with their obvious meanings. Use parentheses to form even more complex expressions.

SELECT * FROM book
WHERE (title LIKE 'A%' OR title LIKE 'a%') AND publication_year > 2005;

In OneWebSQL

Hint: If you want to see the query OneWebSQL sends to the database, do this:

BookDAO bookDAO = new BookDAOImpl(ds, dbAdapter, 
    new PrintStreamDaoMonitor(System.out));

In the console you'll see something like this:

    query string: SELECT book.book_id, book.category_code, 
    book.number_of_pages, book.publication_year, book.title FROM book

We'll repeat the SQL queries we've already seen. To get a list of books published after 2005, invoke this method:

List<Book> books = bookDAO.getBookList(
    BookDAO.PUBLICATION_YEAR.gt(2005));

BookDAO.PUBLICATION_YEAR is a constant representing the column publication_year in the table book. OneWebSQL generates a constant for every column in every table. The column constants have methods to create various conditions.

Operator      Method
= eq
> gt
< lt
>= ge
<= le
<>, != ne
LIKE like

The methods take values (7, 3.14, "A%") or columns (BookDAO.TITLE,
BookDAO.PUBLICATION_YEAR) as arguments.

To get a list of books with titles beginning with 'A', run this query:

List<Book> books = bookDAO.getBookList(BookDAO.TITLE.like("A%"));

To combine two conditions, you can use and and or methods:

List<Book> books = bookDAO.getBookList(
    BookDAO.TITLE.like("A%").and(BookDAO.PUBLICATION_YEAR.gt(2005)));

Use the static LExp.not method to negate a condition:

List<Book> books = bookDAO.getBookList(
    LExp.not(BookDAO.TITLE.like("A%")));

If you want to put an expression in parentheses, use static methods LExp.and, LExp.or.

List<Book> books = bookDAO.getBookList(
    LExp.and(
        BookDAO.TITLE.like("A%").or(BookDAO.TITLE.like("a%")),
        BookDAO.PUBLICATION_YEAR.gt(2005)));

Read more about creating conditions in the documentation for the LExp class.

Want more exercises? Get a list of short (less than 100 pages) Java books.