Creating & Querying Views (CREATE VIEW)0%

Creating & Querying Views (CREATE VIEW)

Intermediate12 min read•Updated: 2026-09-12

Creating & Querying Views (CREATE VIEW)

A View in SQL is a named, stored query definition that acts as a virtual table. It does not store physical rows of data itself (unless indexed or materialized in supporting engines); instead, every time a view is queried, MySQL executes the underlying SELECT query against the base tables and dynamically renders the result set.

Views encapsulate complex SQL logic, provide reusable abstractions, enhance security by exposing only necessary columns/rows, and ensure standard metrics across an entire development team.

The Fundamental Purpose of Views

1
Query Simplification: Hide multi-table joins, subqueries, and mathematical computations behind a simple table-like interface (SELECT * FROM active_customer_summary).
2
Access Security & Column Whitelisting: Grant developers or analytics tools SELECT access only to the view, concealing sensitive columns like passwords, social security numbers, or internal cost margins.
3
Data Integrity & Consistency: Guarantee that business logic (such as calculating net_revenue = gross - tax - discount) is implemented identically across all backend microservices.

Creating a View Syntax

1CREATE [OR REPLACE] VIEW view_name AS
2SELECT column1, column2, ...
3FROM table_name
4WHERE condition;

Using OR REPLACE allows you to overwrite an existing view definition without first issuing a DROP VIEW statement.

Practical Walkthrough: Sales Reporting View

Consider an online retail database with customers, orders, and order_items tables. Querying the lifetime value and latest order for every customer requires multiple joins and aggregations.

1-- Creating a reusable virtual reporting view
2CREATE OR REPLACE VIEW v_customer_order_summary AS
3SELECT 
4    c.customer_id,
5    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
6    c.email,
7    c.country,
8    COUNT(DISTINCT o.order_id) AS total_orders,
9    COALESCE(SUM(o.total_amount), 0.00) AS lifetime_spend,
10    MAX(o.order_date) AS last_order_date
11FROM customers c
12LEFT JOIN orders o ON c.customer_id = o.customer_id
13WHERE c.is_active = 1
14GROUP BY c.customer_id, c.first_name, c.last_name, c.email, c.country;

Querying Views Just Like Tables

Once created, users can filter, sort, and paginate against the view with standard SELECT statements:

1-- Query high-value VIP customers using the view
2SELECT full_name, email, lifetime_spend, total_orders
3FROM v_customer_order_summary
4WHERE lifetime_spend >= 1000.00
5ORDER BY lifetime_spend DESC
6LIMIT 10;

Under the hood, MySQL merges the view query with the outer query using its query optimizer (via the MERGE or TEMPTABLE algorithm).

Viewing View Definitions

To inspect the underlying DDL query of a view:

1SHOW CREATE VIEW v_customer_order_summary;

Or query the system catalog in information_schema:

1SELECT table_name, view_definition, check_option, is_updatable
2FROM information_schema.views
3WHERE table_schema = 'my_ecommerce_db';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is a SQL View?

Question 2

Which clause safely modifies an existing view definition without dropping it first?

Question 3

How do views enhance database security?

Question 4

When querying a view, what standard SQL clauses can be applied?

Question 5

Where does MySQL store the metadata definition of all created views?

Next Lesson

Updatable Views & WITH CHECK OPTION

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Denormalization Strategies & Performance Trade-offs	Updatable Views & WITH CHECK OPTION

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Updatable Views & WITH CHECK OPTION

Creating & Querying Views (CREATE VIEW)

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Creating & Querying Views (CREATE VIEW)

Views encapsulate complex SQL logic, provide reusable abstractions, enhance security by exposing only necessary columns/rows, and ensure standard metrics across an entire development team.

The Fundamental Purpose of Views

1
Query Simplification: Hide multi-table joins, subqueries, and mathematical computations behind a simple table-like interface (SELECT * FROM active_customer_summary).
2
Access Security & Column Whitelisting: Grant developers or analytics tools SELECT access only to the view, concealing sensitive columns like passwords, social security numbers, or internal cost margins.
3
Data Integrity & Consistency: Guarantee that business logic (such as calculating net_revenue = gross - tax - discount) is implemented identically across all backend microservices.

Creating a View Syntax

1CREATE [OR REPLACE] VIEW view_name AS
2SELECT column1, column2, ...
3FROM table_name
4WHERE condition;

Using OR REPLACE allows you to overwrite an existing view definition without first issuing a DROP VIEW statement.

Practical Walkthrough: Sales Reporting View

Consider an online retail database with customers, orders, and order_items tables. Querying the lifetime value and latest order for every customer requires multiple joins and aggregations.

1-- Creating a reusable virtual reporting view
2CREATE OR REPLACE VIEW v_customer_order_summary AS
3SELECT 
4    c.customer_id,
5    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
6    c.email,
7    c.country,
8    COUNT(DISTINCT o.order_id) AS total_orders,
9    COALESCE(SUM(o.total_amount), 0.00) AS lifetime_spend,
10    MAX(o.order_date) AS last_order_date
11FROM customers c
12LEFT JOIN orders o ON c.customer_id = o.customer_id
13WHERE c.is_active = 1
14GROUP BY c.customer_id, c.first_name, c.last_name, c.email, c.country;

Querying Views Just Like Tables

Once created, users can filter, sort, and paginate against the view with standard SELECT statements:

1-- Query high-value VIP customers using the view
2SELECT full_name, email, lifetime_spend, total_orders
3FROM v_customer_order_summary
4WHERE lifetime_spend >= 1000.00
5ORDER BY lifetime_spend DESC
6LIMIT 10;

Under the hood, MySQL merges the view query with the outer query using its query optimizer (via the MERGE or TEMPTABLE algorithm).

Viewing View Definitions

To inspect the underlying DDL query of a view:

1SHOW CREATE VIEW v_customer_order_summary;

Or query the system catalog in information_schema:

1SELECT table_name, view_definition, check_option, is_updatable
2FROM information_schema.views
3WHERE table_schema = 'my_ecommerce_db';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is a SQL View?

Question 2

Which clause safely modifies an existing view definition without dropping it first?

Question 3

How do views enhance database security?

Question 4

When querying a view, what standard SQL clauses can be applied?

Question 5

Where does MySQL store the metadata definition of all created views?

Next Lesson

Updatable Views & WITH CHECK OPTION

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Denormalization Strategies & Performance Trade-offs	Updatable Views & WITH CHECK OPTION

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next