SQLite Temporary Storage

Introduction

In this lab, we will explore SQLite temporary storage, focusing on creating and utilizing temporary tables. Temporary tables provide a mechanism for storing intermediate results or data needed only for a short duration within a database connection.

The lab guides you through creating a temporary table named temp_employees using the CREATE TEMP TABLE statement. You'll learn about the scope, lifetime, and naming conventions of temporary tables, understanding how they differ from regular tables. The subsequent steps will involve populating the temporary table with data, querying the data, and confirming the automatic cleanup of the temporary table upon closing the database connection.

Create a Temporary Table

In this step, you will learn how to create a temporary table in SQLite. Temporary tables are useful for storing intermediate results during complex queries or for holding data that is only needed for a short period. They are automatically deleted when the database connection is closed.

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

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

sqlite3 employees.db

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>

To create a temporary table, you use the CREATE TEMP TABLE statement. The syntax is similar to creating a regular table:

CREATE TEMP TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Let's create a temporary table named temp_employees to store employee information. Enter the following SQL command at the sqlite> prompt and press Enter:

CREATE TEMP TABLE temp_employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    department TEXT
);

This command creates a temporary table named temp_employees with three columns: id (an integer primary key that automatically increments), name (text), and department (text).

CREATE TEMP TABLE temp_employees: This SQL statement creates a temporary table named temp_employees. The TEMP keyword specifies that this table is temporary and will be automatically deleted when the database connection is closed.

id INTEGER PRIMARY KEY AUTOINCREMENT: This defines a column named id as an integer, sets it as the primary key for the table, and configures it to automatically increment with each new row inserted.

name TEXT: This defines a column named name as a text field to store employee names.

department TEXT: This defines a column named department as a text field to store the department each employee belongs to.

Insert Data into the Temporary Table

Now that you have created the temp_employees table, let's add some data to it. We'll insert several employee records into the table.

You can insert data into a temporary table using the INSERT INTO statement, just like you would with a regular table.

Insert the following employee records into the temp_employees table by running these commands one by one at the sqlite> prompt:

INSERT INTO temp_employees (name, department) VALUES ('Alice', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Bob', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Charlie', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('David', 'Sales');
INSERT INTO temp_employees (name, department) VALUES ('Eve', 'Marketing');
INSERT INTO temp_employees (name, department) VALUES ('Frank', 'Engineering');
INSERT INTO temp_employees (name, department) VALUES ('Grace', 'HR');
INSERT INTO temp_employees (name, department) VALUES ('Henry', 'Sales');

These commands add eight rows to the temp_employees table.

INSERT INTO temp_employees (name, department): This specifies that you are inserting data into the name and department columns of the temp_employees table.

VALUES ('Alice', 'Sales'): This 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 temp_employees;

Expected Output:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

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

Query Data from the Temporary Table

In this step, you will learn how to retrieve data from your temporary table using the SELECT statement with different clauses. 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 temp_employees table. Run this command:

SELECT * FROM temp_employees;

Expected Output:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

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

Next, filter data using a condition with the WHERE clause. To view only the records for employees in the 'Sales' department, run:

SELECT * FROM temp_employees WHERE department = 'Sales';

Expected Output:

1|Alice|Sales
4|David|Sales
8|Henry|Sales

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

SELECT * FROM temp_employees selects all columns from the temp_employees table.
WHERE department = 'Sales' filters the results to only include rows where the department column is equal to 'Sales'.

Now, sort the data by name using the ORDER BY clause:

SELECT * FROM temp_employees ORDER BY name;

Expected Output:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering
4|David|Sales
5|Eve|Marketing
6|Frank|Engineering
7|Grace|HR
8|Henry|Sales

The ORDER BY clause sorts the results based on the specified column, in this case, the name column.

Finally, limit the number of results using the LIMIT clause. To view only the first 3 records, run:

SELECT * FROM temp_employees LIMIT 3;

Expected Output:

1|Alice|Sales
2|Bob|Marketing
3|Charlie|Engineering

The LIMIT clause restricts the number of rows returned by the query.

Confirm Auto-Cleanup of the Temporary Table

In this step, you will confirm that the temporary table temp_employees is automatically deleted when the database connection is closed. This is a key characteristic of temporary tables.

First, exit the SQLite shell by typing:

.exit

This will close the connection to the employees.db database.

Now, reopen the SQLite shell by running:

sqlite3 employees.db

Try to query the temp_employees table:

SELECT * FROM temp_employees;

You should see an error message indicating that the table does not exist:

Error: no such table: temp_employees

This confirms that the temporary table was automatically deleted when the previous database connection was closed.

SELECT * FROM temp_employees: This SQL statement attempts to select all columns and rows from the temp_employees table.

Error: no such table: temp_employees: This error message indicates that the temp_employees table does not exist in the database, confirming that it was automatically deleted when the database connection was closed.

Summary

In this lab, you have learned how to create and use temporary tables in SQLite. You created a temporary table named temp_employees, inserted data into it, queried the data using various SELECT statements, and confirmed that the temporary table is automatically deleted when the database connection is closed. These hands-on exercises provided a comprehensive introduction to temporary tables, equipping you with the foundational knowledge to handle temporary data effectively using SQLite.