---
title: "Working with SQLite database"  
description: "Hi everyone in this article I’m explaining about how to download SQLite Database and working with SQLite database."  
author: "Anonymous User"  
published: 2015-01-24  
updated: 2019-09-07  
canonical: https://www.mindstick.com/articles/1559/working-with-sqlite-database  
category: "database"  
tags: ["sqlite", "sqlite3"]  
reading_time: 4 minutes  

---

# Working with SQLite database

Hi everyone in this article I’m explaining about how to download [SQLite Database](https://www.mindstick.com/articles/1554/crud-operation-in-asp-dot-net-using-sqlite-database) and working with SQLite database.\

**Description:**

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional [SQL database](https://www.mindstick.com/articles/337535/connecting-to-sql-databases-ado-dot-net-essentials) engine. SQLite is the most widely deployed SQL database engine in the world. The [source code](https://www.mindstick.com/forum/23271/is-there-a-way-to-get-the-source-code-from-an-apk-file) for SQLite is in the public domain.

This article will give you quick start with SQLite and make you comfortable with SQLite programming.

## What is SQLite?

SQLite is an Open Source database. SQLite supports standard [relational database](https://www.mindstick.com/forum/159659/when-use-a-nosql-database-instead-of-a-relational-database-is-it-okay-to-use-both-on-the-same-site) features like SQL syntax, transactions and [prepared statements](https://www.mindstick.com/forum/776/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection). The database requires limited memory at runtime (approx. 250 KByte) which makes it a good candidate from being embedded into other runtimes.

SQLite supports the data types TEXT (similar to [String in Java](https://www.mindstick.com/interview/33946/how-you-would-reverse-a-string-in-java-without-using-the-stringbuilder-or-stringbuffer-classes)), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before getting saved in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.

## The sqlite3 tool:

The sqlite3 tool is a terminal based frontend to the SQLite library. It evaluates queries interactively and displays the results in multiple formats. It can also be used within scripts. It has its own set of meta commands including .tables, .load, .databases, or .dump. To get the list of all instructions, we type the .help command.

**Step 1:** Download Database.NET (An Intuitive Multiple Database Manager) from: http://fishcodelib.com/Database.htm.

**Step 2:** Now extract your zip file and run .exe file

![Working with SQLite database](https://www.mindstick.com/mindstickarticle/2ccf629f-ff21-4b1d-ae5e-80ea718ac56c/images/d69f888e-1ead-49c1-afa6-061734f0b226.png)

**Step 3:** After click run, your database manager is ready to use.

![Working with SQLite database](https://www.mindstick.com/mindstickarticle/2ccf629f-ff21-4b1d-ae5e-80ea718ac56c/images/fc51a2c9-d06d-4100-beee-4a402870f8c8.png)

**Step 4:** Now create a new database

Click File >> Connect >> SQLite >> Create

Then ask a path where are you want to save [database file](https://www.mindstick.com/forum/159377/how-to-add-edit-retrieve-data-using-local-database-file-in-microsoft-visual-studio-2012)

![Working with SQLite database](https://www.mindstick.com/mindstickarticle/2ccf629f-ff21-4b1d-ae5e-80ea718ac56c/images/185bf0a5-a4c0-4b54-8343-62c389bed4d6.png)

Give a suitable path for database file you found new message

![Working with SQLite database](https://www.mindstick.com/mindstickarticle/2ccf629f-ff21-4b1d-ae5e-80ea718ac56c/images/35d26c42-9b20-469b-80ea-a103d271c650.png)

Click yes for use your database.

**Step 5:** Now create a table

```
CREATE TABLE Student(       Id INTEGER PRIMARY KEY AUTOINCREMENT,       Name VARCHAR(100),       EmailId VARCHAR(200),       Address TEXT,       ContactNO CHAR(12),       ZipCode NVARCHAR(20),       Height REAL)
```

After write this syntax execute this code and [create table](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) successfully.

**Step 6:** Now insert record in [Student table](https://www.mindstick.com/forum/157561/what-is-the-stored-procedure-create-a-procedure-to-find-the-record-by-stu_id-from-the-student-table)

```
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Kamlakar singh','kamlakar@sample.com','Allahabad','1234567890','212106',5.5)INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('pawan shukla','pawan@sample.com','Allahabad','1234567890','212106',5.5)INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Rohit kesharwani','rohit@sample.com','Allahabad','1234567890','212106',5.5)INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Haider','haider@sample.com','Allahabad','1234567890','212106',5.5)
```

**Step 7:** Now [fetch record](https://www.mindstick.com/forum/34494/fetch-record-from-sharepoint-online-to-sql-server) from Student table

SELECT * FROM Student

![Working with SQLite database](https://www.mindstick.com/mindstickarticle/2ccf629f-ff21-4b1d-ae5e-80ea718ac56c/images/c2ceb8ce-4610-48e4-8f1f-944e2bf1fa67.png)

**Step 8:** Now delete record from Student table

```
DELETE FROM Student WHERE Id=4
```

![Working with SQLite database](https://www.mindstick.com/mindstickarticle/2ccf629f-ff21-4b1d-ae5e-80ea718ac56c/images/c8d56cf8-5a4c-48e8-b692-0e556ebfe6c7.png)

**Step 9:** Now update record in Student table

```
UPDATE Student SET Name='Kamlakar Kumar Singh', Address='Rewa' WHERE Id=1
```

![Working with SQLite database](https://www.mindstick.com/mindstickarticle/2ccf629f-ff21-4b1d-ae5e-80ea718ac56c/images/e13144ec-f8ba-42fd-a9f4-126c1f7892f8.png)

SQLite has many built-in functions for performing processing on string or numeric data. Following is the list of few useful SQLite built-in functions and all are case in-sensitive which means you can use these functions either in lower-case form or in upper-case or in mixed form.

## For more details, you can check official documentation for SQLite:

**1. SQLite COUNT Function:** The SQLite COUNT aggregate function is used to count the number of rows in a database table.

**2. SQLite MAX Function:** The SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column.

**3. SQLite MIN Function:** The SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

**4. SQLite AVG Function:** The SQLite AVG aggregate function selects the average value for certain table column.

**5. SQLite SUM Function:** The SQLite SUM aggregate function allows selecting the total for a numeric column.

**6. SQLite RANDOM Function:** The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

**7. SQLite ABS Function:** The SQLite ABS function returns the absolute value of the numeric argument.

**8. SQLite UPPER Function:** The SQLite UPPER function converts a string into upper-case letters.

**9. SQLite LOWER Function:** The SQLite LOWER function converts a string into lower-case letters.

**10. SQLite LENGTH Function:** The SQLite LENGTH function returns the length of a string.

**11. SQLite sqlite_version Function:** The SQLite sqlite_version function returns the version of the SQLite library.

In my next post i'll explain about Bootstrap Tokenfield and autocomplete

---

Original Source: https://www.mindstick.com/articles/1559/working-with-sqlite-database

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.