Views for security and abstraction in SQL - Step-by-Step Execution

Concept Flow - Views for security and abstraction

Create View

↓

View stores SQL query

↓

User queries View

↓

Database runs stored query

↓

Returns filtered/abstracted data

↓

User sees only allowed data

A view stores a SQL query. When a user queries the view, the database runs the stored query and returns only the allowed or abstracted data.

Execution Sample

SQL

CREATE VIEW EmployeeNames AS
SELECT EmployeeID, Name FROM Employees;

SELECT * FROM EmployeeNames;

Creates a view showing only EmployeeID and Name, then selects all data from that view.

Execution Table

Step	Action	Query/Operation	Result/Output
1	Create view	CREATE VIEW EmployeeNames AS SELECT EmployeeID, Name FROM Employees;	View 'EmployeeNames' created storing the query
2	User queries view	SELECT * FROM EmployeeNames;	Database runs: SELECT EmployeeID, Name FROM Employees;
3	Return data	Result of SELECT EmployeeID, Name FROM Employees;	Rows with EmployeeID and Name only
4	End	No more steps	User sees only EmployeeID and Name columns

💡 View query executed and data returned; user cannot see other columns.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
View Definition	None	SELECT EmployeeID, Name FROM Employees	Stored in DB	Used to run query	Used to return filtered data
User Query	None	None	SELECT * FROM EmployeeNames	Triggers view query	Returns filtered rows
Output Data	None	None	None	Partial columns from Employees	Visible to user

Key Moments - 3 Insights

Why does the user only see some columns and not all from the original table?

Because the view stores a query selecting only specific columns (see execution_table step 1 and 3), so when the user queries the view, only those columns are returned.

Does the view store data separately from the original table?

No, the view stores only the query (execution_table step 1). When queried, the database runs that query on the original table to get current data (step 2 and 3).

How does a view help with security?

By limiting what data the user can see through the view's query (execution_table step 3), sensitive columns can be hidden, controlling access without changing the original table.

Visual Quiz - 3 Questions

Look at the execution_table, what does the database do at step 2 when the user queries the view?

A Returns all columns from the original table

B Runs the stored query inside the view

C Creates a new table

D Deletes data from the original table

💡 Hint

See execution_table row 2: 'Database runs: SELECT EmployeeID, Name FROM Employees;'

At which step does the user receive the filtered data from the view?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Check execution_table row 3: 'Return data' with filtered columns

If the view query included a WHERE clause to filter rows, how would the output change?

A The output would include only rows matching the WHERE condition

B The view would store data separately

C The output would include all rows from the original table

D The user could see all columns

💡 Hint

Views run the stored query each time (see concept_flow), so filtering in the query limits output rows

Concept Snapshot

CREATE VIEW view_name AS SELECT columns FROM table WHERE condition;
Views store queries, not data.
Querying a view runs its stored query.
Views show only selected data.
Useful for security and abstraction.

Full Transcript

A view in SQL is like a saved query. When you create a view, you store a SQL SELECT statement under a name. When a user asks for data from the view, the database runs the stored query on the original tables and returns the results. This means the user only sees the data the view query allows, hiding other columns or rows. Views do not store data themselves, only the query. This helps with security by controlling what data users can access without changing the original tables. For example, a view can show only employee names and IDs, hiding salaries or other sensitive info. When the user selects from the view, the database runs the view's query and returns the filtered data. This process is step-by-step: create view, user queries view, database runs stored query, returns filtered data, user sees limited columns. This makes views a powerful tool for abstraction and security in databases.