Query Data

Intro

This is the fifth in a series of tutorials on how to use a relational database.
We are using Postgres, and the PG Admin graphical interface.
We have a database named bookstore with two tables in it, authors and books.
In the last tutorial we introduced the concept of CRUD operations. Those are queries that Create, Read, Update or Delete data from the database. And we focused on queries that modify data in the database: Create, Update, and Delete.
In this tutorial we will wrap up our discussion of CRUD operations by covering queries that Read data from the database.

Read it, watch it, do it, review it:

There is both a written and accompanying video version of this tutorial. There are timestamps under the headings that align with the video version of the topic.

Read it: For each topic heading, First read the topic.
Watch it: Then watch the associated section of the video.
Do it: Then follow the instructions to replicate the steps in your own version of the bookstore database.

The Database CheatSheet has a major category that aligns with this tutorial.

Review it: When you are done with the whole tutorial, open the CheatSheet and review the Query Data category from this tutorial. Make sure you understand everything, and can refer back to the CheatSheet when you are working on your own projects in the future.

Query all rows from the table

[0:00 Video timestamp]

So far we've been using PG Admin to view data. Clicking on the table name, then click the View Data toolbar

But in an application, you get the data using SQL statements.

We'll start with some simple Read queries.

Select all columns and rows from a table

To query all rows in a table use the below format using a wildcard where the column names would go.

SELECT * FROM table_name;

Let's try it out.

Open the PG Admin Query Tool or PSQL Tool.
Select all columns and rows from the books table:

SELECT * FROM books;

Select specific columns:

The format to select specific columns is below. List the columns separated by commas.

SELECT column1, column2, column3 FROM table_name;

Let's try it out.

Select all rows, but only the id, title, quantity, and price columns from the the books table.

SELECT id, title, quantity, price FROM books;

Eliminate duplicates with the DISTINCT keyword

The DISTINCT keyword will only select the first instance of a value for the given column.

The query format is below:

SELECT DISTINCT column_name FROM table_name;

For example, if we want to get the id of all the authors with books in our books table we can execute a select distinct query on the author_id column:

 SELECT DISTINCT author_id FROM books;

This will return the three author_id values in the order they are found without any duplicates: 1, 3, 2.

Add Conditions with the WHERE clause

[1:35 Video timestamp]

Generally, you are not going to select all rows from the database. Instead, use a where clause to set a condition on the data query.

To select a specific row id use the below format:

SELECT * FROM table_name
WHERE id = x;

Let's get the book with id 3

SELECT id, title, quantity, price FROM books
WHERE id = 3;

Select rows that meet a WHERE clause condition:

You can use the where clause to set conditions that may select multiple rows. Use the below format:

SELECT * FROM table_name
WHERE condition;

For example, to select all books that are on sale, use the below query:

SELECT id, title, quantity, price FROM books
WHERE on_sale = true;

This should return the two books that are on sale.

Select rows that meet multiple WHERE clause conditions:

You can also set multiple conditions using the AND or OR keywords.

Use AND if both conditions must be true.
Use OR if either condition must be true.
Use NOT if the condition must not be true.

Use the below format:

SELECT * FROM table_name
WHERE condition1 AND condition2;

Let's select the books that have a quantity of 27 or more and a price of 30 or higher.

SELECT id, title, quantity, price FROM books
WHERE quantity >= 27 AND price >= 30;

Run the same query except with the OR keyword. This will select the books where either condition is true, so it will select more of the books.

SELECT id, title, quantity, price FROM books
WHERE quantity >= 27 OR price >= 30;

Select books from all authors except the author_id 1.

SELECT id, title, quantity, price FROM books
WHERE NOT author_id = 1;

ORDER BY

[3:33 Video timestamp]

When you pull multiple rows from the database you will generally want to sort the results in some kind of order. To do that, add an order by clause with the below format:

SELECT * FROM table_name
ORDER BY column_name [ASC | DESC];

It is ordered in ascending order by default, so adding the ASC keyword is optional.
Add the DESC keyword to order it in descending order.

As an example, to order the books by price in ascending order use the below query:

SELECT id, title, quantity, price FROM books
ORDER BY price;

To order them in descending order change it to the below:

SELECT id, title, quantity, price FROM books
ORDER BY price DESC;

Order by multiple columns:

To order by multiple columns separate the columns by commas. Use the below format:

SELECT * FROM table_name
ORDER BY column_name [ASC | DESC], column_name [ASC | DESC];

It will order by the first column, then duplicate values will be ordered by the second column

Let's order the books first by author_id in descending order, then by title in ascending order:

SELECT id, title, quantity, price FROM books
ORDER BY author_id DESC, title ASC;

LIMIT

[5:04 Video timestamp]

If your database has a lot of data in it you may want to put a limit on the query results.
Use the below format with the limit clause after the order by clause.

SELECT * FROM table_name
ORDER BY column_name
LIMIT x;

Let's select books ordered by title and limit it to the first 5.

SELECT id, title, quantity, price FROM books
ORDER BY title
LIMIT 5;

Limit with Offset

You will notice on some websites, when you query data you will get something like the first 50 results, and there will be a button or link to get the next 50 results.
To accomplish that you use an offset. You add the OFFSET keyword after the LIMIT:

SELECT * FROM table_name
ORDER BY column_name
LIMIT x OFFSET y;

We already selected the first 5 books ordered by title. Now let's select the next 5 books by adding an offset:

SELECT id, title, quantity, price FROM books
ORDER BY title
LIMIT 5 OFFSET 5;

This will get rows 6 through 10.

Aggregation Queries (Count, Avg, Min/Max, Sum)

[6:04 Video timestamp]

Aggregation queries get information about all or a subset of rows in a table.

We can get the count, the average value, minimum or maximum value, or the sum of all the values for a column.

COUNT:

You can get the count of all rows in the database:

SELECT COUNT(*) FROM table_name;

To get the count of books in our database run:

SELECT COUNT(*) FROM books;

You can add a condition:

SELECT COUNT(*) FROM table_name
WHERE condition;

To select all the books where we are out of stock, add a where clause to select books with quantity of 0.

SELECT COUNT(*) FROM books
WHERE quantity = 0;

AVG (average)

To get the average value for a numeric column, use the below format.

SELECT AVG(column_name) FROM table_name;

To get the average for a subset of data, add a where clause to the query.

Get the average price of the books in our database, then the average price of books that are not on sale:

SELECT AVG(price) FROM books;
SELECT AVG(price) FROM books WHERE on_sale = false;

MIN - Smallest:

Select the row with the smallest value using the below format:

SELECT MIN(column_name) FROM table_name;

Get the book with the lowest price:

SELECT MIN(price) FROM books;

MAX - Largest:

Select the row with the largest value using the below format:

SELECT MAX(column_name) FROM table_name;

Get the book with the highest price:

SELECT MAX(price) FROM books;

SUM Column values:

Sum the values in the specified column using the below format:

SELECT SUM(column_name) FROM table_name;

Get the total quantity of books from the books table.

SELECT SUM(quantity) FROM books;

IN

The IN operator compares a value to a list of specified values.
It returns true if the value is in the list, false otherwise.
Use the below format:

SELECT * FROM table_name
WHERE column_name IN (val1, val2, val3, ...);

Let's get all the books from our books table where the author_id is 1, 3, or 7.

SELECT id, title, author_id, quantity, price FROM books
WHERE author_id IN (1, 3, 7);

This will return rows where author id is 1 or 3. There is no author_id 7 so you won't see any with that id.

LIKE

[7:40 Video timestamp]

Use the LIKE operator in the where clause when you want to match against a pattern instead of a specific value.
Use the below format:

SELECT * FROM table_name
WHERE column_name LIKE '[%]pattern[%]';

Put % before the pattern to allow any characters to come before it.
Put % after the pattern to allow any characters to come after it.

For example, to search for all books that have SQL somewhere in the title use the below query:

SELECT id, title, quantity, price FROM books
WHERE title LIKE '%SQL%';

ILIKE (make it case insensitive)

To make it case insensitive (e.g., SQL, sql, Sql, etc.), in Postgres use ILIKE

SELECT id, title, quantity, price FROM books
WHERE title ILIKE '%SQL%';

Note: ILIKE only works in Postgres.

In SQLite, LIKE is already case insensitive.
MySQL has a LOWER keyword that would downcase the title before applying the LIKE operator: WHERE LOWER(title) LIKE '%sql%';

LIKE/ILIKE with the NOT operator

To select all books where SQL is NOT in the title, use the NOT operator:

SELECT id, title, quantity, price FROM books
WHERE NOT title ILIKE '%SQL%';

Add muliple patterns with the OR operator

To select books with SQL, database, or postgres in the title, add multiple patterns with the OR operator:

SELECT id, title, quantity, price FROM books
WHERE title ILIKE '%SQL%' 
OR title ILIKE '%database%'
OR title ILIKE '%postgres%';

Query Data (Video Tutorial)