SQL ADVANCED

OK, so you got through our SQL Basics Tutorial. Now, we are moving on to some more advanced SQL statements. As a refresher, here is our standard connection and recordset information, which shows you where your SQL will go.

<% DIM objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=myCONNECTION.dsn" objConn.Open DIM mySQL mySQL = "SELECT * FROM myTABLE" DIM objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open mySQL, objConn %>



Display data from database here.


Now, let's get to the good stuff. Here are some of the more advanced things you can do with your SQL statements.

mySQL = "SELECT Count(*) AS intTotal FROM tblInvoices"

This example counts the number of records contained in our intInvoices table.

mySQL = "SELECT SUM(Revenue) AS intTotal FROM tblInvoices"

This example sums a currency field called revenue from our intInvoices table. There are other commands you can use other than SUM in the same way. You can also replace SUM with AVG, MIN, or MAX to find the average, minimum, or maximum values. If you are really into statistical analysis, you can even use VARIANCE or STDDEV to return the variance or standard deviation values.

mySQL = "SELECT * FROM tblInvoices WHERE DueDate = # " & Date() & " # "

This example selects all of the records in our tblInvoices tables where the DueDate field equals todays date.

mySQL = "SELECT * FROM tblInvoices WHERE Type = ' " & strVariable & " ' "

This example selects all of the records in our tblInvoices tables where the Type field is equal to a string or text variable such as "Customer" that is entered on your ASP page.

mySQL = "SELECT * FROM tblInvoices WHERE Number = " & intVariable & " "

This example selects all of the records in our tblInvoices tables where the Number field is equal to an integer or numeric variable such as "100" that is entered on your ASP page.

These are only some of the more advanced SQL statements. We are still only scratching the surface here, but we will continue to add more advanced SQL statements to this page as time goes on so be sure to check back.

http://www.aspwebpro.com/tutorials/asp/sqladvanced.asp