SQLite CTE Queries

Introduction

In this lab, you will explore the power of Common Table Expressions (CTEs) in SQLite. You'll learn how to define and use CTEs to enhance query readability and maintainability. You'll start with simple CTEs and then move on to recursive CTEs. By the end of this lab, you'll be able to use CTEs to write cleaner, more efficient, and easier-to-understand SQL code.

Create a Database and a Table

In this step, you will create a SQLite database and an employees table. This table will store employee information, which you'll use in later steps to practice CTE queries.

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

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

sqlite3 company.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>

Next, create a table named employees to store basic employee information. This table will have four columns: id, name, department, and salary. Enter the following SQL command at the sqlite> prompt and press Enter:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

This command sets up the employees table where:

• id is an integer that serves as the primary key for each employee.
• name is a text field for the employee's name.
• department is a text field for the employee's department.
• salary is an integer field for the employee's salary.

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

Insert Data into the Table

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

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

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);

These commands add five rows to the employees table.

• INSERT INTO employees (name, department, salary) specifies that you are inserting data into the name, department, and salary columns of the employees table.
• VALUES ('Alice', 'Sales', 50000) 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 employees;

Expected Output:

1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000

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

Define a Simple CTE

In this step, you will learn how to define and use a simple Common Table Expression (CTE) in SQLite. CTEs are temporary, named result sets that you can reference within a single SQL statement. They are useful for breaking down complex queries into smaller, more manageable parts, improving readability and maintainability.

A CTE is essentially a named subquery that exists only for the duration of a single query. You define a CTE using the WITH clause, giving it a name and specifying the query that generates the result set. You can then reference the CTE's name in the main query as if it were a regular table.

Basic Syntax:

WITH
    cte_name AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    )
SELECT column1, column2
FROM cte_name
WHERE another_condition;

Let's define a CTE to select employees from the 'Sales' department. Execute the following SQL statement at the sqlite> prompt:

WITH
    SalesEmployees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = 'Sales'
    )
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;

This query first defines a CTE named SalesEmployees that selects the id, name, and salary of all employees in the 'Sales' department. Then, the main query selects the id, name, and salary from the SalesEmployees CTE where the salary is greater than 52000.

Expected Output:

3|Charlie|55000

This shows that Charlie is the only employee in the Sales department with a salary greater than 52000.

Build a Recursive CTE

In this step, you will learn how to build and use a recursive Common Table Expression (CTE) in SQLite. Recursive CTEs are used to query hierarchical or tree-structured data. They allow you to traverse relationships within a table and retrieve data at different levels of the hierarchy.

A recursive CTE is a CTE that refers to itself. It consists of two parts:

• Anchor Member: The initial SELECT statement that defines the base case or starting point of the recursion.
• Recursive Member: A SELECT statement that refers to the CTE itself. This part performs the recursive step, building upon the results of the previous iteration.

The anchor member and recursive member are combined using the UNION ALL operator. The recursion continues until the recursive member returns an empty result set.

First, let's create a table named employees_hierarchy with the following structure:

CREATE TABLE employees_hierarchy (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER,
    title TEXT
);

This table represents the employee hierarchy, where manager_id refers to the id of the employee's manager. Execute the above command at the sqlite> prompt.

Next, insert some sample data into the employees_hierarchy table:

INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');

Here, Alice is the CEO (no manager), Bob and Charlie report to Alice, David reports to Bob, Eve reports to Charlie, Frank reports to David, and Grace reports to Eve. Execute the above commands at the sqlite> prompt.

Now, let's build a recursive CTE to retrieve the entire hierarchy under Alice (the CEO). Execute the following SQL statement:

WITH RECURSIVE
    EmployeeHierarchy(id, name, manager_id, title, level) AS (
        -- Anchor member: Select the CEO
        SELECT id, name, manager_id, title, 0 AS level
        FROM employees_hierarchy
        WHERE manager_id IS NULL

        UNION ALL

        -- Recursive member: Select employees reporting to the current level
        SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
        FROM employees_hierarchy e
        JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    )
SELECT id, name, title, level
FROM EmployeeHierarchy;

This query defines a recursive CTE named EmployeeHierarchy. The anchor member selects the CEO (where manager_id is NULL). The recursive member joins the employees_hierarchy table with the EmployeeHierarchy CTE to find employees who report to the employees selected in the previous level. The level column keeps track of the depth in the hierarchy.

Expected Output:

1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3

This shows the entire employee hierarchy, with the level indicating the reporting level.

Integrate CTEs in Complex Queries

In this step, you will learn how to integrate CTEs into more complex queries in SQLite. You'll see how to use multiple CTEs within a single query.

You can define multiple CTEs in a single query by separating them with commas. This allows you to break down a complex query into several logical steps, each represented by a CTE.

Let's create a new table named department_salaries with the following structure:

CREATE TABLE department_salaries (
    department TEXT,
    total_salary INTEGER
);

This table will store the total salary for each department. Execute the above command at the sqlite> prompt.

Now, let's use CTEs to calculate the total salary for each department and insert the results into the department_salaries table. Execute the following SQL statement:

WITH
    DepartmentTotalSalaries AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;

SELECT * FROM department_salaries;

This query first defines a CTE named DepartmentTotalSalaries that calculates the total salary for each department using the employees table. Then, it inserts the results from the DepartmentTotalSalaries CTE into the department_salaries table. Finally, it selects all data from the department_salaries table to display the results.

Expected Output:

Sales|105000
Marketing|122000
Engineering|70000

This shows the total salary for each department.

Summary

In this lab, you have learned how to define and use Common Table Expressions (CTEs) in SQLite. You started with simple CTEs to select data from a table, then moved on to recursive CTEs to query hierarchical data. Finally, you learned how to integrate CTEs into more complex queries. CTEs are a powerful tool for writing cleaner, more efficient, and easier-to-understand SQL code.