Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 1, 2000

Simple SQL: Pt. 2 - Page 2

By Ted Brockwood

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.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date