6.2 Database Objects (Table, View, Materialized View, Sequence, Function)

Level: 6 | Version: v1.1 | Author: Meptrasoft

Overview

A database object is a defined object created and managed inside a database.

In this topic, we will learn five useful database objects:

A simple way to remember them:

TABLE             → STORE
VIEW              → SHOW
MATERIALIZED VIEW → STORE RESULT
SEQUENCE          → GENERATE
FUNCTION          → REUSE LOGIC

1. Table

A table is the main structure used to store relational data in rows and columns.

Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(150)
);

Example data:

Table → Stores actual data

2. View

A view is a named query that presents data from one or more tables.

It is often described as a virtual table because the view definition stores the query rather than a separate copy of the underlying result.

For example:

CREATE VIEW employee_department_view AS
SELECT e.emp_id, e.emp_name, d.dept_name, s.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN salaries s ON e.emp_id = s.emp_id;

You can then query it like a table:

SELECT * FROM employee_department_view;

The underlying query is evaluated according to the database system when the view is queried.

View → Reusable query-based representation

3. Materialized View

A materialized view stores the result of a query so it can be read without recomputing the full underlying query every time.

For example:

CREATE MATERIALIZED VIEW mv_employee_details AS
SELECT e.emp_id, e.emp_name, d.dept_name, s.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN salaries s ON e.emp_id = s.emp_id;

The materialized result must be refreshed to reflect newer underlying data:

REFRESH MATERIALIZED VIEW mv_employee_details;

Materialized View → Store query result for faster/reusable reads

View vs Materialized View

4. Sequence

A sequence is a database object that generates a sequence of numeric values.

It is commonly used when applications need generated identifiers.

For example:

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;

Values can then be obtained using database-specific sequence syntax. In PostgreSQL:

SELECT nextval('emp_seq');

Possible results:

1
2
3
4

Sequence → Generate numeric values

Important: Sequence behavior, syntax, and use with transactions vary between database systems.

5. Function

A function is a reusable block of database logic that can accept inputs and return a value or result.

For example, in PostgreSQL:

CREATE FUNCTION get_employee_salary(p_emp_id INT)
RETURNS NUMERIC AS $$
DECLARE
    v_salary NUMERIC;
BEGIN
    SELECT salary INTO v_salary
    FROM salaries
    WHERE emp_id = p_emp_id;

    RETURN v_salary;
END;
$$ LANGUAGE plpgsql;

The function can be called like:

SELECT get_employee_salary(2);

It returns the salary associated with employee 2.

Function → Reusable database logic

Note: Whether and how functions can perform INSERT, UPDATE, or DELETE depends on the database system and function type. Do not treat "functions cannot perform DML" as a universal SQL rule.

Practice Tables

We will use departments, employees, and salaries:

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50)
);

INSERT INTO departments (dept_name) VALUES
('HR'), ('IT'), ('Finance');

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT REFERENCES departments(dept_id)
);

INSERT INTO employees (emp_name, dept_id) VALUES
('Ravi', 2),
('Anita', 2),
('Kiran', 2),
('Meena', 3);

CREATE TABLE salaries (
    emp_id INT REFERENCES employees(emp_id),
    salary NUMERIC(10, 2)
);

INSERT INTO salaries (emp_id, salary) VALUES
(1, 45000.00),
(2, 60000.00),
(3, 55000.00),
(4, 50000.00);

Table vs View vs Materialized View

These three are commonly confused:

TABLE             → STORES DATA
VIEW              → STORES QUERY DEFINITION
MATERIALIZED VIEW → STORES QUERY RESULT

This is the most important distinction to remember.

Common Beginner Mistakes

Placement Quick Points

TABLE
→ STORE DATA

VIEW
→ QUERY-BASED VIRTUAL RESULT

MATERIALIZED VIEW
→ STORED QUERY RESULT

SEQUENCE
→ GENERATE NUMERIC VALUES

FUNCTION
→ REUSABLE DATABASE LOGIC

• A table stores relational data.
• A view provides a query-based representation of data.
• A materialized view stores a query result and may need refreshing.
• A sequence generates numeric values.
• A function contains reusable database logic and can return a value/result.
• Exact syntax and behavior vary across database systems.

Interview Questions

What is a database object?

A database object is a defined object managed by a database system, such as a table, view, sequence, or function.

What is the difference between a table and a view?

A table stores data directly, while a view provides a query-based representation of data without storing rows separately.

What is the difference between a view and a materialized view?

A normal view stores the query definition, while a materialized view stores the query result and can be refreshed.

What is a sequence?

A sequence is a database object used to generate a series of numeric values.

Why are sequences useful?

They are useful when applications need generated numeric values, such as primary key identifiers.

What is a database function?

A function is reusable database logic that can accept parameters and return a value or result.

Which database object is commonly used to store relational data?

A table.

Which object can store a precomputed query result?

A materialized view.

Practice & Hands-On Exercises

Using the examples above:

1. Identify the purpose of a table, view, materialized view, sequence, and function.
2. Explain the difference between a view and a materialized view.
3. Create a simple view showing employee names and departments:

CREATE VIEW emp_dept_simple AS
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

SELECT * FROM emp_dept_simple;

4. Explain why a materialized view may need refreshing.
5. Create a sequence that starts at 100 and increases by 1:

CREATE SEQUENCE test_seq START WITH 100 INCREMENT BY 1;
SELECT nextval('test_seq');

6. Explain one real-world use of a database function.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

TABLE             → STORE DATA
VIEW              → SHOW DATA THROUGH A QUERY
MATERIALIZED VIEW → STORE QUERY RESULT
SEQUENCE          → GENERATE VALUES
FUNCTION          → REUSE LOGIC

Database objects serve different purposes: tables store data, views present data, materialized views store query results, sequences generate values, and functions provide reusable database logic.

End of Database Objects (Table, View, Materialized View, Sequence, Function)