Simple SQLite Exercise: Creating and Querying a Books Database

Simple SQLite Exercise: Creating and Querying a Books Database

Introduction

This tutorial will guide you through creating a simple SQLite database to store information about books. We'll cover installation, creating tables, inserting data, and performing basic queries. 

Prerequisites

For this tutorial, you'll need the following:

• Access to a Linux system (local or remote)

• Basic familiarity with command-line operations

Step 1: Logging into Your Linux System

If you're using a local Linux system, open your terminal. For remote access, use SSH:

$ ssh username@your_server_ip

Replace `username` and `your_server_ip` with your actual credentials.

Step 2: Verifying SQLite Installation

To check if SQLite is available on your system, run:

$ sqlite3 --version

You should see output similar to this:

3.32.3 2020-06-18 14:16:19 4d9f39d7b8a276c7b091aa4d9d1fbfa7addbf9330ed5802f58d3b3d7a302909c

If SQLite is not installed, you can install it using your distribution's package manager. For example, on Ubuntu or Debian:

$ sudo apt-get update

$ sudo apt-get install sqlite3

Step 3: Creating and Opening a SQLite Database

To create a new database file or open an existing one, use the following command:

$ sqlite3 books.db

You should see output similar to this:

SQLite version 3.32.3 2020-06-18 14:16:19

Enter ".help" for usage hints.

sqlite>

This SQLite prompt indicates that you've successfully created or opened the `books.db` database.

Important note about existing databases:

• If `books.db` already exists, SQLite will open the existing file without overwriting it.

• Any existing tables, data, and structure in the file will remain intact.

• New commands you execute will modify or add to the existing database.

• If you run this exercise multiple times, be aware that trying to create a table that already exists will result in an error, and inserting data multiple times will add duplicate entries unless you've set up constraints to prevent this.

If you want to start with a fresh database each time, you can delete the existing books.db file before running SQLite, or use a different filename. To delete the existing file, exit SQLite if you're in it, and then use this command at your regular Linux prompt:

$ rm books.db

After deleting the file, you can start a fresh SQLite session with a new books.db file using the sqlite3 books.db command as before.

Alternatively, you can use a different filename each time, like sqlite3 books_1.db, sqlite3 books_2.db, etc.

Step 4: Creating Tables

Now that we're in the SQLite prompt, let's create a simple table to store book information:

CREATE TABLE IF NOT EXISTS books (

    id INTEGER PRIMARY KEY,

    title TEXT NOT NULL,

    author TEXT NOT NULL,

    published_year INTEGER,

    price REAL

);

If the command is successful, you'll see no output, just a new prompt. You can verify the table was created by using the `.tables` command:

sqlite> .tables

You should see the following output:

books

This confirms that the 'books' table has been successfully created in your database.

Step 5: Inserting Data

Now, let's add some data to our table:

INSERT INTO books (title, author, published_year, price)

VALUES 

    ('1984', 'George Orwell', 1949, 9.99),

    ('To Kill a Mockingbird', 'Harper Lee', 1960, 12.50),

    ('The Great Gatsby', 'F. Scott Fitzgerald', 1925, 8.75);

If the insertion is successful, you'll see output indicating the number of rows affected:

3 rows affected

This output confirms that three rows were successfully inserted into the `books` table.

Step 6: Querying Data

Let's perform some simple queries:

1. Retrieve all books:

SELECT * FROM books;

Expected output:

1|1984|George Orwell|1949|9.99

2|To Kill a Mockingbird|Harper Lee|1960|12.5

3|The Great Gatsby|F. Scott Fitzgerald|1925|8.75

2. Find books published after 1950:

SELECT title, author FROM books WHERE published_year > 1950;

Expected output:

To Kill a Mockingbird|Harper Lee

3. Get the average price of books:

SELECT AVG(price) as avg_price FROM books;

Expected output:

10.4133333333333

Step 7 : Ending the Exercise

To exit the SQLite prompt and close the database connection, type:

.quit

You'll be returned to your regular command prompt.

Conclusion

Congratulations! You've just created a database, added data, and performed queries using SQLite on Linux. This exercise demonstrates the basics of working with SQLite, including creating a database file, defining tables, inserting data, and running queries.

Remember, unlike some database systems that keep data in memory or require you to explicitly save changes, SQLite automatically saves your changes to the database file as you make them. This makes SQLite particularly robust against data loss.

For your next steps, try adding more books, creating additional tables, or writing more complex queries. And don't forget to explore SQLite's documentation for more advanced operations and best practices. Happy coding!

Image: CopyrightFreePictures from Pixabay