Selecting Data with Raw SQL
The raw SQL command SELECT selects and returns data from a database. An alternative is to use the Wolfram Language 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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".
This loads DatabaseLink and connects to the publisher database.
Needs["DatabaseLink`"];
conn = OpenSQLConnection["publisher"];This retrieves data within the table, ROYSCHED, for which the data in the ROYALTY column is between 0.11 and 0.12.
SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= .11 AND ROYALTY <= .12"]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`.
SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= `1` AND ROYALTY <= `2`", {0.11, 0.12}]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.
SQLExecute[conn, "SELECT `1` FROM ROYSCHED WHERE TITLE_ID = `2`", {SQLColumn["ROYALTY"], "BS1011"}]If you want to give a sequence of arguments to a prepared statement, you can use SQLArgument. This is described in "SQL Execute: Argument Sequences in SQL-Style Queries".
SQLExecute[conn, "SELECT `1` FROM ROYSCHED WHERE TITLE_ID = `2`", {SQLArgument[SQLColumn["LORANGE"], SQLColumn["HIRANGE"], SQLColumn["ROYALTY"]], "BS1011"}]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.
SQLExecute[conn, "SELECT COUNT(ROYALTY) FROM ROYSCHED"]SQLExecute[conn, "SELECT MIN(ROYALTY) FROM ROYSCHED"]Many databases allow you to apply mathematical functions such as +, -, *, or / to the results.
SQLExecute[conn, "SELECT ROYALTY * 2 FROM ROYSCHED"]SQLExecute[conn, "SELECT ROYALTY / 10 FROM ROYSCHED"]SQLExecute[conn, "SELECT - ROYALTY FROM ROYSCHED"]You can also select only distinct values.
SQLExecute[conn, "SELECT DISTINCT ROYALTY FROM ROYSCHED"]SQLExecute[conn, "SELECT TITLE_ID,
MIN(ROYALTY) FROM ROYSCHED GROUP BY TITLE_ID", "ShowColumnHeadings" -> True]//TableFormMany databases also support retrieving a range of results.
SQLExecute[conn, "SELECT TOP 5 * FROM ROYSCHED"]SQLExecute[conn, "SELECT LIMIT 5 10 * FROM ROYSCHED"]More complex SELECT statements using INNER JOIN and OUTER JOIN can be used in a FROM clause to combine records from two tables.
SQLExecute[conn, "SELECT DISTINCT TITLES.TITLE FROM TITLES INNER JOIN ROYSCHED ON TITLES.TITLE_ID=ROYSCHED.TITLE_ID WHERE TITLES.PUB_ID='0877' AND ROYSCHED.ROYALTY > .1"]SQLExecute[conn, "SELECT T.TITLE, T.TITLE_ID, MIN(R.ROYALTY) ROYALTY FROM ROYSCHED R, TITLES T LEFT OUTER JOIN ROYSCHED ON T.TITLE_ID = R.TITLE_ID GROUP BY T.TITLE, T.TITLE_ID ORDER BY ROYALTY, T.TITLE DESC", "ShowColumnHeadings" -> True]//TableFormCloseSQLConnection[conn];