Working with SQLite database
Hi everyone in this article I’m explaining about how to
download SQLite Database and working with SQLite database.
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
Step 3: After click run, your database manager is ready to use.
Step 4: Now create a new database
>> Connect >>
SQLite >> Create
Then ask a path where are you want to save database file
Give a suitable path for database file you found new
Click yes for use your database.
Step 5: Now create a table
CREATE TABLE Student
Id INTEGER PRIMARY KEY
After write this syntax execute this code and create
Step 6: Now insert record in Student table
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Kamlakar singh','email@example.com','Allahabad','1234567890','212106',5.5)
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('pawan shukla','firstname.lastname@example.org','Allahabad','1234567890','212106',5.5)
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Rohit kesharwani','email@example.com','Allahabad','1234567890','212106',5.5)
INSERT INTO Student (Name,EmailId,Address,ContactNO,ZipCode,Height) VALUES('Haider','firstname.lastname@example.org','Allahabad','1234567890','212106',5.5)
Step 7: Now fetch record from Student table
SELECT * FROM Student
Step 8: Now delete record from Student table
DELETE FROM Student WHERE Id=4
Step 9: Now update record in Student table
UPDATE Student SET Name='Kamlakar Kumar Singh', Address='Rewa' WHERE Id=1
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:
SQLite COUNT Function: The SQLite COUNT
aggregate function is used to count the number of rows in a database table.
SQLite MAX Function: The SQLite MAX
aggregate function allows us to select the highest (maximum) value for a
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
SQLite RANDOM Function: The SQLite RANDOM
function returns a pseudo-random integer between -9223372036854775808 and
SQLite ABS Function: The SQLite ABS function
returns the absolute value of the numeric argument.
SQLite UPPER Function: The SQLite UPPER
function converts a string into upper-case letters.
SQLite LOWER Function: The SQLite LOWER
function converts a string into lower-case letters.
SQLite LENGTH Function: The SQLite LENGTH
function returns the length of a string.
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