Working with Data in SQLite

Introduction

In this lab, you will learn the fundamental operations of working with data in SQLite, a lightweight, file-based database ideal for beginners. Through hands-on practice in the LabEx VM environment, you will create a database, set up tables, and perform core tasks such as inserting data, querying records, updating information, and deleting entries. This step-by-step guide will help you build a solid foundation in database management using simple SQL commands tailored for those new to databases.

Create a SQLite Database and Table

In this first step, you will create a SQLite database and set up a table to store data. SQLite is a lightweight database that stores data in a single file, making it easy to use for learning purposes.

First, open your terminal in the LabEx VM. Your default path is /home/labex/project.

Now, let's create a SQLite database named mydb.sqlite. Run the following command to create the database file and open the SQLite command-line tool:

sqlite3 mydb.sqlite

You will see a prompt indicating that you are now inside the SQLite shell:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Next, create a table named users to store basic user information. This table will have three columns: id (a unique identifier), name, and email. Enter the following SQL command at the sqlite> prompt and press Enter:

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

This command sets up the users table where:

id is an integer that automatically increases for each new entry. The PRIMARY KEY constraint ensures that each id is unique, and AUTOINCREMENT makes it increase automatically.
name is a text field that cannot be left empty (NOT NULL).
email is also a text field that cannot be left empty (NOT NULL).

You won't see any output if the command runs successfully.

Insert Data into the Table

Now that you have created the users table, let's add some data to it. We'll insert three user records into the table.

Insert three user records into the users table by running these commands one by one at the sqlite> prompt:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');

These commands add three rows to the users table. Notice that you don't specify a value for the id column because it automatically increments with each new entry.

INSERT INTO users (name, email) specifies that you are inserting data into the name and email columns of the users table.
VALUES ('Alice', 'alice@example.com') provides the values to be inserted for each record.

To confirm that the data was added correctly, run this command to view all records in the table:

SELECT * FROM users;

Expected Output:

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

This output shows the id, name, and email for each record. The SELECT * command retrieves all columns from the specified table.

Query Data with SELECT

In this step, you will learn how to retrieve data from your table using the SELECT statement. Querying data is a core skill for working with databases, as it allows you to view and analyze the information stored.

At the sqlite> prompt, let's first retrieve all data from the users table. Run this command:

SELECT * FROM users;

Expected Output:

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

The * in SELECT * means "all columns," so this command shows every column and row in the users table.

Next, you can choose specific columns to display. To see only the name and email columns, run:

SELECT name, email FROM users;

Expected Output:

Alice|alice@example.com
Bob|bob@example.com
Charlie|charlie@example.com

This command limits the output to just the specified columns, omitting the id.

Finally, filter data using a condition with the WHERE clause. To view only the record for 'Alice', run:

SELECT * FROM users WHERE name = 'Alice';

Expected Output:

1|Alice|alice@example.com

The WHERE clause narrows down the results to rows that match the condition, in this case, where the name is 'Alice'.

SELECT * FROM users selects all columns from the users table.
WHERE name = 'Alice' filters the results to only include rows where the name column is equal to 'Alice'.

Update Records in the Table

Now, you will learn how to modify existing data in your table using the UPDATE statement. Updating records is important when you need to correct or change information in a database.

First, check the current data in the users table to see what you will update. Run:

SELECT * FROM users;

Expected Output:

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

Now, update the email address for 'Bob' to bob.new@example.com. Enter this command at the sqlite> prompt:

UPDATE users SET email = 'bob.new@example.com' WHERE name = 'Bob';

You won't see output if the command succeeds. This command changes the email value for the row where name is 'Bob'. The WHERE clause ensures only the matching record is updated.

UPDATE users specifies that you are updating the users table.
SET email = 'bob.new@example.com' sets the new value for the email column.
WHERE name = 'Bob' filters the update to only apply to the row where the name is 'Bob'.

Verify the change by querying the table again:

SELECT * FROM users;

Expected Output:

1|Alice|alice@example.com
2|Bob|bob.new@example.com
3|Charlie|charlie@example.com

You can see that Bob's email has been updated.

Delete Records from the Table

In this final step, you will learn how to remove data from your table using the DELETE statement. Deleting records is necessary when you need to clean up outdated or incorrect data from a database.

First, view the current data in the users table to identify what you will delete. Run:

SELECT * FROM users;

Expected Output:

1|Alice|alice@example.com
2|Bob|bob.new@example.com
3|Charlie|charlie@example.com

Now, delete the record for 'Charlie' by running this command:

DELETE FROM users WHERE name = 'Charlie';

You won't see output if the command succeeds. This command removes the row where name is 'Charlie'. The WHERE clause ensures only the specified record is deleted.

DELETE FROM users specifies that you are deleting from the users table.
WHERE name = 'Charlie' filters the deletion to only apply to the row where the name is 'Charlie'.

Verify the deletion by querying the table again:

SELECT * FROM users;

Expected Output:

1|Alice|alice@example.com
2|Bob|bob.new@example.com

You can see that Charlie's record is no longer in the table.

Summary

In this lab, you have learned the essential skills for working with data in SQLite within the LabEx VM environment. You created a SQLite database and table, inserted data using the INSERT INTO statement, queried information with SELECT, updated records with UPDATE, and deleted entries using DELETE. These hands-on exercises provided a comprehensive introduction to database management, equipping you with the foundational knowledge to handle data effectively using SQLite.