CREATE VIEW syntax in SQL - Deep Dive

Overview - CREATE VIEW syntax

What is it?

A VIEW in SQL is like a saved query that you can treat as a virtual table. The CREATE VIEW syntax lets you define this saved query with a name, so you can use it repeatedly without rewriting the query. It does not store data itself but shows data from the underlying tables when you use it. This helps simplify complex queries and organize your database work.

Why it matters

Without views, you would have to write complex queries every time you want to see specific data. Views save time and reduce mistakes by letting you reuse queries easily. They also help protect sensitive data by showing only certain columns or rows to users. Without views, managing and securing data would be harder and more error-prone.

Where it fits

Before learning CREATE VIEW, you should understand basic SELECT queries and how tables work in SQL. After mastering views, you can learn about materialized views, stored procedures, and database security to control data access better.

Mental Model

Core Idea

A VIEW is a named saved query that acts like a virtual table, showing data dynamically from underlying tables whenever you use it.

Think of it like...

Think of a VIEW like a recipe card for a dish you like. Instead of cooking the dish every time from scratch, you just follow the recipe card. The card itself doesn't hold food, but it tells you exactly how to make the dish whenever you want.

┌─────────────┐       ┌─────────────┐
│  CREATE    │       │  SELECT     │
│  VIEW v1   │──────▶│  columns    │
│ AS query   │       │  FROM table │
└─────────────┘       └─────────────┘
       │                     │
       ▼                     ▼
┌─────────────────────────────────┐
│  v1 behaves like a virtual table │
│  showing data from the query    │
└─────────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic SELECT Queries

Concept: Learn how to retrieve data from tables using SELECT statements.

A SELECT query lets you choose columns and rows from a table. For example, SELECT name, age FROM employees; shows the name and age of all employees.

Result

A list of names and ages from the employees table.

Knowing how SELECT works is essential because views are built from SELECT queries.

2

Foundation

What Is a View in SQL?

Concept: Introduce the idea of a view as a virtual table based on a SELECT query.

A view is like a saved SELECT query with a name. When you query the view, it runs the saved SELECT and shows the results as if it were a table.

Result

You can use the view name in queries just like a table name.

Understanding that views do not store data but show live query results helps avoid confusion.

3

Intermediate

Basic CREATE VIEW Syntax

🤔Before reading on: do you think CREATE VIEW stores data or just saves a query? Commit to your answer.

Concept: Learn the syntax to create a simple view from a SELECT query.

The syntax is: CREATE VIEW view_name AS SELECT columns FROM table WHERE condition; For example, CREATE VIEW young_employees AS SELECT name, age FROM employees WHERE age < 30;

Result

A view named young_employees that shows only employees younger than 30.

Knowing the exact syntax lets you create reusable queries that simplify your work.

4

Intermediate

Using Views in Queries

🤔Before reading on: do you think you can join a view with a table in a query? Commit to your answer.

Concept: Learn how to use views just like tables in SELECT statements.

Once a view is created, you can query it like a table: SELECT * FROM young_employees; You can also join it with other tables: SELECT y.name, d.department FROM young_employees y JOIN departments d ON y.department_id = d.id;

Result

Results combining data from the view and other tables.

Understanding that views behave like tables in queries helps you integrate them smoothly into your database work.

5

Intermediate

Updating Data Through Views

🤔Before reading on: do you think you can update data through any view? Commit to your answer.

Concept: Learn when and how you can update data using views.

Some views allow updates if they are simple enough (like selecting from one table without joins). For example, UPDATE young_employees SET age = age + 1 WHERE name = 'Alice'; works if the view supports updates. Complex views with joins or aggregations usually do not allow updates.

Result

Data in the underlying table changes if the view supports updates.

Knowing the limits of updating through views prevents errors and confusion.

6

Advanced

Modifying Views with CREATE OR REPLACE

🤔Before reading on: do you think you must drop a view before changing it? Commit to your answer.

Concept: Learn how to change an existing view without dropping it first.

Many SQL systems support CREATE OR REPLACE VIEW view_name AS SELECT ...; This updates the view definition without removing it first, preserving permissions and dependencies.

Result

The view definition changes smoothly without losing access rights.

Understanding this syntax helps maintain views safely in production environments.

7

Expert

Performance and Security Implications of Views

🤔Before reading on: do you think views always improve query speed? Commit to your answer.

Concept: Explore how views affect performance and data security in real systems.

Views do not store data, so querying a view runs its underlying SELECT each time, which can be slow for complex queries. Materialized views store data to improve speed but need refreshing. Views can restrict user access by showing only certain columns or rows, enhancing security without duplicating data.

Result

Better control over data access but potential performance trade-offs.

Knowing when to use views or materialized views and how views affect security is key for professional database design.

Under the Hood

When you query a view, the database replaces the view name with its saved SELECT query and runs that combined query against the actual tables. The view itself does not hold data; it is a stored SQL statement. This means views always show current data from the tables. Permissions on views can restrict what users see without changing the underlying tables.

Why designed this way?

Views were designed to simplify complex queries and improve security by abstracting data access. Storing data in views would duplicate storage and cause synchronization problems. By storing only the query, views remain lightweight and always show fresh data. This design balances flexibility, performance, and security.

┌───────────────┐
│ User queries  │
│   the view   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ View definition│
│ (saved SELECT) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Underlying     │
│ tables/data   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a view store data separately from tables? Commit yes or no.

Common Belief: A view stores its own copy of data like a table.

Reality: A view only stores the query definition, not the data itself. It shows live data from the underlying tables.

Why it matters: Thinking views store data can lead to confusion about data freshness and storage needs.

Quick: Can you always update data through any view? Commit yes or no.

Common Belief: You can update data through any view just like a table.

Reality: Only simple views based on one table without joins or aggregations allow updates. Complex views do not support updates.

Why it matters: Trying to update through unsupported views causes errors and wasted effort.

Quick: Does creating a view improve query speed automatically? Commit yes or no.

Common Belief: Views always make queries faster because they are pre-made.

Reality: Views run their underlying queries each time, so they can be slower. Materialized views are needed for speed improvements.

Why it matters: Assuming views speed up queries can cause performance problems in real systems.

Quick: Must you drop a view before changing its query? Commit yes or no.

Common Belief: You must drop a view before redefining it.

Reality: Many SQL systems support CREATE OR REPLACE VIEW to update views without dropping them.

Why it matters: Dropping views unnecessarily can break permissions and dependencies.

Expert Zone

1

Views can be nested, meaning a view can be defined using other views, which can complicate query optimization.

2

Some databases optimize views by merging their queries into the main query, but others do not, affecting performance.

3

Security policies can be layered using views to expose only specific data slices to different user roles without changing the base tables.

When NOT to use

Avoid using views for very large or complex queries that run frequently and need fast response; instead, use materialized views or indexed tables. Also, do not use views to hide business logic that should be in application code or stored procedures.

Production Patterns

In production, views are often used to simplify reporting queries, enforce data access rules, and provide backward compatibility when underlying tables change. They are combined with roles and permissions to secure sensitive data without duplicating it.

Connections

Materialized Views

Builds-on

Understanding views helps grasp materialized views, which store query results physically for faster access but require manual refresh.

Database Security

Builds-on

Views are a key tool in database security to restrict user access to specific columns or rows without changing the underlying tables.

Functional Programming

Same pattern

Views are like pure functions in programming: they produce results based on input (tables) without side effects or storing state.

Common Pitfalls

#1 Trying to update data through a complex view with joins.

Wrong approach:

UPDATE complex_view SET column = value WHERE id = 1;

Correct approach:

Update the underlying tables directly: UPDATE base_table SET column = value WHERE id = 1;

Root cause:

Misunderstanding that only simple views support updates; complex views are read-only.

#2 Assuming views improve query performance automatically.

Wrong approach:

CREATE VIEW slow_view AS SELECT * FROM huge_table JOIN another_table ON ...; -- expecting faster queries

Correct approach:

Use materialized views or optimize queries instead: CREATE MATERIALIZED VIEW fast_view AS SELECT ...;

Root cause:

Confusing views as stored data rather than saved queries executed on demand.

#3 Dropping a view before changing it, causing permission loss.

Wrong approach:

DROP VIEW my_view; CREATE VIEW my_view AS SELECT ...;

Correct approach:

CREATE OR REPLACE VIEW my_view AS SELECT ...;

Root cause:

Not knowing the CREATE OR REPLACE syntax preserves permissions and dependencies.

Key Takeaways

CREATE VIEW defines a named saved query that acts like a virtual table showing live data.

Views simplify complex queries and help secure data by restricting access to specific columns or rows.

Views do not store data themselves; they run their underlying SELECT queries each time they are used.

Only simple views support data updates; complex views are read-only.

Using CREATE OR REPLACE VIEW lets you modify views safely without dropping them first.

Practice

1. What is the main purpose of the CREATE VIEW statement in SQL?

easy

A. To save a SELECT query as a virtual table

B. To permanently store data in the database

C. To delete rows from a table

D. To update existing records in a table

Solution

1. Step 1: Understand what a view is

   A view is a virtual table created by saving a SELECT query.

2. Step 2: Identify the purpose of CREATE VIEW

   CREATE VIEW stores the SELECT query so you can use it like a table without storing data.

3. Final Answer:

   To save a SELECT query as a virtual table -> Option A

4. Quick Check:

   CREATE VIEW = virtual table [OK]

Hint: Views save SELECT queries as virtual tables [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing CREATE VIEW with INSERT or UPDATE
• Assuming views delete or modify data

2. Which of the following is the correct syntax to create a view named EmployeeView that selects all columns from the Employees table?

easy

A. CREATE EmployeeView VIEW AS SELECT * FROM Employees;

B. CREATE VIEW EmployeeView AS SELECT * FROM Employees;

C. VIEW CREATE EmployeeView AS SELECT * FROM Employees;

D. CREATE VIEW Employees AS SELECT * FROM EmployeeView;

Solution

1. Step 1: Recall the correct CREATE VIEW syntax

   The syntax is: CREATE VIEW view_name AS SELECT ...

2. Step 2: Match the syntax with options

   CREATE VIEW EmployeeView AS SELECT * FROM Employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW EmployeeView AS SELECT * FROM Employees; -> Option B

4. Quick Check:

   CREATE VIEW view_name AS SELECT ... [OK]

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

Common Mistakes:

• Swapping keywords CREATE and VIEW
• Mixing table and view names incorrectly
• Using wrong keyword order

3. Given the view creation:
CREATE VIEW ActiveUsers AS SELECT id, name FROM Users WHERE active = 1;
What will the query SELECT * FROM ActiveUsers; return?

medium

A. Only users with active = 1

B. Only user IDs without names

C. An error because views cannot filter data

D. All users including inactive ones

Solution

1. Step 1: Understand the view definition

   The view selects id and name from Users where active = 1, so only active users are included.

2. Step 2: Analyze the SELECT from the view

   Selecting * from ActiveUsers returns all columns defined in the view, filtered by active = 1.

3. Final Answer:

   Only users with active = 1 -> Option A

4. Quick Check:

   View filters rows = active users only [OK]

Hint: View returns filtered rows as defined in SELECT [OK]

Common Mistakes:

• Assuming view returns all table rows
• Thinking views cannot filter data
• Expecting columns not in view to appear

4. Identify the error in this view creation statement:
CREATE VIEW SalesView SELECT * FROM Sales;

medium

A. SELECT * is not allowed in views

B. View name cannot be SalesView

C. Missing AS keyword before SELECT

D. CREATE VIEW must include WHERE clause

Solution

1. Step 1: Check the syntax of CREATE VIEW

   The correct syntax requires AS before the SELECT statement.

2. Step 2: Identify the missing keyword

   The statement misses AS, causing a syntax error.

3. Final Answer:

   Missing AS keyword before SELECT -> Option C

4. Quick Check:

   CREATE VIEW ... AS SELECT ... [OK]

Hint: Always include AS before SELECT in CREATE VIEW [OK]

Common Mistakes:

• Omitting AS keyword
• Misplacing SELECT clause
• Assuming WHERE clause is mandatory

5. You want to create a view TopProducts that shows product names and total sales only for products with sales over 1000. Which SQL statement correctly creates this view?

hard

A. CREATE VIEW TopProducts AS SELECT product_name, sales FROM Products WHERE sales > 1000;

B. CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name WHERE SUM(sales) > 1000;

C. CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products WHERE SUM(sales) > 1000 GROUP BY product_name;

D. CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name HAVING SUM(sales) > 1000;

Solution

1. Step 1: Understand the requirement

   We need product names and total sales, only for products with total sales over 1000.

2. Step 2: Use GROUP BY and HAVING correctly

   SUM(sales) requires GROUP BY product_name, and filtering on aggregated values uses HAVING.

3. Step 3: Check each option

   CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name HAVING SUM(sales) > 1000; uses GROUP BY and HAVING correctly; others misuse WHERE or clause order.

4. Final Answer:

   CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name HAVING SUM(sales) > 1000; -> Option D

5. Quick Check:

   Use HAVING for aggregated filters in views [OK]

Hint: Use HAVING for conditions on aggregates in views [OK]

Common Mistakes:

• Using WHERE with aggregate functions
• Placing WHERE after GROUP BY
• Omitting GROUP BY when using SUM