Race Results Tutorial
1. You do not need to modify the text in the Select Statement or Order By Statement edit boxes.
2. In the Where Statement edit box, replace "John" with your first name and "Smith" with your last name.
3. Make sure your first and last names are enclosed in quotes.
4. Click the SQL Execute button to retrieve your data.
1. A query, using Structured Query Language (SQL), retrieves your data from the GWTC race results database.
2. To make it easier for novice users, the query is divided into three parts -- Select...From, Where, and Order By.
3. The Select...From edit box rarely needs changing -- the default will retrieve all fields from the RaceResults table.
4. Most modifications will be made in the Where and Order By edit boxes. A knowledge of the fields in the RaceResults table will be helpful in creating more complex queries.
5. You should always use a Where statement (filters out unwanted records). The RaceResults table contains 240,000+ records and can be very slow retrieving all of the records through an internet connection.
6. The Example buttons will provide you with samples of Select...From, Where, and Order By statements. Click the appropriate button then double-click the selected sample and it will automatically be placed in the proper edit box.
7. In most instances, you will only need to change the text enclosed in quotes. Double or single quotes are acceptable with the exception that they can't be mixed (i.e., either one or the other). You are required to use quotes on text and date fields.
8. Semicolons are not required to end an SQL statement in this application -- best not to use them.
9. When you have completed constructing your SQL statement (Select…From, Where, and Order By edit boxes), click the Execute SQL button to execute your query.
10. Incorrect SQL syntax will generate a "Server Error in '/' Application" page. The first few lines of this page will provide information on what type of error was encountered and where in the SQL statement the error occurred. This page does not harm the GWTC database nor your original web page. Once you have an understanding of the error, just hit your browser's BACK arrow to return to your previous web page to make corrections.
11. The "Records per Page" control allows you to change the record grid's page size. The default page size is 100 records per page. You can change the page size to 400 records per page in increments of 100. Once you have made your change, click the Execute Query button to implement.
12. The Print button will only print one page at a time. You will have to manually set your printer to landscape mode (if desired). This is due to limitations in the current HTML5 standard and our desire not to use third-party software to control devices on your computer.
13. The Save Data button will create a window with your data in a CSV-type format. It is up to the user to copy and paste into an application onto your computer. Again, it is our intent to not write data onto any client computer.
14. Your returned dataset can be quickly resorted by clicking on the underlined column heading of the record grid.
1. The Gulf Winds Track Club (GWTC) race results database uses the MySQL Database Software as a client/server system. The SQL part of "MySQL" stands for "Structured Query Language, which is used to retrieve data from this database.
2. An SQL statement is constructed by using the SELECT, FROM, WHERE and ORDER BY key words in conjunction with the table name (RaceResults) and the table’s fields. To ease the writing of an SQL statement, we have split the user input into three parts (Select…From, Where, and Order By) and have added sample statements that can be used as a guide.
1. RecId: Integer; Unique identifier for each record (auto-generated); do not use quotes when used in an expression.
2. Place: Small Integer; do not use quotes when used in an expression.
3. AdjPlace: Small Integer; do not use quotes when used in an expression. AdjPlace is only used in those races where the race director provides place results by both the gun time and chip time. Usually the Place field will be the gun time while the AdjPlace will be the chip time.
4. FirstName: Character; does require quotes when used in an expression.
5. LastName: Character; does require quotes when used in an expression.
6. Age: Small Integer; do not use quotes when used in an expression.
7. AgeGrp: Character; does require quotes when used in an expression; may not be related to the Grand Prix age groups.
8. Sex: Character; does require quotes when used in an expression.
9. GunTime: Character; does require quotes when used in an expression.
10. SplitTime: Character; does require quotes when used in an expression.
11. ChipTime: Character; does require quotes when used in an expression.
12. Distance: Character; does require quotes when used in an expression.
13. Race: Character; does require quotes when used in an expression.
14. RaceDate: Date; does require quotes when used in an expression and must be in a valid date format (i.e., YYYY-MM-DD).
1. The simplest select statement is "Select * From RaceResults" (default value). This statement will select all 14 fields from the RaceResults table. The "From RaceResults" is always required. In most instances the default value need not be changed.
2. The format to select a subset of the fields is "Select field1, field2, field3…field14 From RaceResults".
Select FirstName, LastName, Age, Guntime, Race, RaceDate From RaceResults
3. You can create fields: "Select CONCAT(FirstName, " " , LastName) as Name, field2, field3…field14 From RaceResults". This select statement will combine the FirstName and LastName into a new field called Name and will return a dataset with the other specified fields.
Select Concat(FirstName, " ", LastName) as Name, Age, Guntime, Race, RaceDate From RaceResults
4. You can count records based on certain conditions specified by the Where statement. The first example below will count the number of records in the RaceResults table; the second will count the number of female racers in the table.
Select Count(*) From RaceResults
Select Count(*) From RaceResults Where Sex = "F"
5. To the right of the user edit box is a Select Examples button. This button will provide you with various samples of a Select statement that you can use (double click the desired sample).
1. The purpose of a Where statement is to filter out unwanted or unneeded records from the database. Currently, there are approximately 240,000+ records contained in the database. It can and has taken over 200-seconds to download all records into a dataset (a slow connection can take much longer). Using a filter will dramatically improve download speeds and will return a dataset that a user can actually use.
2. Most SQL mistakes are made in the Where statement. Common errors are:
a. The MySQL database engine expects each field parameter to be enclosed in either single or double quotes. Many of the newer programs use extended character sets and will use quotes that are not recognized by the MySQL parser. If your Where statement is syntactically correct, then retype your quotes around your parameters. This should only be a problem if you cut and paste an SQL statement from another source or program (i.e., LiveMail or MS Word).
b. Your syntax must be correct—
Where FirstName = "Jane" or "Mary" (WRONG)
Where FirstName = "Jane" or FirstName = "Mary" (RIGHT)
c. Parenthesis must be used to assure the AND/OR keywords are properly executed. The first example below will return all records with "John" as the FirstName and all "Mary Smith" records. The second and third examples will return all records for "John Smith" or "Mary Smith".
Where FirstName = "John" or FirstName "Mary" and LastName = "Smith" (WRONG)
Where (FirstName = "John" or FirstName "Mary") and LastName = "Smith" (RIGHT)
Where (FirstName = "John" and LastName = "Smith") or (FirstName "Mary" and LastName = "Smith") (RIGHT)
3. A Where statement is not required. An empty Where statement will return all records in the RaceResults table.
4. To the right of the user edit box is a Where Examples button. This button will provide you with various samples of a Where statement that you can use (double click the desired sample).
1. Sorting your dataset is done by the Order By statement followed by a list of fields (first field is the primary sorting field, second field is the secondary sorting field, etc.). Examples of an Order By statement are:
Order By RaceDate, Race, Distance, Place, Guntime
Order By Race, Distance, Place
Order By LastName, Distance, RaceDate
Order By LastName, FirstName, Distance, RaceDate
Order By Distance, RaceDate
Order By RaceDate, Race
Order By Race, Place, RaceDate
2. An Order By statement is not required. An empty Order By statement will return an unsorted dataset.
3. You can also quickly sort your data by clicking on the underlined column heading of the record grid.
4. To the right of the user edit box is an Order By Examples button. This button will provide you with various samples of an Order By statement that you can use (double click the desired sample).
1. The print function will only print the records on the current page. Note: the record grid's page size can be changed to 400 records per page.
2. The current HTML5 standard has deprecated the page size style (one of the styles was landscape). Only some browsers still recognize that some print jobs are requesting landscape versus portrait. As a result, the user will be required to manually set their printer to landscape mode (if desired).
3. For Google Chrome, on the left of the print preview window there is a layout option where you may select either Portrait or Landscape.
4. For Internet Explorer, in the Print dialog box you will select Preferences and then select Print Shortcuts. The Orientation selection will allow you to choose Landscape.
1. The Save Data function will convert your records to a CSV type format.
2. It is up to the user to copy and paste this data to an application on a client computer.
3. When pasting into an Excel spreadsheet, it is best to use an import wizard. Most specifically, it is important for Excel to understand that the three time fields are text and not a time and date field (Excel's default is to turn this data into a 24-hour clock time -- works fine if all of your races are under 24-minutes -- anything over 23:59 is considered a new day).