SQLite: SQL SELECT Statements

You use the SELECT statement to query data from one or more tables. In this Delphi tutorial, we'll deal with basic SELECT statements for a single table.

In its simplest form, the syntax of a SELECT statement is:

SELECT columnlist   FROM table

columnlist can be:

• a star * for all of the columns
• the name of one column
• a comma-separated list of column names

table: the name of a table that is contained in the database

Example: SELECT * FROM countries

A more complete syntax of a SELECT statement is as follows:

SELECT DISTINCT columnlist  FROM table WHERE rowfilter ORDER BY column LIMIT count 

The meaning of the different parts:

• ORDER BY sorts the result based on one or more columns, ascending and descending (specify the keyword ASC or DESC; if you don't specify ASC or DESC, SQLite uses ascending order by default).
  Note: you even can sort on a column that does not appear in the column list of the SELECT clause.
  Example:
     SELECT * FROM countries ORDER BY name ASC
• DISTINCT is an optional clause, that removes duplicate rows from the result.
  Specify a column or a list of columns after DISTINCT. If you specify one column, that column is used to evaluate the duplicates. If you specify multiple columns, the combination of those columns is used to evaluate the duplicates.
  Example:
     SELECT DISTINCT currency ORDER BY currency
• WHERE filters the rows that are returned by evaluating a condition that you specify.
  Examples:
     SELECT * FROM countries WHERE population < 10000
     SELECT * FROM countries WHERE curcode = "USD"
     SELECT * FROM countries WHERE name LIKE "Z%"
     SELECT * FROM countries WHERE name LIKE "%land%"
  
• LIMIT is used to limit the number of rows returned, to a given value.
  Example:
     SELECT * FROM countries ORDER BY population DESC LIMIT 5

See the WHERE clause for more advanced features of SELECT.

Preparations

1. If you haven't done so already, create a new folder \DelphiLand.
2. If you haven't done so already, create directory SQLiteGeo2 "under" \DelphiLand.
3. Next, download sqlitegeo2.zip and unzip it to this directory.

Testing SQL statements

1. Start Delphi and open sqlitegeo2.dpr.
2. In the the menu, select Project / Options...
3. In the dialog that appears, select Delphi Compiler / Output directory
   Clear this option and click OK. 
   That way, you avoid the need to enter a hardwired full path when setting FDConnection.Params.Database (see later).
4. Open Delphi's File menu and click Save All.
5. Let's have a look at the extra source code:
   

   procedure TForm1.btnTestSQLClick(Sender: TObject);
   begin
     qCountries.Close; // query must be inactive before changing
     qCountries.SQL.Text := edSQL.Text;
     qCountries.Open;
   end;

6. Run your project.
   In the edit box, enter a select statement (chosen from the examples above, or make up your own) and click the button. Enjoy!