The reason for having a database is to have vast amounts of data organized and readily available. SQL is the means by which we can communicate with the database and tell it to find exactly what we need. From thousands of records, we can filter it down to precisely what we are searching for, granted if the database schema was created efficiently.

Let’s look at an example, using the HAVING clause. Let’s say we wanted to search for all employees who have an average salary of $50,000.

*Note, when using HAVING you always have to incorporate GROUP BY filtering.

Here’s how we would do it.

SELECT employee, AVG(salary)
FROM employee_data
GROUP BY employee
HAVING AVG(salary) > 50000;

See how used the AVG aggragate function to get the average salaray?

You can also find this information using the WHERE clause.

We have previously found the minimum and maximum range for a price column in a table.

But what if we wanted to find the values in between? Say for example you wanted to view all the products you sell between $50 and $100 dollars?

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

See how easy that is, it almost reads like a normal sentence, this is why SQL is considered to have natural language syntax.

Now, the between operator can also be used to find in a range of letters.

For example, if we have a table of employees and a column with employee last names, and we wanted to find all the employees whose last names range from a-m, we would do a query like this:

SELECT * FROM employees WHERE lastname BETWEEN a AND m;

Easy as pie.