Execute SQL Tab

Although SQLite Manager simplifies many database operations, there are still situations where you will likely need to create a sql statement to accomplish a particular task such as updating multiple rows at a time or creating a table which uses a foreign key.

Features of the Execute SQL Tab

SQL statements are entered into the upper text box.

You can navigate through previousily run queries by clicking on the left and right arrows to the right of the text "Enter SQL"

Tip: You can shrink or expand the Enter SQL text box by clicking an dragging the horizontal line below the Run SQL button.

SELECT Queries and other SQL statements can be stored in the database and rerun at a later time from the Execute SQL tab of SQLite Manager.

To enable this functionality, go to the Tools menu in the Menu bar and check the Use Table for Extension Data item.

To save a SQL statement, click on the SAVE QUERY icon in the Execute SQL tab, which will prompt you to enter a query name.

To retrieve the SQL query, select it by name from the drop down menu below the Enter SQL text box. The text of the statment will be written to the Enter SQL text box where it can be modified if necessary before being run.

To delete a saved query statement from the list, run the following sql statement containing the name of the query to be deleted in the "Enter SQL" text box.

DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery:query_name"

If you want to, you can also add the above SQL statement to the list by saving it as Delete_A_Query . Then in the future, it will only be necessary to type in the correct query name and excute the sql.

The arrows to the left of the SAVE QUERY icon will allow you to scroll through the previous SQL statements entered during the session.

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

Add Records to a Table

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

UPDATE

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

Colin Riley --Updated January 2011--

SQLite Introduction     SQLite Site Index