Structured Query Language (SQL) - brianhigh/data-workshop GitHub Wiki

  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%" 
   AND moz_places.id = moz_historyvisits.place_id
ORDER BY TimeStamp DESC 
LIMIT 3;

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
   ON moz_places.id = moz_historyvisits.place_id 
WHERE URL LIKE "%youtube.com%" 
ORDER BY TimeStamp DESC 
LIMIT 3;

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: