Simple SQL Statements

SQL is a standard database access language used to communicate with relational databases. Relational databases contain collections of tables that consist of rows and columns. With SQL, you can run complex queries on the data in a database, insert and modify the data, and control it in many different ways. SQL is a powerful language, so SQL expressions can become quite complex. However, unlike some languages, simple operations in SQL still result in simple expressions.

With ODBC, you use a standardized SQL syntax within C or C++ to construct statements that manipulate the data in the database according to whatever criteria you specify. There are many types of SQL statements. You can consult any SQL book or the online help for a list of them, but the most common statements are SELECT, INSERT, UPDATE, and DELETE. They retrieve records from a database (optionally doing even complex calculations), insert new ones, update existing records, or delete records from the database, respectively. I will not explain in detail the syntax of these statements, but a simple example of each command gives a clear idea of how to use them (lines broken to fit on the page):

SELECT [Column 1], [Column 2]
   FROM [Table Name]
   WHERE [Column 3]=?
   ORDER BY [Column 1]

INSERT INTO [Table Name]
   ([Column 1], [Column 2],
      [Column 3]) VALUES (?,?,?)

UPDATE [Table Name] SET [Column 1]=?,
   [Column 2]=?
   WHERE [Column 3]='Blaise'

DELETE FROM [Table Name]
   WHERE [Column 1]=? AND
      [Column 2]=33

In this example, [Table Name] is the name of the table being worked on; [Column 1], [Column 2], and [Column 3] are the names of the columns (or fields). The square brackets can be omitted if the names within them don't contain blanks. These are very simple statements that work on only one table; more complex statements can join different tables, calculate expressions, group results, etc.

While the above statements are self-explanatory, I need to make two points about them. First, the SELECT statement defines a result set, which means that multiple rows of data (records) can result from the command. Later, you will have to iterate through the data (fetch the records) and extract the requested data from the fields (columns). The extracted columns will be identified by their index number, starting with one. Second, the question marks in the SQL statements identify parameters, which must be specified before executing the SQL statement and can change each time the statement is run. The parameters are also identified by their position in the SQL statement, starting with one. The value of the parameters can also be written directly into the SQL command, for example:

DELETE FROM [Table Name]
  WHERE [Column 1]=1 AND
      [Column 2]='Jurgen'

but the advantage of the question mark is that the same SQL statements can be used with different parameters. You can compile the command once and execute it multiple times with different parameters, so you don't have to build your SQL command dynamically.