SQLite Notes

SQL Quick Reference

CREATE TABLE

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 PHRASE with CASCADE

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 Record in TABLE

UPDATE table_name SET column_name= value WHERE criteria;

SELECT Statements

					Syntax or Purpose
---------	---------	--------------	------------	------------	-------------
SELECT
	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
	GROUP BY
	HAVING
		ORDER BY			Sorting of the output using a comma delimited list of column names
		LIMIT			Limit the number of rows returned

Expressions

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.	REPLACE(field_name,'old_string','new_string')

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();
3.7.5
sqlite>

vacuum;
sqlite>

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

.dump: 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
.schema: list all tables and indices in database main. If followed by table, view name will list the CREATE statement for that element
.explain: Changes output mode to column and creates apropriate widths for data in each displayed column.
.tables: 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
.read FILENAME	Execute SQL in FILENAME
.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 --