SQL Tutorial - The Basics - Part II

By now you should already have a basic understanding of the SQL SELECT statement from the article SQL Tutorial - The Basics - Part I. This article will expand on that knowledge and introduce some of the more intricate commands that can be used.

The following commands will be introduced, clicking an item below will skip directly to that section:

• ORDER BY
• DISTINCT
• GROUP BY
• TOP and ROWCOUNT
• MIN and MAX
• SUM, AVG and COUNT

All of the examples in this artilce will use data from the following 'Employees' table:

ORDER BY

The ORDER BY command allows the returned results to be sorted by one or more columns. The following query returns all of the rows from the table but sorts them alphabetically on the Surname column:

This would return the following results:

Additionally you can add the keywords 'asc' or 'desc' to the end of you query to decide whether the results should be returned in ascending or descending order. By default the records are sorted in acsending order.

The ORDER BY clause should appear afer any WHERE, AND and OR clauses that you may be using. If you wish to order by more than one column, i.e. Job Title first and then Age, simply separate the fields with a comma. The 'asc' or 'desc' can be added to each of the ORDER By fields. A more advanced example could read:

This would return the following results:

DISTINCT

The DISTINCT command allows you strip out and duplicate values within a field in your results, i.e.

The following results are returned:

GROUP BY

The GROUP BY is similar in some aspects to the DISTINCT command in that the result appears to remove duplicate values. Unlike DISTINCT the GROUP BY command is used when another field alongside the grouped field is to have an aggregate function (such as SUM, COUNT or AVG) applied to it, e.g.:

Returns the following results:

TOP and ROWCOUNT

The TOP command allows you to specify the number of records you wish to return that match your request. So setting a query to select the TOP 5 results returns the first 5 rows which meet the criteria, e.g.:

The ROWCOUNT command acts in a very similar manner, simply set the ROWCOUNT before executing a standard query and the desired number of rows will be returned, e.g.:

Both of these queries will return exactly the same result set:

You may be asking what the difference between the 2 is. There are a couple of things to consider when deciding which is the best option:

• If you're testing your full queries but only want to return a couple of rows then ROWCOUNT is the best option, you only need to set it once and all subsequent queries will return the desired number of rows until the ROWCOUNT is reset.
• You can set the ROWCOUNT via a variable but TOP can only use a constant, i.e.:

This will NOT work:

But this will:

NB: Always remember to remove the ROWCOUNT restrictions after your query, this can be done by setting it to 0, i.e.:

MIN and MAX

The MIN and MAX commands are a quick way of extracting the minimum and maximum values of a field from a table, for example the 2 queries below:

Return the following recordsets:

SUM, AVG and COUNT

The SUM, AVG (average) and COUNT commands let you perform simple arithmetic on your specified table. The SUM function adds the value of a single fields across all of the rows which meet you criteria and returns the result. The AVG function returnd the mean average of the requested items i.e. The sum of all of the items / total number of items. Finally the COUNT command returns the number of rows which meet your criteria. The following examples show wach of these commands in action:

The following record sets will be returned:

Back To SQL