Getting Started with SqlLite3

In this blog post we cover the basic of sqlite for beginners to get started in sqlite once we learn some basic we move to some advance topic in later posts.

What is SqlLite ?

Sqlite is a free and open-source file based, serveless, cross-plaform relation database management system. that means you don’t need any server to store you data, the data will store locally in disk, we access this data using popular SQL language (Structured Query Language)

Where it is Used ?

As i previously mention SqlLite is cross-platform it can be used for different platform Ex: Windows, Mac, Linux, and also for Mobile

Before we exploring to Sqlite i will recommend you to download GUI based Application for Writing Sqlite queries because when we write Sqlite on shell/ command line we need to face some difficulties in our early stage of learning Once we gain some knowledge we can use command line I will recommend some free open-source SqLite editor such as SQLiteStudio or db browser . I use SQLiteStudio for this blog.

SQLite is based on RDBMS (Relational Database Management System) on RDBMS we usually store’s our data into Table’s it has coloums for attribute and row for our record’s

Let’s take a example we need to manage a Online course record like how many people enrolled specific course, name of the course they enrolled in, price of the specific course etc… How we store it / manage it

Creating our first table

Create a database named Course.db

In that database create a table called Course in this tables we add our required fields

To create a table in SQLite we specify like that:

CREATE TABLE Course(
course_id INTEGER PRIMARY KEY AUTOINCREMENT,
course_name TEXT NOT NULL,
course_price REAL,
course_duration TEXT
);

Note: SQL is not case-sensitive means we can also write both upper-case and smaller case both would be treated as same

Inserting values into this table

INSERT INTO Course (
                       course_id,
                       course_name,
                       course_price,
                       course_duration
                   )
                   VALUES (
                       1, -- Auto increment
                       'Java for beginners',
                       450.0,
                       '12-hrs'
                   ); -- Add more record's if you want simply change the course_id +1

Now we also populated our Course table the only last step is Fetching or querying the inserted data.

Querying our inserted data

SELECT * FROM Course;

This is very simple query we doesn’t filter or sort the data This will return all our records from Course table.

Now let’s understand the queries which we used in this example

Creating table :

To create a table we need to write CREATE TABLE and name of your table

and you also need to add your coloum or we can tell attribute in this example that is course_id,course_name,course_price and course_duration

In that column we also need to provide constraint all table must need at least single PRIMARY KEY that is unique for column.

Populating table:

To insert record you will use INSERT INTO your_table_name and add the column name into (this_field) Ex: INSERT INTO Course (add_your_coloumn) separated with comma , and to provide value to those coloumn add VALUES and provide value as shown in previous code

Querying table:

To simply query your record use SELECT with column name in this example i provide (*) for column name it means it fetches all the column from the table

Sorting and filtering our result

To sort our result use ORDER BY Ex:

SELECT * FROM Course ORDER By course_id DESC;

ORDER BY your_coloumn ASC or DESC or Sort Order

SELECT DISTINCT * FROM Course Where course_name LIKE 'j%' ORDER By course_name ASC LIMIT 2

This Query has multiple filters Lets understand what they really do;

DISTINCT -- It doesn't duplicate values in our rows if our coloumn has duplicate element it simply doen't include.
Where -- Conditional clause
LIKE 'j%' -- it queries data based on pattern they matching using wildcard characters: (%) for all marching query and underscore (_) for single matching query

There are also some other clause to filter our query Ex: Glob same as LIKE but it will consider case-sensitive only return that data that we provide in it another diff is glob uses (*) for all matching query and (?) for single matching query

We will cover those in that in next part

That will be all of this article see you in a next part…