B.1. SQL Primer

SQL has a large number of data types, but JRaceman only uses four types of data: string, integer, floating, and boolean.

String values are quoted using single-quote character ('). Integers are entered using decimal digits (0 to 9) with an optional leading negative sign (-). Floating numbers are entered as integers optionally immediately following by a decimal point (.) and decimal digits (0 to 9). Boolean values are entered as the word true or the word false.

There are four basic SQL commands: SELECT, INSERT, UPDATE, and DELETE. The SELECT command retrieves data from the database; the other three commands modify the database.

In the descriptions below, the use of square brackets ([]) indicates that the contents of the square bracket are options. The use of an asterisk (*) after the square brackets indicates that the contents of the square brackets can be repeated zero or more times.

The format of the basic SELECT command is

        SELECT 
        columns
        FROM 
        table
        [ WHERE 
        condition
        ]
        [ ORDER BY 
        sort-columns
        ]
      

For example, to see minAge and maxAge values in all rows of the Levels table, you would enter the SQL command

        SELECT minAge, maxAge FROM Levels
      

To select all columns from all rows of the Competitions table, and to order by the name column, you would enter the SQL command

        SELECT * from Competitions ORDER BY name
      

The format of the basic INSERT command is

        INSERT INTO 
        table
        (
        column-names
        )
        VALUES (
        values
        )
      

For example, to insert a new Level, you could enter the SQL command

        INSERT INTO Levels(id,name,minAge,maxAge)
        VALUES('L9','Midget',10,12)
      

The format of the basic UPDATE command is

        UPDATE 
        table
        SET 
        column-name
        =
        value
        [ , 
        column-name
        =
        value
        ]*
        [ WHERE 
        condition
        ]
      

For example, to change the teamId of all people on the team with id T1 to T2, you would enter the SQL command

        UPDATE People SET teamId='T2' WHERE teamId='T1'
      

Note

If you omit the WHERE clause, all rows of the table will be updated. Be careful always to include a WHERE clause unless you really want to update every row of the table.

The format of the basic DELETE command is

        DELETE FROM 
        table
        [WHERE 
        condition
        ]
      

For example, to delete all Lane records for the race with id R12, you would enter the command

        DELETE FROM Lanes where raceId='R12'
      

Note

If you omit the WHERE clause, all rows of the table will be deleted. Be careful always to include a WHERE clause unless you really want to delete every row of the table.

There are many more powerful things you can do with SQL, such as using expressions rather than column names in a SELECT, or using a SELECT statement to get a list of values which are used with the IN operator in the WHERE clause of a containing statement. For more information on these more advanced kinds of SQL statements, consult an SQL reference.