16.4. Lesson: Queries¶

16.4.1. Ordering Results¶

Let’s retrieve a list of people ordered by their house numbers:

select name, house_no from people order by house_no;

Result:

     name     | house_no
--------------+----------
 Joe Bloggs   |        3
 Roger Jones  |       33
 Jane Smith   |       55
 Sally Norman |       83
(4 rows)

You can sort the results by the values of more than one column:

select name, house_no from people order by name, house_no;

Result:

     name     | house_no
--------------+----------
 Jane Smith   |       55
 Joe Bloggs   |        3
 Roger Jones  |       33
 Sally Norman |       83
(4 rows)

16.4.2. Filtering¶

Often you won’t want to see every single record in the database - especially if there are thousands of records and you are only interested in seeing one or two.

Here is an example of a numerical filter which only returns objects whose house_no is less than 50:

select name, house_no from people where house_no < 50;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

You can combine filters (defined using the WHERE clause) with sorting (defined using the ORDER BY clause):

select name, house_no from people where house_no < 50 order by house_no;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

You can also filter based on text data:

select name, house_no from people where name like '%s%';

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

Here we used the LIKE clause to find all names with an s in them. You’ll notice that this query is case-sensitive, so the Sally Norman entry has not been returned.

If you want to search for a string of letters regardless of case, you can do a case in-sensitive search using the ILIKE clause:

select name, house_no from people where name ilike '%r%';

       name     | house_no
  --------------+----------
   Roger Jones  |       33
   Sally Norman |       83
  (2 rows)

That query returned every people object with an r or R in their name.

16.4.3. Liitokset¶

What if you want to see the person’s details and their street’s name instead of the ID? In order to do that, you need to join the two tables together in a single query. Lets look at an example:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

Here is what the correct output will look like:

     name     | house_no |    name
--------------+----------+-------------
 Joe Bloggs   |        3 | Low Street
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
(4 rows)

We will revisit joins as we create more complex queries later. Just remember they provide a simple way to combine the information from two or more tables.

16.4.4. Sub-Select¶

Sub-selections allow you to select objects from one table based on the data from another table which is linked via a foreign key relationship. In our case, we want to find people who live on a specific street.

First, let’s do a little tweaking of our data:

insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;

Let’s take a quick look at our data after those changes: we can reuse our query from the previous section:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

Result:

     name     | house_no |    name
--------------+----------+-------------
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
 Joe Bloggs   |        3 | Low Street
(4 rows)

Now let’s show you a sub-selection on this data. We want to show only people who live in street_id number 1:

select people.name
from people, (
    select *
    from streets
    where id=1
  ) as streets_subset
where people.street_id = streets_subset.id;

Result:

     name
--------------
 Roger Jones
 Sally Norman
(2 rows)

Although this is a very simple example and unnecessary with our small data-sets, it illustrates how useful and important sub-selections can be when querying large and complex data-sets.

16.4.5. Aggregate Queries¶

One of the powerful features of a database is its ability to summarise the data in its tables. These summaries are called aggregate queries. Here is a typical example which tells us how many people objects are in our people table:

select count(*) from people;

Result:

 count
-------
     4
(1 row)

If we want the counts to be summarised by street name we can do this:

select count(name), street_id
from people
group by street_id;

Result:

 count | street_id
-------+-----------
     2 |         1
     1 |         3
     1 |         2
(3 rows)

16.4.6. In Conclusion¶

You’ve seen how to use queries to return the data in your database in a way that allows you to extract useful information from it.

16.4.7. What’s Next?¶

Next you’ll see how to create views from the queries that you’ve written.