How to Install and Use SQLite in Fedora Linux

SQLite is a versatile, lightweight, serverless, and self-contained database engine that finds applications in a variety of settings, ranging from embedded systems to mobile applications and serverless environments. In this article, we will delve into the installation and usage of SQLite on a Fedora Linux system. We will cover installation steps, how to create a database, insert data into it, and perform various database operations with detailed examples and complete code snippets.

Installing SQLite in Fedora Linux

Before we can begin, make sure you have a working Fedora Linux system. SQLite can be easily installed using the DNF package manager, the default package manager for Fedora. Follow the steps below:

Update Package Database:

It’s good practice to ensure you have the latest information on available packages by updating the package database. Open your terminal and execute the following command:

sudo dnf update

Install SQLite:

With the package database up-to-date, proceed to install SQLite by running the following command:

sudo dnf install sqlite

Output:

Installing SQLite

This command will prompt you to confirm the installation. Type ‘y’ and press Enter. SQLite is now installed on your Fedora system. You can verify the installation by checking the version:

sqlite3 --version

Output:

checking version

Creating a SQLite Database

With SQLite successfully installed, let’s create a SQLite database to work with. For this example, we will create a simple database to store information about books, including their title, author, and publication year.

Open SQLite Command-Line Interface

Open your terminal and start the SQLite command-line interface by typing:

sqlite3

Output:

Create a Database

To create a new database or open an existing one, use the .open command. Let’s create a new database called “books.db”:

.open books.db

Create a Table

In a database, you store data in tables. To create a table to store our book data, execute the following SQL command:

CREATE TABLE books ( id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER);

Verify Table Creation

To verify that the table has been created successfully, you can use the `.schema` command:

.schema

Output:

Verify table creation

This command will display the structure of the “books” table you just created.

Inserting Data into the SQLite Database

Now that you have a database and a table, it’s time to insert some data. We will add a few book records to our “books” table.

Insert Data:

Use the INSERT INTO statement to add data to the table. Here’s an example of how to add records:

INSERT INTO books (title, author, year) VALUES (‘Example1’, ‘Shivansh”, 1960);

INSERT INTO books (title, author, year) VALUES (‘1984’, ‘Rishabh’, 1949);

INSERT INTO books (title, author, year) VALUES (‘Pride’, ‘Ajay’, 1813);

Verify Data Insertion

You can verify that the data has been added by running a simple query:

SELECT * FROM books;

Output:

Verify Data Insertion

Querying Data

SQLite provides a powerful SQL interface for querying and manipulating data. Here are some example queries:

Retrieve Books by a Specific Author

To retrieve all the books written by a specific author, you can use the `SELECT` statement with a `WHERE` clause:

SELECT title FROM books WHERE author = 'Shivansh';

Output:

Retrieve Books by a Specific Author

Find Books Published After a Certain Year

To find books published after a certain year, you can use the SELECT statement with the > operator:

SELECT title, year FROM books WHERE year < 1950;

Output:

Find Books Published After a Certain Year

Count the Number of Books

If you want to count the number of books in the table, you can use the COUNT function:

SELECT COUNT(*) FROM books;

Output:

Count the Number of Books

These are just a few examples of what you can do with SQLite. The possibilities are endless when it comes to querying and manipulating data.

Updating and Deleting Data

SQLite allows you to update and delete data as well. Here’s how you can do it:

Update Data

To update information for a specific book, you can use the UPDATE statement. For example, let’s change the publication year of “1984” to 1984:

UPDATE books SET year = 1984 WHERE title = '1984';

Output:

To verify we can use command:

SELECT * FROM books;

Update Data

Delete Data:

To delete a book from the table, you can use the `DELETE` statement. Let’s remove “Pride and Prejudice”:

DELETE FROM books WHERE title = 'Pride and Prejudice';

Output:

To verify we can use command:

SELECT * FROM books;

Delete Data:

These operations allow you to maintain and keep your database up-to-date.

Conclusion

SQLite is a versatile and lightweight database engine that can be easily installed and used on a Fedora Linux system. With a basic understanding of SQL commands, you can create, manage, and interact with databases, making it a valuable tool for a wide range of applications. Whether you’re building a small-scale application or just want to explore and learn more about databases, SQLite is a great choice for Fedora users.