We’ve seen the basic SELECT FROM WHERE syntax that lets us build a result set in response to an information need. The result set is another name for the table returned by a SELECT FROM WHERE query. Here we will see some ways to customize that result set by computing new values from existing data and controlling the order and/or amount of data.
Computing new values
In the SELECT clause we can compute new values from existing data. As a reminder we’ve seen that the SELECT clause determines which column we return in the query result. The columns come from the table that we build using the FROM and WHERE clauses. More specifically, in a query
- The FROM clause specifies the table we will use
- The WHERE clause filters rows from that table by keeping only ones that meet certain conditions
- The SELECT clause is used to specify which columns should be returned
The third step above is worded very generally. We’ve seen that we can specify columns by name (or by the * wildcard) to return in the result set, but we can also make new columns in the SELECT clause using existing columns. We can create these new columns using arithmetical operations like +, -, *, and / for add, subtract, multiply, and divide, respectively. Let’s imagine in the Sailors table that the best sailors are the youngest ones with a higher rating, and we want to quantify that by dividing the rating by the age (because dividing the same rating by the smaller age will yield a bigger rating than dividing by the age of an older sailor). We can do by
SELECT rating/age FROM Sailors
and if we would like to name that column, we can do so as well:
SELECT rating/age AS SailorYears FROM Sailors
That’s pretty much it for customizing in the SELECT clause. We can mix and match new columns with existing one by separating with commas, and we can renames columns one by one with AS:
SELECT rating/age AS SailorYears, age, sname AS SailorAge FROM Sailors WHERE age < 40
Controlling result set rows
We can use other clauses in a query to control the order or number of rows in the result set. The ORDER BY clause allows us to control the order (big surprise there!) of rows we get back. We can specify a column or columns to sort the results by. ORDER BY will come after the WHERE clause in SELECT FROM WHERE query. If we leave out the WHERE clause, it comes after the FROM clause. If we want to sort sailors by name, we can use
SELECT * FROM Sailors WHERE age < 40 ORDER BY sname
By default, this will alphabetize the results (i.e. sort from least to greatest, ascending order). To sort in descending order, we can add the DESC keyword:
SELECT * FROM Sailors WHERE age < 40 ORDER BY sname DESC
There is a corresponding ASC keyword we can use (ORDER BY sname ASC), but since it’s the default, it’s not required. We can sort by multiple columns by separating by commas in the ORDER BY clause. To sort by age from oldest to youngest (descending order) but then organize all sailors with the same as in alphabetical order by name, we can write
SELECT * FROM Sailors WHERE age < 40 ORDER BY age DESC, sname
Controlling the number of rows
The LIMIT clause allows you to describe the maximum number of rows in your result. It’s a maximum number, because generally before running a query, you may not be sure how many rows it would actually return. It also allows you to optionally specify how many of the first rows to not include in the result. The LIMIT clause comes after ORDER BY, or if there’s no ORDER BY after WHERE, or if there’s no ORDER BY and no WHERE, after FROM!
Here are all 10 of our sailors sorted by name in alphabetical order.
SELECT * FROM Sailors ORDER BY sname
| sid | sname | rating | age |
|---|---|---|---|
| 32 | Andy | 8 | 25.5 |
| 85 | Art | 3 | 25.5 |
| 95 | Bob | 3 | 63.5 |
| 29 | Brutus | 1 | 33.0 |
| 22 | Dustin | 7 | 45.0 |
| 64 | Horatio | 7 | 35.0 |
| 74 | Horatio | 9 | 35.0 |
| 31 | Lubber | 8 | 55.5 |
| 58 | Rusty | 10 | 35.0 |
| 71 | Zorba | 10 | 16.0 |
| sid | sname | rating | age |
To keep the first 5 sailors, we would add LIMIT 5 as the last clause in the query (it even goes after ORDER BY)
SELECT * FROM Sailors ORDER BY sname LIMIT 5
| sid | sname | rating | age |
|---|---|---|---|
| 32 | Andy | 8 | 25.5 |
| 85 | Art | 3 | 25.5 |
| 95 | Bob | 3 | 63.5 |
| 29 | Brutus | 1 | 33.0 |
| 22 | Dustin | 7 | 45.0 |
| sid | sname | rating | age |
If we had used a value greater than 10 in that query, like LIMIT 15, then we would get 10 rows back–the full table–since the database wouldn’t have any way to come up with a version of that table with 15 rows without making stuff up!
LIMIT clause with offsets
We can add one more value to the LIMIT clause to tell the database to begin returning rows after a specified number called the offset. If we wanted to start returning rows after Brutus, we would need to know Brutus is row number 4 and then specify that value as the offset.
SELECT * FROM Sailors ORDER BY sname LIMIT 4, 5
| sid | sname | rating | age |
|---|---|---|---|
| 22 | Dustin | 7 | 45.0 |
| 64 | Horatio | 7 | 35.0 |
| 74 | Horatio | 9 | 35.0 |
| 31 | Lubber | 8 | 55.5 |
| 58 | Rusty | 10 | 35.0 |
| sid | sname | rating | age |
So when we specify x as the offset we begin getting results with row x + 1. The second value is still the maximum number of rows we will get back. So in the example above we get 5 rows starting from row 5 (i.e. we get rows 5, 6, 7, 8, and 9). If the table didn’t have at least9 rows in it, we would have gotten fewer than 5 rows back.
That’s pretty much it! Try out these practice queries: