SQL Views Interview Questions and Answers

Views are a favourite interview topic because they look simple but hide real trade-offs around freshness, updatability and performance. Analysts, backend developers and data engineers all get asked about them, and the questions quickly separate people who think a view "makes queries faster" from people who understand it is just a stored query.

The examples use employees(id, name, dept_id, salary, active) and departments(id, name), and run on PostgreSQL, MySQL 8+, SQL Server and Oracle unless a dialect note says otherwise.

What is a view, really?

A view is a named, stored SELECT statement. Querying the view runs that SELECT against the current base tables and returns the result — the view itself holds no data.

Because it is a saved query, a view always reflects live base-table data and costs exactly what its underlying query costs each time. That single fact — "stored query, not stored data" — answers half of all view questions, so lead with it.

CREATE VIEW active_employees AS
SELECT id, name, dept_id, salary
FROM employees
WHERE active = TRUE;

Q1. Why use a view at all?

Three reasons: abstraction (hide a complex join behind a simple name), security (grant access to a view exposing only some columns/rows without granting table access), and consistency (everyone uses the same definition of "active customer").

The security angle is the one interviewers probe: you can revoke SELECT on the base table and grant it on a view that omits the salary column, giving row- and column-level control without touching the underlying data.

CREATE VIEW employee_directory AS
SELECT id, name, dept_id FROM employees;  -- salary deliberately excluded
GRANT SELECT ON employee_directory TO reporting_role;

State all three benefits and you cover the "why" completely; most candidates stop at abstraction.

Interview note: Follow-up: "does a view enforce security if the user can still query the base table?" No — security only holds if you revoke base-table access. The view is the only granted path.

Q2. When is a view updatable?

A view is updatable when each of its rows maps unambiguously to exactly one base-table row. That rules out aggregation, GROUP BY, DISTINCT, UNION, window functions, and usually multi-table joins.

-- Updatable: simple projection of one table
UPDATE active_employees SET salary = salary * 1.1 WHERE id = 5;

-- Not updatable: which base rows would this UPDATE touch?
CREATE VIEW dept_totals AS
SELECT dept_id, SUM(salary) AS total FROM employees GROUP BY dept_id;

The rule follows from logic, not a lookup table: if the engine cannot decide which single row your change applies to, it refuses the write. For genuinely complex views, an INSTEAD OF trigger lets you define exactly how an insert/update/delete maps onto the base tables.

Interview note: Follow-up: "how do you make an aggregate view writable?" You don't directly — you attach an INSTEAD OF trigger that translates the operation, or you write to the base tables instead.

Q3. What does WITH CHECK OPTION do?

It makes every INSERT and UPDATE through the view obey the view's WHERE clause, so you cannot create a row that would immediately vanish from the view.

CREATE VIEW active_employees AS
SELECT id, name, dept_id, salary FROM employees WHERE active = TRUE
WITH CHECK OPTION;

-- Rejected: would insert an inactive row into an "active only" view
INSERT INTO active_employees (id, name, active) VALUES (99, 'Riya', FALSE);

Without the option, that insert would succeed (setting active = FALSE on the base row) yet the new row would not appear in the view — a confusing "phantom" write. The clause enforces that what you write through the view stays visible through the view.

Interview note: Trap: "does WITH CHECK OPTION affect reads?" No — only writes. Reads always just apply the WHERE filter.

Q4. View vs materialized view — how do you choose?

A regular view recomputes on every read and is always current. A materialized view stores the computed result for fast reads but must be refreshed and can be stale. Choose by how expensive the query is versus how often the data changes.

-- Materialized view: result stored, refreshed on demand
CREATE MATERIALIZED VIEW dept_salary_summary AS
SELECT dept_id, COUNT(*) AS headcount, SUM(salary) AS payroll
FROM employees GROUP BY dept_id;

REFRESH MATERIALIZED VIEW dept_salary_summary;  -- PostgreSQL

Use a materialized view for a dashboard aggregate that is read thousands of times an hour but only needs to be current to within a few minutes. Use a regular view when correctness demands live data. Naming the read/write ratio as the deciding factor is the senior answer.

Interview note: Follow-up: "PostgreSQL vs Oracle refresh?" Postgres refreshes fully (or CONCURRENTLY to avoid locking); Oracle supports fast incremental refresh with materialized view logs. Dialects differ — say so.

Q5. Do views make queries faster?

No — a regular view is inlined into the outer query before execution, so it runs identically to writing the SQL directly. Any speed comes from base-table indexes or from a materialized view, never from the view wrapper itself.

-- These two run the same plan:
SELECT * FROM active_employees WHERE dept_id = 3;
SELECT * FROM employees WHERE active = TRUE AND dept_id = 3;

The misconception that "a view caches its result" is a common wrong answer; correct it clearly. Views help maintainability and security, not raw speed — that distinction is exactly what the question is testing.

Interview note: Trap: "can nesting views hurt performance?" It can — deeply nested views can produce plans the optimizer handles poorly, and they hide complexity. Keep view nesting shallow.

Q6. What happens to a view if a base table column is dropped?

The view breaks. A regular view stores its definition as text/parse tree; dropping a referenced column leaves the view invalid, and querying it errors until you recreate or fix it.

Some engines let a view become "invalid" silently until first use (Oracle), others fail at query time (PostgreSQL blocks the drop if a view depends on the column unless you use CASCADE). This dependency is why schema migrations must account for views.

-- PostgreSQL blocks this if a view uses salary, unless CASCADE
ALTER TABLE employees DROP COLUMN salary;

Mentioning that views create schema dependencies — and that CREATE OR REPLACE VIEW is how you evolve them — shows operational awareness.

Interview note: Follow-up: "what is a SELECT * view's danger?" It captures the columns at creation time in some engines; adding a base column may not appear until you recreate the view. Prefer explicit column lists.

Q7. Can a view have an index?

A regular view cannot be indexed directly because it has no stored data. A materialized view can — and SQL Server's "indexed view" (a materialized view with a clustered index) is exactly this feature under a different name.

-- SQL Server indexed view requires SCHEMABINDING
CREATE VIEW dbo.dept_summary WITH SCHEMABINDING AS
SELECT dept_id, COUNT_BIG(*) AS cnt FROM dbo.employees GROUP BY dept_id;
CREATE UNIQUE CLUSTERED INDEX ix ON dbo.dept_summary (dept_id);

The terminology trips people up: "indexed view" (SQL Server) and "materialized view" (PostgreSQL, Oracle) name the same idea — a stored, indexable result. Knowing they are the same concept is a nice cross-dialect signal.

Interview note: Trap: "why does SQL Server require SCHEMABINDING?" So the base tables can't change out from under a materialized result — it locks the dependency.

Q8. How do views support security and multi-tenant data?

A view with a WHERE clause on a tenant or user column, exposed instead of the base table, gives row-level isolation — each role sees only its own rows — while column projection hides sensitive fields.

CREATE VIEW my_orders AS
SELECT id, total, status FROM orders
WHERE customer_id = current_setting('app.current_user')::int;

Modern databases also offer native row-level security policies, and interviewers like to hear you mention that views are the classic, portable version of that idea. The point is that a view is an access-control surface, not just a convenience.

Interview note: Follow-up: "view-based security vs native RLS?" Views are portable and simple; native RLS (Postgres policies, SQL Server RLS) enforces at the table level regardless of query path, which is stronger.

How to prepare

Create a filtered view WITH CHECK OPTION and try to insert a row that violates its WHERE clause — watching the rejection makes the concept stick. Then build a materialized view over an expensive aggregate, query it, change the base data, and see the staleness until you refresh; that single experiment teaches the entire view-versus-materialized-view trade-off.

Pair this with the stored procedures questions, since procedures and views are the two main abstractions over base tables, and the subqueries set, because a view is essentially a named subquery. For the query foundations underneath, the SQL learning path is the place to start. A mock interview focused on schema design will naturally pull in when to reach for a view versus a table.