Starting with SQLite...

SQLite is a small library which implements a lightweight DB engine. There is no DB server or configuration files used in SQLite. All tables views etc are written in to a single file which can be used in different platforms.  Above features together with less size, less memory consumption, portability and reliability have make SQLite a widely used component in various applications and devices.

SQLite's command line utility allows you to easily execute SQLite queries.
You can download SQLite3.exe from this page http://www.sqlite.org/download.html.
To use the command line utility open command window and point to the folder where SQLite3.exe exists. Now in this window you can run SQLite commands and queries.

Some of the basic queries and commands for a beginner are listed below.

☛ This command create a new Database with the name 'myDatabase'. SQLite will create a file with the same name. Note that here we haven't put the semicolon at the end. If you put a semicolon, it will append to the file name.

> sqlite3 myDatabase

☛ Following line creates a new table with the name 'employee'. It will contains two columns 'id' and 'name'.

> create table employee(id INTEGER, name TEXT);

☛ The above query can be changed as below so that 'id' column becomes a primary key.

> create table employee(id INTEGER PRIMARY KEY, name TEXT);

☛ Insert data

> insert into employee (id, name) values(10, 'john');

☛ Retrieve data

> select * from employee;

☛ Update a row

> update employee set id = 20, name = 'Steven' where id = 10;

☛ Drop table

> drop table employee;

☛ 'Create Trigger' syntax allows you to create triggers. Following trigger runs after inserting a record in to the employee table. It automatically adds 100 to the newly inserted row id. Note that the id of the newly inserted row has been referred as 'new.id'.

> create trigger trigger1 after insert on employee

> begin

> update employee set id = new.id + 100 where id = new.id;

> end;

Now insert a record to the employee table by using normal insert query and check the record by using a select query. You will see the id of the row has been changed.

☛ View all triggers
'sqlite_master' table contains the information about the database. To view all the triggers you can directly query from this table.

> SELECT name FROM sqlite_master WHERE type = 'trigger';

☛ Following command drops the trigger named 'myTrigger'.

> drop trigger myTrigger;

☛ In SQLite you use '||' to concatenate Strings. Following line query the name from employee table and appends the word 'Mr. '.

> select 'Mr. ' || name from employee where id = 10;

☛ '.read' reads queries from a file and executes them. Don't use semicolon at the end.

> .read D:/temp/my-queries.txt

☢ If it says that the file cannot be opened, check to make sure you haven't put a semicolon at the end of the command.
☢ If it gave a error message saying "incomplete SQL", make sure you have put a semicolon at the end of the query in the file.

☛ List all tables in current database.

> .table

☛ You can list all the tables which match a certain pattern. Following command lists the tables starts with 'emp'.

> .table emp%

☛ The ".dump" command shows information of the current database and data inserted.

> .dump

☛ To exit SQLite, you can use any of the following commands

> .quit
> .exit