Why views are needed in SQL - Visual Breakdown

Concept Flow - Why views are needed

User queries view

↓

View definition runs

↓

Underlying tables accessed

↓

Data returned to user

↓

User sees simplified, secure data

A view acts like a saved query. When you ask the view, it runs the query on the real tables and shows you the result. This helps simplify and secure data access.

Execution Sample

SQL

CREATE VIEW SimpleEmployees AS
SELECT id, name FROM Employees WHERE active = 1;

SELECT * FROM SimpleEmployees;

This creates a view showing only active employees with id and name, then selects all from that view.

Execution Table

Step	Action	Query/Operation	Result
1	Create view	CREATE VIEW SimpleEmployees AS SELECT id, name FROM Employees WHERE active = 1;	View SimpleEmployees created
2	Query view	SELECT * FROM SimpleEmployees;	Runs underlying SELECT on Employees table
3	Access table	SELECT id, name FROM Employees WHERE active = 1;	Filters active employees
4	Return data	Result set with id and name of active employees	Data sent to user
5	Display	User sees simplified employee list	Query ends

💡 Query ends after returning filtered, simplified data from underlying table via view

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
View SimpleEmployees	Not created	Created with definition	Definition used to query Employees	Used to return filtered data
Query Result	Empty	Empty	Filtered active employees	Final result set returned

Key Moments - 3 Insights

Why does querying a view run a query on the underlying tables?

Because a view is a saved query, when you select from it, the database runs the original query on the real tables to get fresh data (see execution_table step 3).

Can a view hide columns or rows from the user?

Yes, views can show only certain columns or filter rows, so users see only what the view allows (see execution_table step 3 filtering active employees).

Does creating a view store data separately?

No, views do not store data themselves; they just store the query. Data is fetched fresh from tables each time (see variable_tracker for view definition vs data).

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 3?

A The view is created and stored

B Data is returned to the user

C The underlying table is queried with filters

D The query ends

💡 Hint

Check the 'Action' and 'Query/Operation' columns at step 3 in execution_table

According to variable_tracker, what is the state of 'Query Result' after step 3?

A Contains filtered active employees

B Contains all employees

C Empty

D Contains view definition

💡 Hint

Look at the 'Query Result' row and 'After Step 3' column in variable_tracker

If the view included all employees without filtering, how would the execution_table change at step 3?

A The query would filter active employees

B The query would return all employees without filtering

C The view would not be created

D Data would not be returned

💡 Hint

Step 3 shows the filtering condition; removing it means no filtering

Concept Snapshot

Views are saved queries that show data from tables.
They simplify complex queries and hide details.
Views do not store data, only the query.
Querying a view runs its query on tables.
Useful for security and easier data access.

Full Transcript

Views in SQL are like saved queries. When you create a view, you define a query that selects data from one or more tables. When you query the view, the database runs the saved query on the real tables and returns the result. This means views do not store data themselves but show data dynamically. Views help by simplifying complex queries, hiding sensitive columns or rows, and providing a consistent interface to data. For example, a view can show only active employees with selected columns. When you select from this view, the database runs the underlying query filtering active employees and returns that data. This process is shown step-by-step in the execution table and variable tracker. Understanding that views run queries on tables each time helps avoid confusion about data storage and freshness.

Practice

1. Why do we use views in a database?
SELECT * FROM view_name; What is the main purpose of this?

easy

A. To speed up the database server hardware

B. To permanently store data physically in the database

C. To delete data from multiple tables at once

D. To simplify complex queries by showing only needed data

Solution

1. Step 1: Understand what a view does

   A view is a saved query that shows data in a simpler way without storing data separately.

2. Step 2: Identify the main purpose of views

   Views help users see only the data they need, hiding complexity and sensitive info.

3. Final Answer:

   To simplify complex queries by showing only needed data -> Option D

4. Quick Check:

   Views simplify data = C [OK]

Hint: Views show simpler data without storing it [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing views with database hardware
• Believing views delete data

2. Which of the following is the correct syntax to create a view named EmployeeView that shows only name and salary from Employees table?

easy

A. CREATE VIEW EmployeeView AS SELECT name, salary FROM Employees;

B. CREATE TABLE EmployeeView AS SELECT name, salary FROM Employees;

C. INSERT VIEW EmployeeView SELECT name, salary FROM Employees;

D. VIEW CREATE EmployeeView SELECT name, salary FROM Employees;

Solution

1. Step 1: Recall the syntax for creating a view

   The correct syntax starts with CREATE VIEW, then the view name, followed by AS and the SELECT query.

2. Step 2: Match the options with correct syntax

   CREATE VIEW EmployeeView AS SELECT name, salary FROM Employees; matches the correct syntax exactly; others use wrong keywords or order.

3. Final Answer:

   CREATE VIEW EmployeeView AS SELECT name, salary FROM Employees; -> Option A

4. Quick Check:

   CREATE VIEW + AS + SELECT = B [OK]

Hint: Use CREATE VIEW ... AS SELECT ... [OK]

Common Mistakes:

• Using CREATE TABLE instead of CREATE VIEW
• Wrong keyword order like INSERT VIEW
• Omitting AS keyword

3. Given the table Sales with columns product, region, and amount, and the view:
CREATE VIEW RegionalSales AS SELECT region, SUM(amount) AS total FROM Sales GROUP BY region;
What will the query SELECT * FROM RegionalSales WHERE total > 1000; return?

medium

A. All rows from Sales table without filtering

B. Rows showing regions where total sales amount is more than 1000

C. Syntax error because views cannot use GROUP BY

D. Empty result because total is not a column in Sales

Solution

1. Step 1: Understand the view definition

   The view groups sales by region and sums amounts, creating a total per region.

2. Step 2: Analyze the query on the view

   The query filters regions where total sales exceed 1000, so it returns those regions and totals.

3. Final Answer:

   Rows showing regions where total sales amount is more than 1000 -> Option B

4. Quick Check:

   View groups and filters totals > 1000 = A [OK]

Hint: Views can use GROUP BY and filters on aggregated columns [OK]

Common Mistakes:

• Thinking views cannot use GROUP BY
• Confusing view columns with base table columns
• Assuming syntax error due to alias

4. You tried to create a view with:
CREATE VIEW MyView AS SELECT id, password FROM Users;
But you want to hide the password column for security. What is the best fix?

medium

A. Remove password from the SELECT list in the view

B. Rename the view to hide the password

C. Add WHERE password IS NOT NULL clause

D. Use DELETE to remove password column from Users table

Solution

1. Step 1: Identify the problem with the view

   The view currently shows the password column, which exposes sensitive data.

2. Step 2: Fix the view to hide sensitive data

   Removing the password column from the SELECT statement prevents it from appearing in the view.

3. Final Answer:

   Remove password from the SELECT list in the view -> Option A

4. Quick Check:

   Exclude sensitive columns from view SELECT = D [OK]

Hint: Exclude sensitive columns in view SELECT to hide them [OK]

Common Mistakes:

• Trying to rename view to hide data
• Using WHERE clause to filter columns
• Deleting columns from base table instead of view

5. You want to create a view that shows only active customers with their total orders, but the Orders table has millions of rows. Which approach best uses views to improve performance and security?

hard

A. Create a view that deletes inactive customers from the database

B. Create a view selecting all customers and orders without filters

C. Create a view filtering active customers and pre-aggregating order totals

D. Create a view that stores all orders physically to speed queries

Solution

1. Step 1: Understand the goal for the view

   The view should show only active customers and their total orders to reduce data size and protect inactive data.

2. Step 2: Choose the best approach for performance and security

   Filtering and pre-aggregating in the view reduces data scanned and hides unnecessary info, improving speed and security.

3. Final Answer:

   Create a view filtering active customers and pre-aggregating order totals -> Option C

4. Quick Check:

   Filter and aggregate in view for speed and security = A [OK]

Hint: Filter and aggregate in views to improve speed and hide data [OK]

Common Mistakes:

• Selecting all data without filters
• Trying to delete data via views
• Misunderstanding views store data physically