SQLite View Creation

Introduction

In this lab, we will explore SQLite view creation, focusing on building complex views, querying through them, updating via updatable views, and removing obsolete views.

We'll start by understanding views as virtual tables based on SQL statement result-sets, useful for simplifying queries and controlling data access. The lab will guide you through creating simple views from single tables, like an employees table, and then progress to more complex views involving joins between tables, such as joining employees and departments tables. You'll learn how to query these views as if they were regular tables and explore the possibilities of updating data through updatable views, as well as how to properly remove views when they are no longer needed.

Create Tables: employees and departments

In this step, you will create two tables, employees and departments, and insert some sample data. These tables will be used to create and query views in the following steps.

First, open the SQLite shell by running the following command in the terminal:

sqlite3 /home/labex/project/employees.db

This command opens the SQLite shell and connects to the employees.db database. If the database file does not exist, SQLite will create it.

Now, create the employees table with the following SQL statement:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary REAL
);

This SQL statement creates a table named employees with five columns: id, first_name, last_name, department, and salary. The id column is the primary key for the table.

Next, insert some sample data into the employees table:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Smith', 'Marketing', 60000.00),
('Peter', 'Jones', 'Sales', 55000.00),
('Mary', 'Brown', 'IT', 70000.00);

This SQL statement inserts four rows into the employees table.

Now, create the departments table with the following SQL statement:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    location TEXT
);

This SQL statement creates a table named departments with three columns: id, name, and location. The id column is the primary key for the table.

Next, insert some sample data into the departments table:

INSERT INTO departments (name, location) VALUES
('Sales', 'New York'),
('Marketing', 'London'),
('IT', 'San Francisco');

This SQL statement inserts three rows into the departments table.

You can verify the table creation and data insertion by querying the tables:

SELECT * FROM employees;
SELECT * FROM departments;

These commands will display the contents of the employees and departments tables, respectively.

Create a Simple View

In this step, you will create a simple view called employee_info that selects specific columns from the employees table.

A view is a virtual table based on the result-set of an SQL statement. It simplifies complex queries and provides a level of abstraction.

To create the employee_info view, run the following SQL statement in the SQLite shell:

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

This SQL statement creates a view named employee_info that selects the id, first_name, last_name, and department columns from the employees table.

You can now query the view as if it were a table:

SELECT * FROM employee_info;

This command will display the contents of the employee_info view, which is a subset of the columns in the employees table.

Create a Complex View with Joins

In this step, you will create a more complex view called employee_department_info that joins the employees and departments tables.

Joining tables allows you to combine data from multiple tables based on a related column.

To create the employee_department_info view, run the following SQL statement in the SQLite shell:

CREATE VIEW employee_department_info AS
SELECT
    e.first_name,
    e.last_name,
    e.department,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department = d.name;

This SQL statement creates a view named employee_department_info that joins the employees and departments tables on the department column. It selects the employee's first name, last name, department, and the department's location.

You can now query the view to see the combined data:

SELECT * FROM employee_department_info;

This command will display the contents of the employee_department_info view, showing employee information along with the location of their department.

Create a View with Aggregate Functions

In this step, you will create a view called department_salary_stats that uses aggregate functions to calculate the average salary for each department.

Aggregate functions perform calculations on a set of values and return a single result.

To create the department_salary_stats view, run the following SQL statement in the SQLite shell:

CREATE VIEW department_salary_stats AS
SELECT
    department,
    AVG(salary) AS average_salary
FROM
    employees
GROUP BY
    department;

This SQL statement creates a view named department_salary_stats that calculates the average salary for each department using the AVG function and groups the results by department using the GROUP BY clause.

You can now query the view to see the average salary for each department:

SELECT * FROM department_salary_stats;

This command will display the contents of the department_salary_stats view, showing the average salary for each department.

Update Data Through an Updatable View

In this step, you will learn about SQLite view limitations and how to create truly updatable views using INSTEAD OF triggers.

Important: By default, SQLite views are read-only. You cannot directly UPDATE, INSERT, or DELETE data through a view. However, you can create updatable views using INSTEAD OF triggers.

First, let's understand the limitation by attempting a direct update on our existing view:

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

This will fail with an error because SQLite views are read-only by default.

To create a truly updatable view, we need to use INSTEAD OF triggers. Let's recreate the employee_info view and add an INSTEAD OF trigger for updates:

DROP VIEW IF EXISTS employee_info;

CREATE VIEW employee_info AS
SELECT id, first_name, last_name, department
FROM employees;

Now, create an INSTEAD OF trigger to handle UPDATE operations:

CREATE TRIGGER update_employee_info
INSTEAD OF UPDATE ON employee_info
BEGIN
    UPDATE employees
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        department = NEW.department
    WHERE id = OLD.id;
END;

Now you can update data through the view:

UPDATE employee_info
SET department = 'HR'
WHERE id = 1;

This UPDATE statement will now work because the INSTEAD OF trigger redirects the update to the underlying employees table.

You can verify the update by querying the employees table directly:

SELECT * FROM employees WHERE id = 1;

This command will display the row in the employees table with id = 1, and you should see that the department column has been updated to 'HR'.

Remove Obsolete Views and Triggers

In this step, you will remove the views and triggers that you created in the previous steps.

As your database evolves, some views and triggers may become outdated or unnecessary. It's important to remove these objects to maintain a clean and efficient database schema.

First, remove the INSTEAD OF trigger:

DROP TRIGGER IF EXISTS update_employee_info;

Then remove the views. To remove a view, use the DROP VIEW statement. For example, to remove the employee_info view, run the following command:

DROP VIEW IF EXISTS employee_info;

The IF EXISTS clause prevents an error if the view does not exist.

Remove the other views as well:

DROP VIEW IF EXISTS employee_department_info;
DROP VIEW IF EXISTS department_salary_stats;

You can verify that the views have been removed by querying the sqlite_master table:

SELECT name FROM sqlite_master WHERE type='view';

This command should return an empty result, indicating that there are no views in the database.

Finally, exit the SQLite shell:

.exit

This command closes the connection to the employees.db database and returns you to the Linux terminal.

Summary

In this lab, you have learned how to create, query, update, and remove views in SQLite. You started by creating simple views based on a single table and then progressed to more complex views involving joins and aggregate functions. You discovered that SQLite views are read-only by default, but learned how to create truly updatable views using INSTEAD OF triggers. You also learned how to properly remove obsolete views and triggers to maintain a clean database schema. These skills are essential for simplifying complex queries, controlling data access, and managing your SQLite databases effectively.