-------------------------------------------------------------------------------
sqlite3 -- getting started

sqlite3 places.sqlite
  .help
  .databases
  .dump

-------------------------------------------------------------------------------
SQL Commands

From... http://www.w3schools.com/sql/sql_syntax.asp

The SQL commands are not case sensitive, though it is recommended
for the keywords to be written uppercase.


SELECT             extracts things from tables

  SELECT name FROM table
    Lists all 'name' entries in table, and JUST the name
  SELECT * FROM table
    Lists everything in table

  SELECT DISTINCT * FROM table
    Returns only Distinct (different) values

To find something specific you need to add 'WHERE'

  SELECT * FROM table WHERE Id=967
     lists the complete entry, where the value of Id = 967

  SELECT * FROM table WHERE Country='Mexico'
  SELECT * FROM table WHERE Country='Mexico'

      Comparision Operators
         =        Equal
         <>       Not equal.   (do not use '!=')
         >        Greater than
         <        Less than
         >=       Greater than or equal
         <=       Less than or equal
         BETWEEN  Between an inclusive range
         IN       To specify multiple possible values for a column
         LIKE     Search for a pattern

         AND
         OR
         NOTE

     WHERE Id BETWEEN 100 AND 200
     WHERE Name BETWEEN 'C' AND 'M'
        Strings are just big numbers! The table schema defines type

     WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#
        '#' defines a date rather than a string ????

       WARNING: BETWEEN may be inclusive or exclusive depending on
       the database you are searching.  Check edge conditions.

     WHERE Country='Germany' AND City='Berlin'
     WHERE Country='Germany' AND (City='Berlin' OR City='München')
     WHERE City IN ('Paris','London')

     WHERE (Price BETWEEN 10 AND 20) AND NOT Id IN (1,2,3);

     WHERE Name LIKE 'A%'
       finds all names starting with A
            %       is the SQL string wildcard pattern
            _       substitutes a single character
          [...]   character group or range
          [^...]  not in character group



  SELECT ...  ORDER BY column_name,column_name ASC|DESC;

      Order defined the order in which to list the entries,
      in Asccening or Decending sequence.

  SELECT ... LIMIT number            - mysql
  SELECT TOP number [PERCENT] ....   - SQL Server / MS Access
  SELECT ... WHERE ROWNUM <= number  - Oracle Syntax

      Limit the amount of output generated

  SELECT output AS  alias_name,...  FROM ...

    SELECT FirstName+' '+LastName   AS 'Users Name',
           Address+', '+City+', '+PostCode+', '+Country   AS 'Poastal Address'
       FROM People

       Names a specific column of output.

    SELECT o.OrderID, o.OrderDate, c.CustomerName
    FROM Customers AS c, Orders AS o
    WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

       Proviced a short name for a database to mak ethe command more compact

  SELECT ...  FROM table1 INNER JOIN table2 ON  table1.field=table2.field

       merge two tables based on a matching field
       Commonly you would use a INNER JOIN

         INNER JOIN: Return rows with at least one match in BOTH tables
         LEFT JOIN:    from the left table, and matched from the right
         RIGHT JOIN:   from the right table, and matched from the left
         FULL JOIN:    when there is a match in ONE tables

     INNER is default, and returns 'common to both'
     LEFT is all of the from table, and only the mathing ones from right
     RIGHT  ditto but reversed
     FULL  returns all values even those that did not get 'joined'

     missing values are just left blank (or null).

   SELECT ...  UNION [ALL]  SELECT ...

     Just merges the results from the two seperate Selects as if it
     was one Selection.  But will remove all duplicates, unless ALL
     is used.


Arithmetic Operators:
   +  -  *  /  %

Functions:
See http://www.tutorialspoint.com/sql/sql-useful-functions.htm

   NOW()                    the current time
   FORMAT(value,format)     set date format?


   COUNT(values)
   MAX(values)
   MIN(values)
   AVG(values)
   SUM(values)


   CONCAT()      concatinate strings  - not in sqlite3


-------------------------------------------------------------------------------