Database Query Automation with Python and SQLite

This challenge focuses on automating common SQL queries using Python and the sqlite3 library. You'll be provided with a database schema and a series of query descriptions. Your task is to write Python functions that execute these queries against an in-memory SQLite database and return the results as lists of tuples.

Problem Description

You are tasked with creating a set of Python functions that interact with an SQLite database. The database will contain a single table named employees with the following schema:

• employee_id (INTEGER, PRIMARY KEY)
• first_name (TEXT)
• last_name (TEXT)
• department (TEXT)
• salary (REAL)

Each function will take no arguments and should:

1. Establish a connection to an in-memory SQLite database.
2. Execute the specified SQL query.
3. Fetch all results from the query.
4. Close the database connection.
5. Return the results as a list of tuples. Each tuple represents a row from the result set.

Key Requirements:

• Use the sqlite3 library for database interaction.
• Create an in-memory database for each query (do not persist data).
• Handle potential errors gracefully (though error handling is not explicitly required for this challenge, consider it for robustness).
• Return an empty list if the query returns no results.

Expected Behavior:

The functions should execute the SQL queries correctly and return the expected data in the specified format. The database should be created and populated with sample data within each function.

Edge Cases to Consider:

• Empty tables: The queries should still execute without errors, returning an empty list if the table is empty.
• Invalid SQL: While not explicitly required, consider how your code might handle invalid SQL (though the provided queries are guaranteed to be valid).

Examples

Example 1: Get all employees

Input: None
Output: [(1, 'Alice', 'Smith', 'Sales', 60000.0), (2, 'Bob', 'Johnson', 'Marketing', 75000.0), (3, 'Charlie', 'Brown', 'Sales', 55000.0), (4, 'David', 'Lee', 'Engineering', 90000.0), (5, 'Eve', 'Davis', 'Marketing', 80000.0)]
Explanation: This query retrieves all rows from the `employees` table. The database is populated with the sample data described in the Notes section.

Example 2: Get employees in the Sales department

Input: None
Output: [(1, 'Alice', 'Smith', 'Sales', 60000.0), (3, 'Charlie', 'Brown', 'Sales', 55000.0)]
Explanation: This query filters the `employees` table to return only those employees whose `department` is 'Sales'.

Example 3: Get the average salary of all employees

Input: None
Output: [ (72500.0,) ]
Explanation: This query calculates the average salary of all employees in the `employees` table. The result is a single tuple containing the average salary.

Constraints

• The sqlite3 library is the only allowed library for database interaction.
• All queries must be executed against an in-memory database.
• The employees table will always have the schema described above.
• The functions must return a list of tuples.
• The database will be populated with the following sample data within each function:

  INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
  (1, 'Alice', 'Smith', 'Sales', 60000.0),
  (2, 'Bob', 'Johnson', 'Marketing', 75000.0),
  (3, 'Charlie', 'Brown', 'Sales', 55000.0),
  (4, 'David', 'Lee', 'Engineering', 90000.0),
  (5, 'Eve', 'Davis', 'Marketing', 80000.0);
  

Notes

You will need to implement three functions:

1. get_all_employees(): Executes SELECT * FROM employees;
2. get_sales_employees(): Executes SELECT * FROM employees WHERE department = 'Sales';
3. get_average_salary(): Executes SELECT AVG(salary) FROM employees;

Remember to populate the database with the sample data within each function before executing the query. This ensures that each function operates on a consistent dataset. The order of the columns in the returned tuples must match the order of columns in the SELECT statement.