Introduction to SQLite

SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. It is the most used database engine in the world. It is an in-process library and its code is publicly available. It is free for use for any purpose, commercial or private. It is basically an embedded SQL database engine. Ordinary disk files can be easily read and write by SQLite because it does not have any separate server like SQL. The SQLite database file format is cross-platform so that anyone can easily copy a database between 32-bit and 64-bit systems. Due to all these features, it is a popular choice as an Application File Format.

History:
It was designed by D. Richard Hipp for the purpose of no administration required for operating a program. in August 2000. As it is very lightweight compared to others like MySql and Oracle, it is called SQLite. Different versions of SQLite are released since 2000.

Installation on Windows:
1. Visit the official website of SQLite for downloading the zip file.
2. Download that zip file.
3. Create a folder in C or D ( wherever you want ) for storing SQLite by expanding zip file.
4. Open the command prompt and set the path for the location of SQLite folder given in the previous step. After that write “sqlite3” and press enter.

You can also directly open the .exe file from the folder where you have stored the SQLite whole thing.

After clicking on the selected .exe file it will open SQLite application

Installation on Linux:



Open Terminal, type this command and enter password

sudo apt-get install sqlite3 libsqlite3-dev

sqlite3

It will automatically install and once it asks Do you want to continue (Y/N) type Y and press enter. After successful installation, we can check it by command sqlite3.
installation of sqlite3

Features of SQLite

  1. The transactions follow ACID properties i.e. atomicity, consistency, isolation, and durability even after system crashes and power failures.
  2. The configuration process is very easy, no setup or administration needed.
  3. All the features of SQL are implemented in it with some additional features like partial indexes, indexes on expressions, JSON, and common table expressions.
  4. Sometimes it is faster than the direct file system I/O.
  5. It supports terabyte-sized databases and gigabyte-sized strings and blobs.
  6. Almost all OS supports SQLite like Android, BSD, iOS, Linux, Mac, Solaris, VxWorks, and Windows (Win32, WinCE, etc. It is very much easy to port to other systems.
  7. Complete database can be stored in a single cross-platform disk file.

Applications of SQLite

  1. Due to its small code print and efficient usage of memory, it is the popular choice for the database engine in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets.
  2. It is used as an alternative for open to writing XML, JSON, CSV or some proprietary format into disk files used by the application.
  3. As it has no complication for configuration and easily stores file in an ordinary disk file, so it can be used as a database for small to medium sized websites.
  4. It is faster and accessible through a wide variety of third-party tools, so it has great application in different software platforms.

SQLite Commands
In SQLite, there are several dot commands which do not end with a semicolon(;). Here are all commands and their description:

Some DDL and DML Commands
It is same as compared to previous technology like MySQL, Oracle.

  • Creating Table:
    CREATE TABLE STUDENT(
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    FEES REAL
    );
  • Insert Command:
    INSERT INTO STUDENT (ID, NAME, AGE, ADDRESS, FEES)
    VALUES (1, 'Sunil', 28, 'Mumbai', 20000.00);
  • Drop Table:
    Drop Table Student;

Disadvantages of SQLite

  • It is only used where there is low to medium traffic requests are there.
  • The database size is restricted i.e. it is 2GB in most cases.

References :
https://www.sqlite.org/index.html
https://www.javatpoint.com



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.





Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.