15.4. Lesson: Queries
When you write a SELECT ... command it is commonly known as a query - you are interrogating the database for information.
The goal of this lesson: To learn how to create queries that will return useful information.
Note
If you did not do so in the previous lesson, add the following people objects to your people table. If you receive any errors related to foreign key constraints, you will need to add the ‘Main Road’ object to your streets table first
insertintopeople(name,house_no,street_id,phone_no) values('Joe Bloggs',3,2,'072 887 23 45'); insertintopeople(name,house_no,street_id,phone_no) values('Jane Smith',55,3,'072 837 33 35'); insertintopeople(name,house_no,street_id,phone_no) values('Roger Jones',33,1,'072 832 31 38'); insertintopeople(name,house_no,street_id,phone_no) values('Sally Norman',83,1,'072 932 31 32');
15.4.1. Ordering Results
Let’s retrieve a list of people ordered by their house numbers:
selectname,house_nofrompeopleorderbyhouse_no;
Result:
name|house_no --------------+---------- JoeBloggs|3 RogerJones|33 JaneSmith|55 SallyNorman|83 (4rows)
You can sort the results by the values of more than one column:
selectname,house_nofrompeopleorderbyname,house_no;
Result:
name|house_no --------------+---------- JaneSmith|55 JoeBloggs|3 RogerJones|33 SallyNorman|83 (4rows)
15.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:
selectname,house_nofrompeoplewherehouse_no<50; name|house_no -------------+---------- JoeBloggs|3 RogerJones|33 (2rows)
You can combine filters (defined using the WHERE clause) with sorting
(defined using the ORDER BY clause):
selectname,house_nofrompeoplewherehouse_no<50orderbyhouse_no; name|house_no -------------+---------- JoeBloggs|3 RogerJones|33 (2rows)
You can also filter based on text data:
selectname,house_nofrompeoplewherenamelike'%s%'; name|house_no -------------+---------- JoeBloggs|3 RogerJones|33 (2rows)
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:
selectname,house_nofrompeoplewherenameilike'%r%'; name|house_no --------------+---------- RogerJones|33 SallyNorman|83 (2rows)
That query returned every people object with an r or R in their name.
15.4.3. Joins
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:
selectpeople.name,house_no,streets.name frompeople,streets wherepeople.street_id=streets.id;
Note
With joins, you will always state the two tables the information is coming from, in this case people and streets. You also need to specify which two keys must match (foreign key & primary key). If you don’t specify that, you will get a list of all possible combinations of people and streets, but no way to know who actually lives on which street!
Here is what the correct output will look like:
name|house_no|name --------------+----------+------------- JoeBloggs|3|LowStreet RogerJones|33|Highstreet SallyNorman|83|Highstreet JaneSmith|55|MainRoad (4rows)
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.
15.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:
insertintostreets(name)values('QGIS Road'); insertintostreets(name)values('OGR Corner'); insertintostreets(name)values('Goodle Square'); updatepeoplesetstreet_id=2whereid=2; updatepeoplesetstreet_id=3whereid=3;
Let’s take a quick look at our data after those changes: we can reuse our query from the previous section:
selectpeople.name,house_no,streets.name frompeople,streets wherepeople.street_id=streets.id;
Result:
name|house_no|name --------------+----------+------------- RogerJones|33|Highstreet SallyNorman|83|Highstreet JaneSmith|55|MainRoad JoeBloggs|3|LowStreet (4rows)
Now let’s show you a sub-selection on this data. We want to show only people who
live in street_id number 1:
selectpeople.name frompeople,( select* fromstreets whereid=1 )asstreets_subset wherepeople.street_id=streets_subset.id;
Result:
name -------------- RogerJones SallyNorman (2rows)
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.
15.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:
selectcount(*)frompeople;
Result:
count ------- 4 (1row)
If we want the counts to be summarised by street name we can do this:
selectcount(name),street_id frompeople groupbystreet_id;
Result:
count|street_id -------+----------- 2|1 1|3 1|2 (3rows)
Note
Because we have not used an ORDER BY clause, the order of your results may not match what is shown here.
Try Yourself: ★★☆
Summarise the people by street name and show the actual street names instead of the street_ids.
Answer
Here is the correct SQL statement you should use:
select count(people.name), streets.name frompeople, streets where people.street_id=streets.id group by streets.name;
Result:
count | name ------+------------- 1 | Low Street 2 | High street 1 | Main Road (3 rows)
You will notice that we have prefixed field names with table names (e.g. people.name and streets.name). This needs to be done whenever the field name is ambiguous (i.e. not unique across all tables in the database).
15.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.
15.4.7. What’s Next?
Next you’ll see how to create views from the queries that you’ve written.