UploadLanguage (EN)Support
BusinessMobileSocial MediaMarketingTechnologyArt & PhotosCareerDesignEducationPresentations & Public SpeakingGovernment & NonprofitHealthcareInternetLawLeadership & ManagementAutomotiveEngineeringSoftwareRecruiting & HRRetailSalesServicesScienceSmall Business & EntrepreneurshipFoodEnvironmentEconomy & FinanceData & AnalyticsInvestor RelationsSportsSpiritualNews & PoliticsTravelSelf ImprovementReal EstateEntertainment & HumorHealth & MedicineDevices & HardwareLifestyle
Upload
Sign in
Diogenes Silva Sousa, profile picture
Uploaded byDiogenes Silva Sousa
1,494 views

Sqlite tutorial

This document provides a tutorial on SQLite, an open-source SQL database that does not require a separate server. It begins with an overview of SQLite and its features, then discusses how to install SQLite on different operating systems. The remainder of the document covers SQLite commands, syntax, data types, and how to create and manage SQLite databases and tables. It aims to help beginners learn the basic concepts and get started with SQLite programming.

Technology◦
Downloaded 44 times
1 / 172
2 / 172
3 / 172
4 / 172
5 / 172
6 / 172
7 / 172
8 / 172
9 / 172
10 / 172
11 / 172
12 / 172
13 / 172
14 / 172
15 / 172
16 / 172
17 / 172
18 / 172
19 / 172
20 / 172
SQLite Tutorial
i SQLITE TUTORIAL Simply Easy Learning by tutorialspoint.com tutorialspoint.com
TUTORIALS POINT Simply Easy Learning ABOUT THE TUTORIAL SQLite Tutorial SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. This tutorial will give you quick start with SQLite and make you comfortable with SQLite programming. Audience This reference has been prepared for the beginners to help them understand the basic to advanced concepts related to SQLite Database Engine. Prerequisites Before you start doing practice with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. Copyright & Disclaimer Notice All the content and graphics on this tutorial are the property of tutorialspoint.com. Any content from tutorialspoint.com or this tutorial may not be redistributed or reproduced in any way, shape, or form without the written permission of tutorialspoint.com. Failure to do so is a violation of copyright laws. This tutorial may contain inaccuracies or errors and tutorialspoint provides no guarantee regarding the accuracy of the site or its contents including this tutorial. If you discover that the tutorialspoint.com site or this tutorial content contains some errors, please contact us at webmaster@tutorialspoint.com
TUTORIALS POINT Simply Easy Learning Table of Content SQLite Tutorial......................................................................... 2 Audience.................................................................................. 2 Prerequisites............................................................................ 2 Copyright & Disclaimer Notice.................................................. 2 SQLite Overview.................................................................... 12 What is SQLite? .......................................................................................12 Why SQLite?............................................................................................12 History:.....................................................................................................13 SQLite Limitations: ...................................................................................13 SQLite Commands:..................................................................................13 DDL - Data Definition Language: .............................................................13 DML - Data Manipulation Language:........................................................13 DQL - Data Query Language: ..................................................................14 SQLite Installation.................................................................. 15 Install SQLite On Windows.......................................................................15 Install SQLite On Linux.............................................................................15 Install SQLite On Mac OS X.....................................................................16 SQLite Commands................................................................. 17 Formatting output .....................................................................................19 The sqlite_master Table...........................................................................19 SQLite Syntax........................................................................ 20 Case Sensitivity........................................................................................20 Comments................................................................................................20 SQLite Statements ...................................................................................20 SQLite ANALYZE Statement:...................................................................20 SQLite AND/OR Clause: ..........................................................................21 SQLite ALTER TABLE Statement: ...........................................................21 SQLite ALTER TABLE Statement (Rename): ..........................................21 SQLite ATTACH DATABASE Statement:.................................................21 SQLite BEGIN TRANSACTION Statement: .............................................21 SQLite BETWEEN Clause: ......................................................................21 SQLite COMMIT Statement: ....................................................................21 SQLite CREATE INDEX Statement: ........................................................21 SQLite CREATE UNIQUE INDEX Statement:..........................................21 SQLite CREATE TABLE Statement: ........................................................22 SQLite CREATE TRIGGER Statement : ..................................................22 SQLite CREATE VIEW Statement : .........................................................22
TUTORIALS POINT Simply Easy Learning SQLite CREATE VIRTUAL TABLE Statement: ........................................22 SQLite COMMIT TRANSACTION Statement:..........................................22 SQLite COUNT Clause: ...........................................................................22 SQLite DELETE Statement:.....................................................................22 SQLite DETACH DATABASE Statement: ................................................23 SQLite DISTINCT Clause:........................................................................23 SQLite DROP INDEX Statement :............................................................23 SQLite DROP TABLE Statement: ............................................................23 SQLite DROP VIEW Statement : .............................................................23 SQLite DROP TRIGGER Statement : ......................................................23 SQLite EXISTS Clause: ...........................................................................23 SQLite EXPLAIN Statement :...................................................................23 SQLite GLOB Clause: ..............................................................................23 SQLite GROUP BY Clause: .....................................................................23 SQLite HAVING Clause: ..........................................................................24 SQLite INSERT INTO Statement: ............................................................24 SQLite IN Clause: ....................................................................................24 SQLite Like Clause: .................................................................................24 SQLite NOT IN Clause:............................................................................24 SQLite ORDER BY Clause: .....................................................................24 SQLite PRAGMA Statement: ...................................................................24 SQLite RELEASE SAVEPOINT Statement:.............................................25 SQLite REINDEX Statement:...................................................................25 SQLite ROLLBACK Statement:................................................................25 SQLite SAVEPOINT Statement: ..............................................................25 SQLite SELECT Statement:.....................................................................25 SQLite UPDATE Statement: ....................................................................25 SQLite VACUUM Statement: ...................................................................25 SQLite WHERE Clause:...........................................................................25 SQLite Data Type .................................................................. 26 SQLite Storage Classes:..........................................................................26 SQLite Affinity Type: ................................................................................26 SQLite Affinity and Type Names: .............................................................27 Boolean Datatype:....................................................................................28 Date and Time Datatype: .........................................................................28 SQLite Create Database........................................................ 29 Syntax: .....................................................................................................29 Example: ..................................................................................................29 The .dump Command...............................................................................30
TUTORIALS POINT Simply Easy Learning SQLite Attach Database......................................................... 31 Syntax: .....................................................................................................31 Example: ..................................................................................................31 SQLite Detach Database ....................................................... 32 Syntax: .....................................................................................................32 Example: ..................................................................................................32 SQLite Create Table .............................................................. 33 Syntax: .....................................................................................................33 Example: ..................................................................................................33 SQLite Drop Table ................................................................. 35 Syntax: .....................................................................................................35 Example: ..................................................................................................35 SQLite Insert Query ............................................................... 36 Syntax: .....................................................................................................36 Example: ..................................................................................................36 Populate one table using another table:...................................................37 SQLite Select Query .............................................................. 38 Syntax: .....................................................................................................38 Example: ..................................................................................................38 Setting output column width: ....................................................................39 Schema Information: ................................................................................39 SQLite Operators................................................................... 41 SQLite Arithmetic Operators: ...................................................................41 Example ...................................................................................................42 SQLite Comparison Operators:................................................................42 Example ...................................................................................................43 SQLite Logical Operators:........................................................................44 Example ...................................................................................................44 SQLite Bitwise Operators:........................................................................46 Example ...................................................................................................47 SQLite Expressions ............................................................... 49 Syntax: .....................................................................................................49 SQLite - Boolean Expressions: ................................................................49 SQLite - Numeric Expression:..................................................................50 SQLite - Date Expressions:......................................................................50 SQLite Where Clause ............................................................ 51 Syntax: .....................................................................................................51 Example: ..................................................................................................51 SQLite AND and OR Operator ............................................... 54
TUTORIALS POINT Simply Easy Learning The AND Operator: ..................................................................................54 Syntax: .....................................................................................................54 Example: ..................................................................................................54 The OR Operator: ....................................................................................55 Syntax: .....................................................................................................55 Example: ..................................................................................................55 SQLite Update Query............................................................. 56 Syntax: .....................................................................................................56 Example: ..................................................................................................56 SQLite Delete Query.............................................................. 58 Syntax: .....................................................................................................58 Example: ..................................................................................................58 SQLite Like Clause ................................................................ 60 Syntax: .....................................................................................................60 Example: ..................................................................................................61 SQLite Glob Clause ............................................................... 63 Syntax: .....................................................................................................63 Example: ..................................................................................................64 SQLite LIMIT Clause.............................................................. 66 Syntax: .....................................................................................................66 Example: ..................................................................................................66 SQLite Order By Clause......................................................... 68 Syntax: .....................................................................................................68 Example: ..................................................................................................68 SQLite Group By Clause........................................................ 70 Syntax: .....................................................................................................70 Example: ..................................................................................................70 SQLite Having Clause............................................................ 73 Syntax: .....................................................................................................73 Example: ..................................................................................................73 SQLite Distinct Keyword ........................................................ 75 Syntax: .....................................................................................................75 Example: ..................................................................................................75 SQLite PRAGMA ................................................................... 77 Syntax: .....................................................................................................77 auto_vacuum Pragma ..............................................................................77 cache_size Pragma..................................................................................78 case_sensitive_like Pragma.....................................................................78 count_changes Pragma ...........................................................................78
TUTORIALS POINT Simply Easy Learning database_list Pragma...............................................................................78 encoding Pragma.....................................................................................78 freelist_count Pragma ..............................................................................78 index_info Pragma ...................................................................................79 index_list Pragma.....................................................................................79 journal_mode Pragma..............................................................................79 max_page_count Pragma ........................................................................79 page_count Pragma.................................................................................80 page_size Pragma ...................................................................................80 parser_trace Pragma................................................................................80 recursive_triggers Pragma .......................................................................80 schema_version Pragma..........................................................................80 secure_delete Pragma .............................................................................80 sql_trace Pragma.....................................................................................81 synchronous Pragma ...............................................................................81 temp_store Pragma..................................................................................81 temp_store_directory Pragma..................................................................81 user_version Pragma ...............................................................................82 writable_schema Pragma.........................................................................82 SQLite Constraints................................................................. 83 NOT NULL Constraint ..............................................................................83 EXAMPLE: ...............................................................................................83 DEFAULT Constraint................................................................................83 EXAMPLE: ...............................................................................................84 UNIQUE Constraint..................................................................................84 EXAMPLE: ...............................................................................................84 PRIMARY KEY Constraint .......................................................................84 EXAMPLE: ...............................................................................................84 CHECK Constraint ...................................................................................85 EXAMPLE: ...............................................................................................85 Dropping Constraints:...............................................................................85 SQLite Joins .......................................................................... 86 The CROSS JOIN ....................................................................................87 The INNER JOIN......................................................................................87 The OUTER JOIN ....................................................................................88 SQLite UNIONS Clause......................................................... 90 Syntax: .....................................................................................................90 Example: ..................................................................................................90 COMPANY TABLE...................................................................................90
TUTORIALS POINT Simply Easy Learning The UNION ALL Clause:..........................................................................92 Syntax: .....................................................................................................92 Example: ..................................................................................................93 SQLite NULL Values.............................................................. 94 Syntax: .....................................................................................................94 Example: ..................................................................................................94 SQLite ALIAS Syntax............................................................. 96 Syntax: .....................................................................................................96 Example: ..................................................................................................96 SQLite Triggers...................................................................... 98 Syntax: .....................................................................................................98 Example ...................................................................................................99 Listing TRIGGERS .................................................................................100 Dropping TRIGGERS.............................................................................100 SQLite Indexes .................................................................... 101 The CREATE INDEX Command: ...........................................................101 Single-Column Indexes: .........................................................................101 Unique Indexes: .....................................................................................101 Composite Indexes: ...............................................................................102 Implicit Indexes: .....................................................................................102 Example .................................................................................................102 The DROP INDEX Command: ...............................................................102 When should indexes be avoided? ........................................................103 SQLite Indexed By............................................................... 104 Syntax ....................................................................................................104 Example .................................................................................................104 SQLite Alter Command ........................................................ 106 Syntax: ...................................................................................................106 Example: ................................................................................................106 SQLite Truncate Table......................................................... 108 Syntax: ...................................................................................................108 Example: ................................................................................................108 SQLite Views ....................................................................... 109 Creating Views: ......................................................................................109 Example: ................................................................................................109 Dropping Views: .....................................................................................110 SQLite TRANSACTIONS..................................................... 111 Properties of Transactions: ....................................................................111 Transaction Control:...............................................................................111
TUTORIALS POINT Simply Easy Learning The BEGIN TRANSACTION Command:................................................112 The COMMIT Command:.......................................................................112 The ROLLBACK Command: ..................................................................112 Example: ................................................................................................112 SQLite Sub Queries ............................................................. 114 Subqueries with the SELECT Statement:...............................................114 Example: ................................................................................................115 Subqueries with the INSERT Statement: ...............................................115 Example: ................................................................................................115 Subqueries with the UPDATE Statement:..............................................116 Example: ................................................................................................116 Subqueries with the DELETE Statement:...............................................116 Example: ................................................................................................116 SQLite AUTOINCREMENT.................................................. 118 Syntax: ...................................................................................................118 Example: ................................................................................................118 SQLite Injection.................................................................... 120 Preventing SQL Injection:.......................................................................120 SQLite Explain ..................................................................... 122 Syntax: ...................................................................................................122 Example: ................................................................................................122 SQLite Vacuum.................................................................... 125 Manual VACUUM...................................................................................125 Auto-VACCUM.......................................................................................125 SQLite Date & Time ............................................................. 127 Time Strings:..........................................................................................127 Modifiers.................................................................................................128 Formatters:.............................................................................................128 Examples ...............................................................................................129 SQLite Useful Functions ...................................................... 131 SQLite COUNT Function........................................................................132 SQLite MAX Function.............................................................................132 SQLite MIN Function..............................................................................132 SQLite AVG Function.............................................................................132 SQLite SUM Function.............................................................................133 SQLite RANDOM Function.....................................................................133 SQLite ABS Function .............................................................................133 SQLite UPPER Function ........................................................................133 SQLite LOWER Function .......................................................................134
TUTORIALS POINT Simply Easy Learning SQLite LENGTH Function......................................................................134 SQLite sqlite_version Function...............................................................134 SQLite C/C++ Tutorial.......................................................... 135 C/C++ Interface APIs .............................................................................135 Connecting To Database........................................................................136 Create a Table .......................................................................................136 INSERT Operation .................................................................................137 SELECT Operation ................................................................................138 UPDATE Operation................................................................................140 DELETE Operation ................................................................................141 SQLite Java Tutorial ............................................................ 144 Connecting To Database........................................................................144 Create a Table .......................................................................................145 INSERT Operation .................................................................................145 SELECT Operation ................................................................................146 UPDATE Operation................................................................................148 DELETE Operation ................................................................................149 SQLite PHP Tutorial............................................................. 151 PHP Interface APIs ................................................................................151 Connecting To Database........................................................................152 Create a Table .......................................................................................152 INSERT Operation .................................................................................153 SELECT Operation ................................................................................154 UPDATE Operation................................................................................155 DELETE Operation ................................................................................156 SQLite Perl Tutorial.............................................................. 158 DBI Interface APIs..................................................................................158 Connecting To Database........................................................................159 Create a Table .......................................................................................160 INSERT Operation .................................................................................161 SELECT Operation ................................................................................161 UPDATE Operation................................................................................162 DELETE Operation ................................................................................164 SQLite Python...................................................................... 165 Python sqlite3 module APIs ...................................................................165 Connecting To Database........................................................................167 Create a Table .......................................................................................167 INSERT Operation .................................................................................168 SELECT Operation ................................................................................168
TUTORIALS POINT Simply Easy Learning UPDATE Operation................................................................................169 DELETE Operation ................................................................................170
TUTORIALS POINT Simply Easy Learning SQLite Overview Tthis tutorial helps you to understand what is SQLite , how it differs from SQL, why it is needed and the way in which it handles the applications Database. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is one of the fastest-growing database engines around, but that's growth in terms of popularity, not anything to do with its size. The source code for SQLite is in the public domain. What is SQLite? SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the one database, which is zero-configured, that means like other database you do not need to configure it in your system. SQLite engine is not a standalone process like other databases, you can link it statically or dynamically as per your requirement with your application. The SQLite accesses its storage files directly. Why SQLite?  SQLite does not require a separate server process or system to operate (serverless).  SQLite comes with zero-configuration, which means no setup or administration needed.  A complete SQLite database is stored in a single cross-platform disk file.  SQLite is very small and light weight, less than 400KiB fully configured or less than 250KiB with optional features omitted.  SQLite is self-contained, which means no external dependencies.  SQLite transactions are fully ACID-compliant, allowing safe access from multiple processes or threads.  SQLite supports most of the query language features found in the SQL92 (SQL2) standard.  SQLite is written in ANSI-C and provides simple and easy-to-use API.  SQLite is available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). CHAPTER 1
TUTORIALS POINT Simply Easy Learning History: 1. 2000 -- D. Richard Hipp had designed SQLite for the purpose of no administration required for operating a program. 2. 2000 -- In August, SQLite 1.0 released with GNU Database Manager. 3. 2011 -- Hipp announced to add UNQl interface to SQLite DB and to develop UNQLite (Document oriented database). SQLite Limitations: There are few unsupported features of SQL92 in SQLite which are shown below: Feature Description RIGHT OUTER JOIN Only LEFT OUTER JOIN is implemented. FULL OUTER JOIN Only LEFT OUTER JOIN is implemented. ALTER TABLE The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT not supported. Trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers. VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. GRANT and REVOKE The only access permissions that can be applied are the normal file access permissions of the underlying operating system. SQLite Commands: The standard SQLite commands to interact with relational databases are similar as SQL. They are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their operational nature: DDL - Data Definition Language: Command Description CREATE Creates a new table, a view of a table, or other object in database ALTER Modifies an existing database object, such as a table. DROP Deletes an entire table, a view of a table or other object in the database. DML - Data Manipulation Language: Command Description INSERT Creates a record UPDATE Modifies records
TUTORIALS POINT Simply Easy Learning DELETE Deletes records DQL - Data Query Language: Command Description SELECT Retrieves certain records from one or more tables
TUTORIALS POINT Simply Easy Learning SQLite Installation The SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X. Install SQLite On Windows  Go to SQLite download page, and download precompiled binaries from Windows section.  You will need to download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.  Create a folder C:>sqlite and unzip above two zipped files in this folder which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.  Add C:>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display a result something as below. C:>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> Install SQLite On Linux Today, almost all the flavours of Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine or not. $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> If you do not see above result, then it means you do not have SQLite installed on your Linux machine. So let's follow the following steps to install SQLite:  Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.  Follow the following steps: CHAPTER 2
TUTORIALS POINT Simply Easy Learning $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install Above procedure will end with SQLite installation on your Linux machine which you can verify as explained above. Install SQLite On Mac OS X Though latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow the following steps:  Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.  Follow the following steps: $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install Above procedure will end with SQLite installation on your Mac OS X machine which you can verify by issuing following command: $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> Finally, you have SQLite command prompt where you can issue SQLite commands to do your excercises.
TUTORIALS POINT Simply Easy Learning SQLite Commands This chapter will take you through simple and useful commands used by SQLite programmers. These commands are called SQLite dot commands and exception with these commands is that they should not be terminated by a semi-colon (;). Let's start with typing a simple sqlite3 command at command prompt which will provide you SQLite command prompt where you will issue various SQLite commands. $sqlite3 SQLite version 3.3.6 Enter ".help" for instructions sqlite> For a listing of the available dot commands, you can enter ".help" at any time. For example: sqlite>.help Above command will display a list of various important SQLite dot commands, which are as follows: Command Description .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 SQLite prompt .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 CHAPTER 3
TUTORIALS POINT Simply Easy Learning 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 Set output mode where MODE is one of:  csv Comma-separated values  column Left-aligned columns.  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 .print STRING... Print literal STRING .prompt MAIN CONTINUE Replace the standard prompts .quit Exit SQLite prompt .read FILENAME Execute SQL in FILENAME .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 ?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 .timer ON|OFF Turn the CPU timer measurement on or off Let's try .show command to see default setting for your SQLite command prompt. sqlite>.show echo: off explain: off headers: off mode: column nullvalue: "" output: stdout separator: "|" width: sqlite>
TUTORIALS POINT Simply Easy Learning Make sure there is no space in between sqlite> prompt and dot command, otherwise it will not work. Formatting output You can use the following sequence of dot commands to format your output the way I have listed down in this tutorial: sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite> Above setting will produce the output in the following format: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 CPU Time: user 0.000000 sys 0.000000 The sqlite_master Table The master table holds the key information about your database tables and it is called sqlite_master. You can see its schema as follows: sqlite>.schema sqlite_master This will produce the following result: CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text );
TUTORIALS POINT Simply Easy Learning SQLite Syntax SQLite is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQLite by listing all the basic SQLite Syntax. Case Sensitivity Important point to be noted is that SQLite is case insensitive, but there are some commands, which are case sensitive like GLOB and glob have different meaning in SQLite statements. Comments SQLite comments are extra notes, which you can add in your SQLite code to increase its readability and they can appear anywhere; whitespace can occur, including inside expressions and in the middle of other SQL statements but they can not be nested. SQL comments begin with two consecutive "-" characters (ASCII 0x2d) and extend up to and including the next newline character (ASCII 0x0a) or until the end of input, whichever comes first. You can also use C-style comments, which begin with "/*" and extend up to and including the next "*/" character pair or until the end of input, whichever comes first. C-style comments can span multiple lines. sqlite>.help -- This is a single line comment SQLite Statements All the SQLite statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., and all the statements end with a semicolon (;). SQLite ANALYZE Statement: ANALYZE; or ANALYZE database_name; or ANALYZE database_name.table_name; CHAPTER 4
TUTORIALS POINT Simply Easy Learning SQLite AND/OR Clause: SELECT column1, column2....columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDITION-2; SQLite ALTER TABLE Statement: ALTER TABLE table_name ADD COLUMN column_def...; SQLite ALTER TABLE Statement (Rename): ALTER TABLE table_name RENAME TO new_table_name; SQLite ATTACH DATABASE Statement: ATTACH DATABASE 'DatabaseName' As 'Alias-Name'; SQLite BEGIN TRANSACTION Statement: BEGIN; or BEGIN EXCLUSIVE TRANSACTION; SQLite BETWEEN Clause: SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2; SQLite COMMIT Statement: COMMIT; SQLite CREATE INDEX Statement: CREATE INDEX index_name ON table_name ( column_name COLLATE NOCASE ); SQLite CREATE UNIQUE INDEX Statement: CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);
TUTORIALS POINT Simply Easy Learning SQLite CREATE TABLE Statement: CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); SQLite CREATE TRIGGER Statement : CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; .... END; SQLite CREATE VIEW Statement : CREATE VIEW database_name.view_name AS SELECT statement....; SQLite CREATE VIRTUAL TABLE Statement: CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log ); or CREATE VIRTUAL TABLE database_name.table_name USING fts3( ); SQLite COMMIT TRANSACTION Statement: COMMIT; SQLite COUNT Clause: SELECT COUNT(column_name) FROM table_name WHERE CONDITION; SQLite DELETE Statement: DELETE FROM table_name WHERE {CONDITION};
TUTORIALS POINT Simply Easy Learning SQLite DETACH DATABASE Statement: DETACH DATABASE 'Alias-Name'; SQLite DISTINCT Clause: SELECT DISTINCT column1, column2....columnN FROM table_name; SQLite DROP INDEX Statement : DROP INDEX database_name.index_name; SQLite DROP TABLE Statement: DROP TABLE database_name.table_name; SQLite DROP VIEW Statement : DROP INDEX database_name.view_name; SQLite DROP TRIGGER Statement : DROP INDEX database_name.trigger_name; SQLite EXISTS Clause: SELECT column1, column2....columnN FROM table_name WHERE column_name EXISTS (SELECT * FROM table_name ); SQLite EXPLAIN Statement : EXPLAIN INSERT statement...; or EXPLAIN QUERY PLAN SELECT statement...; SQLite GLOB Clause: SELECT column1, column2....columnN FROM table_name WHERE column_name GLOB { PATTERN }; SQLite GROUP BY Clause: SELECT SUM(column_name)
TUTORIALS POINT Simply Easy Learning FROM table_name WHERE CONDITION GROUP BY column_name; SQLite HAVING Clause: SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition); SQLite INSERT INTO Statement: INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN); SQLite IN Clause: SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N); SQLite Like Clause: SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN }; SQLite NOT IN Clause: SELECT column1, column2....columnN FROM table_name WHERE column_name NOT IN (val-1, val-2,...val-N); SQLite ORDER BY Clause: SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC}; SQLite PRAGMA Statement: PRAGMA pragma_name; For example: PRAGMA page_size; PRAGMA cache_size = 1024; PRAGMA table_info(table_name);
TUTORIALS POINT Simply Easy Learning SQLite RELEASE SAVEPOINT Statement: RELEASE savepoint_name; SQLite REINDEX Statement: REINDEX collation_name; REINDEX database_name.index_name; REINDEX database_name.table_name; SQLite ROLLBACK Statement: ROLLBACK; or ROLLBACK TO SAVEPOINT savepoint_name; SQLite SAVEPOINT Statement: SAVEPOINT savepoint_name; SQLite SELECT Statement: SELECT column1, column2....columnN FROM table_name; SQLite UPDATE Statement: UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ]; SQLite VACUUM Statement: VACUUM; SQLite WHERE Clause: SELECT column1, column2....columnN FROM table_name WHERE CONDITION;
TUTORIALS POINT Simply Easy Learning SQLite Data Type SQLite data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQLite. You would use these data types while creating your tables. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. SQLite Storage Classes: Each value stored in an SQLite database has one of the following storage classes: Storage Class Description NULL The value is a NULL value. INTEGER The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. REAL The value is a floating point value, stored as an 8-byte IEEE floating point number. TEXT The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE) BLOB The value is a blob of data, stored exactly as it was input. SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. SQLite Affinity Type: SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities: Affinity Description TEXT This column stores all data using storage classes NULL, TEXT or BLOB. NUMERIC This column may contain values using all five storage classes. INTEGER Behaves the same as a column with NUMERIC affinity with an exception in a CAST CHAPTER 5
TUTORIALS POINT Simply Easy Learning expression. REAL Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation NONE A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another. SQLite Affinity and Type Names: Following table lists down various data type names which can be used while creating SQLite3 tables and corresponding applied affinity also has been shown: Data Type Affinity  INT  INTEGER  TINYINT  SMALLINT  MEDIUMINT  BIGINT  UNSIGNED BIG INT  INT2  INT8 INTEGER  CHARACTER(20)  VARCHAR(255)  VARYING CHARACTER(255)  NCHAR(55)  NATIVE CHARACTER(70)  NVARCHAR(100)  TEXT  CLOB TEXT
TUTORIALS POINT Simply Easy Learning  BLOB  no datatype specified NONE  REAL  DOUBLE  DOUBLE PRECISION  FLOAT REAL  NUMERIC  DECIMAL(10,5)  BOOLEAN  DATE  DATETIME NUMERIC Boolean Datatype: SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). Date and Time Datatype: SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values. Storage Class Date Formate TEXT A date in a format like "YYYY-MM-DD HH:MM:SS.SSS". REAL The number of days since noon in Greenwich on November 24, 4714 B.C. INTEGER The number of seconds since 1970-01-01 00:00:00 UTC. You can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
TUTORIALS POINT Simply Easy Learning SQLite Create Database The SQLite sqlite3 command is used to create new SQLite database. You do not need to have any special privilege to create a database. Syntax: Basic syntax of sqlite3 command is as follows: $sqlite3 DatabaseName.db Always, database name should be unique within the RDBMS. Example: If you want to create new database <testDB.db>, then SQLite3 statement would be as follows: $sqlite3 testDB.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> Above command will create a file testDB.db in the current directory. This file will be used as database by SQLite engine. If you have noticed while creating database, sqlite3 command will provide a sqlite>prompt after creating database file successfully. Once a database is created, you can check it in the list of databases using SQLite .databases command as follows: sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db You will use SQLite .quit command to come out of the sqlite prompt as follows: sqlite>.quit $ CHAPTER 6
TUTORIALS POINT Simply Easy Learning The .dump Command You can use .dump dot command to export complete database in a text file using SQLite command at command prompt as follows: $sqlite3 testDB.db .dump > testDB.sql Above command will convert the entire contents of testDB.db database into SQLite statements and dump it into ASCII text file testDB.sql. You can do restoration from the generated testDB.sql in simple way as follows: $sqlite3 testDB.db < testDB.sql At this moment your database is empty, so you can try above two procedures once you have few tables and data in your database. For now, let's proceed to next chapter.
TUTORIALS POINT Simply Easy Learning SQLite Attach Database Consider a case when you have multiple databases available and you want to use any one of them at a time. SQLite ATTACH DTABASE statement is used to select a particular database, and after this command, all SQLite statements will be executed under the attached database. Syntax: Basic syntax of SQLite ATTACH DATABASE statement is as follows: ATTACH DATABASE 'DatabaseName' As 'Alias-Name'; Above command will also create a database in case database is already not created, otherwise it will just attach database file name with logical database 'Alias-Name'. Example: If you want to attach an existing database testDB.db, then ATTACH DATABASE statement would be as follows: sqlite> ATTACH DATABASE 'testDB.db' as 'TEST'; Use SQLite .database command to display attached database. sqlite> .database seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db The database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment, otherwise you will get a warning message something as follows: sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP'; Error: database TEMP is already in use sqlite> ATTACH DATABASE 'testDB.db' as 'main'; Error: database TEMP is already in use CHAPTER 7
TUTORIALS POINT Simply Easy Learning SQLite Detach Database SQLite DETACH DTABASE statement is used to detach and dissociate a named database from a database connection which was previously attached using ATTACH statement. If the same database file has been attached with multiple aliases, then DETACH command will disconnect only given name and rest of the attachement will still continue. You cannot detach the main or temp databases. If the database is an in-memory or temporary database, the database will be destroyed and the contents will be lost. Syntax: Basic syntax of SQLite DETACH DATABASE 'Alias-Name' statement is as follows: DETACH DATABASE 'Alias-Name'; Here 'Alias-Name' is the same alias, which you had used while attaching database using ATTACH statement. Example: Consider you have a database, which you created in previous chapter and attached it with 'test' and 'currentDB' as we can see using .database command: sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db 3 currentDB /home/sqlite/testDB.db Now let's try to detach 'currentDB' from testDB.db as follows: sqlite> DETACH DATABASE 'currentDB'; Now, if you will check current attachment, you will find that testDB.db is still connected with 'test' and 'main'. sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db CHAPTER 8
TUTORIALS POINT Simply Easy Learning SQLite Create Table The SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column's data type. Syntax: Basic syntax of CREATE TABLE statement is as follows: CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype, ); CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally you can specify database_name along with table_name. Example: Following is an example, which creates a COMPANY table with ID as primary key and NOT NULL are the constraints showing that these fields can not be NULL while creating records in this table: sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Let us create one more table, which we will use in our exercises in subsequent chapters: sqlite> CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); CHAPTER 9
TUTORIALS POINT Simply Easy Learning You can verify if your table has been created successfully using SQLIte command .tables command, which will be used to list down all the tables in an attached database. sqlite>.tables COMPANY DEPARTMENT Here, you can see COMPANY table twice because it's showing COMPANY table for main database and test.COMPANY table for 'test' alias created for your testDB.db. You can get complete information about a table using SQLite .schema command as follows: sqlite>.schema COMPANY CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
TUTORIALS POINT Simply Easy Learning SQLite Drop Table The SQLite DROP TABLE statement is used to remove a table definition and all associated data, indexes, triggers, constraints and permission specifications for that table. You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever. Syntax: Basic syntax of DROP TABLE statement is as follows. You can optionally specify database name along with table name as follows: DROP TABLE database_name.table_name; Example: Let us first verify COMPANY table and then we would delete it from the database. sqlite>.tables COMPANY test.COMPANY This means COMPANY table is available in the database, so let us drop it as follows: sqlite>DROP TABLE COMPANY; sqlite> Now, if you would try .TABLES command, then you will not find COMPANY table anymore: sqlite>.tables sqlite> It shows nothing means the table from your database has been dropped successfully. CHAPTER 10
TUTORIALS POINT Simply Easy Learning SQLite Insert Query The SQLite INSERT INTO Statement is used to add new rows of data into a table in the database. Syntax: There are two basic syntaxes of INSERT INTO statement as follows: INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN); Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data. You may not need to specify the column(s) name in the SQLite query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQLite INSERT INTO syntax would be as follows: INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); Example: Consider you already have created COMPANY table in your testDB.db as follows: sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Now, following statements would create six records in COMPANY table: INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); CHAPTER 11
TUTORIALS POINT Simply Easy Learning INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 ); You can create a record in COMPANY table using second syntax as follows: INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 ); All the above statements would create following records in COMPANY table. Next chapter will teach you how to display all these records from a table. ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Populate one table using another table: You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate first table. Here is the syntax: INSERT INTO first_table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM second_table_name [WHERE condition]; For now, you can skip above statement, first let's learn SELECT and WHERE clauses, which will be covered in subsequent chapters.
TUTORIALS POINT Simply Easy Learning SQLite Select Query SQLite SELECT statement is used to fetch the data from a SQLite database table which returns data in the form of result table. These result tables are also called result-sets. Syntax: The basic syntax of SQLite SELECT statement is as follows: SELECT column1, column2, columnN FROM table_name; Here, column1, column2...are the fields of a table, whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax: SELECT * FROM table_name; Example: Consider COMPANY table is having following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example to fetch and display all these records using SELECT statement. Here, first three commands have been used to set properly formatted output. sqlite>.header on sqlite>.mode column sqlite> SELECT * FROM COMPANY; Finally, you will get the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- CHAPTER 12
TUTORIALS POINT Simply Easy Learning 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 If you want to fetch only selected fields of COMPANY table, then use the following query: sqlite> SELECT ID, NAME, SALARY FROM COMPANY; Above query will produce the following result: ID NAME SALARY ---------- ---------- ---------- 1 Paul 20000.0 2 Allen 15000.0 3 Teddy 20000.0 4 Mark 65000.0 5 David 85000.0 6 Kim 45000.0 7 James 10000.0 Setting output column width: Sometimes, you will face a problem related to truncated output in case of .mode column which happens because of default width of the column to be displayed. What you can do is that you can set column displayable column width using .width num, num.... command as follows: sqlite>.width 10, 20, 10 sqlite>SELECT * FROM COMPANY; Above .width command sets first column width to 10, second column width to 20 and third column width to 10. So finally above SELECT statement will give the following result: ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Schema Information: Because all the dot commads are available at SQLite prompt only, so while doing your programming with SQLite, you will use the following statement to list down all the tables created in your database using the following SELECT statement with sqlite_master table: sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table'; Assuming you have only COMPANY table in your testDB.db, this will produce the following result: tbl_name ----------
TUTORIALS POINT Simply Easy Learning COMPANY You can list down complete information about COMPANY table as follows: sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY'; Assuming you have only COMPANY table in your testDB.db, this will produce the following result: CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL )
TUTORIALS POINT Simply Easy Learning SQLite Operators What is an operator in SQLite? An operator is a reserved word or a character used primarily in an SQLite statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. Operators are used to specify conditions in an SQLite statement and to serve as conjunctions for multiple conditions in a statement.  Arithmetic operators  Comparison operators  Logical operators  Bitwise operators SQLite Arithmetic Operators: Assume variable a holds 10 and variable b holds 20, then: Operator Description Example + Addition - Adds values on either side of the operator a + b will give 30 - Subtraction - Subtracts right hand operand from left hand operand a - b will give -10 * Multiplication - Multiplies values on either side of the operator a * b will give 200 / Division - Divides left hand operand by right hand operand b / a will give 2 % Modulus - Divides left hand operand by right hand operand and returns remainder b % a will give 0 CHAPTER 13
TUTORIALS POINT Simply Easy Learning Example Here are simple examples showing usage of SQLite Arithmetic Operators: sqlite> .mode line sqlite> select 10 + 20; 10 + 20 = 30 sqlite> select 10 - 20; 10 - 20 = -10 sqlite> select 10 * 20; 10 * 20 = 200 sqlite> select 10 / 5; 10 / 5 = 2 sqlite> select 12 % 5; 12 % 5 = 2 SQLite Comparison Operators: Assume variable a holds 10 and variable b holds 20, then: Operator Description Example == Checks if the values of two operands are equal or not, if yes then condition becomes true. (a == b) is not true. = Checks if the values of two operands are equal or not, if yes then condition becomes true. (a = b) is not true. != Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a != b) is true. <> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a <> b) is true. > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b) is not true. < Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b) is true. >= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b) is not true. <= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b) is true. !< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true. (a !< b) is false. !> Checks if the value of left operand is not greater than the value of right operand, if yes (a !> b)
TUTORIALS POINT Simply Easy Learning then condition becomes true. is true. Example Consider COMPANY table has the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Below example will show the usage of various SQLite Comparison Operators. Here, we have used WHERE clause, which will be explained in a separate chapter but for now you can understand that WHERE clause is used to put a conditional statement along with SELECT statement. Following SELECT statement lists down all the records having SALARY greater than 50,000.00: sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records having SALARY equal to 20,000.00: sqlite> SELECT * FROM COMPANY WHERE SALARY = 20000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 Following SELECT statement lists down all the records having SALARY not equal to 20,000.00: sqlite> SELECT * FROM COMPANY WHERE SALARY != 20000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records having SALARY not equal to 20,000.00: sqlite> SELECT * FROM COMPANY WHERE SALARY <> 20000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
TUTORIALS POINT Simply Easy Learning 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records having SALARY greater than or equal to 65,000.00: sqlite> SELECT * FROM COMPANY WHERE SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 SQLite Logical Operators: Here is a list of all the logical operators available in SQLite. Operator Description AND The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. EXISTS The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria. IN The IN operator is used to compare a value to a list of literal values that have been specified. NOT IN The negation of IN operator which is used to compare a value to a list of literal values that have been specified. LIKE The LIKE operator is used to compare a value to similar values using wildcard operators. GLOB The GLOB operator is used to compare a value to similar values using wildcard operators. Also, GLOB is case sensitive, unlike LIKE. NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is negate operator. OR The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. IS NULL The NULL operator is used to compare a value with a NULL value. IS The IS operator work like = IS NOT The IS operator work like != || Adds two different strings and make new one. UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates). Example Consider COMPANY table has the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0
TUTORIALS POINT Simply Easy Learning 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Here are simple examples showing usage of SQLite Logical Operators. Following SELECT statement lists down all the records where AGE is greater than or equal to 25 and salary is greater than or equal to 65000.00: sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00: sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE is not NULL which means all the records because none of the record is having AGE equal to NULL: sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki'. sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0 Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki': sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0 Following SELECT statement lists down all the records where AGE value is either 25 or 27: sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ----------
TUTORIALS POINT Simply Easy Learning 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE value is neither 25 nor 27: sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27: sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by sub-query: sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000); AGE ---------- 32 25 23 25 27 22 24 Following SELECT statement makes use of SQL sub-query where subquery finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from outside query is greater than the age in the result returned by sub-query: sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 SQLite Bitwise Operators: Bitwise operator works on bits and perform bit-by-bit operation. The truth table for & and | is as follows: P Q p & q p | q 0 0 0 0 0 1 0 1
TUTORIALS POINT Simply Easy Learning 1 1 1 1 1 0 0 1 Assume if A = 60; and B = 13; now in binary format, they will be as follows: A = 0011 1100 B = 0000 1101 ----------------- A&B = 0000 1100 A|B = 0011 1101 ~A = 1100 0011 The Bitwise operators supported by SQLite language are listed in the following table. Assume variable A holds 60 and variable B holds 13, then: Operator Description Example & Binary AND Operator copies a bit to the result if it exists in both operands. (A & B) will give 12 which is 0000 1100 | Binary OR Operator copies a bit if it exists in either operand. (A | B) will give 61 which is 0011 1101 ~ Binary Ones Complement Operator is unary and has the effect of 'flipping' bits. (~A ) will give -60 which is 1100 0011 << Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. A << 2 will give 240 which is 1111 0000 >> Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. A >> 2 will give 15 which is 0000 1111 Example Here are simple examples showing usage of SQLite Bitwise Operators: sqlite> .mode line sqlite> select 60 | 13; 60 | 13 = 61 sqlite> select 60 & 13; 60 & 13 = 12 sqlite> select 60 ^ 13; 10 * 20 = 200 sqlite> select (~60); (~60) = -61 sqlite> select (60 << 2);
TUTORIALS POINT Simply Easy Learning (60 << 2) = 240 sqlite> select (60 >> 2); (60 >> 2) = 15
TUTORIALS POINT Simply Easy Learning SQLite Expressions An expression is a combination of one or more values, operators and SQL functions that evaluate to a value. SQL EXPRESSIONs are like formulas and they are written in query language. You can also use to query the database for specific set of data. Syntax: Consider the basic syntax of the SELECT statement as follows: SELECT column1, column2, columnN FROM table_name WHERE [CONTION | EXPRESSION]; There are different types of SQLite expressions, which are mentioned below: SQLite - Boolean Expressions: SQLite Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax: SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION; Consider COMPANY table has the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Here is simple examples showing usage of SQLite Boolean Expressions: sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000; CHAPTER 14
TUTORIALS POINT Simply Easy Learning ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 James 24 Houston 10000.0 SQLite - Numeric Expression: These expressions are used to perform any mathematical operation in any query. Following is the syntax: SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ; Here, numerical_expression is used for mathematical expression or any formula. Following is a simple example showing usage of SQLite Numeric Expressions: sqlite> SELECT (15 + 6) AS ADDITION ADDITION = 21 There are several built-in functions like avg(), sum(), count(), etc., to perform what is known as aggregate data calculations against a table or a specific table column. sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; RECORDS = 7 SQLite - Date Expressions: Date Expressions return current system date and time values and these expressions will be used in various data manipulations. sqlite> SELECT CURRENT_TIMESTAMP; CURRENT_TIMESTAMP = 2013-03-17 10:43:35
TUTORIALS POINT Simply Easy Learning SQLite Where Clause The SQLite WHERE clause is used to specify a condition while fetching the data from one table or multiple tables. If the given condition is satisfied, means true, then it returns specific value from the table. You would use WHERE clause to filter the records and fetching only necessary records. The WHERE clause not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which we would study in subsequent chapters. Syntax: The basic syntax of SQLite SELECT statement with WHERE clause is as follows: SELECT column1, column2, columnN FROM table_name WHERE [condition] Example: You can specify a condition using Comparision or Logical Operators like >, <, =, LIKE, NOT, etc. Consider COMPANY table has the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Here are simple examples showing usage of SQLite Logical Operators. Following SELECT statement lists down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00: sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 CHAPTER 15
TUTORIALS POINT Simply Easy Learning 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00: sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE is not NULL which means all the records because none of the record is having AGE equal to NULL: sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki'. sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0 Following SELECT statement lists down all the records where NAME starts with 'Ki', does not matter what comes after 'Ki': sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0 Following SELECT statement lists down all the records where AGE value is either 25 or 27: sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement lists down all the records where AGE value is neither 25 nor 27: sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0
TUTORIALS POINT Simply Easy Learning 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where AGE value is in BETWEEN 25 AND 27: sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by sub-query: sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000); AGE ---------- 32 25 23 25 27 22 24 Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from outside query is greater than the age in the result returned by sub-query: sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
TUTORIALS POINT Simply Easy Learning SQLite AND and OR Operator The SQLite AND and OR operators are used to combine multiple conditions to narrow down selected data in an SQLite statement. These two operators are called conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQLite statement. The AND Operator: The AND operator allows the existence of multiple conditions in an SQLite statement's WHERE clause. While using AND operator, complete condition will be assumed true when all the conditions are true. For example, [condition1] AND [condition2] will be true only when both condition1 and condition2 are true. Syntax: The basic syntax of AND operator with WHERE clause is as follows: SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN]; You can combine N number of conditions using AND operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE. Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where AGE is greater than or equal to 25 ANDsalary is greater than or equal to 65000.00: sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; CHAPTER 16
TUTORIALS POINT Simply Easy Learning ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 The OR Operator: The OR operator is also used to combine multiple conditions in an SQLite statement's WHERE clause. While using OR operator, complete condition will be assumed true when at least any of the conditions is true. For example, [condition1] OR [condition2] will be true if either condition1 or condition2 is true. Syntax: The basic syntax of OR operator with WHERE clause is as follows: SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN] You can combine N number of conditions using OR operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE. Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where AGE is greater than or equal to 25 ORsalary is greater than or equal to 65000.00: sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
TUTORIALS POINT Simply Easy Learning SQLite Update Query The SQLite UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows, otherwise all the rows would be updated. Syntax: The basic syntax of UPDATE query with WHERE clause is as follows: UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition]; You can combine N number of conditions using AND or OR operators. Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which would update ADDRESS for a customer whose ID is 6: sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6; Now, COMPANY table would have the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 CHAPTER 17
TUTORIALS POINT Simply Easy Learning 6 Kim 22 Texas 45000.0 7 James 24 Houston 10000.0 If you want to modify all ADDRESS and SALARY column values in COMPANY table, you do not need to use WHERE clause and UPDATE query would be as follows: sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00; Now, COMPANY table will have the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 Texas 20000.0 2 Allen 25 Texas 20000.0 3 Teddy 23 Texas 20000.0 4 Mark 25 Texas 20000.0 5 David 27 Texas 20000.0 6 Kim 22 Texas 20000.0 7 James 24 Texas 20000.0
TUTORIALS POINT Simply Easy Learning SQLite Delete Query The SQLite DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted. Syntax: The basic syntax of DELETE query with WHERE clause is as follows: DELETE FROM table_name WHERE [condition]; You can combine N number of conditions using AND or OR operators. Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which would DELETE a customer whose ID is 7: sqlite> DELETE FROM COMPANY WHERE ID = 7; Now, COMPANY table will have the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 CHAPTER 18
TUTORIALS POINT Simply Easy Learning 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 If you want to DELETE all the records from COMPANY table, you do not need to use WHERE clause with DELETE query, which would be as follows: sqlite> DELETE FROM COMPANY; Now, COMPANY table does not have any record because all the records have been deleted by DELETE statement.
TUTORIALS POINT Simply Easy Learning SQLite Like Clause The SQLite LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. There are two wildcards used in conjunction with the LIKE operator:  The percent sign (%)  The underscore (_) The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations. Syntax: The basic syntax of % and _ is as follows: SELECT FROM table_name WHERE column LIKE 'XXXX%' or SELECT FROM table_name WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE column LIKE 'XXXX_' or SELECT FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM table_name WHERE column LIKE '_XXXX_' You can combine N number of conditions using AND or OR operators. Here XXXX could be any numeric or string value. CHAPTER 19
TUTORIALS POINT Simply Easy Learning Example: Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators: Statement Description WHERE SALARY LIKE '200%' Finds any values that start with 200 WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length WHERE SALARY LIKE '%2' Finds any values that end with 2 WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3 WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3 Let us take a real example, consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which would display all the records from COMPANY table where AGE starts with 2: sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%'; This would produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which would display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text: sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%'; This would produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0
TUTORIALS POINT Simply Easy Learning
TUTORIALS POINT Simply Easy Learning SQLite Glob Clause The SQLite GLOB operator is used to match only text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the GLOB operator will return true, which is 1. Unlike LIKE operator, GLOB is case sensitive and it follows syntax of UNIX for specifying the following wildcards.  The asterisk sign (*)  The question mark (?) The asterisk sign represents zero or multiple numbers or characters. The ? represents a single number or character. Syntax: The basic syntax of * and ? is as follows: SELECT FROM table_name WHERE column GLOB 'XXXX*' or SELECT FROM table_name WHERE column GLOB '*XXXX*' or SELECT FROM table_name WHERE column GLOB 'XXXX?' or SELECT FROM table_name WHERE column GLOB '?XXXX' or SELECT FROM table_name WHERE column GLOB '?XXXX?' or CHAPTER 20
TUTORIALS POINT Simply Easy Learning SELECT FROM table_name WHERE column GLOB '????' You can combine N number of conditions using AND or OR operators. Here XXXX could be any numberic or string value. Example: Here are number of examples showing WHERE part having different LIKE clause with '*' and '?' operators: Statement Description WHERE SALARY GLOB '200*' Finds any values that start with 200 WHERE SALARY GLOB '*200*' Finds any values that have 200 in any position WHERE SALARY GLOB '?00*' Finds any values that have 00 in the second and third positions WHERE SALARY GLOB '2??' Finds any values that start with 2 and are at least 3 characters in length WHERE SALARY GLOB '*2' Finds any values that end with 2 WHERE SALARY GLOB '?2*3' Finds any values that have a 2 in the second position and end with a 3 WHERE SALARY GLOB '2???3' Finds any values in a five-digit number that start with 2 and end with 3 Let us take a real example, consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which would display all the records from COMPANY table where AGE starts with 2: sqlite> SELECT * FROM COMPANY WHERE AGE GLOB '2*'; This would produce following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which would display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text: sqlite> SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*'; This would produce the following result:
TUTORIALS POINT Simply Easy Learning ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0
TUTORIALS POINT Simply Easy Learning SQLite LIMIT Clause The SQLite LIMIT clause is used to limit the data amount returned by the SELECT statement. Syntax: The basic syntax of SELECT statement with LIMIT clause is as follows: SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] Following is the syntax of LIMIT clause when it is used along with OFFSET clause: SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num] SQLite engine will return rows starting from the next row to the given OFFSET as shown below in the last example. Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which limits the row in the table according to the number of rows you want to fetch from table: sqlite> SELECT * FROM COMPANY LIMIT 6; This would produce the following result: CHAPTER 21
TUTORIALS POINT Simply Easy Learning ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 But in certain situations, you may need to pick up a set of records from a particular offset. Here is an example, which picks up 3 records starting from 3rd position: sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2; This would produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
TUTORIALS POINT Simply Easy Learning SQLite Order By Clause The SQLite ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Syntax: The basic syntax of ORDER BY clause is as follows: SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be available in column-list. Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which would sort the result in descending order by SALARY: sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC; This would produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 CHAPTER 22
TUTORIALS POINT Simply Easy Learning 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following is an example, which would sort the result in descending order by NAME and SALARY: sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC; This would produce following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 5 David 27 Texas 85000.0 7 James 24 Houston 10000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 Following is an example, which would sort the result in descending order by NAME: sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC; This would produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 3 Teddy 23 Norway 20000.0 1 Paul 32 California 20000.0 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 5 David 27 Texas 85000.0 2 Allen 25 Texas 15000.0
TUTORIALS POINT Simply Easy Learning SQLite Group By Clause The SQLite GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. Syntax: The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used. SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN You can use more than one column in the GROUP BY clause. Make sure whatever column you are using to group, that column should be available in column-list. Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 If you want to know the total amount of salary on each customer, then GROUP BY query would be as follows: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME; This would produce following result: CHAPTER 23
TUTORIALS POINT Simply Easy Learning NAME SUM(SALARY) ---------- ----------- Allen 15000.0 David 85000.0 James 10000.0 Kim 45000.0 Mark 65000.0 Paul 20000.0 Teddy 20000.0 Now, let us create three more records in COMPANY table using the following INSERT statements: INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 ); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 ); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 ); Now, our table has the following records with duplicate names: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0 Again, let us use the same statement to group-by all the records using NAME column as follows: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME; This would produce the following result: NAME SUM(SALARY) ---------- ----------- Allen 15000 David 85000 James 20000 Kim 45000 Mark 65000 Paul 40000 Teddy 20000 Let us use ORDER BY clause along with GROUP BY clause as follows: sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC; This would produce the following result: NAME SUM(SALARY) ---------- ----------- Teddy 20000 Paul 40000 Mark 65000 Kim 45000
TUTORIALS POINT Simply Easy Learning James 20000 David 85000 Allen 15000
TUTORIALS POINT Simply Easy Learning SQLite Having Clause The HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. Syntax: The following is the position of the HAVING clause in a SELECT query: SELECT FROM WHERE GROUP BY HAVING ORDER BY The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause: SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2 Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 CHAPTER 24
TUTORIALS POINT Simply Easy Learning 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0 Following is the example, which would display record for which name count is less than 2: sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2; This would produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000 5 David 27 Texas 85000 6 Kim 22 South-Hall 45000 4 Mark 25 Rich-Mond 65000 3 Teddy 23 Norway 20000 Following is the example, which would display record for which name count is greater than 2: sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2; This would produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 10 James 45 Texas 5000
TUTORIALS POINT Simply Easy Learning SQLite Distinct Keyword The SQLite DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records. There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. Syntax: The basic syntax of DISTINCT keyword to eliminate duplicate records is as follows: SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition] Example: Consider COMPANY table is having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0 First, let us see how the following SELECT query returns duplicate salary records: sqlite> SELECT name FROM COMPANY; This would produce the following result: NAME ---------- CHAPTER 25
TUTORIALS POINT Simply Easy Learning Paul Allen Teddy Mark David Kim James Paul James James Now, let us use DISTINCT keyword with the above SELECT query and see the result: sqlite> SELECT DISTINCT name FROM COMPANY; This would produce the following result, where we do not have any duplicate entry: NAME ---------- Paul Allen Teddy Mark David Kim James
TUTORIALS POINT Simply Easy Learning SQLite PRAGMA The SQLite PRAGMA command is a special command to be used to control various environmental variables and state flags within the SQLite environment. A PRAGMA value can be read and it can also be set based on requirements. Syntax: To query the current PRAGMA value, just provide the name of the pragma: PRAGMA pragma_name; To set a new value for PRAGMA, you will use the following syntax: PRAGMA pragma_name = value; The set mode can be either the name or the integer equivalent but the returned value will always be an integer. auto_vacuum Pragma The auto_vacuum pragma gets or sets the auto-vacuum mode. Following is the simple syntax: PRAGMA [database.]auto_vacuum; PRAGMA [database.]auto_vacuum = mode; Where mode can be any of the following: Pragma Value Description 0 or NONE Auto-vacuum is disabled. This is default mode which means that a database file will never shrink in size unless it is manually vacuumed using the VACUUM command. 1 or FULL Auto-vacuum is enabled and fully automatic which allows a database file to shrink as data is removed from the database. 2 or INCREMENTAL Auto-vacuum is enabled but must be manually activated. In this mode the reference data is maintained, but free pages are simply put on the free list. These pages can be recovered using the incremental_vacuum pragmaany time. CHAPTER 26
TUTORIALS POINT Simply Easy Learning cache_size Pragma The cache_size pragma can get or temporarily set the maximum size of the in-memory page cache. Following is the simple syntax: PRAGMA [database.]cache_size; PRAGMA [database.]cache_size = pages; The pages value represents the number of pages in the cache. The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages. case_sensitive_like Pragma The case_sensitive_like pragma controls the case-sensitivity of the built-in LIKE expression. By default, this pragma is false which means that the built-in LIKE operator ignores letter case. Following is the simple syntax: PRAGMA case_sensitive_like = [true|false]; There is no way to query for the current state of this pragma. count_changes Pragma The count_changes pragma gets or sets the return value of data manipulation statements such as INSERT, UPDATE and DELETE. Following is the simple syntax: PRAGMA count_changes; PRAGMA count_changes = [true|false]; By default, this pragma is false and these statements do not return anything. If set to true, each of the mentioned statement will return an one-column, one-row table consisting of a single integer value indicating impacted rows by the operation. database_list Pragma The database_list pragma will be used to list down all the databases attached. Following is the simple syntax: PRAGMA database_list; This pragma will return a three-column table with one row per open or attached database giving database sequence number, its name and file associated. encoding Pragma The encoding pragma controls how strings are encoded and stored in a database file. Following is the simple syntax: PRAGMA encoding; PRAGMA encoding = format; The format value can be one of UTF-8, UTF-16le, or UTF-16be. freelist_count Pragma The freelist_count pragma returns a single integer indicating how many database pages are currently marked as free and available. Following is the simple syntax: PRAGMA [database.]freelist_count;
TUTORIALS POINT Simply Easy Learning The format value can be one of UTF-8, UTF-16le, or UTF-16be. index_info Pragma The index_info pragma returns information about a database index. Following is the simple syntax: PRAGMA [database.]index_info( index_name ); The result set will contain one row for each column contained in the index giving column sequence, column index within table and column name. index_list Pragma The index_list pragma lists all of the indexes associated with a table. Following is the simple syntax: PRAGMA [database.]index_list( table_name ); The result set will contain one row for each index giving index sequence, index name and flag indicating whether index is unique or not. journal_mode Pragma The journal_mode pragma gets or sets the journal mode which controls how the journal file is stored and processed. Following is the simple syntax: PRAGMA journal_mode; PRAGMA journal_mode = mode; PRAGMA database.journal_mode; PRAGMA database.journal_mode = mode; There are five supported journal modes: Pragma Value Description DELETE This is default mode. Here at the conclusion of a transaction, the journal file is deleted. TRUNCATE The journal file is truncated to a length of zero bytes. PERSIST The journal file is left in place, but the header is overwritten to indicate the journal is no longer valid. MEMORY The journal record is held in memory, rather than on disk. OFF No journal record is kept. max_page_count Pragma The max_page_count pragma gets or sets the maximum allowed page count for a database. Following is the simple syntax: PRAGMA [database.]max_page_count; PRAGMA [database.]max_page_count = max_page; The default value is 1,073,741,823 which is one giga-page which means if the default 1 KB page size, this allows databases to grow up to one terabyte.
TUTORIALS POINT Simply Easy Learning page_count Pragma The page_count pragma returns the current number of pages in database. Following is the simple syntax: PRAGMA [database.]page_count; The size of the database file should be page_count * page_size. page_size Pragma The page_size pragma gets or sets the size of the database pages. Following is the simple syntax: PRAGMA [database.]page_size; PRAGMA [database.]page_size = bytes; By default, the allowed sizes are 512, 1024, 2048, 4096, 8192, 16384, and 32768 bytes. The only way to alter the page size on an existing database is to set the page size and then immediately VACUUM the database. parser_trace Pragma The parser_trace pragma controls printing the debugging state as it parses SQL commands. Following is the simple syntax: PRAGMA parser_trace = [true|false]; By default, it is set to false but when enabled by setting it to true, the SQL parser will print its state as it parses SQL commands. recursive_triggers Pragma The recursive_triggers pragma gets or sets the recursive trigger functionality. If recursive triggers are not enabled, a trigger action will not fire another trigger. Following is the simple syntax: PRAGMA recursive_triggers; PRAGMA recursive_triggers = [true|false]; schema_version Pragma The schema_version pragma gets or sets the schema version value that is stored in the database header. Following is the simple syntax: PRAGMA [database.]schema_version; PRAGMA [database.]schema_version = number; This is a 32-bit signed integer value that keeps track of schema changes. Whenever a schema-altering command is executed (like, CREATE... or DROP...), this value is incremented. secure_delete Pragma The secure_delete pragma is used to control how content is deleted from the database. Following is the simple syntax: PRAGMA secure_delete; PRAGMA secure_delete = [true|false]; PRAGMA database.secure_delete; PRAGMA database.secure_delete = [true|false];
TUTORIALS POINT Simply Easy Learning The default value for the secure delete flag is normally off, but this can be changed with the SQLITE_SECURE_DELETE build option. sql_trace Pragma The sql_trace pragma is used to dump SQL trace results to the screen. Following is the simple syntax: PRAGMA sql_trace; PRAGMA sql_trace = [true|false]; SQLite must be compiled with the SQLITE_DEBUG directive for this pragma to be included. synchronous Pragma The synchronous pragma gets or sets the current disk synchronization mode which controls how aggressively SQLite will write data all the way out to physical storage. Following is the simple syntax: PRAGMA [database.]synchronous; PRAGMA [database.]synchronous = mode; SQLite supports the following synchronisation modes: Pragma Value Description 0 or OFF No syncs at all 1 or NORMAL Sync after each sequence of critical disk operations 2 or FULL Sync after each critical disk operation temp_store Pragma The temp_store pragma gets or sets the storage mode used by temporary database files. Following is the simple syntax: PRAGMA temp_store; PRAGMA temp_store = mode; SQLite supports the following storage modes: Pragma Value Description 0 or DEFAULT Use compile-time default. Normally FILE. 1 or FILE Use file-based storage. 2 or MEMORY Use memory-based storage. temp_store_directory Pragma The temp_store_directory pragma gets or sets the location used for temporary database files. Following is the simple syntax: PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory_path';
TUTORIALS POINT Simply Easy Learning user_version Pragma The user_version pragma gets or sets the user-defined version value that is stored in the database header. Following is simple syntax: PRAGMA [database.]user_version; PRAGMA [database.]user_version = number; This is a 32-bit signed integer value, which can be set by the developer for version tracking purpose. writable_schema Pragma The writable_schema pragma gets or sets the ability to modify system tables. Following is the simple syntax: PRAGMA writable_schema; PRAGMA writable_schema = [true|false]; If this pragma is set, tables that start with sqlite_ can be created and modified, including the sqlite_master table. Be careful while using pragma because it can lead to complete database corruption.
TUTORIALS POINT Simply Easy Learning SQLite Constraints Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table. Following are commonly used constraints available in SQLite.  NOT NULL Constraint: Ensures that a column cannot have NULL value.  DEFAULT Constraint : Provides a default value for a column when none is specified.  UNIQUE Constraint: Ensures that all values in a column are different.  PRIMARY Key: Uniquely identified each rows/records in a database table.  CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. NOT NULL Constraint By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NULL is not the same as no data, rather, it represents unknown data. EXAMPLE: For example, the following SQLite statement creates a new table called COMPANY and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULLs: CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); DEFAULT Constraint The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. CHAPTER 27
TUTORIALS POINT Simply Easy Learning EXAMPLE: For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, SALARY column is set to 5000.00 by default, so in case INSERT INTO statement does not provide a value for this column, then by default, this column would be set to 5000.00. CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 ); UNIQUE Constraint The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having identical age. EXAMPLE: For example, the following SQLite statement creates a new table called COMPANY and adds five columns. Here, AGE column is set to UNIQUE, so that you can not have two records with same age: CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 ); PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique ids. We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a 'longstanding coding oversight', primary keys can be NULL in SQLite. This is not the case with other databases. A primary key is a field in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key. If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s). EXAMPLE: You already have seen various examples above where we have created COMAPNY table with ID as primary key: CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,
TUTORIALS POINT Simply Easy Learning NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); CHECK Constraint The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table. EXAMPLE: For example, the following SQLite creates a new table called COMPANY and adds five columns. Here, we add a CHECK with SALARY column, so that you can not have any SALARY Zero: CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) ); Dropping Constraints: SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.
TUTORIALS POINT Simply Easy Learning SQLite Joins The SQLite Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. SQL defines three major types of joins:  The CROSS JOIN  The INNER JOIN  The OUTER JOIN Before we proceed, let's consider two tables COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let's assume the list of records available in COMPANY table: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Another table is DEPARTMENT has the following definition: CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); Here is the list of INSERT statements to populate DEPARTMENT table: INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 ); CHAPTER 28
TUTORIALS POINT Simply Easy Learning INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 ); Finally, we have the following list of records available in DEPARTMENT table: ID DEPT EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineerin 2 3 Finance 7 The CROSS JOIN A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y columns, respectively, the resulting table will have x+y columns. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to only use them when appropriate. Following is the syntax of CROSS JOIN: SELECT ... FROM table1 CROSS JOIN table2 ... Based on the above tables, we can write a cross join as follows: sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT; Above query will produce the following result: EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Paul Engineerin 7 Paul Finance 1 Allen IT Billing 2 Allen Engineerin 7 Allen Finance 1 Teddy IT Billing 2 Teddy Engineerin 7 Teddy Finance 1 Mark IT Billing 2 Mark Engineerin 7 Mark Finance 1 David IT Billing 2 David Engineerin 7 David Finance 1 Kim IT Billing 2 Kim Engineerin 7 Kim Finance 1 James IT Billing 2 James Engineerin 7 James Finance The INNER JOIN A INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
TUTORIALS POINT Simply Easy Learning An INNER JOIN is the most common type of join and is the default type of join. You can use INNER keyword optionally. Following is the syntax of INNER JOIN: SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ... To avoid redundancy and keep the phrasing shorter, INNER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns: SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ... A NATURAL JOIN is similar to a JOIN...USING, only it automatically tests for equality between the values of every column that exists in both tables: SELECT ... FROM table1 NATURAL JOIN table2... Based on the above tables, we can write a INNER JOIN as follows: sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; Above query will produce the following result: EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineerin 7 James Finance The OUTER JOIN The OUTER JOIN is an extension of the INNER JOIN. Though SQL standard defines three types of OUTER JOINs: LEFT, RIGHT and FULL but SQLite only supports the LEFT OUTER JOIN. The OUTER JOINs have a condition that is identical to INNER JOINs, expressed using an ON, USING or NATURAL keyword. The initial results table is calculated the same way. Once the primary JOIN is calculated, an OUTER join will take any unjoined rows from one or both tables, pad them out with NULLs, and append them to the resulting table. Following is the syntax of LEFT OUTER JOIN: SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ... To avoid redundancy and keep the phrasing shorter, OUTER JOIN conditions can be declared with a USING expression. This expression specifies a list of one or more columns: SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ... Based on the above tables, we can write a inner join as follows: sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; Above query will produce the following result: EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineerin
TUTORIALS POINT Simply Easy Learning Teddy Mark David Kim 7 James Finance
TUTORIALS POINT Simply Easy Learning SQLite UNIONS Clause The SQLite UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length. Syntax: The basic syntax of UNION is as follows: SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] Here given condition could be any given expression based on your requirement. Example: The tables COMPANY and DEPARTMENT are shown here: COMPANY TABLE This is the file to create COMPANY table and to populate it with 7 records. -- Just copy and past them on sqlite> prompt. DROP TABLE COMPANY; CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); CHAPTER 29
TUTORIALS POINT Simply Easy Learning INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 ); INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 ); DEPARTMENT TABLE -- This is the file to create DEPARTMENT table and to populate it with 7 records. -- Just copy and past them on sqlite> prompt. DROP TABLE DEPARTMENT; CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (1, 'IT Billing', 1); INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (2, 'Engineering', 2); INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (3, 'Finance',7); INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (4, 'Engineering',3); INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (5, 'Finance', 4); INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (6, 'Engineering', 5); INSERT INTO DEPARTMENT(ID,DEPT,EMP_ID) VALUES (7, 'Finance', 6); Consider following two tables, (a) COMPANY table is as follows: sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
TUTORIALS POINT Simply Easy Learning 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 (b) Another table is DEPARTMENT as follows: ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6 Now, let us join these two tables using SELECT statement along with UNION clause as follows: sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This would produce the following result: EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance The UNION ALL Clause: The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator as well. Syntax: The basic syntax of UNION ALL is as follows: SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] Here given condition could be any given expression based on your requirement.
TUTORIALS POINT Simply Easy Learning Example: Now, let us join above-mentioned two tables in our SELECT statement as follows: sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID UNION ALL SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; This would produce the following result: EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance 1 Paul IT Billing 2 Allen Engineerin 3 Teddy Engineerin 4 Mark Finance 5 David Engineerin 6 Kim Finance 7 James Finance
TUTORIALS POINT Simply Easy Learning SQLite NULL Values The SQLite NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. Syntax: The basic syntax of using NULL while creating a table: SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); Here, NOT NULL signifies that column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL which means these columns could be NULL. A field with a NULL value is one that has been left blank during record creation. Example: The NULL value can cause problems when selecting data, however, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results. Consider the following table, COMPANY having the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Let us use UPDATE statement to set few nullable values as NULL as follows: CHAPTER 30
TUTORIALS POINT Simply Easy Learning sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7); Now, COMPANY table should have the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 7 James 24 Next, let us see the usage of IS NOT NULL operator to list down all the records where SALARY is not NULL: sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL; Above SQLite statement will produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following is the usage of IS NULL operator, which will list down all the records where SALARY is NULL: sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL; Above SQLite statement will produce the following result: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 7 James 24
TUTORIALS POINT Simply Easy Learning SQLite ALIAS Syntax You can rename a table or a column temporarily by giving another name, which is known as ALIAS. The use of table aliases means to rename a table in a particular SQLite statement. Renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table's columns for the purpose of a particular SQLite query. Syntax: The basic syntax of table alias is as follows: SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition]; The basic syntax of column alias is as follows: SELECT column_name AS alias_name FROM table_name WHERE [condition]; Example: Consider the following two tables, (a) COMPANY table is as follows: sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 (b) Another table is DEPARTMENT as follows: ID DEPT EMP_ID ---------- -------------------- ---------- CHAPTER 31
TUTORIALS POINT Simply Easy Learning 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6 Now, following is the usage of TABLE ALIAS where we use C and D as aliases for COMPANY and DEPARTMENT tables respectively: sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID; Above SQLite statement will produce the following result: ID NAME AGE DEPT ---------- ---------- ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance Let us see an example for the usage of COLUMN ALIAS where COMPANY_ID is an alias of ID column and COMPANY_NAME is an alias of name column: sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT FROM COMPANY AS C, DEPARTMENT AS D WHERE C.ID = D.EMP_ID; Above SQLite statement will produce the following result: COMPANY_ID COMPANY_NAME AGE DEPT ---------- ------------ ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance
TUTORIALS POINT Simply Easy Learning SQLite Triggers SQLite Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs. Following are the important points about SQLite triggers:  SQLite trigger may be specified to fire whenever a DELETE, INSERT or UPDATE of a particular database table occurs or whenever an UPDATE occurs on on one or more specified columns of a table.  At this time, SQLite supports only FOR EACH ROW triggers, not FOR EACH STATEMENT triggers. Hence, explicitly specifying FOR EACH ROW is optional.  Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form NEW.column-name and OLD.column-name, where column-name is the name of a column from the table that the trigger is associated with.  If a WHEN clause is supplied, the SQL statements specified are only executed for rows for which the WHEN clause is true. If no WHEN clause is supplied, the SQL statements are executed for all rows.  The BEFORE or AFTER keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row.  Triggers are automatically dropped when the table that they are associated with is dropped.  The table to be modified must exist in the same database as the table or view to which the trigger is attached and one must use just tablename not database.tablename.  A special SQL function RAISE() may be used within a trigger-program to raise an exception. Syntax: The basic syntax of creating a trigger is as follows: CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name BEGIN -- Trigger logic goes here.... END; Here, event_name could be INSERT, DELETE, and UPDATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name. CHAPTER 32
TUTORIALS POINT Simply Easy Learning Following is the syntax of creating a trigger on an UPDATE operation on one or more specified columns of a table as follows: CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name BEGIN -- Trigger logic goes here.... END; Example Let us consider a case where we want to keep audit trial for every record being inserted in COMPANY table, which we create newly as follows (Drop COMPANY table if you already have it): sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); To keep audit trial, we will create a new table called AUDIT where log messages will be inserted whenever there is an entry in COMPANY table for a new record: sqlite> CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL ); Here, ID is the AUDIT record ID, and EMP_ID is the ID which will come from COMPANY table and DATE will keep timestamp when the record will be created in COMPANY table. So now let's create a trigger on COMPANY table as follows: sqlite> CREATE TRIGGER audit_log AFTER INSERT ON COMPANY BEGIN INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now')); END; Now, we will start actual work, let's start inserting record in COMPANY table which should result in creating an audit log record in AUDIT table. So let's create one record in COMPANY table as follows: sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); This will create one record in COMPANY table, which is as follows: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 Same time, one record will be create in AUDIT table. This record is the result of a trigger, which we have created on INSERT operation on COMPANY table. Similar way, you can create your triggers on UPDATE and DELETE operations based on your requirements. EMP_ID ENTRY_DATE ---------- ------------------- 1 2013-04-05 06:26:00