SELECT Statements

			Function
SELECT
	DISTINCT		Exclude duplicate records for fields selected.
	CASE		Conditional expression
FROM			Table or tables that are being queried.
WHERE			Row level filtering
		AND	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		wildcard (%) enclosed in parenthesis
	NOT LIKE		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

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 (' ', '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 )
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 )
------------	---------------------------------------------	-----------------------

---

Comments in SQLite

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

-- Single line commentss 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 */

Other Keywords and Functions

CURRENT_TIME
CURRENT_DATE
CURRENT_TIMESTAMP
sqlite_master	table recording schema of the database. Automatically updated Read only to user.
sqlite_temp_master
last_insert_rowid()

Identify The Version of SQLite in use

sqlite> select sqlite_version();
3.5.9
sqlite>

vacuum;
sqlite>

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.5.2

.Enter ".help" for instructions
sqlite> .help
.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
.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.
.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 ON TABLE
.LOAD FILE ?ENTRY?	LOAD AN EXTENSION LIBRARY
.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
.SCHEMA ?TABLE?	SHOW THE CREATE STATEMENTS
.SEPARATOR STRING	CHANGE SEPARATOR USED BY OUTPUT MODE AND .IMPORT
.SHOW	SHOW THE CURRENT VALUES FOR VARIOUS SETTINGS
.TABLES ?PATTERN?	LIST NAMES OF TABLES MATCHING A LIKE PATTERN
.TIMEOUT MS	TRY OPENING LOCKED TABLES FOR MS MILLISECONDS
.WIDTH NUM NUM ...	SET COLUMN WIDTHS FOR "COLUMN" MODE

---

Colin Riley -- Updated July 2008 -- Alphabetical Subject Index -- Contact form

Hosted by www.Geocities.ws