SQLite Tutorial
Advanced SQLite
SQLite Interfaces
SQLite Useful Resources
Selected Reading
- SQLite - DISTINCT Keyword
- SQLite - HAVING Clause
- SQLite - GROUP By Clause
- SQLite - ORDER By Clause
- SQLite - LIMIT Clause
- SQLite - GLOB Clause
- SQLite - LIKE Clause
- SQLite - DELETE Query
- SQLite - UPDATE Query
- SQLite - AND & OR Clauses
- SQLite - WHERE Clause
- SQLite - Expressions
- SQLite - Operators
- SQLite - SELECT Query
- SQLite - INSERT Query
- SQLite - DROP Table
- SQLite - CREATE Table
- SQLite - DETACH Database
- SQLite - ATTACH Database
- SQLite - CREATE Database
- SQLite - Data Type
- SQLite - Syntax
- SQLite - Commands
- SQLite - Installation
- SQLite - Overview
- SQLite - Home
Advanced SQLite
- SQLite - Useful Functions
- SQLite - Date & Time
- SQLite - VACUUM
- SQLite - EXPLAIN
- SQLite - Injection
- SQLite - AUTOINCREMENT
- SQLite - Subqueries
- SQLite - Transactions
- SQLite - Views
- SQLite - TRUNCATE Command
- SQLite - ALTER Command
- SQLite - INDEXED By Clause
- SQLite - Indexes
- SQLite - Triggers
- SQLite - ALIAS Syntax
- SQLite - NULL Values
- SQLite - UNIONS Clause
- SQLite - JOINS
- SQLite - Constraints
- SQLite - PRAGMA
SQLite Interfaces
SQLite Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
SQLite - Overview
SQLite - Overview
This chapter helps you understand what is SQLite, how it differs from SQL, why it is needed and the way in which it handles the apppcations Database.
SQLite is a software pbrary 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 pubpc domain.
What is SQLite?
SQLite is an in-process pbrary that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is a database, which is zero-configured, which means pke other databases you do not need to configure it in your system.
SQLite engine is not a standalone process pke other databases, you can pnk it statically or dynamically as per your requirement with your apppcation. 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 pght 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-comppant, allowing safe access from multiple processes or threads.
SQLite supports most of the query language features found in 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).
SQLite A Brief History
2000 - D. Richard Hipp designed SQLite for the purpose of no administration required for operating a program.
2000 - In August, SQLite 1.0 released with GNU Database Manager.
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 psted in the following table.
| Sr.No. | Feature & Description |
|---|---|
| 1 | RIGHT OUTER JOIN Only LEFT OUTER JOIN is implemented. |
| 2 | FULL OUTER JOIN Only LEFT OUTER JOIN is implemented. |
| 3 | ALTER TABLE The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported. |
| 4 | Trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers. |
| 5 | VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. |
| 6 | GRANT and REVOKE The only access permissions that can be appped are the normal file access permissions of the underlying operating system. |
SQLite Commands
The standard SQLite commands to interact with relational databases are similar to 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
| Sr.No. | Command & Description |
|---|---|
| 1 | CREATE Creates a new table, a view of a table, or other object in database. |
| 2 | ALTER Modifies an existing database object, such as a table. |
| 3 | DROP Deletes an entire table, a view of a table or other object in the database. |
DML - Data Manipulation Language
| Sr.No. | Command & Description |
|---|---|
| 1 | INSERT Creates a record |
| 2 | UPDATE Modifies records |
| 3 | DELETE Deletes records |
DQL - Data Query Language
| Sr.No. | Command & Description |
|---|---|
| 1 | SELECT Retrieves certain records from one or more tables |