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.

Skills Graph

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 update data in the employees table through the employee_info view.

Not all views are updatable. A view is generally updatable if it selects from a single table and includes the primary key of that table.

First, let's drop and recreate the employee_info view to ensure it includes the id column, which is the primary key of the employees table:

DROP VIEW IF EXISTS employee_info;

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

Now, update the department of an employee with id = 1 to 'HR':

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

This SQL statement updates the department column in the employees table for the employee with id = 1 through the employee_info view.

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

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

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

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 also learned how to update data through updatable views and how to remove obsolete views to maintain a clean database schema. These skills are essential for simplifying complex queries, controlling data access, and managing your SQLite databases effectively.