Execute SQL Tab

Although SQLite Manager simplifies many database operations, there are still tasks that can only be accomplished by using SQL in the Execute SQL tab such as updating multiple rows at a time or using SELECT across multiple tables.

Once you have entered your sql statement(s) into the Enter SQL text box, you can click on the Run SQL button or press ctrl + F12 or ctrl ; (control semicolon) When multiple sql statements are to be run, each sql statement must be terminated by a semicolon.

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

Copy or Save Query Results

Output for SELECT queries is displayed in the lower text box of the Execute SQL tab as table rows. A drop down menu to the right of the Run SQL button provides the option of displaying the query result as CSV (comma separated values) or saving the query result as a CSV text file. One or more rows from the query result can also be copied on to the clip board by highlighting the rows to be copied and right clicking to bring up the context menu. This menu provides the option of copying the rows as SQL statements or in CSV format. They can then be pasted into a text file or a spreadsheet program such as MS Excel.

Save Query By Name

SELECT Queries and other SQL statements can be stored in a SQLite 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 statement 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 menu run the following sql statement containing the query name in the Enter SQL text box.

DELETE FROM __sm_ext_mgmt WHERE type = "NamedQuery: query_name"

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

INSERT 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 Queries

String Manipulation

Expressions

Aggregate Functions