Querying and Filtering Data

Introduction

In this lab, you will learn how to query and filter data in a SQLite database. This hands-on lab is designed for beginners and will introduce you to essential SQL techniques. You'll learn how to filter data using the WHERE clause, match patterns with LIKE, sort results with ORDER BY, and limit the number of results with LIMIT. By the end of this lab, you'll be able to retrieve specific data from a database efficiently.

Connect to SQLite and View the Data

In this first step, you'll connect to a SQLite database named employees.db and view the data within the staff table. This allows you to understand the data you'll be working with.

Open a terminal in the LabEx VM environment. The default directory is /home/labex/project.
Connect to the employees.db database using the sqlite3 command-line tool:
```
sqlite3 employees.db
```
This command opens the SQLite shell, indicated by the sqlite> prompt.
View the data in the staff table. Execute the following SQL command:
```
SELECT * FROM staff;
```
This command selects all columns (*) from the staff table and displays the results. You should see the following output:
```
1|Alice|HR
2|Bob|IT
3|Charlie|HR
4|David|IT
5|Eve|Marketing
6|Frank|IT
```
This output shows the id, name, and department for each employee in the staff table.
To exit the SQLite shell, type:
```
.quit
```
IMPORTANT: Make sure .quit before you click the "Continue" button. Otherwise, the step cannot be verified because the sqlite history will not be recorded.

Filter Data with the WHERE Clause

The WHERE clause allows you to filter data based on specific conditions. You can use it to retrieve only the rows that meet your criteria.

Connect to the employees.db database:
```
sqlite3 employees.db
```
To retrieve only employees from the HR department, use the following SQL command:
```
SELECT * FROM staff WHERE department = 'HR';
```
This command selects all columns from the staff table, but only includes rows where the department column is equal to HR. The output will be:
```
1|Alice|HR
3|Charlie|HR
```
Now, let's find employees with an id greater than 3:
```
SELECT * FROM staff WHERE id > 3;
```
This command selects all columns from the staff table, but only includes rows where the id column is greater than 3. The output will be:
```
4|David|IT
5|Eve|Marketing
6|Frank|IT
```
Exit the SQLite shell:
```
.quit
```
IMPORTANT: Make sure .quit before you click the "Continue" button. Otherwise, the step cannot be verified because the sqlite history will not be recorded.

Pattern Matching with the LIKE Operator

The LIKE operator is used for pattern matching. It's helpful when you want to find data that matches a specific pattern, rather than an exact value. You'll use wildcards like % (zero or more characters) and _ (single character).

Connect to the employees.db database:
```
sqlite3 employees.db
```
To find employees whose names start with A, use the following command:
```
SELECT * FROM staff WHERE name LIKE 'A%';
```
This command selects all columns from the staff table, but only includes rows where the name column starts with A. The % wildcard matches zero or more characters. The output will be:
```
1|Alice|HR
```
To find employees whose names contain the letter i, use:
```
SELECT * FROM staff WHERE name LIKE '%i%';
```
This command selects all columns from the staff table, but only includes rows where the name column contains the letter i. The output will be:
```
1|Alice|HR
3|Charlie|HR
4|David|IT
```
Exit the SQLite shell:
```
.quit
```

Sort Data with the ORDER BY Clause

The ORDER BY clause allows you to sort the results of your query. You can sort in ascending (default) or descending order.

Connect to the employees.db database:
```
sqlite3 employees.db
```
To sort the data alphabetically by name in ascending order, use:
```
SELECT * FROM staff ORDER BY name;
```
This command selects all columns from the staff table and sorts the results by the name column in ascending order (A to Z). The output will be:
```
1|Alice|HR
2|Bob|IT
3|Charlie|HR
4|David|IT
5|Eve|Marketing
6|Frank|IT
```
To sort by name in descending order, use:
```
SELECT * FROM staff ORDER BY name DESC;
```
This command selects all columns from the staff table and sorts the results by the name column in descending order (Z to A). The output will be:
```
6|Frank|IT
5|Eve|Marketing
4|David|IT
3|Charlie|HR
2|Bob|IT
1|Alice|HR
```
Exit the SQLite shell:
```
.quit
```

Limit Results with the LIMIT Clause

The LIMIT clause restricts the number of rows returned by a query. This is useful when you only need a subset of the data.

Connect to the employees.db database:
```
sqlite3 employees.db
```
To retrieve only the first 3 rows from the staff table, use:
```
SELECT * FROM staff LIMIT 3;
```
This command selects all columns from the staff table and limits the results to the first 3 rows. The output will be:
```
1|Alice|HR
2|Bob|IT
3|Charlie|HR
```
Combine LIMIT with ORDER BY to get the first 2 employees sorted by name in descending order:
```
SELECT * FROM staff ORDER BY name DESC LIMIT 2;
```
This command selects all columns from the staff table, sorts the results by the name column in descending order, and limits the results to the first 2 rows. The output will be:
```
6|Frank|IT
5|Eve|Marketing
```
Exit the SQLite shell:
```
.quit
```

Summary

In this lab, you have learned how to query and filter data in a SQLite database. You connected to a database, filtered data using the WHERE clause, matched patterns with the LIKE operator, sorted results with ORDER BY, and limited the number of rows returned using LIMIT. These techniques provide you with the fundamental skills to retrieve and organize data effectively.