Creating and Managing SQLite Databases

Introduction

In this lab, you will learn the fundamentals of working with SQLite databases using the sqlite3 command-line tool. SQLite is a lightweight, file-based database system ideal for beginners. You'll create a database, create tables, insert data, query data, add columns, update data, and finally, remove the database file. Let's get started!

Create a SQLite Database and Connect

In this step, you will create a new SQLite database file and connect to it using the sqlite3 command-line tool.

Open a terminal in the LabEx VM environment. By default, you should be in the /home/labex/project directory. Confirm this by running the following command:
```
pwd
```
The output should be:
```
/home/labex/project
```
If you are not in this directory, navigate to it by running:
```
cd /home/labex/project
```
Create a new SQLite database named mydatabase.db and connect to it using the sqlite3 command:
```
sqlite3 mydatabase.db
```
This command creates the mydatabase.db file if it doesn't exist and opens a connection to it. You will see the SQLite prompt:
```
SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>
```

The sqlite3 command is used to both create and connect to SQLite databases. If the specified database file doesn't exist, SQLite will create it. Now you are ready to execute SQL commands within the SQLite environment.

Create the users Table

In this step, you will create a table named users inside the mydatabase.db database.

At the SQLite prompt, create a table named users with the following SQL command:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

This command defines the structure of the users table. The id column is an integer and the primary key, ensuring each user has a unique identifier. The name column is a text field that cannot be left empty (NOT NULL), and the age column is an integer.

The CREATE TABLE command is a fundamental SQL command used to define the structure of a table in a database. The PRIMARY KEY constraint ensures that the id column contains unique values for each row, and the NOT NULL constraint ensures that the name column cannot be empty.

Insert Data into the users Table

Now that you have created the users table, you can insert data into it. This involves adding rows (records) to the table, with each row representing a user and their corresponding information.

Insert the following records into the users table using the INSERT INTO command:

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO users (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO users (id, name, age) VALUES (3, 'Charlie', 22);

These commands add three new rows to the users table. Each row includes an id, a name, and an age.

The INSERT INTO command is used to add new rows to a table. The values specified in the VALUES clause are inserted into the corresponding columns listed in the parentheses after the table name.

Query Data from the users Table

In this step, you will learn how to query data from the users table using the SELECT command. This allows you to retrieve specific information from the database.

Retrieve all data from the users table using the following SQL command:

SELECT * FROM users;

This command selects all columns (*) from all rows in the users table. The output will display the data you inserted in the previous step. You should see something like this:

1|Alice|25
2|Bob|30
3|Charlie|22

The SELECT command is a powerful tool for retrieving data from a database. The * wildcard character specifies that all columns should be selected. You can also select specific columns by listing their names separated by commas (e.g., SELECT name, age FROM users;).

Add a New Column and Update Data

In this step, you will add a new column named email to the users table and then update the existing records with email addresses.

Add a new column named email to the users table using the ALTER TABLE command:
```
ALTER TABLE users ADD COLUMN email TEXT;
```
This command adds a new column named email of type TEXT to the users table.

Update the email column for each user using the UPDATE command:

UPDATE users SET email = 'alice@example.com' WHERE id = 1;
UPDATE users SET email = 'bob@example.com' WHERE id = 2;
UPDATE users SET email = 'charlie@example.com' WHERE id = 3;

These commands update the email column for each user with a corresponding email address.

Check the updated data by querying the users table:

SELECT * FROM users;

1|Alice|25|alice@example.com
2|Bob|30|bob@example.com
3|Charlie|22|charlie@example.com

The ALTER TABLE command is used to modify the structure of an existing table. In this case, we are adding a new column. The UPDATE command is used to modify existing data in a table. The SET clause specifies which column to update and the new value. The WHERE clause specifies which rows to update based on a condition.

Exit SQLite and Remove the Database File

In this step, you will exit the SQLite interface and then remove the database file. Since SQLite stores all data in a single file, removing a database is as simple as deleting the file using the rm command. Be careful, as this action is permanent!

Exit the SQLite prompt:
```
.quit
```
Ensure you are in the /home/labex/project directory. Confirm with:
```
pwd
```
The output should be:
```
/home/labex/project
```
If not, navigate to it using:
```
cd /home/labex/project
```
Delete the mydatabase.db file using the rm command:
```
rm mydatabase.db
```
There will be no output if the deletion is successful.
Confirm that the file has been removed by listing the directory contents again:
```
ls
```
You should no longer see mydatabase.db in the output, confirming the file has been deleted.

The .quit command exits the SQLite shell and returns you to the Linux terminal. The rm command is a standard Linux command used to delete files. In this case, it permanently removes the mydatabase.db file, which contains all the data and structure of your SQLite database.

Summary

In this lab, you have learned the essential skills for creating and managing SQLite databases using the sqlite3 command-line tool. You created a database, created a table, inserted data, queried the data, added a new column, updated data, and finally, removed the database file. These steps provide a solid foundation for working with SQLite databases.