[R] SQL Primer for R

[Thomas Lumley]

On Mon, 25 Aug 2008, ivo welch wrote:

> Dear R wizards:
>
> I decided to take the advice in the R data import/export manual and
> want to learn how to work with SQL for large data sets.  I am trying
> SQLite with the DBI and RSQLite database interfaces.  Speed is nice.
> Alas, I am struggling to find a tutorial that is geared for the kind
> of standard operations that I would want in R.  Simple things:
>
> *  how to determine the number of rows in a table.  (Of course, I
> could select a row of data and then use this.)

To get number of rows
   SELECT COUNT(*) FROM table_name
For number of columns the quickest reasonably portable way I know is
   SELECT * FROM table_name LIMIT 1
and then count the columns of the result.

> *  how to insert a new column into my existing SQL table---say, the
> rank of another variable---and save it back.  Am I supposed to create
> a new data frame, then save it as a new table, then delete the old SQL
> table?

   ALTER TABLE table_name ADD column_name
(you may have to/want to specify a data type as well)

If you are adding a bunch of columns you might also want to put the new 
columns in a separate table and JOIN the tables, especially if the need 
for these extra columns is sporadic or temporary.

> *  how to save a revised version of my table in a different sort order
> (with or without deleting the original table).  <-- I guess this is
> not appropriate, as I should think of SQL tables as unordered.

As you note, SQL tables are conceptually unordered. You can order results 
of a query as you read them:
   SELECT foo, bar FROM table_name ORDER BY baz


 	-thomas

Thomas Lumley			Assoc. Professor, Biostatistics
tlumley at u.washington.edu	University of Washington, Seattle