SQL Quick Reference


CREATE TABLE table_name(fieldname_1 data_type, fieldname_2 data_type, fieldname_3 data_type);

Create Tables with Foreign Keys

One to Many

CREATE TABLE child_table_name (field_1 INTEGER PRIMARY KEY, field_2 TEXT, foreign_key_field INTEGER , FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name(parent_key_field));

One to One relationship

CREATE TABLE child_table_name (field_1 INTEGER PRIMARY KEY, field_2 TEXT, foreign_key_field INTEGER UNIQUE , FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name (parent_key_field));


FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name(parent_key_field) ON DELETE CASCADE ON UPDATE CASCADE );

CREATE TABLE from SQL Query Results

CREATE TABLE new_table_name AS SELECT select statement

Delete Table

DROP TABLE table_name ;
DROP TABLE main.table_name ;
DROP TABLE attached_database_name.table_name ;

Add Records to a Table

INSERT INTO table_name (field_1, field_2, field_3) VALUES ("value a", "value b",0.00 )


UPDATE table_name SET column_name= value WHERE criteria;

SELECT Statements

Syntax or Purpose
--------- --------- -------------- ------------ ------------ -------------
DISTINCT Exclude duplicate records for fields selected.
CASE WHEN expression Conditional expression
THEN expression
ELSE expression END
FROM table_name Multiple table names are separated by commas.
WHERE Row level filtering
expression AND expression
expression OR expression
IN Comma delimited list enclosed in parenthesis
NOT IN Comma delimited list enclosed in parenthesis
BETWEEN Select records within the specified numeric range
NOT BETWEEN Select records outside of the specified numeric range
LIKE String with wildcard (%) enclosed in parenthesis
NOT LIKE String with wildcard (%) enclosed in parenthesis
ORDER BY Sorting of the output using a comma delimited list of column names
LIMIT Limit the number of rows returned


Symbol Meaning Example
* Multiply field_name * 0.05
+ Add field_name + 1
- Subtract field_name - 0.5
/ Divide field_1 / field_2
= equal to field_1 = field_2
== equal to field_1 = 1
< less than field_b < 100
<= less than or equal to field_a <= 99
> Greater than field_name > 2
>= Greater than or equal to field_name >= 15.142
<> Not equal to field_name <> 0
!= Not equal to field_a != field_b

SELECT Statement Examples

SELECT * FROM table_name;

SELECT delivery_addr,invoice_number FROM customer_info,invoice_picked WHERE customer_info.customer_id = invoice_picked.customer_id;

SELECT acct_number , customer_name FROM sales_2007 WHERE purchase_total BETWEEN 1200 AND 3300;
SELECT acct_number , customer_name FROM sales_2007 WHERE city IN ('Chicago','New York','Cleveland');

SELECT acct_number , customer_name FROM sales_2007 WHERE purchase_total NOT BETWEEN 1700 AND 2200;

SELECT DISTINCT customer_name , acct_number FROM orders WHERE invoice_no > 20000 ;

SELECT employee_id, hours, CASE WHEN (hours>40 THEN hours-40 ELSE 0 END AS overtime FROM tbl_Hours;

String Manipulation

Function Explanation Syntax
------------ ---------------------------------------------------------------------- -----------------------
LENGTH( ) returns the number of characters in the string length( fieldname or expression)
LTRIM( ) Trims listed characters from the beginning of a string, if the only argument that
is provided is a field name or expression then the function will trim only white space.
LTRIM( fieldname or expression,' characters')
RTRIM( ) Trims listed characters from the end of a string, if the only argument that
is provided is a field name or expression then the function will trim only white space.
RTRIM( fieldname or expression,' characters')
TRIM ( ) Trims listed characters from both ends of a string, if the only argument that
is provided is a field name or expression then the function will trim only white space.
TRIM( fieldname or expression ,' characters')
QUOTE( ) returns field enclosed in single quotes QUOTE( fieldname or expression)
|| Concatenate strings string_one || string_two
SUBSTR( ) Extracts part of a string. SUBSTR(field_name,start_location,substring_length )
REPLACE( ) Searches column or field for string specified in the second argument
and replaces it with the string in the third argument.

Aggregate Functions

Function Explanation Syntax
------------ ---------------------------------------------------------------------- -----------------------
AVG( ) Averages the value of the column or grouping AVG( fieldname or expression )
COUNT( ) Returns the number of rows in the column or grouping COUNT( fieldname or expression )
GROUP_CONCAT( ) Generates a string of non null values in a column separated by commas or some other specified delimiter GROUP_CONCAT( column name )
MAX( ) Returns the highest value found in the column or grouping MAX( fieldname or expression )
MIN( ) Returns the lowest value found in the column or grouping MIN( fieldname or expression )
SUM( ) Total of the values in the column or grouping added together SUM( fieldname or expression )
TOTAL( ) Specific to SQLITE SQL, always returns the floating point sum of the values a column or grouping TOTAL( fieldname or expression )
------------ --------------------------------------------- -----------------------

Date and Time

Function Explanation
------------ --------------------------------------------------------
CURRENT_DATE Returns UTC Date in YYYY-MM-DD Format
CURRENT_TIME Returns UTC Time in HH:MM:SS Format
CURRENT_TIMESTAMP Returns Current UTC Date and time
datetime("now","localtime") Returns current local date and time as YYYY-MM-DD HH:MM:SS
date("now","localtime") Returns local date as YYYY-MM-DD
time("now","localtime") Returns local time as HH:MM:SS
------------ ---------------------------------------------

Other Keywords and Functions

sqlite_master table recording schema of the database. Automatically updated Read only to user.
sqlite_temp_master table recording schema of temporary objects during a database session.
last_insert_rowid() Row id of last record inserted during a session. Caution this value is not table specific.

Identify The Version of SQLite in use

sqlite> select sqlite_version();


Comments in SQLite

It is often useful to include comments within CREATE TABLE or CREATE VIEW statements for later reference.

-- Single line comments are preceeded by two minus signs.
/* Multiline comments are preceeded by a forward slash and asterisk and
are terminated by an asterisk followed by a forward slash

SQLITE Dot Commands

list SQL statements used to create the database including INSERT statements. If the dump command is followed by a table,view name will only list SQL statements associated with that element
list all tables and indices in database main. If followed by table, view name will list the CREATE statement for that element
Changes output mode to column and creates apropriate widths for data in each displayed column.
list all tables in database main. Equivalent to SHOW TABLES in other SQL versions.

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
  If TABLE specified, only dump tables matching
  LIKE pattern TABLE.
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.
With no args, it turns EXPLAIN on.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices ?TABLE? Show names of all indices
  If TABLE specified, only show indices for tables
  matching LIKE pattern TABLE.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode where MODE is one of:
  csv Comma-separated values
  column    Left-aligned columns. (See .width)
  html      HTML <table> code
  insert    SQL insert statements for TABLE
  line      One value per line
  list      Values delimited by .separator string
  tabs      Tab-separated values
  tcl       TCL list elements
.nullvalue STRING Print STRING in place of NULL values
.output FILENAME Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE    Replace the standard prompts
.quit Exit this program
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.schema ?TABLE? Show the CREATE statements
  If TABLE specified, only show tables matching
  LIKE pattern TABLE.
.separator STRING Change separator used by output mode and .import
.show Show the current values for various settings
.stats ON|OFF Turn stats on or off
.tables ?TABLE? List names of tables
  If TABLE specified, only list tables matching
  LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.width NUM1 NUM2 ... Set column widths for "column" mode
.timer ON|OFF Turn the CPU timer measurement on or off

Colin Riley -- Updated February 2011 --