Skip to main content

SQLite

  • .schema <tablename> - describe table schema
  • .tables - enumerate existing tables and views
  • .save <filename> - save in-memory database into specified file
  • To import a file into a database, specify mode of source file first, then import. .mode csv or .mode tabs then .import <filename> <tablename>
  • .header on - to toggle display of column names
  • .quit - to exit sqlite
  • Create index to make queries faster, do: CREATE INDEX <indexname> ON <tablename>(<columnname>);

Using Views​

  • Create view:
 CREATE VIEW <view_name>
 AS
    <select_statement>
  • To call: SELECT * FROM <view_name>
  • To list all available views in sqlite: SELECT name FROM sqlite_master WHERE type='view';

Common Table Expression​

  • basically a subquery with an assigned name and defined prior to the query in which it is referenced.
  • Examples:
WITH one as (SELECT 1)
SELECT * FROM one;
WITH aCTE AS (SELECT 'a'), 
     bCTE AS (SELECT 'b')
SELECT * FROM aCTE, bCTE;

Using CTE as variable​

WITH temptabl AS (select '2021-01-01' as mydate)
SELECT * from price_view, temptabl WHERE price_view.CurrentCostDate >= temptabl.mydate;