SQL Views | Academic Block

SQL VIEWS

Learn how to create, use, modify, and remove virtual tables using SQL Views in SQLite.

What is a SQL View?

A VIEW is a virtual table based on the result of a SQL query. It does not normally store a separate copy of the data. Instead, SQLite stores the query definition and uses it when the view is queried.

Views are useful for simplifying complex queries, presenting selected information, and controlling which columns or rows users can access.

Basic CREATE VIEW Syntax

The CREATE VIEW statement creates a view from a SELECT query.

DROP VIEW IF EXISTS employee_directory;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  department TEXT NOT NULL,
  city TEXT NOT NULL
);

INSERT INTO employees (employee_name, department, city)
VALUES
  ('Aarav', 'Engineering', 'Delhi'),
  ('Meera', 'Design', 'Pune'),
  ('Kabir', 'Engineering', 'Jaipur');

CREATE VIEW employee_directory AS
SELECT
  employee_name,
  department,
  city
FROM employees;

SELECT *
FROM employee_directory
ORDER BY employee_name;

Querying a View

Once a view has been created, you can query it in much the same way as a table using SELECT.

DROP VIEW IF EXISTS available_products;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL NOT NULL,
  stock INTEGER NOT NULL
);

INSERT INTO products (product_name, price, stock)
VALUES
  ('Laptop Stand', 1800, 12),
  ('USB Hub', 650, 0),
  ('Webcam', 2400, 8),
  ('Desk Lamp', 1200, 5);

CREATE VIEW available_products AS
SELECT
  product_name,
  price,
  stock
FROM products
WHERE stock > 0;

SELECT *
FROM available_products
ORDER BY price;

View with WHERE Clause

A view can contain a WHERE clause to expose only rows that satisfy a particular condition.

DROP VIEW IF EXISTS premium_courses;
DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_name TEXT NOT NULL,
  category TEXT NOT NULL,
  fee REAL NOT NULL
);

INSERT INTO courses (course_name, category, fee)
VALUES
  ('SQL Basics', 'Database', 900),
  ('Advanced SQL', 'Database', 1800),
  ('Python Basics', 'Programming', 1200),
  ('Data Analysis', 'Data', 2200);

CREATE VIEW premium_courses AS
SELECT
  course_name,
  category,
  fee
FROM courses
WHERE fee >= 1500;

SELECT *
FROM premium_courses
ORDER BY fee DESC;

View with Calculated Columns

A view can include calculated columns created from expressions in the underlying query.

DROP VIEW IF EXISTS product_values;
DROP TABLE IF EXISTS inventory;

CREATE TABLE inventory (
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  price REAL NOT NULL,
  quantity INTEGER NOT NULL
);

INSERT INTO inventory (item_name, price, quantity)
VALUES
  ('Monitor', 12500, 3),
  ('Keyboard', 2400, 10),
  ('Mouse', 850, 15);

CREATE VIEW product_values AS
SELECT
  item_name,
  price,
  quantity,
  price * quantity AS total_value
FROM inventory;

SELECT *
FROM product_values
ORDER BY total_value DESC;

View with ORDER BY

A view can be defined using ORDER BY. The query that reads the view can also specify its own ordering.

DROP VIEW IF EXISTS top_scores;
DROP TABLE IF EXISTS students;

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL,
  score INTEGER NOT NULL
);

INSERT INTO students (student_name, score)
VALUES
  ('Riya', 88),
  ('Dev', 95),
  ('Sana', 76),
  ('Karan', 91);

CREATE VIEW top_scores AS
SELECT
  student_name,
  score
FROM students
WHERE score >= 85;

SELECT *
FROM top_scores
ORDER BY score DESC;

View Using DISTINCT

A view can use DISTINCT to expose only unique values from the underlying table.

DROP VIEW IF EXISTS city_list;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  city TEXT NOT NULL
);

INSERT INTO customers (customer_name, city)
VALUES
  ('Isha', 'Delhi'),
  ('Rohan', 'Mumbai'),
  ('Nisha', 'Delhi'),
  ('Arjun', 'Bengaluru'),
  ('Tara', 'Mumbai');

CREATE VIEW city_list AS
SELECT DISTINCT city
FROM customers;

SELECT *
FROM city_list
ORDER BY city;

View Using Aggregate Functions

Views can also be based on queries containing aggregate functions such as COUNT() and AVG().

DROP VIEW IF EXISTS department_summary;
DROP TABLE IF EXISTS staff;

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  staff_name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary REAL NOT NULL
);

INSERT INTO staff (staff_name, department, salary)
VALUES
  ('Aman', 'Engineering', 65000),
  ('Neha', 'Engineering', 72000),
  ('Vikram', 'Design', 58000),
  ('Priya', 'Design', 62000),
  ('Kunal', 'Support', 50000);

CREATE VIEW department_summary AS
SELECT
  department,
  COUNT(*) AS employee_count,
  ROUND(AVG(salary), 2) AS average_salary
FROM staff
GROUP BY department;

SELECT *
FROM department_summary
ORDER BY department;

View Using JOIN

A view can combine information from multiple tables using a JOIN.

DROP VIEW IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO customers (customer_name)
VALUES
  ('Aarav'),
  ('Meera'),
  ('Kabir');

INSERT INTO orders (customer_id, product, amount)
VALUES
  (1, 'Headphones', 2200),
  (2, 'Keyboard', 3500),
  (1, 'USB Cable', 450);

CREATE VIEW order_details AS
SELECT
  orders.order_id,
  customers.customer_name,
  orders.product,
  orders.amount
FROM orders
INNER JOIN customers
  ON orders.customer_id = customers.customer_id;

SELECT *
FROM order_details
ORDER BY order_id;

CREATE VIEW IF NOT EXISTS

The IF NOT EXISTS option prevents an error if a view with the same name already exists.

DROP VIEW IF EXISTS active_members;
DROP TABLE IF EXISTS members;

CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  member_name TEXT NOT NULL,
  active INTEGER NOT NULL
);

INSERT INTO members (member_name, active)
VALUES
  ('Aditi', 1),
  ('Rohan', 0),
  ('Sahil', 1);

CREATE VIEW IF NOT EXISTS active_members AS
SELECT
  member_id,
  member_name
FROM members
WHERE active = 1;

SELECT *
FROM active_members
ORDER BY member_id;

Replacing a View

SQLite does not support CREATE OR REPLACE VIEW. To change a view definition, drop the existing view and create it again.

DROP VIEW IF EXISTS product_list;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products (product_name, price)
VALUES
  ('Tablet', 18000),
  ('Monitor', 12500),
  ('Keyboard', 2400);

CREATE VIEW product_list AS
SELECT
  product_name,
  price
FROM products;

SELECT *
FROM product_list
ORDER BY price DESC;

DROP VIEW product_list;

CREATE VIEW product_list AS
SELECT
  product_name,
  price,
  price * 0.90 AS discounted_price
FROM products;

SELECT *
FROM product_list
ORDER BY discounted_price DESC;

Dropping a View

Use DROP VIEW to remove a view from the database. IF EXISTS can be used to avoid an error when the view does not exist.

DROP VIEW IF EXISTS recent_orders;
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  order_date TEXT NOT NULL
);

INSERT INTO orders (customer_name, order_date)
VALUES
  ('Maya', '2026-08-20'),
  ('Arjun', '2026-08-24'),
  ('Tanya', '2026-08-26');

CREATE VIEW recent_orders AS
SELECT
  order_id,
  customer_name,
  order_date
FROM orders
WHERE order_date >= '2026-08-24';

SELECT *
FROM recent_orders
ORDER BY order_date;

DROP VIEW recent_orders;

SELECT name
FROM sqlite_schema
WHERE type = 'view';

Viewing Existing Views

SQLite stores information about database objects in sqlite_schema. You can query it to find the views in the current database.

DROP VIEW IF EXISTS active_projects;
DROP VIEW IF EXISTS expensive_projects;
DROP TABLE IF EXISTS projects;

CREATE TABLE projects (
  project_id INTEGER PRIMARY KEY,
  project_name TEXT NOT NULL,
  budget REAL NOT NULL,
  active INTEGER NOT NULL
);

CREATE VIEW active_projects AS
SELECT project_name, budget
FROM projects
WHERE active = 1;

CREATE VIEW expensive_projects AS
SELECT project_name, budget
FROM projects
WHERE budget >= 50000;

SELECT
  name,
  type
FROM sqlite_schema
WHERE type = 'view'
ORDER BY name;

Common SQL View Statements

Statement	Purpose	Example
CREATE VIEW	Creates a view	CREATE VIEW active_users AS …
CREATE VIEW IF NOT EXISTS	Creates a view only if it does not already exist	CREATE VIEW IF NOT EXISTS active_users AS …
SELECT FROM VIEW	Reads data through a view	SELECT * FROM active_users
DROP VIEW	Removes a view	DROP VIEW active_users

Advantages of SQL Views

Simplifies complex SQL queries.
Provides a convenient way to reuse query logic.
Can expose only selected rows and columns.
Can combine data from multiple tables.
Can provide calculated and aggregated information.

Best Practices

Give views clear and meaningful names.
Select only the columns that users actually need.
Use views to simplify frequently used complex queries.
Remember that a normal SQLite view does not store a separate copy of the underlying data.
Use DROP VIEW IF EXISTS when recreating views in practice examples.
Remember that SQLite does not support CREATE OR REPLACE VIEW.

A SQL VIEW is a virtual table created from a query. Views make complex queries easier to reuse and can provide a simplified or restricted representation of data without requiring a separate copy of the underlying table data.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.