YouTip

# SQLite Introduction This tutorial helps you understand what SQLite is, how it differs from SQL, why you need it, and how its application database processing works. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the fastest growing database engine in terms of popularity, regardless of its size. The SQLite source code is not copyrighted. ## What is SQLite? SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a zero-configuration database, which means, unlike other databases, you do not need to configure it in the system. Like other databases, the SQLite engine is not a standalone process that can be linked to or dynamically connected to an application as needed. SQLite directly accesses its storage file. ## Why use SQLite? * No separate server process or operating system is required (serverless). * SQLite requires no configuration, meaning no installation or administration is needed. * A complete SQLite database is stored in a single, cross-platform disk file. * SQLite is very small and lightweight, less than 400KiB when fully configured, and less than 250KiB when configured with optional features omitted. * SQLite is self-contained, meaning it has 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 of the SQL92 (SQL2) standard. * SQLite is written in ANSI-C and provides a simple and easy-to-use API. * SQLite can run on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). ## History 1. 2000 -- D. Richard Hipp designed SQLite to operate programs without needing management. 2. 2000 -- In August, SQLite 1.0 was released with the GNU Database Manager. 3. 2011 -- Hipp announced adding the UNQl interface to the SQLite DB, developing UNQLite (a document-oriented database). ## SQLite Limitations The following features are not supported by SQL92 in SQLite: | Feature | Description | | --- | --- | | RIGHT OUTER JOIN | Only LEFT OUTER JOIN is implemented. | | FULL OUTER JOIN | Only LEFT OUTER JOIN is implemented. | | ALTER TABLE | Supports RENAME TABLE and ALTER TABLE's ADD COLUMN variants, but does not support DROP COLUMN, ALTER COLUMN, or ADD CONSTRAINT. | | Trigger Support | Supports FOR EACH ROW triggers, but not FOR EACH STATEMENT triggers. | | VIEWs | In SQLite, views are read-only. You cannot execute DELETE, INSERT, or UPDATE statements 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 Standard SQLite commands for interacting with relational databases are similar to SQL. Commands include CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be categorized based on their operational nature as follows: ## DDL - Data Definition Language | Command | Description | | --- | --- | | CREATE | Creates a new table, a view of a table, or another object in the database. | | ALTER | Modifies an existing database object, such as a table, in the database. | | DROP | Deletes an entire table, a view of a table, or another object in the database. | ## DML - Data Manipulation Language | Command | Description | | --- | --- | | INSERT | Creates a record. | | UPDATE | Modifies a record. | | DELETE | Deletes a record. | ## DQL - Data Query Language | Command | Description | | --- | --- | | SELECT | Retrieves certain records from one or more tables. | [](#)(#) (#)[](#)