SQLite is a library that is serverless, zero-configuration, transactional SQL database engine. SQLite engine is not a standalone process like other databases. SQLite accesses its storage files directly.
Following are the reasons why SQLite is preferred:
● SQLite is very small and light weight.
● It does not require a separate server process to operate.
● It comes with zero-configuration, which means no initial setup is needed.
● SQLite supports most of the query language features.
● It has easy to use API.
● It can be easily integrated with different operating systems.
No, we cant alter column of a table in SQLite. This is not supported in SQLite.
No, we cant have UPDATE statement on a view. Views are read only in SQLite.
No, only left outer join is possible in SQLite.
The process stops after hitting an error.
It show names of all indices. If TABLE specified, only show indices for tables matching LIKE pattern TABLE.
It load an extension library.
It show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE.
Following commands can be used to format the output in SQLite:
sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite>
It is the master table that holds the key information about your database tables.
sqlite>sqlite>.schema sqlite_master
No, SQLite is case insensitive but there are few commands which are case sensitive, Example GLOB and glob both have different meaning in SQLite.
Here is the command which is used to analyze database in SQLite:
ANALYZE; or ANALYZE database_name; or ANALYZE database_name.table_name;
Here is the command:
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
Suppose there is the system with multiple databases and that system can only use single database at a time, then in such cases ATTACH DATABASE is used to select particular database.
Here is the command:
BEGIN; or BEGIN EXCLUSIVE TRANSACTION;
Here is the command:
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);
Here is the command:
CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; .... END;
Here is the command:
CREATE VIEW database_name.view_name AS SELECT statement.;
Here is the command:
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log ); or CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
Here is the command:
DETACH DATABASE 'Alias-Name';
DETACH DATABASE statement is used to detach and dissociate a named database from a database connection which was previously attached using ATTACH statement.
Here is the command:
DROP INDEX database_name.index_name;
Here is the command:
DROP INDEX database_name.view_name;
Here is the command:
SELECT column1, column2....columnN FROM table_name WHERE column_name GLOB { PATTERN };
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.
Here is the command:
PRAGMA pragma_name; For example: PRAGMA page_size; PRAGMA cache_size = 1024; PRAGMA table_info(table_name);
Here is the command:
REINDEX collation_name; REINDEX database_name.index_name; REINDEX database_name.table_name;
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.
sqlite3 command is used to create a new SQLite database. Here is the basic syntax:
$sqlite3 DatabaseName.db
In SQLite, database can be exported in text file using .dump dot command. Here is the syntax:
$sqlite3 testDB.db .dump > testDB.sql
Different type of operators are:
● Arithmetic operators
● Comparison operators
● Logical operators
● Bitwise operators
An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.
sqlite> SELECT (15 + 6) AS ADDITION ADDITION = 21
SQLite LIMIT clause is used to limit the data amount returned by the SELECT statement.
Here is the syntax:
sqlite>SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
In case of OFFSET clause here is the syntax:
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
This clause is used to sort the data in an ascending or descending order, based on one or more columns.
Here is the syntax:
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
This clause is used in collaboration with the SELECT statement to arrange identical data into groups.
GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Here is the syntax:
SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN;
It enables 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 GROUP BY clause.
Here is the syntax:
SELECT FROM WHERE GROUP BY HAVING ORDER BY