SQLite Cheat Sheet

After years of painful fights with spreadsheets I have started to use SQL to organise coursework date, e.g. for grading. Main advantage is the separation of code and data, as well as more readable column identifiers than “B$6”.

I am using sqlite3 and sqlitebrowser. The latter, as a convenient, graphical interface to update single entries or as a simple, dynamic visualisation.

Recipes

Common tasks that can be easy, if you know how to do them.

Row-Sum

The Operation sum(column) provides the sum of all values in a column. If you want to calculate a sum of multiple columns in a single row you have to do so by naming each column and explicitly write the operations as in (col_1 + col_2 + …col_n).

To update this automatically you can update this into a table within a trigger:

CREATE TRIGGER IF NOT EXISTS auswertung
  after UPDATE
  on klausur
begin
  update klausur
  set summe = (aufg_1 + aufg_2 + aufg_3 + aufg_4 + aufg_5 + aufg_6 + aufg_7);
end

(Triggers seem to not be executed by SQLite Browser, but UPDATE with sqlite3 works fine.)

Inserting missing rows

Table a is containing a complete list of users, table b is missing some users. The following inserts a new line if a user is missing:

insert into b (uid)
select uid from a 
where uid not in (select uid from b);

Deleting Duplicate Rows

If you carefully have defined a primary key in your tables, then you should not have the problem of duplicate rows. But if you happen to run into a situation where you have forgotten to attribute a primary key in a table (or have none) and some opperation turned your table into a mess of duplicate entries, the following recipe might help you to get rid of duplicate lines.

You must be able to identify duplicate rows by a “group by” grouping. But this should not be a problem, as “duplicate rows” implies that they contain the same information in at least one column.

delete from table where rowid not in (select MIN(rowid) from table group by column);

Explanation:

Every entry has assigned a unique rowid. By selecting all rows except the row with minimum rowid you are able to delete all but one of the group of equal rows.

Replace column by whatever column describes identical rows.