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
Step 3: Verify the version of SQLite through the below command:
sqlite3 --version
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);
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);
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;
For viewing a specific value according to its id, then use the following command:
SELECT * FROM CAR WHERE id IS 2;
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.
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.
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
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.
