Select
This is a very flexible command, used to select data from a table, or from several tables. This command has options to get totals from several records into a single record, to sort the results, and several more. It is frequently used as a first step to obtain a report.
The result goes to a "cursor", which, in this case, means a temporary table. As soon as the cursos is closed, it is deleted automatically.
Both the act of selecting data, and the result, are called query.
Data from a single table
This is the simplest case.
SELECT <field_list> FROM <table> WHERE <condition>
An asterisk can be used for the field list; this will select all fields.
SELECT Client_Code, Names, CurrentDebt; FROM Client; WHERE CurrentDebt >= 1000; ORDER BY CurrentDebt DESCENDING; INTO CURSOR TempResult
Data from two or more tables
SELECT Invoice.InvoiceNumber, Invoice.Date, Invoice.InvoiceTotal,Invoice.ClientCode, Client.Names; FROM Invoice LEFT JOIN Client ON Invoice.ClientCode = Client.ClientCode; INTO CURSOR TempResult
The LEFT clause specifies that you want to include all data from the table on the left side, in this case, all invoices, even those that have no related data in the other table (invoices without a client).
To obtain results based on more than two tables, you can specify up to ten tables in a single SELECT command; however, in actual practice, I found that Visual FoxPro is much faster if you separate this into several SELECT commands.
Get totals of several records
To get totals for several records (join several records into a single one), you use the GROUP BY clause.
SELECT ClientCode, sum(InvoiceTotal) as ClientTotal; FROM Invoice; GROUP BY ClientCode; INTO CURSOR TempResult
Subqueries
To include a query (a SELECT command) within another one is a powerful tool that makes it possible, for instance, to see what records in a table exist, or don't exist, in another table.
SELECT * FROM Invoice; WHERE ClientCode NOT IN (SELECT ClientCode FROM Client)
UNION
The UNION clause makes it possible to join the result of two queries vertically. The main difficulty, in practice, is that the result of both individual queries has to have exactly the same structure. The name of the fields doesn't matter, but each field of the query has to have the same type and the same size as the corresponding field in the other query.
DELETE
Visual FoxPro supports the DELETE command from the SQL language, used frequently to delete records that fulfil certain conditions.
Warning: If you don't specify a condition, all records from the table will be deleted.
DELETE FROM Invoice; WHERE InvoiceDate < {^1995-01-01}
Insert
The INSERT command of the SQL language is used to add a record. It is said to be faster that the alternative that doesn't use SQL syntax (that is, use the APPEND BLANK command, followed by REPLACE).
INSERT INTO Client (ClientCode, Names) VALUES (1, "John Doe")
UPDATE
The UPDATE command of the SQL language is used to modify existing records.
Warning: If you don't specify a condition, all records in the table will be modified.
Example (increase the price of all articles by 10%):
UPDATE Product SET CurrentPrice = CurrentPrice * 1.1
Data Definition
The aforementioned SQL commands are used to modify data in existing tables, therefore, they are part of the "data manipulation language". There is also a "data definition language" in standard SQL.
The CREATE TABLE command is used to create a new table.
CREATE TABLE Client (NumClient I, Names C(30), Current Debt Y)
The ALTER TABLE command is used to modify the definition of an existing table, for instance, add or delete columns (fields).
ALTER TABLE Client ALTER COLUMN Names C(35)
The DROP TABLE command is used to erase a table.
Views
A view is an updatable query: you select data from a table to a temporary table, do some changes, and then send the results back to the original table.
Views are defined with the view definer, in the database. You can also define views through commands, but for most requirements, the interactive view designer is satisfactory.
Once you define a view, you open it like any table, with the USE command. And just like any table, changes are updated with TableUpdate() and cancelled with TableRevert().
You can define parameters in a view, to get a subset of the data. Paramters are usually defined with a question mark, "?", in front of the variable name.
Views can solve certain speed problems: a grid is a component that you place on a form, that lets you see several records at a time. If you combine a grid with a filter, to see a small subset of a large table (for instance, the details for a a single invoice), things will get quite slow. Using a parameterized view as a data source for the grid solves these speed problems.
Views can be local (to access DBF tables), or remote (to access a database server).
