Views (VIEW) — Definition and Use Cases

Data used in this article — employee / department / sales

A view (VIEW) is a named, stored SELECT statement that you can reference like a table.

Unlike a table, a view doesn't hold data of its own — every time you reference it, the stored SELECT runs again.

Before diving into the exercises, take a look at the column definitions and sample data for the three tables used in this article — employee / department / sales.

① Run PRAGMA table_info(employee); / PRAGMA table_info(department); / PRAGMA table_info(sales); to check the column definitions for all three tables.

② Run SELECT * FROM employee LIMIT 5; / SELECT * FROM department LIMIT 5; / SELECT * FROM sales LIMIT 5; to preview the first five rows of each table.

CREATE VIEW — Give a complex query a name

Define a view with CREATE VIEW view_name AS SELECT ...;.

Once it's defined, writing SELECT * FROM view_name runs the stored SELECT and returns its result.

Because a view is a stored query with no data of its own, any update to the underlying tables shows up the next time you reference the view.

When you no longer need it, drop it with DROP VIEW view_name;.

If you want to recreate a view with the same name, run DROP VIEW IF EXISTS view_name; before CREATE VIEW so the script stays re-runnable.

-- Name a per-department headcount query as a view
DROP VIEW IF EXISTS dept_headcount;
CREATE VIEW dept_headcount AS
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- After definition, reference it like a table
SELECT dept_name, headcount FROM dept_headcount
ORDER BY headcount DESC;

-- Drop it when you're done
DROP VIEW dept_headcount;

Imagine the requirement: "I want to see an employee list with department name and location alongside each row, without writing the JOIN every time." Inside a single run, take the view from definition through reference to deletion as a self-contained script. (When it runs correctly, the explanation will appear.)

① Define a view named emp_dept that LEFT JOINs employee and department on dept_id and returns emp_id, name, salary, dept_name, and location. Put a statement that drops any existing view of the same name before the definition, so the script never fails on rerun.

② Pull name, dept_name, and salary from the view and display the top 5 rows ordered by salary descending.

③ Finally, drop the view so its definition doesn't linger on the page.

Three use cases — reuse, authorization, abstraction

There are three main reasons to use a view.

Reuse: bundle a frequently used JOIN or aggregation under a single name so you don't have to rewrite the same query over and over.

Authorization: create a view that exposes only a subset of columns or rows, and grant access to the view rather than the underlying table (hide high-salary columns, show only rows from the user's own department, and so on).

Abstraction: callers don't need to know the view's internal structure (which tables it joins, how) — knowing the view name and the columns it returns is enough.

-- (1) Abstraction: a view that labels employees by hire-year cohort (callers don't see the expression)
DROP VIEW IF EXISTS emp_cohort;
CREATE VIEW emp_cohort AS
SELECT name, hired_on,
  CASE
    WHEN hired_on < '2018-01-01' THEN 'Veteran'
    WHEN hired_on < '2021-01-01' THEN 'Mid'
    ELSE 'Recent'
  END AS cohort
FROM employee;

SELECT cohort, COUNT(*) AS cnt
FROM emp_cohort
GROUP BY cohort
ORDER BY cohort;

DROP VIEW emp_cohort;

-- (2) Authorization: a view that excludes salary and exposes only publishable columns
DROP VIEW IF EXISTS emp_public;
CREATE VIEW emp_public AS
SELECT emp_id, name, dept_id FROM employee;
DROP VIEW emp_public;

Imagine this requirement: "I want to bundle the logic that classifies employees into salary tiers (High / Mid / Low) in one place, and confirm that updating the underlying employee table makes the view's aggregated result update automatically." Because a view is a stored SELECT with no data of its own, it re-evaluates the underlying table every time you reference it.

① Drop any existing view of the same name, then define a view emp_tier with a tier column that returns High when salary is 6,500,000 or more, Mid when it's between 5,000,000 and 6,500,000, and Low when it's below that.

② Aggregate the headcount per tier under the alias cnt, and check the counts before the update.

③ INSERT a new employee into employee (emp_id 999 / name 'Zoe' / dept_id 1 / manager_id NULL / city 'Tokyo' / salary 8000000 / hired_on '2024-01-01'), who lands in the High tier.

④ Run the same aggregation query as ②, and confirm that the High count went up by 1 (proof that an update to the underlying table immediately shows up through the view).

⑤ Clean up by DELETing the row with emp_id = 999 and DROPping the view.

Aggregation views shorten the caller's query

Views can store GROUP BY aggregations, not just JOINs.

If you bundle a frequently used aggregation — like total sales per employee — into a single view, callers can skip JOIN and GROUP BY and simply SELECT rows that have already been aggregated.

The example below aggregates sales per employee, joins it with employee to attach the employee name, and defines that as a view.

Callers just reference the view to see "who sold how much" — they don't have to think about the internal join and aggregation.

-- A view that tags per-department sales totals with the department name
DROP VIEW IF EXISTS dept_sales;
CREATE VIEW dept_sales AS
SELECT d.dept_name, SUM(s.amount) AS total
FROM sales s
JOIN employee e   ON s.emp_id = e.emp_id
JOIN department d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- Callers just SELECT from the aggregated view
SELECT dept_name, total FROM dept_sales
ORDER BY total DESC;

DROP VIEW dept_sales;

Imagine the requirement: "For the sales-performance dashboard, I want to show the top employees by total sales without writing the JOIN and aggregation every time." Take the view from definition through reference to deletion as a self-contained script in a single run.

① Join employee and sales on emp_id, aggregate per-employee sales totals under the alias total, and define the result as a view named emp_sales. The view's columns should be emp_id, name, and total — three columns total. Put a drop-if-exists statement for the same view name before the definition.

② Pull name and total from the view and show the top 5 rows ordered by total descending, then by name ascending as a tiebreaker.

③ Finally, drop the view.