Lesson Wednesday

SQL databases are an extremely powerful tool. We could easily spend a month covering all that PostgreSQL has to offer. However, this isn't an IT or database administration course and we will be moving on to Rails and ActiveRecord in the next course section. Because ActiveRecord creates a wrapper around our SQL statements, we'll be using ActiveRecord syntax instead of SQL syntax to make queries.

That being said, it's still useful to have a strong foundation with SQL. In addition to providing other career opportunities, understanding SQL can help us better understand ActiveRecord and similar tools used with other languages.

All of the content included in this lesson is optional but you are encouraged to explore some of these queries in your multi-day project. You are also welcome to include bonus queries in this course section's independent project as well.

Keep in mind that you will need to wrap these queries with Ruby methods and test them. We will omit these steps because this lesson is designed to improve SQL knowledge. Writing tests and creating Ruby methods is relatively straightforward and is good practice.

Searching Results


We could create a search function in our record store application by using PostgreSQL's LIKE operator. For instance, the following query would find "A Love Supreme":

SELECT * FROM albums WHERE name LIKE '%Love%';

This uses % as a wildcard much like other regular expressions. The % specifically is used to match any number of characters. In plain English, %Love% means "look for a name that includes 'Love' anywhere inside it, regardless of the number of characters that come before or after the phrase."

We could also use the ILIKE, which is case-insensitive:

SELECT * FROM albums WHERE name ILIKE '%love%';

This would still return "A Love Supreme."

For more information on LIKE and ILIKE, check the LIKE documentation.

Ordering Results


Let's say we wanted to list all our Albums alphabetically. It would be slow to do this with Ruby. SQL can do it much faster and has built-in functionality for it.

To do so alphabetically, we can do the following:

SELECT * FROM albums ORDER BY name ASC;

We can also do this in reverse alphabetical order:

SELECT * FROM albums ORDER BY name DESC;

This can be very useful for organizing numbers and dates as well. For example, which book is due next or what is the order of trains leaving the station today?

For more information on sorting, check the ORDER BY documentation.

Limiting Results


Currently, our databases are small. However, in a real-world application, we generally wouldn't want to return all the results in our database. It's more common to paginate the results, which means we might return just the first ten results. Then, if the user were to click on page 2, they'd get the next ten results.

We can always chain a LIMIT clause to a SELECT statement like this:

SELECT * FROM albums LIMIT 10;

This will return just the first ten rows in the albums table.

What if we wanted to return the next ten? We can use OFFSET to do that. Specifically, OFFSET skips a specified number of rows before returning results. To return results 11-20, our query would look like this:

SELECT * FROM albums LIMIT 10 OFFSET 10;

To explore PostgreSQL further, check out the following tutorial. The following sections are recommended:

  • Section 2. Querying Data: This section has some good information on the basics of querying.
  • Section 3. Filtering Data: More good information on the basics.
  • Section 4. Joining Multiple Tables: This section is more advanced. Only dive into this one if you're feeling confident using SQL and want to explore joins in more depth.

Searching Results

SELECT * FROM albums WHERE name LIKE '%Love%';

ILIKE is case-insensitive:

SELECT * FROM albums WHERE name LIKE '%love%';

Ordering Results

Order alphabetically:

SELECT * FROM albums ORDER BY name ASC;

Order reverse alphabetically:

SELECT * FROM albums ORDER BY name DESC;

Limiting Results

Limit the number of results returned:

SELECT * FROM albums LIMIT 10;

Use an offset:

SELECT * FROM albums LIMIT 10 OFFSET 10;

Lesson 25 of 29
Last updated August 7, 2022