# Passing commands to sqlite prompt/ Import CSV.
    echo -e '.separator "@"\n.import csv_file.csv tablename' | sqlite example.db
    # Command should starts with a dot & at the beginning of line.
 
# Convert all values in a column into a number
    UPDATE MyTable SET MyColumn = CAST(MyColumn AS INTEGER)
 
# Format table output.
    sqlite3 -cmd ".width 13 15" -column -header example.db "SELECT * tablename;"
 
# Describe TABLENAME.
    .schema TABLENAME
 
# Show all tables.
    .tables
    SELECT name FROM sqlite_master WHERE type='table'
 
# Output sql query to file.
    .once table_name.sql
 
# Exit sqlite3 console.
    .quit
 
# Table and index creation.
    CREATE TABLE Shelf (  uid            INTEGER PRIMARY KEY AUTO INCREMENT,
                          canonical_path TEXT NOT NULL,
                          filename       TEXT NOT NULL,
                          last_modified  INTEGER NOT NULL,
                          hash           TEXT,
                          comment        TEXT);
    CREATE INDEX shelf_hash ON Shelf (hash);
 
# Data types.
    NULL. The value is a NULL value.
    INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
    REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
    TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
    BLOB. The value is a blob of data, stored exactly as it was input.
 
# DATE
    # Compute the current date.
        SELECT date('now');
    # Compute the last day of the current month.
        SELECT date('now','start of month','+1 month','-1 day');
    # Compute the date and time given a unix timestamp 1092941466.
        SELECT datetime(1092941466, 'unixepoch');
    # Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.
        SELECT datetime(1092941466, 'unixepoch', 'localtime');
    # Compute the current unix timestamp.
        SELECT strftime('%s','now');
    # Compute the number of days since the signing of the US Declaration of Independence.
        SELECT julianday('now') - julianday('1776-07-04');
    # Compute the number of seconds since a particular moment in 2004:
        SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
    # Compute the date of the first Tuesday in October for the current year.
        SELECT date('now','start of year','+9 months','weekday 2');
    # Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):
        SELECT (julianday('now') - 2440587.5)*86400.0;
 
    strftime() substitutions:
    %d      day of month: 00
    %f      fractional seconds: SS.SSS
    %H      hour: 00-24
    %j      day of year: 001-366
    %J      Julian day number
    %m      month: 01-12
    %M      minute: 00-59
    %s      seconds since 1970-01-01
    %S      seconds: 00-59
    %w      day of week 0-6 with Sunday==0
    %W      week of year: 00-53
    %Y      year: 0000-9999
    %%      %
 
    date(timestring, modifiers...)  This returns the date in this format: YYYY-MM-DD
    time(timestring, modifiers...)  This returns the time as HH:MM:SS
    datetime(timestring, modifiers...)  This returns YYYY-MM-DD HH:MM:SS
    julianday(timestring, modifiers...) This returns the number of days since noon in Greenwich on November 24, 4714 B.C.
    strftime(modifiers, timestring) This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below.
 
# Compact database file size.
    VACUUM;
    PRAGMA auto_vacuum = FULL;
    PRAGMA auto_vacuum = INCREMENTAL;
    PRAGMA auto_vacuum = NONE;
 
    # Ensure databse file size is always compacted.
    echo "PRAGMA auto_vacuum = FULL;" | sqlite3 "${db_file}"
 
    # Compact now.
    echo "VACUUM;" | sqlite3 "${db_file}"
 
# Index
    CREATE INDEX StudentNameIndex ON Students(colname1, colname2,...);
    PRAGMA index_list(Students);
 
    CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
        WHERE Quantity > 10000;
 
    CREATE UNIQUE INDEX indexName ....;
    DROP INDEX indexName;
 
# Primary key
    CREATE TABLE IF NOT EXISTS tblWeekDays(...)
 
    CREATE TABLE table_name(
       column_1 NOT NULL INTEGER PRIMARY KEY,
       ...
    );
 
    CREATE TABLE test (a TEXT, b TEXT
            , PRIMARY KEY(a,b)
            );
 
    DROP TABLE IF EXISTS tblWeekDays
 
# String manipulation.
    substr(str, position, offset): position starts from 1.
    || operator is "concatenate"
    select substr('20180824',1,4)||'-'||substr('20180824',5,2)||'-'||substr('20180824',7,2);
 
# Add column
    ALTER TABLE <table_name>
      ADD <new_column_name> <column_definition>;
 
# Drop column
    You can not use the ALTER TABLE statement to drop a column in a table. 
    Instead you will need to rename the table, create a new table, and copy the data into the new table.
 
# Rename column in table
    You can not use the ALTER TABLE statement to rename a column in SQLite. 
    Instead you will need to rename the table, create a new table, and copy the data into the new table.
 
# Rename table
    ALTER TABLE <table_name> RENAME TO <new_table_name>;
 
# Error: database disk image is malformed
    PRAGMA integrity_check;
    # This lost case. You need to start over but try this:
    sqlite3 mydata.db ".dump" | sqlite3 new.db
 
 
# Hidden rowid field.
    select rowid _id from new_lines limit 10;
 
# GROUP BY functions
    avg(X)
        The avg() function returns the average value of all non-NULL X within a group. String and BLOB values that do not look like numbers are interpreted as 0. The result of avg() is always a floating point value as long as at there is at least one non-NULL input even if all inputs are integers. The result of avg() is NULL if and only if there are no non-NULL inputs.
 
    count(X)
    count(*)
        The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group.
 
    group_concat(X)
    group_concat(X,Y)
        The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (",") is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary.
 
    max(X)
        The max() aggregate function returns the maximum value of all values in the group. The maximum value is the value that would be returned last in an ORDER BY on the same column. Aggregate max() returns NULL if and only if there are no non-NULL values in the group.
 
    min(X)
        The min() aggregate function returns the minimum non-NULL value of all values in the group. The minimum value is the first non-NULL value that would appear in an ORDER BY of the column. Aggregate min() returns NULL if and only if there are no non-NULL values in the group.
 
    sum(X)
    total(X)
        The sum() and total() aggregate functions return sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided as a convenient way to work around this design problem in the SQL language.
        The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers. If any input to sum() is neither an integer or a NULL then sum() returns a floating point value which might be an approximation to the true sum.
        Sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. Total() never throws an integer overflow.
 
# Sqlite group_concat ordering
    SELECT ID, GROUP_CONCAT(Val)
    FROM (
       SELECT ID, Val
       FROM YourTable
       ORDER BY ID, Val
       )
    GROUP BY ID;
 
# Match multiple fields from different table
    DELETE FROM Table1 
    WHERE (address, city, state, zip)
           NOT IN
          (SELECT address, city, state, zip  FROM Table2)
      AND city IN
          (SELECT city FROM Table2) ;
 
# IF THEN ELSE
    SELECT *,
           CASE WHEN Password IS NOT NULL
           THEN 'Yes'
           ELSE 'No'
           END AS PasswordPresent
    FROM myTable
 
    CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
    CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
 
# Rounding, set decimal precision.
    round(1929.236, 2)
    -----------------
    1929.24

DROP TABLE IF EXISTS Persons;
CREATE TABLE Persons(created_on TEXT UNIQUE);

INSERT INTO Persons(created_on) VALUES('2019-09-04');

DELETE FROM Persons WHERE DATE <'2019-09-04'