# View

> Source: Courseiva IT Certification Glossary — https://courseiva.com/glossary/view

## Quick definition

A view is like a saved search in a database. You tell the database which columns and rows from one or more tables you want to see, and the view shows you that result every time you open it. It doesn’t store the data itself; it just reads from the original tables.

## Simple meaning

Think of a spreadsheet with many columns, name, address, phone number, birthday, favorite color, and shoe size. If you only need to see names and birthdays for a party invitation, you could manually hide the other columns every time you open the file. That gets annoying. A view is like creating a saved snapshot of those two columns. You name it 'Birthday List,' and whenever you click it, you see only the names and birthdays, as if you had a separate mini-spreadsheet. The original data stays safe and unchanged in the big spreadsheet. In a database, a view works exactly this way. It’s a definition of which columns and which rows you want from one or more tables. It looks and acts like a table when you query it, but it never stores its own copy of the data. Instead, every time you access the view, the database runs the underlying query again to pull fresh data from the original tables. This is great because the view always shows the most up-to-date information without you having to rewrite the same query. Views also help keep sensitive data hidden, for instance, a view could show employee names and departments but leave out salary columns. And because views are saved in the database, other people on your team can use them without understanding the complex joins or filters you built. Just remember: a view is not a table. If you delete a view, you lose only the saved query, the original tables remain untouched.

## Technical definition

In database management systems, a view is a virtual table defined by a stored SELECT query. It does not physically hold data; instead, it is a saved query that retrieves rows and columns from underlying base tables (or other views) at runtime. Views are a core feature of SQL databases, supported by all major RDBMS platforms including MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite. A view is created using the CREATE VIEW statement, for example: CREATE VIEW ActiveCustomers AS SELECT CustomerID, Name, Email FROM Customers WHERE Status = 'Active'; Once created, this view can be queried exactly like a real table: SELECT * FROM ActiveCustomers;. The database engine internally expands the view definition into the original query, applies any additional filters from the outer query, and returns the result set. Views provide several layers of abstraction: they can simplify complex joins, aggregate data, limit access to specific rows (row-level security) or columns (column-level security), and present a consistent interface even if the underlying table schema changes. Views can be classified as simple (based on a single table with no functions or grouping) or complex (involving multiple tables, expressions, aggregation, or DISTINCT). Some databases support materialized views (also called indexed views or snapshots) that physically store the result set for faster retrieval, but these are separate from standard views and require explicit refresh. Updatable views allow INSERT, UPDATE, or DELETE operations under certain conditions, typically only when the view is based on a single table and does not use aggregation, GROUP BY, or set operations. When a view is accessed, the query optimizer treats it as a subquery and attempts to merge it with the outer query for efficient execution. Performance considerations are important: using views that wrap complex queries can sometimes hide inefficient joins or missing indexes. In exam contexts, you should understand that views are not performance shortcuts, they do not speed up queries unless materialized. The Information Schema in SQL databases often contains a VIEWS table where metadata about views (name, definition, check option) is stored. Standard SQL also supports the WITH CHECK OPTION clause when creating a view, which prevents inserting or updating rows that would disappear from the view after the change. Views are a fundamental concept tested in exams like CompTIA Data+, IBM Certified Database Associate, Microsoft DP-900, and Oracle Database SQL Certified Associate.

## Real-life example

Imagine you run a large library. You have one giant master catalog, a huge binder, that lists every book, its author, genre, location on the shelf, who has borrowed it, and when it was last checked. Whenever a patron asks, 'Which science fiction books are currently available?' you don't want to flip through hundreds of pages and cross out checked-out books each time. Instead, you create a separate card: you write down the search rules, 'show only science fiction books where borrower is empty', and tape that card to the front desk. Now, any librarian can look at that card and quickly pull the relevant info from the main binder. That card is your view. It doesn't have its own copy of the book list. It just points to the master catalog and says, 'Give me these specific rows and columns.' If someone returns a book, the master catalog changes, and the next time you use the card, the returned book shows up automatically. If a new science fiction book arrives, it appears in the view because it meets the rules. The card never needs updating. And if you want to allow younger patrons to browse a list that doesn't include adult content, you create another card with different rules. That second card is also a view. In the library world, these cards save time and protect privacy. In the database world, views do exactly the same thing: they save a reusable set of instructions so you don't have to repeat complex filtering or hide sensitive columns every time someone asks for data.

## Why it matters

In any IT environment where data is stored in relational databases, views are a practical tool for controlling what users see and how they interact with information. For a database administrator, views provide a security layer, you can grant a user permission to access a view without ever giving them direct access to the underlying tables. This means you can hide salary columns, personal identification numbers, or internal audit fields without creating separate copies of data. For developers, views simplify application code. Instead of embedding a six-table join in every report, you create a view once and reference it with a simple SELECT. This reduces code duplication and makes maintenance easier: if the schema changes, you only update the view definition, not every query in the application. Views also enforce consistency. When multiple teams are writing reports, each team might write the same complex filter slightly differently, leading to inconsistent numbers. A centrally managed view ensures everyone uses the same logic. On the downside, views can mask performance problems. A developer might use a view without realizing it joins five large tables, causing slow queries. Understanding when to use a view versus a materialized view or a simple ad-hoc query is an important real-world skill. In audit and compliance contexts, views can be used to expose only the minimum necessary data to meet regulatory requirements, for example, a view for a customer service agent might show only the last four digits of a credit card number. For IT professionals working with any SQL-backed system, from e-commerce platforms to hospital management software, views are a fundamental tool for data access, security, and maintainability.

## Why it matters in exams

Views appear across a broad range of IT certification exams, typically as part of database fundamentals sections. For the CompTIA Data+ exam (DA0-001), you may be asked to identify the purpose of a view, differentiate views from tables, or choose when to use a view versus a derived table. In Microsoft DP-900 (Azure Data Fundamentals), views are covered under the 'describe core data concepts' objective, with questions focusing on the difference between views and tables and the concept of virtual tables. The IBM Certified Database Associate exam (C2090-600) includes questions about creating views, dropping views, and the restrictions on data modification through views. Oracle's Oracle Database SQL Certified Associate (1Z0-071) tests views more deeply, including the WITH CHECK OPTION, updatable views, and the difference between a view and a synonym. In the Microsoft SQL Server context (DP-300, Administering Relational Databases on Azure), views are part of implementing database security, especially row-level and column-level security scenarios. Exam questions often present a scenario where a user needs to see only certain columns or rows, and the correct answer is to create a view. Another common pattern asks which SQL command creates a virtual table, the answer is CREATE VIEW. Multiple-choice questions may include distractors like 'CREATE TABLE FROM SELECT' or 'CREATE VIRTUAL TABLE.' You might also see a question where a view is created and then a user tries to insert a row that doesn't meet the view's condition, the WITH CHECK OPTION clause is the key. Know that without the CHECK OPTION, a view may allow an INSERT that makes the row invisible from that view. Expect scenario-based questions where you must choose between a view, a temporary table, or a materialized view for a given requirement. Performance questions are rarer on entry-level exams but appear on advanced ones, for instance, when a view causes a performance issue, the best fix might be adding missing indexes on underlying tables rather than changing the view itself. Mastering views gives you easy points in these exams because the concept is straightforward and the answer patterns are predictable.

## How it appears in exam questions

In IT certification exams, questions about views typically fall into three categories: definition-based, scenario-based, and SQL-syntax based. Definition-based questions are the most common on entry-level exams. You'll see something like: 'Which of the following is a virtual table that does not store data but retrieves data from one or more tables?' The correct answer is a view. Another variant: 'Which SQL statement creates a view?' with options like CREATE TABLE, CREATE INDEX, CREATE VIEW, or CREATE SEQUENCE. Scenario-based questions are more applied. For example: 'A company stores employee data in a table called Employees with columns EmployeeID, Name, Department, Salary, and HireDate. The HR manager should be able to see all columns except Salary. Which database object should you create?' The answer: a view that excludes the Salary column. A more advanced scenario: 'A view is created on the Orders table with WHERE Status = 'Pending'. A user inserts a new order with Status = 'Completed'. Will this row appear in the view?' The answer is no, and the follow-up might ask how to prevent such inserts, the answer is WITH CHECK OPTION. Configuration and troubleshooting questions are less common for views at the associate level but exist. For instance: 'A view that previously returned rows suddenly returns no rows. What could be the cause?' Possible answers: the underlying table was dropped, the table was renamed, or the user no longer has SELECT permission on the underlying tables. Another troubleshooting scenario: 'A view performs slowly. Which of the following is the most likely cause?' The correct answer might be that the underlying table is missing an index, or the view uses a function in a WHERE clause that prevents index usage. On Microsoft exams, you might see a drag-and-drop question asking you to order the steps to create a view: write the query, test the query, add CREATE VIEW, specify the view name. On Oracle exams, you may need to know that views can be created using the FORCE option, which creates the view even if the underlying table doesn't exist yet. For all exam types, be comfortable reading CREATE VIEW syntax and recognizing what a given view definition will return. Avoid confusing views with synonyms (which simply alias object names without filtering data) or temporary tables (which physically store data).

## Example scenario

You work at a university that uses a database with a table called StudentRecords. It has columns: StudentID, FullName, Email, Major, GPA, TuitionOwed, and DateOfBirth. The accounting department only needs to see StudentID, FullName, and TuitionOwed. The academic advisors need to see StudentID, FullName, Major, and GPA but never TuitionOwed. Instead of creating two separate tables that copy the data (and risk having outdated copies), you create two views. First, you create AccountingView that selects StudentID, FullName, and TuitionOwed from StudentRecords. Next, you create AdvisorView that selects StudentID, FullName, Major, and GPA. You then grant read access to AccountingView to the accounting team and read access to AdvisorView to the advisors. The original StudentRecords table itself is hidden from both teams. Later, the registrar adds a new column, 'EnrollmentStatus,' to StudentRecords. Neither view includes this column, so both teams see the same data as before. The views continue to work without changes. One day, an advisor tries to update a student's GPA through AdvisorView. The update succeeds because the view is based on a single table and contains only simple columns, no aggregation, no joins. However, if the advisor tried to insert a new student with only StudentID, FullName, Major, and GPA, the insert would fail because the underlying table requires TuitionOwed and DateOfBirth (which are not nullable). Trying to update a view that joins two tables would typically fail as well. This scenario shows how views provide controlled, up-to-date access without duplicating data.

## Common mistakes

- **Mistake:** Thinking a view stores a copy of the data like a table does.
  - Why it is wrong: A view does not store data; it stores a query. Every time you access the view, the database re-runs the query against the base tables. Changes in the base tables are immediately reflected in the view.
  - Fix: Remember the phrase 'virtual table.' It looks like a table but it's just a saved SELECT statement. No physical data storage.
- **Mistake:** Believing that creating a view will speed up queries automatically.
  - Why it is wrong: Views do not improve performance by default. They are just saved queries. The database still needs to execute the underlying query. In fact, poorly written views can hide slow joins or missing indexes.
  - Fix: Views are for convenience and security, not for speed. If you need faster queries, consider materialized views (if supported) or indexing the underlying tables.
- **Mistake:** Assuming you can always INSERT, UPDATE, or DELETE through a view.
  - Why it is wrong: Not all views are updatable. Most databases restrict data modification in views that use joins, aggregation (SUM, COUNT), GROUP BY, DISTINCT, or set operations (UNION).
  - Fix: Check the documentation for your database. If a view modifies data, it should be based on a single table without functions or grouping. Use WITH CHECK OPTION to prevent invisible inserts.
- **Mistake:** Dropping a view thinking it also drops the underlying tables.
  - Why it is wrong: A view is independent from the tables it references. Dropping a view only removes the saved query definition. The underlying tables and their data remain untouched.
  - Fix: Use DROP VIEW for views, and DROP TABLE for tables. Never confuse the two. This is a common exam trap, a question asks 'What happens when you drop a view?' and the distractor says 'the underlying tables are dropped.'
- **Mistake:** Creating a view that references another view without understanding the nesting limitation.
  - Why it is wrong: Most databases allow views based on other views, but excessive nesting can make the query hard to debug and may hit system limits (like 32 levels of nesting in SQL Server).
  - Fix: Limit nested views to 2-3 levels. Document the chain. If performance is an issue, flatten the logic into a single view.

## Exam trap

{"trap":"A question states: 'A view is created on a table. The underlying table is dropped. What happens when a user queries the view?' Many learners think the query returns an empty result set or shows an error immediately.","why_learners_choose_it":"Learners assume the view still exists and might still work, just returning no data because the table is gone. They confuse 'no data' with 'empty view.'","how_to_avoid_it":"The correct answer is that the query will fail with an error (object not found or invalid object name). A view depends on the base table being present. Without the table, the view's stored query cannot resolve. This is different from a view returning zero rows, which happens when the WHERE clause matches nothing but the table exists."}

## Commonly confused with

- **View vs Table:** A table physically stores data on disk. A view does not store data; it stores a query. Deleting a table removes the data permanently, while deleting a view removes only the saved query. A view always shows current data from the underlying tables. (Example: If you create a table named 'Students' and insert 100 rows, those rows exist even if the database is restarted. If you create a view on 'Students' and then delete the table, the view becomes broken.)
- **View vs Materialized View:** A materialized view physically stores the result set of the query, like a table. A standard view does not. Materialized views need to be refreshed to reflect changes in base tables, while standard views always show current data. (Example: In a weekly sales report, a materialized view could cache the aggregated totals so queries are fast. A standard view would recalculate the totals every time, which could be slow.)
- **View vs Synonym:** A synonym (or alias) is an alternative name for a database object like a table or view. It does not filter rows or columns. A view can filter and compute. A synonym simply points to the original object with a different name. (Example: If you create a synonym 'Emp' for table 'Employees_OldName', then SELECT * FROM Emp is the same as SELECT * FROM Employees_OldName. A view could instead filter to show only active employees.)
- **View vs Stored Procedure:** A stored procedure is a saved set of SQL statements that can include logic, parameters, and multiple operations (INSERT, UPDATE, DELETE, etc.). A view can only be a single SELECT query. Stored procedures can modify data in complex ways; views are for reading data. (Example: A stored procedure could take a department ID, update a budget table, and then return a list of employees. A view can only return the list of employees for a given department if that filter is applied in the WHERE clause of the query against the view.)

## Step-by-step breakdown

1. **Define the purpose of the view** — Decide what data you want to expose and to whom. For example, you want to show only the names and email addresses of active customers. This step determines the columns and filter conditions.
2. **Write the SELECT query** — Write and test a SELECT statement that returns exactly the rows and columns you need. For example: SELECT FirstName, LastName, Email FROM Customers WHERE IsActive = 1. This query must work correctly before you can save it as a view.
3. **Create the view using CREATE VIEW** — Prefix the query with CREATE VIEW ViewName AS. For example: CREATE VIEW ActiveCustomerEmails AS SELECT FirstName, LastName, Email FROM Customers WHERE IsActive = 1. The view is now stored in the database metadata.
4. **Grant permissions on the view** — Assign SELECT (and possibly INSERT/UPDATE/DELETE) privileges on the view to the users or roles that need access. The users do not need direct access to the underlying tables. This provides security.
5. **Query the view like a table** — Users can now run SELECT * FROM ActiveCustomerEmails. The database engine merges the view definition with any additional filters in the outer query and executes against the base tables.
6. **Maintain the view over time** — If the underlying table schema changes (e.g., a column is renamed), you must alter or recreate the view using CREATE OR REPLACE VIEW or ALTER VIEW. Regular checks ensure the view remains valid.

## Practical mini-lesson

In a professional IT environment, views are one of your first tools for data governance. Suppose you work at a healthcare organization using a database with a table called PatientRecords that includes columns: PatientID, Name, Diagnosis, InsuranceProvider, and BillingAmount. Compliance with HIPAA requires that billing staff see only PatientID, Name, and BillingAmount, while doctors need to see the Diagnosis column but not billing data. You create two views: BillingView (PatientID, Name, BillingAmount) and ClinicalView (PatientID, Name, Diagnosis). You then create database roles for billing_staff and clinical_staff and grant SELECT on the appropriate views. Importantly, you do not grant any permissions on the base PatientRecords table. This way, even if a billing staff member tries to query the base table directly, the database denies access. When a new column, 'LastVisitDate,' is added to PatientRecords, both views remain unaffected because they don't reference it. But if the 'Name' column is later split into FirstName and LastName, you must update both views. You'd use: CREATE OR REPLACE VIEW BillingView AS SELECT PatientID, FirstName || ' ' || LastName AS FullName, BillingAmount FROM PatientRecords. In practice, you must test the view after schema changes, run a SELECT * on the view to confirm it works. A common pitfall: a developer creates a view with SELECT * instead of listing specific columns. If a column is added or removed, the view's column order or count may change, causing application errors. Best practice is to always list columns explicitly. View dependencies can be tracked in system catalog tables. In SQL Server, you can query sys.sql_expression_dependencies to see which objects a view depends on. In MySQL, SHOW CREATE VIEW ViewName reveals the underlying query. For performance, if a view is used in a complex reporting dashboard and the underlying tables are large, consider whether a materialized view (if supported) or an indexed view (in SQL Server) could help. But for day-to-day operations, standard views provide a clean, secure, and maintainable way to share data across roles.

## Memory tip

Think 'View = Saved SELECT.' It's a window, not a warehouse - no data stored, just a way to look at existing data.

## FAQ

**Can I update data in a view and have it affect the original table?**

Yes, but only if the view is updatable. That means it must be based on a single table, without joins, aggregation, GROUP BY, or DISTINCT. The WITH CHECK OPTION can also be used to prevent updates that would make a row disappear from the view.

**What happens to a view if the underlying table is renamed?**

The view will break and return an error when queried because the view's stored query references the old table name. You need to recreate or alter the view to point to the new table name.

**Is a view faster than a direct query?**

Not usually. Views are just saved queries. The database still executes the underlying query. Performance depends on indexes, query complexity, and the database optimizer. Materialized views can be faster because they store pre-computed results.

**Can a view be based on another view?**

Yes, most databases allow nesting views. But it's best to limit nesting to avoid complexity and performance issues. Deeply nested views can be hard to debug and may hit system limits.

**Can I use a view to hide sensitive columns from users?**

Absolutely. Create a view that selects only the non-sensitive columns. Grant access to the view, but do not grant any permissions on the underlying table. This is a common security pattern.

**Do views consume storage space?**

Standard views do not consume storage for data. They only store the query definition, which takes negligible space. Materialized views do consume storage because they store the result set.

## Summary

A view is a powerful but frequently misunderstood database object. It is a saved SQL query that acts as a virtual table, allowing you to present a customized subset of data from one or more tables without duplicating any data. Views serve three primary purposes: security (by hiding sensitive columns or rows), simplicity (by hiding complex joins behind a simple name), and consistency (by ensuring everyone uses the same logic). The key exam takeaway is that views do not store data; they are just definitions stored in the database metadata. When you query a view, the database dynamically executes the underlying SELECT statement against the base tables. This means views always show the most current data, but they do not automatically improve performance. You must also understand the limitations of views, not all views support data modification, and dropping a view never affects the underlying tables. In exams, you will encounter views in definition questions, scenario-based choices, and SQL syntax recognition tasks. Remember the classic exam trap: a view based on a table that is later dropped will produce an error, not an empty result set. Mastering views is a straightforward way to earn points on database-related certification exams, and in real-world IT, views are an indispensable tool for data governance and efficient application development.

---

Practice questions and the full interactive page: https://courseiva.com/glossary/view