Lesson 36:   Structured Query Language (SQL)           [Explore an SQL Server]     [Data Structures]

In the last project, we used a line of code that looked like this: SQL = "Select * From [" & RSName & "]" SQL is simply a variable and RSName is another with a value "tblMovie" the string when concatenated looks like this: Select * From [tblMovie].
Select * (in SQL) means select all of the fields in the table. If we want only the Title and Genre fields you would use: Select [Title], [Genre] List Fields in square brackets, separated by commas. The square brackets ([ ]) are not necessary for a one-word Table or Field Name. However, any Table Name or Field Name that is two or more words requires the square brackets. So just to be consistent, I use them all the time (it helps set off the Table/Field Name.)
From [tblMovie] tells the ADODB DLL which table to open (tblMovie).
Note: It has been traditional to write SQL in Uppercase, but as you can see, SQL is case insensitive.
This SQL is the simplest SQL you can write. There is, of course, more!

Select statment syntax:

Select [ All | Distinct ] select_list
  [ Into new_table ]
[ From table_name(s) | view_name(s) ]
[ [ Left [ Inner | Outer ] | Right [ Inner | Outer ] | Cross ]
  Join table_name(s) | view_name(s) On join_condition ]
[ Where search_condition ]
[ Group By group_by_expression ]
  [ Having search_condition ]
[ Order By order_expression [ ASC | DESC ] ]
[ Compute aggregate_functions ]
[ For { Browse | XML | { Raw | Auto | Explicit }
        [ , XMLData ]
        [ , Elements ]
        [ , Binary Base64 ]
  } ]

Ordering:

SQL has the ability to Order (alphabetize) a list of records by any field, even if the database does not have an index for that field. To order the records by Title in the table tblMovie do this: SQL = "Select * From [tblMovie] Order by [Title]" This can be used to Order by any field in the recordset! You can even create a secondary order field and a tertiary field (etc.) Some movies have the same name you can order by date so that earlier movies come first: SQL = "Select * From [tblMovie] Order by [Title],[Date]"
And you can reverse the order: SQL = "Select * From [tblMovie] Order by [Title] DESC"

Filtering:

Filtering is the ability to select records out of a recordset based on certain criteria. For example, suppose you want a list of only the movies Ray picked. you would do this by writing the following SQL Code: SQL = "Select * From [tblMovie] Where [EnteredBy] = 'Ray' "
If however, I but Ray LePine in the EnteredBy Field, the above filter would not work!
SQL = "Select * From [tblMovie] Where [EnteredBy] Like '%Ray%' "
The Like and % signs in this SQL tells SQL to look for Ray anywhere within the EnteredBy field.
You may use more than one criterion: SQL = "Select * From [tblMovie] Where [EnteredBy] Like '%Ray%' And [Date] > #01/01/1999# And [MovieID] = 9"
String type fields are in single quotes ( [EnteredBy] Like '%Ray%' ), date fields us number signs ( [Date] = #01/01/1999# )
and numeric fields use nothing at all ([MovieID] = 9 ).
Note: Microsoft SQL Server uses single quotes around Dates as well as Strings ( [Date] > '01/01/1999')
You may filter for more then one value:
SQL = "Select * From [tblMovie] Where [Rating] in ('G','PG','PG-13')"

Limiting:

Limiting is the ability to remove duplications. For example, we have the EnteredBy field, this field has the name of the person that entred the Movie listing. If we were to list this field we would see the names appear maltiple times. We can use SQL to make a list that has only one entry for each unique name in the list. check out the following code: SQL = "Select Distinct [EnteredBy] From [tblMovie] "
This would produce a list of Directors: SQL = "Select Distinct [Director],* From [tblMovie] " This SQL will create a unique list of Directors and include the others fields (,*) as well. Now back to the EnteredBy, if you do this listing you'll see that when a Movie is submitted by two people both their names (separated by comma) are listed, so a unique listing will include these as a unique Enteredby. To not include these in a unique listing try the following: SQL = "Select Distinct [EnteredBy] From [tblMovie] Where Instr([EnteredBy],',')=0 ")
Instr([EntredBy],',')=0 will exclude all fields with a comma.

Relating:

Relationships are the same as in Access, but a relationship does not have to be created in Access in order to be use in SQL. In other words SQL will create any relationship you wish regardless of whether it exists in Access or not. For example lets make a list of movie Titles and Rating Descriptions: SQL = "Select * From [tblMovie] as M, [tblRatingCode] as R Where M.[Rating]=R.[Rating]"
We are introducing two new SQL features here. First notice the as M, what this does is allow the Table Name to be referred to as M throughout the SQL string (it helps to reduce the length of the SQL string.) Secondly Where M.[Rating]=R.[Rating] sets the relationship between the two Tables tblMovie and tblRatingCode and the field they both have in common, Rating.

Creating "Virtual Fields":

A Vertual Field is a field that exists only in memory as long as the Recordset is open. For example: SQL = "Select *,[Date] + [Genre] as [Category] From [tblMovie]" This SQL string creates the field Catagory. If you list all the fields in tblMovie, you'll find a new one named Catagory which is a concatenation of the Date and Genre Fields. Or this one: SQL = "Select Month( [Date] ) as [Month] From [tblMovie] this will return the numeric month partion of the date in the new field name [Month] or DateName( Month, [Date] ) will return the Month Name. (T-SQL Only).

Convert Data types:

To change a text data field to an integer: SQL = SQL & "Order by cast( [Text_Number] as integer) This SQL "Order by" command will sort a text field as though it were a numberic field.
e.g.   normal Order: 1, 11, 101, 2, 20, 3, 4, 49, 5
cast interger Order: 1, 2, 3, 4, 5, 11, 20, 49, 101

Other convertions are:
number(8,4)
char(8)
datetime
varchar

Web Page Design: Exercise 36