**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Basic Data Retrieval and Filtering **Topic:** Write queries to filter, sort, and limit data in an SQLite database. (Lab topic) Welcome to this lab topic where we'll put your SQL skills to the test. We'll create queries that filter, sort, and limit data in an SQLite database. This is a hands-on exercise, so make sure you have a functioning SQLite environment set up on your machine. If you're new to SQLite, you can download the SQLite browser (DB Browser for SQLite) from [the official website](https://sqlitebrowser.org/). **Objective:** By the end of this lab, you'll be able to: * Write SQL queries that filter data using the WHERE clause * Sort data using the ORDER BY clause * Limit the number of rows returned in a query using the LIMIT clause **Database Setup:** For this lab, we'll use a sample database called "employees.db". You can create the database and tables using the following SQL script: ```sql -- Create database -- Please Note: SQLite automatically creates a new database if it does not exist when you try to connect to a new Database file. -- Create tables CREATE TABLE departments ( department_id INTEGER PRIMARY KEY, department_name TEXT NOT NULL ); CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, department_id INTEGER NOT NULL, salary REAL NOT NULL, FOREIGN KEY (department_id) REFERENCES departments(department_id) ); -- Insert data into tables INSERT INTO departments (department_id, department_name) VALUES (1, 'Sales'), (2, 'Marketing'), (3, 'HR'), (4, 'IT'); INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES (1, 'John', 'Doe', 1, 50000), (2, 'Jane', 'Doe', 2, 60000), (3, 'Bob', 'Smith', 3, 70000), (4, 'Alice', 'Johnson', 4, 80000), (5, 'Mike', 'Williams', 1, 55000), (6, 'Emma', 'Taylor', 2, 65000), (7, 'David', 'Brown', 3, 75000), (8, 'Sophia', 'Lee', 4, 85000); ``` **Query 1: Filter data using the WHERE clause** Write a SQL query to retrieve all employees who work in the 'Sales' department. ```sql SELECT * FROM employees WHERE department_id = 1; ``` This query will return all employees who have a department_id of 1, which corresponds to the 'Sales' department. **Query 2: Sort data using the ORDER BY clause** Write a SQL query to retrieve all employees and sort them by their last name in descending order. ```sql SELECT * FROM employees ORDER BY last_name DESC; ``` This query will return all employees and sort them by their last name in descending order. **Query 3: Limit the number of rows returned using the LIMIT clause** Write a SQL query to retrieve the first 3 employees who have the highest salary. ```sql SELECT * FROM employees ORDER BY salary DESC LIMIT 3; ``` This query will return the first 3 employees who have the highest salary. **Challenge Exercises:** 1. Write a SQL query to retrieve all employees who have a salary greater than 70000. 2. Write a SQL query to retrieve all employees and sort them by their first name in ascending order. 3. Write a SQL query to retrieve the last 2 employees who have the lowest salary. We encourage you to try these exercises and test your SQL skills. If you have any questions or need help, please leave a comment below. In the next topic, we'll explore how to use aggregate functions in SQLite. We'll learn how to use COUNT, SUM, AVG, MIN, and MAX to perform calculations on our data. **What's Next:** Using aggregate functions in SQLite: COUNT, SUM, AVG, MIN, MAX.