sqlite tutorialSQLite is a RDBMS (Relational Database Management System) similar to MySQL or Oracle. It has all the features of relational database engine including normalization. It’s lightweight and one of the most often used database engine across the globe, particularly with mobile applications.

Learn how to build a shopping cart using SQLite at Udemy.com.

Why Use SQLite?

SQLite is a database engine designed with an objective to keep database design and management as simple as possible. SQLite is easier to administer with no overhead, easy to operate and embed in an application, simpler to maintain, and extremely efficient in terms of resource usage and customization. The following are some of the distinguishing attributes of SQLite Database.

With the simplicity of SQLite, reliability is also there because of less complication involved in the implementation. To achieve this simplicity, SQLite removed some characteristics that include a set of built-in functions, stored procedure, XML and Java extensions and full-gained access control. Therefore, SQLite is not designed to replace an enterprise database engine like Oracle.

When to Use SQLite?

Due to the small size of SQLite code structure that makes it memory efficient, it’s widely used in mobile application development owing to the limitations of memory and storage on hand held devices. It  is also preferable to use SQLite in small to medium size websites since there is no need for complex configuration and maintenance, which also makes SQLite a replacement for an enterprise database engine for testing and early launch of an application.

SQLite Commands

SQLite commands interact with the relational database in a way similar to SQL. There are three groups of these commands based on the category of operations they perform. These are Data Definition Language (CREATE, ALTER and DROP), Data Manipulation Language (INSERT, UPDATE and DELETE) and Data Query Language (SELECT).

SQLite Data Types

Unlike SQL, SQLite uses a dynamic type system. In SQL, the data type of  a value is determined by its column data type in a table. In contrast, the data type of value is related to the value itself.

Every value stored in a SQLite database is associated with one of five storage classes.

SQLite uses the concept of ‘type affinity’ on columns of a table that reduces the compatibility issues with other database engines. Any column can store any type of data, but the recommended storage class for a column is called its affinity. These type affinities are ‘TEXT’, ‘NUMERIC’, ‘INTEGER’, ‘REAL’ and ‘NONE’. The following is the details of the data types that are used to create the database table and their affinities.

From the above list of affinities, it is clear that there is no specified data type available for date-time in SQLite, but by default, these values are saved as numeric data (number of seconds since the Jan 01, 1970 midnight). Sometimes, a text string is also used to save the date-time. Same goes for a Boolean where true and false are stored as 1 and 0 respectively in a column.

Learn more about database management and design at Udemy.com.

SQLite CRUD Statement

SQLite statements start with keywords such as CREATE, INSERT, UPDATE, DELETE, SELECT, ALTER, DROP, etc. and is case insensitive. CRUD (Create, Read, Update and Delete) are commonly used operations in every database engine. To use CRUD, a SQLite database is required that is created using a  ‘sqlite3’ command (need precompiled binaries of SQLite with a folder path set in PATH environment variable) followed by ‘DatabaseName.db’ in a command prompt.

The ‘CREATE’ statement is used to create new tables in SQLite database. Basic syntax and a simple example of ‘CREATE’ ‘TABLE’ are as follow.

Create Table Statement Syntax:

CREATE TABLE DATABASE_NAME. TABLE_NAME (

column1 datatype,

column2 datatype,

columnN datatype,

PRIMARY KEY (one or more columns)

);

Create EMPLOYEE Table:

sqlite3> CREATE TABLE COMPANY.EMPLOYEE (

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

);

The ‘INSERT INTO’ statement inserts new rows in a table and ‘UPDATE’ is used to modify the existing rows based on a certain criteria (‘WHERE’ clause). Following is the SQLite syntax and examples for these statements.

Insert Into Statement Syntax:

INSERT INTO TABLE_NAME (column1, column2… columnN) VALUES (value1, value2…

valueN);

Update Statement Syntax:

UPDATE TABLE_NAME SET column1=value1, column2=value2 … columnN=valueN [where

criteria_To_Update_Row];

Insert 1 row in EMPLOYEE Table:

INSERT INTO EMPLOYEE (ID, NAME, AGE) VALUES (1, ‘John’, 22);

Update 1 row in EMPLOYEE Table:

UPDATE EMPLOYEE SET AGE = 21 WHERE ID = 1;

The ‘SELECT’ statement retrieves the specific data of specific rows in table. Finally ‘DELETE’ removes the particular rows based on criteria or all the rows in table. Following example demonstrates the syntax of these statements in SQLite.

Select Statement Syntax:

SELECT column1, column2, columnN FROM TABLE_NAME [where criteria_To_Retrieve_Row];

Delete Statement Syntax:

DELETE FROM TABLE_NAME [where criteria_To_Delete_Row];

Select All EMPLOYEE’s Data with AGE greater than 25:

SELECT * FROM EMPLOYEE WHERE AGE > 25;

Delete All EMPLOYEE’s Data:

DELETE FROM EMPLOYE;

Learn more about relational databases by taking a course at Udemy.com.

SQLite students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo