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.

When running more complex queries, such as concatenating strings, often times you may need to trim whitespace from the values.

Let’s look at an example of this. Remember the query we ran for concatenating:

SELECT CONCAT (productName, ” , ” , price) FROM products;

As it is the output would be XBOX , $399

Let’s say we wanted to trim the whitespace between XBOX and the comma.

Here’s the query:

SELECT CONCAT (RTRIM(productName) ” , ” , price) FROM products;

In this case we used rtrim because we wanted to trim to the right or immediately following the value. If we wanted to trim space before the value we would use the LTRIM, and if we wanted to trim from both sides, we would simply use trim.

Aggregate means a whole, the term is most used in economics (if you have ever taken an economics class, you probably remember how painful it was ), it basically means ‘as a whole’. So, we’ll cover a few simple aggregate functions , avg,max, and min.

Remember the price we concatenated with a string? What if we wanted to see the average price of all our the products in our products table?

SELECT AVG(price) AS average_price FROM products;

the query would return something along the lines of 23.94443, obviously depending the prices in your table , if it were cars, it might be 23,232.2238576.

Notice we also used an alias in there.

Now , what if we wanted to find the minimum and maximum price in our products table?

SELECT MIN(price) AS mini_price, MAX(price) AS max_price FROM products;

The output should be two numbers let’s say : $3.99 $599.99

That’s it, aggregate functions are often overlooked as an easy way to get quick info.

A word of warning about concatenating with sql, if the data used is one that is created by a user where query strings are created dynamically, there could be a possibility of an sql injection. This method is best used for outputting information you have in a database. For example, let’s say you have a products table and you want to concatenate the product name and price.

SELECT CONCAT (productName, ” , ” , price) FROM products;

The output would be: XBOX, $399

You can see the power in this function. We could just as well add some text modifiers:

SELECT CONCAT (productName, ” , “”Buy it Now,” , price) FROM products;

The output would be: XBOX, Buy it Now, $399

The Concat operator is used mainly by mysql. Other database engines such as Oracle use the “||” operator.