SQL Nuts and Bolts (Part 2)

In this second tutorial of the SQL Nuts and Bolts Series, we’ll delve further into SQL basics and demonstrate more clauses for the select statement: group by, having, in, and between.

In part one of our SQL Nuts & Bolts tutorial, we started with the SELECT statement and explained three operators: where, order by, and distinct. If you need a refresher on what they do, please review before continuing.

Group By Clauses

The group by clause will return all the specified rows from the columns you choose to select data from and perform aggregate functions (such as the average of values or maximum number) on them.

Here is what the code would look like for a “group by” clause, with the “max” (maximum value) aggregate function:

SELECT max(column_name1), column_name2
FROM table_name
GROUP BY column_name2

To better understand this, here is a “real-world” example based on the “people_list” table we used in part one:

SELECT max(age), sex
FROM people_list
GROUP BY sex

What the first line of this does is selects the largest value found in the column “age” (in other words, the oldest age) and takes each “max” from every value listed under the column “sex” (male and female). The second line, of course defines from what table you’re extracting the data. And the last line groups the selected values from the “sex” column.

So what would be returned? The names of both sexes and the oldest ages of each sex.

Is there a full list of aggregate fuctions, you might ask? Certainly. Here is a complete list of them and what functions they perform:

AVG	the average value of a column
COUNT	the total number of values in a column
COUNT(*)	the number of rows in the entire table
MIN	the smallest value in a column
MAX	the largest value in a column
SUM	the sum of the numeric values in a column

Aggregate functions can use the same conditional selections we defined in the last tutorial:

= Equal
> Greater than
< Less than
>= Greater than or equal to
< = Less than or equal to
<> Not equal to
LIKE It shows data that is “like” what you specify.

Having Clauses

A having clause adds an extra condition to a group that was not specified beforehand. The raw code would be:

SELECT max(column_name1), column_name2
FROM table_name
GROUP BY column_name2
HAVING condition

To append it to the previous “people_list” table example:

SELECT max(age), sex
FROM people_list
GROUP BY sex
HAVING max(age) > 45

This will return the data from each “sex” column ONLY if the maximum age from each one is greater than 45.

In Clauses

The in operator is used to detect if particular values reside within a certain column. The in clause is used in conjunction with the where clause, which was introduced in the previous SQL tutorial. The “plain jane” code is:

SELECT max(column_name1), column_name2
FROM table_name
WHERE column_name1 IN (list of values)

And employing it with the “people_list” table:

SELECT first_name, last_name, age from people_list
WHERE age IN ('25', '26', '27', '28', '29', '30')

And it will return only the first names, last names, and ages of those who are precisely 25, 26, 27, 28, 29, and 30 years of age.

The in operator can be used to retrieve any type of alphanumerical data.

You can replace “IN” in your code with “NOT IN” to exclude particular rows in the returned list.

Between

The between clause is used to define returned data that resides between two numerical values. “Between” must be used in conjunction with “and”:

SELECT first_name, last_name, age from people_list
WHERE age BETWEEN 25 AND 30

Similarly to the “in” clause, you can replace “BETWEEN” in your code with “NOT BETWEEN” to exclude particular rows in the returned list.

You can get more defined data by rewriting the code without using the between operator, especially if you chose to return data using both the less-than and greater-than conditionals. The subsequent example will return the first name, last name, and age of everyone under the age of 25 and over the age of 30:

SELECT first_name, last_name, age from people_list
WHERE age > 25 AND < 30

Coming soon: part three!