How to Install and Use SQLite On Ubuntu 20.04 LTS Focal Fossa

Learn the commands to install SQLite Database server on Ubuntu 20.04, 18.04, or 21.04 for creating and managing Databases using it.

What is SQLite?

In spite of the many options available for database systems, SQLite is the standard and most simple system. This free, compact, and command-line database program allows you to share data easily with others and save it in a simple format. It is a widely used database application in computers, smartphones, and other everyday applications.

Also, the SQLLite format is used for many desktop applications such as CAD programs, version control systems, record-keeping tools, financial analysis tools, media catalogs, and so forth.

This type of database engine is sometimes called an embedded database as well since you can run it as a part of another program. SQLite becomes even more powerful with the SQLite browser as it is a graphical and completely free software platform for developing and designing SQLite databases. These codes are free to use for private and commercial purposes because SQLite is part of the public domain.

Installing SQLite can be an easy task, but it requires correct knowledge. That’s why we have written this tutorial to give you in-depth details on installing and using SQLite on Ubuntu 20.04.

 

How to Install and Use SQLite On Ubuntu 20.04

Now let’s take a look at the method to install SQLite’s latest version in the Ubuntu machine.

Install SQLite On Ubuntu

Step 1: Initially, update your system as per the latest available updates:

sudo apt update

Step 2: Now you can install the SQLite (the latest version of SQLite is 3.3 while doing this tutorial):

sudo apt install sqlite3

Install SQLITE3 on Ubuntu 20.04 min

Step 3: Verify the version of SQLite through the below command:

sqlite3 --version

Check SQLITE Version min

Create an SQLite Database

Tables are the building blocks of SQLLite databases, and they store data. Let’s make a “CAR” database with a table that has different columns for different data:

  • An ID
  • Name
  • Car Type
  • Pricing

Now, execute this command in order to create the database file:

sqlite <database file name>.db

You will see that the prompt will change to sqlite> after executing the command. Here we created a database named “CAR.db.”

To insert the values into the database file, follow these steps:

CREATE TABLE <database name> (id integer NOT NULL, name text NOT NULL, cartype text NOT NULL, pricing Integer NOT NULL);

Create SQlite Database on Ubuntu min

 

To insert values into a table, the general form is:

INSERT INTO tablename VALUES(different values);

In the above command line, tablename is the table’s name, and values are enclosed in parentheses.

Now we can insert rows of the VALUES into the CAR table:

INSERT INTO CAR VALUES (1, "Suzuki", "SUV", 1200000);
INSERT INTO CAR VALUES (2, "Ford", "Sedan", 1800000);
INSERT INTO CAR VALUES (3, "BMW", "Sports Car", 2200000);

Insert Command Database Table min

The NOT NULL markings are applied to every variable, so every row must contain some values.

Read an SQLite Database

With “SELECT” and “*”(all) in the terminal, you can access the table:

SELECT * FROM CAR;

Read SQlite Database value min

For viewing a specific value according to its id, then use the following command:

SELECT * FROM CAR WHERE id IS 2;

Select Database value min

 

Update an SQLite Database

Use the ALTER TABLE command to change the table values. This command allows an individual to add or modify columns in a table.

ALTER TABLE CAR ADD COLUMN pricing integer;

The “UPDATE” command is used for changing the values of a table.  So use the below command:

UPDATE CAR SET Pricing = 1272000 WHERE id=1;
UPDATE CAR SET Pricing = 1970000 WHERE id=2;
UPDATE CAR SET Pricing = 2400000 WHERE id=3;
1|Suzuki|SUV|1272000
2|Ford |Sedan|1970000
3|BMW|Sports Car|2400000

 

Delete Information from SQLite Database

As the argument evolves, we can delete the values from SQLite. We are deleting values whose pricing is less than 1500000 in this example.

DELETE FROM CAR WHERE Pricing <=1500000;

SQLite’s first row was deleted by the above command.

Delete Information from SQLite Database min

 

Join Information from SQLite

We can join the information of different through the following join commands:

  • INNER JOIN
  • LEFT JOIN
  • OUTER JOIN
  • CROSS JOIN

Now, we will create a new table and then execute the INNER JOIN for joining the data.

Join Information from SQLite min

 

As you can see in the image above, we have created an availability table. To join the “CAR” and “Availability” tables, we will use the following command:

SELECT * FROM CAR INNER JOIN Availability on CAR. id = Availability. Id
1|Suzuki|Suzuki|SUV|1272000|1|No

 

Uninstall SQLite On Ubuntu 20.04

You can execute the following command to uninstall SQLite:

sudo apt --purge remove sqlite3

Uninstall SQLite On Ubuntu 20.04 min

 

Wrapping Up

SQLite is a powerful tool when creating database programs in Linux. We have covered the creation, use, and modification of databases using SQLite on Linux in our tutorial. Hopefully, this tutorial has been helpful to you. If so, please visit more on our website.

 

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.