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
Copyright 2017 © QuinStreet Inc. All Rights Reserved