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 Open Database Connection code, 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 tblInvoices table.
mySQL = "SELECT SUM(Revenue) AS intTotal FROM tblInvoices"This example sums a currency field called Revenue from our tblInvoices 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 table 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 a few of the more advanced SQL statements you can use. When you have a good understanding of these, try to continue building your own SQL statements. They can be much longer and much more complex than these few examples when you need them to be.