# Simple SQL: Pt. 2

Friday Sep 1st 2000 by Ted Brockwood
Share:

Now that you've learned exactly what SQL is, and how to use the standard SELECT functions, it's time to move ahead to other features. In this section, we'll be covering special grouping, logical and mathematical functions.

## Grouping and Sorting

Now that you've learned exactly what SQL is (in my previous column), and how to use the standard `SELECT` functions, it's time to move ahead to other features. In this section, I'll be covering special grouping, logical and mathematical functions. These functions build upon what you've hopefully already learned, and allow you to leverage that to use SQL in your work.

While a `SELECT` statement allows you to look up data in your database, it's far more useful to be able to group and sort the resulting data. Without grouping and sorting, you end up with globs of data, which while they may be useful, are time-consuming to work with.

## Grouping in a SELECT clause

Grouping in a SELECT clause is both obvious and simple. As an example, imagine you have a SQL database of all the checks paid to vendors by your accounts receivable department. Each record contains the payee, check number, amount of payment, and any comments about the account. You are trying, via a SQL statement, to find the total amount paid to each vendor.

Your basic query, which would find the payees, check numbers, and amounts would look like this:

``````SELECT PAYEE,CHECK_NUM,PAY_AMOUNT
FROM ACCOUNTS;``````

Which would obtain the following results:

 PAYEE CHECK_NUM PAY_AMOUNT Smith Co. 1125 1100.00 James Co. 1126 500.00 Tango Co. 1127 150.00 Smith Co. 1128 500.00

Obviously, this isn't what you need, as Smith Co. is duplicated in the results, and you wanted their total payments grouped together. So, you would modify the query as follows:

``````SELECT PAYEE ,SUM (PAY_AMOUNT)
FROM ACCOUNTS
GROUP BY PAYEE;``````

Which results in:

 PAYEE SUM Smith Co. 1600.00 James Co. 500.00 Tango Co. 150.00

Notice the use of the `SUM` function. `SUM` is one of the more common SQL functions you will use. `SUM` does what its name implies, it totals up the amounts you've told it to. As you can see from our example, the structure of a `SUM` function is

``"SUM"(Column to sum)``

Taking the previous examples as a base, I'll now introduce you to the `COUNT` function. Just as `SUM` works as its name implies, `COUNT` does likewise. With the `COUNT` function, you can total up the number of references to a field. Here's a modification of the previous query that will illustrate:

``````SELECT PAYEE,SUM(PAY_AMOUNT),COUNT(PAYEE)
FROM ACCOUNTS
GROUP BY PAYEE;``````

Our query produces the following results:

 PAYEE SUM COUNT Smith Co. 1600.00 2 James Co. 500.00 1 Tango Co. 150.00 1

## Three Other Useful Functions

Three other functions that will be of use to you are `AVG`, `MIN`, and `MAX`. I'll cover each rather briefly.

The `AVG` function performs an averaging calculation on your selected data. For example, you are looking for the average amount you've paid each vendor. Your query would be:

``````SELECT AVG(PAYMENT) AVERAGE_PAYMENT
FROM ACCOUNTS
GROUP BY PAYEE;``````

`MIN` and MAX allow you to find the lowest and highest values in a column respectively. A quick example of the use of `MAX` (or reverse it for `MIN`) would be:

``````SELECT MAX(PAY_AMOUNT)
FROM ACCOUNTS;``````

Returning:

 MAX(PAY_AMOUNT) 1100.00

By now, you've probably had your fill of selecting data, and wouldn't mind doing something else with it. We can now move ahead to the various data manipulation statements in SQL.

As with any database, there comes a time when you actually want to add data into your tables. After all, what good is an empty database? To insert data into a SQL table, you would use the aptly named `INSERT` statement. In this example, I'll use `INSERT` to add a new record to the `VENDOR` table.

The `VENDOR` table is laid-out with three fields:

`VENDOR` - A 30 character text field
`PAY_SCHEDULE` -An `INT` (integer) field
`CONTACT_NAME` -A 50 character text field

A quick

``````SELECT *
FROM VENDOR;``````

results in:

 VENDOR PAY_SCHEDULE CONTACT_NAME All-One 30 Fritz Jones Overmax 15 Davey Crockett Wondernet 45 Johnny Guru

You need to add a record for the vendor "Netbase1", with a payment schedule of 90 days, and "Joey Smith" as the contact point.

Your `INSERT` statement would look like this:

``````INSERT INTO VENDOR
(VENDOR, PAY_SCHEDULE, CONTACT_NAME)
VALUES ('Netbase1', 90, 'Joey Smith');``````

After execution, the new record is immediately added.

The `INSERT` statement can be streamlined by the removal of the column names in the query. To shorten up the previous query, and save yourself some typing, you could input it as follows:

``````INSERT INTO VENDOR
VALUES ('Netbase1', 90, 'Joey Smith');``````

As long as you have the correct number of values, and they are of the correct type (text, integer, etc.) the streamlined `INSERT` query will work just fine.

Let's assume you misspelled Netbase1 when inputting it, maybe you miskeyed it as "NetbaseOne" and now you need to change it. This is where an `UPDATE` query is useful. `UPDATE` allows you to make either single record, or batch modifications to your data table. The `UPDATE` query allows you to modify data meeting your specific criteria. To use `UPDATE` to change "NetbaseOne" to "Netbase1" your query would be designed as such:

``````UPDATE VENDORS
SET VENDOR = 'Netbase1'
WHERE VENDOR = 'NetbaseOne';``````

And with that simple statement, you have cleaned up an earlier typo.

No lesson on data manipulation would be complete without covering how to remove data records. To delete data records, you would use, you guessed it, the `DELETE` statement. `DELETE` is as easy as `SELECT`, and our example below details removing the new record for "Netbase1" which we just created.

``````DELETE FROM VENDOR
WHERE VENDOR = 'Netbase1';``````

That easily, you've removed a record. Be careful when using `DELETE`, as it's not always easy to bring back records once they've been removed. Some SQL servers have "rollback" which works like an undo operation in most applications, but it's not a given that all do. Make sure to check with your SQL server documentation on this feature.

## Summing Up

To sum up, here is a basic reference to the SQL statements we've covered:

#### SELECT Statements

``````SELECT (FIELDS) FROM (TABLE)
WHERE (CONDITION)
GROUP BY (COLUMN)``````

#### UPDATE Statements

``````UPDATE (TABLE)
SET (COLUMN) = (VALUE)
WHERE (CONDITION)``````

#### INSERT Statements

``````INSERT INTO (TABLE)
(COLUMN1), (COLUMN2), (COLUMN3) etc.
VALUES (VALUE1), (VALUE2), (VALUE3), etc.``````

Remember that most SQL queries end with a semicolon, and to check your documentation on the preferred wildcard (asterisk or percentage symbol). Both of these are extremely important.

By now you hopefully understand the basics of the SQL language. While I've covered quite a bit in a short time, there are other features of SQL we did not cover. For those features and statements, it's a good idea to purchase a SQL reference guide. I'm partial to SAMS Publishing's "Teach Yourself SQL in 21 Days" as it covers not only an overview of SQL, but details the specifics of many SQL servers.

Now that you have the basics down, the next part in this series will cover using SQL statements in Visual InterDev 6.0 and the Drumbeat Web application development environments.

### References:

"Teach Yourself SQL in 21 Days, Second Edition"
SAMS Publishing (c) 1997
ISBN 0-672-31110-0

SQL Servers:

Microsoft SQL Server
Microsoft NT Platform only

Sybase SQL Server
Windows NT, Linux, various other platforms

Oracle
Windows NT,Linux, various other platforms

Informix
Windows NT, Linux, other platforms

MySQL (free for Linux users)
Linux and Windows platforms

Ted Brockwood is the Information Services Manager for a real estate listing service in Oregon. His experience covers Java, Linux, UNIX, NT, Win95/98, Win3.x, and DOS.

Share:
Home
Mobile Site | Full Site