Basic SQL Tutorial - Part 2
|
|
|
Simple SQL
Getting Started With SQL, Part II
by Ted Brockwood
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.
April 1999
Simple SQL
- Part One
- Part Two
- Part Three
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 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 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:
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 he 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.
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
http://www.sams.com
SQL Servers:
Microsoft SQL Server
http://www.microsoft.com
Microsoft NT Platform only
Sybase SQL Server
http://www.sybase.com
Windows NT, Linux, various other platforms
Oracle
http://www.oracle.com
Windows NT,Linux, various other platforms
Informix
http://www.informix.com
Windows NT, Linux, other platforms
MySQL (free for Linux users)
http://www.mysql.com
Linux and Windows platforms
Simple SQL
- Part One
- Part Two
- Part Three
Other articles by Ted Brockwood:
NetObjects Fusion
Drumbeat 2000
Linux
BeOS
PhP
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.
|
|
|
