Skip to content

Structured Query Language (SQL)

Jump to bottom
brianhigh edited this page Mar 23, 2014 · 2 revisions
  1. CRUD: Create, Read, Update and Delete
  2. Creating tables with SQL (CREATE TABLE)
  3. SELECT, WHERE, and GROUP BY
  4. Various kinds of JOINs
  5. HoE: Try some SQL on your tables
  6. Discussion
  7. Action Items (readings, videos and tasks)
  8. See also:

Examples

Example 1: INNER JOIN with Firefox History

Here is one way to query multiple tables using the the WHERE clause of a SELECT statement:

SELECT SUBSTR(moz_places.url,0,50) AS`URL`, datetime(moz_historyvisits.visit_date/1000000,"unixepoch") AS`TimeStamp`FROM moz_places, moz_historyvisits WHERE URL LIKE"%youtube.com%"ANDmoz_places.id=moz_historyvisits.place_idORDER BYTimeStampDESCLIMIT3;

This will find the date and time of the most recent visits to youtube.com.

The same result can be obtained using the INNER JOIN syntax:

SELECT SUBSTR(moz_places.url,0,50) AS`URL`, datetime(moz_historyvisits.visit_date/1000000,"unixepoch") AS`TimeStamp`FROM moz_places INNER JOIN moz_historyvisits
ONmoz_places.id=moz_historyvisits.place_idWHERE URL LIKE"%youtube.com%"ORDER BYTimeStampDESCLIMIT3;

In both cases, you will see output similar to this in sqlite3 (with .header on, .mode column and .width 50):

URL TimeStamp -------------------------------------------------- -------------------
https://www.youtube.com/watch?v=z2kbsG8zsLM 2014-03-23 17:02:38
https://www.youtube.com/watch?v=zoXLU86ohmw 2014-03-23 17:02:33
https://www.youtube.com/watch?v=KA4rRnihLII 2014-03-23 17:02:27

See also:

Clone this wiki locally