SQL views interview questions, with answers

The questions, with answers

1. 1.What is a view, and how is it different from a table?

   A view is a named, stored SELECT query that you can use in FROM as if it were a table. It holds no rows of its own: every time you query it, the database runs the underlying SELECT against the base tables, so a view always shows current data and takes no storage beyond its definition. A table owns its rows and its storage. That is the whole distinction, and it explains the rest of the topic — a view cannot be faster than the query it wraps, it disappears harmlessly when dropped, and whether you can write through it depends on whether the database can map your change back to one base table row.

   CREATE VIEW ActiveCustomers AS
   SELECT customer_id, name, email
   FROM Customers
   WHERE status = 'active';
   
   SELECT name FROM ActiveCustomers WHERE email LIKE '%@example.com';   -- runs the stored SELECT underneath

2. 2.Why use a view at all if it is just a stored query?

   Four reasons that come up in interviews. Simplification: a ten-table reporting join written once as a view, then queried with a one-line SELECT by everyone else. Security: grant users SELECT on a view that exposes some columns and rows — employees without salaries, customers of one region — while denying access to the base table, so the view is the only door. Stability: applications query the view, and the base tables can be restructured underneath without changing them, as long as the view is redefined to produce the same columns. Consistency: one agreed definition of "active customer" lives in the database rather than in a dozen copies of a WHERE clause. None of these is about speed.

3. 3.Can you insert, update or delete through a view?

   Only when the database can map each affected view row to exactly one row of one base table — an updatable view. That rules out views with GROUP BY, aggregates, DISTINCT, UNION, most joins, and computed columns: if a view shows a department's average salary, there is no single row an UPDATE could mean. A simple view that selects columns from one table with a WHERE clause is updatable in every major database, and MySQL, PostgreSQL and Oracle will let you INSERT through it if the columns it omits are nullable or have defaults. The trap in the question is the DISTINCT or aggregate view; say the mapping rule, not just "some views are updatable".

4. 4.What does WITH CHECK OPTION do?

   It stops a write through a view from producing a row the view cannot see. With a view of active customers and no check option, UPDATE ActiveCustomers SET status = 'closed' succeeds and the rows vanish from the view — a legal but confusing result, and an INSERT of an inactive customer through the view succeeds too. WITH CHECK OPTION makes the database reject any INSERT or UPDATE through the view whose result would fail the view's WHERE clause. It is how a view used as a security door stays a door: a regional manager updating through a view of their region cannot move a customer out of it. Supported in MySQL, PostgreSQL, Oracle and SQL Server; in MySQL and PostgreSQL the CASCADED and LOCAL variants control how the rule applies through nested views.

   CREATE VIEW PuneOrders AS
   SELECT * FROM Orders WHERE city = 'Pune'
   WITH CHECK OPTION;
   
   -- rejected: the row would not be visible through the view
   UPDATE PuneOrders SET city = 'Mumbai' WHERE order_id = 42;

5. 5.What is a materialized view, and when do you use one?

   A materialized view stores the result of its query as real rows, so reading it is a table scan rather than re-running the query — the opposite trade-off from an ordinary view. You pay for that with staleness and refresh cost: the stored result is only as fresh as the last refresh, which PostgreSQL does on demand with REFRESH MATERIALIZED VIEW (CONCURRENTLY to keep it readable meanwhile), Oracle can do on a schedule or on commit, and SQL Server approximates with indexed views that update automatically. Use one for an expensive aggregate that is read far more often than it changes — a nightly sales summary, a dashboard total. MySQL has no materialized views; the equivalent is a summary table maintained by a job or triggers.

6. 6.Does a view make queries faster?

   No — an ordinary view is a macro. When you query it, the optimiser merges the view's SELECT into your query and plans the whole thing, so it is exactly as fast as writing the underlying query yourself, with the same indexes on the same base tables. The two ways a view can be slower: a view that cannot be merged (one with DISTINCT, GROUP BY, window functions or LIMIT) may be evaluated as a whole and then filtered, so a WHERE you add outside does not get pushed inside; and a view stacked on views stacked on views hides a query the optimiser must still fully plan. Materialized views and indexed views are the exception, because they store results. If the interviewer asks how to speed up a view, the answer is indexes on the base tables, or materialising it.

7. 7.What happens to a view when its base table changes or is dropped?

   If a column the view uses is dropped or renamed, the view breaks: PostgreSQL refuses the ALTER TABLE unless you drop the dependent views first (or use CASCADE, which drops them), while MySQL and SQL Server let the change through and the view fails at its next use. Dropping the base table follows the same pattern — blocked in PostgreSQL without CASCADE, allowed in MySQL with the view left invalid. Adding a column is harmless; a view defined with SELECT * captures the column list at creation time in most databases, so the new column does not appear until the view is recreated. Dropping the view itself, DROP VIEW name, never touches the base table or its rows. The lesson: name the columns in a view's SELECT, and treat base-table changes as view changes.

8. 8.How do views, CTEs and temporary tables differ?

   All three give a name to a query result; they differ in lifetime and storage. A view is a permanent schema object, visible to every session, storing only the query. A common table expression — WITH name AS (...) — exists for one statement and is invisible afterward; it is the right tool for breaking a single complex query into steps, and it can be recursive. A temporary table holds real rows for the length of a session (or a transaction) and is dropped automatically; use it when an intermediate result is large, is reused by several statements, or needs an index. The interview framing: view for sharing a definition, CTE for readability inside one query, temporary table for staging data — and none of the three is a performance feature on its own.

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 4 on views and 60 across SQL.