Creating and using VIEWs in SQL

1. Introduction: What is a VIEW?

In SQL, a VIEW is essentially a virtual table. Unlike a base table, a view does not physically store data itself. Instead, it is defined by a stored SQL query. When you query a view, the database engine executes the underlying stored query and presents the results to you as if they were coming from a regular table.

Think of a view as a saved query that you can interact with using standard SELECT statements.

Why use VIEWs?

  • Simplicity: Views can hide the complexity of underlying table structures and intricate join operations. A user can query a simple view without needing to know the complex query that defines it. This is particularly useful for frequently executed complex queries.
  • Security: Views can restrict access to data. You can create a view that exposes only specific columns or rows from a table (or combination of tables) to certain users, hiding sensitive information present in the base tables.
  • Consistency: Views provide a consistent, stable structure for users and applications, even if the underlying base tables are restructured. As long as the view’s definition remains valid, queries against the view can continue to work without modification.
  • Data Abstraction: Views provide a layer of abstraction over the database schema.

2. Creating VIEWs in PostgreSQL

The fundamental syntax to create a view in PostgreSQL is:

CREATE [ OR REPLACE ] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];
-- Or any other valid SELECT statement, including JOINs, GROUP BY, functions, etc.
  • CREATE VIEW view_name: This clause initiates the view creation process and assigns a name (view_name) to it. The name must be unique within the schema.
  • OR REPLACE: This optional clause allows you to modify the definition of an existing view without having to drop it first. If the view view_name already exists, its definition is replaced; otherwise, a new view is created.
  • AS: This keyword separates the view definition from the SELECT statement that defines it.
  • SELECT ...: This is the core of the view – a standard SQL SELECT query. Whatever this query returns defines the structure (columns) and content (rows) of the view at the time it is queried.

Prerequisites: Ensure you have appropriate privileges to create views (typically the CREATE privilege on the schema).

Example Scenario Data:

Let’s assume we have the following simplified tables from our previous sessions (P2-P11):

-- Departments Table
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

-- Employees Table
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary NUMERIC(10, 2),
    department_id INT REFERENCES departments(department_id)
);

-- Sample Data Insertion (You should have similar tables from previous work)
INSERT INTO departments (department_name, location) VALUES
('Technology', 'Building A'),
('Human Resources', 'Building B'),
('Sales', 'Building C');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
('Alice', 'Smith', 'alice.s@example.com', '2022-01-15', 75000, 1),
('Bob', 'Johnson', 'bob.j@example.com', '2021-11-01', 68000, 1),
('Charlie', 'Davis', 'charlie.d@example.com', '2023-03-10', 82000, 3),
('Diana', 'Miller', 'diana.m@example.com', '2022-07-20', 60000, 2),
('Eve', 'Wilson', 'eve.w@example.com', '2023-01-05', 90000, 1);

Example 1: Simple View

Create a view showing only employee names and email addresses.

CREATE VIEW employee_contact_list AS
SELECT
    first_name,
    last_name,
    email
FROM
    employees;

Example 2: View with Filtering and Ordering

Create a view showing employees in the ‘Technology’ department hired after 2022, ordered by hire date. Note: While ORDER BY can be included in a view definition, it’s often more flexible to apply ordering when querying the view itself, unless the ordering is fundamental to the view’s purpose.

CREATE VIEW recent_tech_employees AS
SELECT
    employee_id,
    first_name,
    last_name,
    hire_date
FROM
    employees
WHERE
    department_id = (SELECT department_id FROM departments WHERE department_name = 'Technology')
    AND hire_date >= '2022-01-01'
ORDER BY
    hire_date DESC;

Example 3: View with JOINs (Using P8/P11 concepts)

Create a view that combines employee information with their department name.

CREATE VIEW employee_department_details AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.email,
    d.department_name,
    d.location
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id;

Example 4: View with Aggregation

Create a view showing the number of employees and average salary per department.

CREATE VIEW department_stats AS
SELECT
    d.department_name,
    COUNT(e.employee_id) AS number_of_employees,
    AVG(e.salary)::NUMERIC(10, 2) AS average_salary -- Casting to NUMERIC for better formatting
FROM
    departments d
LEFT JOIN -- Use LEFT JOIN to include departments with no employees
    employees e ON d.department_id = e.department_id
GROUP BY
    d.department_name;

3. Using VIEWs

Once created, views can be queried just like regular tables using the SELECT statement.

-- Query the simple contact list view
SELECT * FROM employee_contact_list;

-- Query the view with filtering applied *to the view*
SELECT * FROM employee_contact_list WHERE last_name = 'Smith';

-- Query the joined view
SELECT first_name, last_name, department_name
FROM employee_department_details
WHERE location = 'Building A';

-- Query the aggregation view
SELECT * FROM department_stats
WHERE number_of_employees > 1
ORDER BY average_salary DESC;

-- Query the view that already had ordering and filtering
SELECT * FROM recent_tech_employees;

Notice how querying employee_department_details is much simpler than writing the full JOIN query each time.

4. Modifying VIEWs

If you need to change the underlying query of a view, you can use the CREATE OR REPLACE VIEW syntax. This is generally preferred over dropping and recreating, especially if other database objects or applications depend on the view.

Example: Modify employee_contact_list to include employee_id.

CREATE OR REPLACE VIEW employee_contact_list AS
SELECT
    first_name,
    last_name,
    email,
    employee_id -- Added column
FROM
    employees;

Now, querying employee_contact_list will also return the employee_id.

5. Dropping VIEWs

If a view is no longer needed, you can remove it using the DROP VIEW statement.

DROP VIEW view_name;
  • CASCADE: You can add CASCADE (DROP VIEW view_name CASCADE;) to automatically drop objects that depend on the view (use with caution!).
  • RESTRICT: This is the default behaviour. If any other objects depend on the view, the DROP VIEW statement will fail.

Example:

-- Drop the simple contact list view
DROP VIEW employee_contact_list;

-- Attempt to drop the department details view (might fail if other objects depend on it)
DROP VIEW employee_department_details; -- Defaults to RESTRICT