articles

Working with SQLite database

Anonymous User6046 24-Jan-2015

Hi everyone in this article I’m explaining about how to download SQLite Database and working with SQLite database.

Description:

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code 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 features like SQL syntax, transactions and prepared statements. 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), 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

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

Working with SQLite database

Step 4: Now create a new database

Click File  >>  Connect  >>  SQLite  >>  Create

Then ask a path where are you want to save database file

Working with SQLite database

Give a suitable path for database file you found new message

Working with SQLite database

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 successfully.

Step 6: Now insert record in 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 from Student table

SELECT * FROM Student

Working with SQLite database

Step 8: Now delete record from Student table

DELETE FROM Student WHERE Id=4

Working with SQLite database

Step 9: Now update record in Student table

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

Working with SQLite database

 

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


Updated 07-Sep-2019
I am a content writter !

Leave Comment

Comments

Liked By