Selecting Data with Raw SQL
The raw SQL command SELECT selects and returns data from a database. An alternative is to use the
Mathematica command
SQLSelect, described in
"Selecting Data".
If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the
DatabaseExamples` package, as described in
"Using the Example Databases".
This loads
DatabaseLink and connects to the
publisher database.
This retrieves data within the table,
ROYSCHED, for which the data in the
ROYALTY column is between 0.11 and 0.12.
| Out[3]= |  |
This carries out the same SELECT statement but uses a prepared statement. The arguments to the statement are given as the third element of the
SQLExecute command. The first argument is placed in the location of the `1` and the second in the location of the `2`.
| Out[4]= |  |
Column and table names must be wrapped in
SQLColumn and
SQLTable, respectively. This will ensure they are not quoted as strings. The following selects elements of the
ROYALTY column in the
ROYSCHED table for which the
TITLE_ID column value is
BS1011.
| Out[5]= |  |
If you want to give a sequence of arguments to a prepared statement, you can use
SQLArgument. This is described in
Argument Sequences in SQL-Style Queries.
| Out[6]= |  |
Many databases offer functions that apply to the results of a select operation. Typical examples are COUNT, MIN, MAX, SUM, and AVG. The documentation for your database will describe the details of the functions that are available. The following examples demonstrate some of these functions.
| Out[7]= |  |
| Out[8]= |  |
Many databases allow you to apply mathematical functions such as +, -, *, or / to the results.
| Out[9]= |  |
| Out[10]= |  |
| Out[11]= |  |
You can also select only distinct values.
| Out[12]= |  |
You can also group values.
Out[13]//TableForm= |
| |  |
Many databases also support retrieving a range of results.
| Out[14]= |  |
| Out[15]= |  |
More complex SELECT statements using INNER JOIN and OUTER JOIN can be used in a FROM clause to combine records from two tables.
| Out[16]= |  |
Out[17]//TableForm= |
| |  |
This closes the connection.
