Querying through views in SQL - Step-by-Step Execution

Concept Flow - Querying through views

Create View

↓

Store View Definition

↓

Query View

↓

Database Replaces View with Underlying Query

↓

Execute Underlying Query

↓

Return Result Set

A view stores a query. When you query the view, the database runs the stored query and returns its results.

Execution Sample

SQL

CREATE VIEW TopStudents AS
SELECT name, score FROM Students WHERE score > 90;

SELECT * FROM TopStudents;

Create a view named TopStudents for students scoring above 90, then query it to get those students.

Execution Table

Step	Action	Query/Condition	Result/Output
1	Create view	CREATE VIEW TopStudents AS SELECT name, score FROM Students WHERE score > 90	View TopStudents stored with query definition
2	Query view	SELECT * FROM TopStudents	Database replaces with underlying query
3	Execute underlying query	SELECT name, score FROM Students WHERE score > 90	Rows with score > 90 returned
4	Return result	N/A	[('Alice', 95), ('Bob', 92)]

💡 Query completes after returning all rows matching score > 90

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
View Definition	None	Stored as SELECT name, score FROM Students WHERE score > 90	Used to replace view in query	N/A
Query Result	None	None	[('Alice', 95), ('Bob', 92)]	[('Alice', 95), ('Bob', 92)]

Key Moments - 2 Insights

Why does querying a view run the underlying query instead of returning stored data?

The execution_table row 2 shows the database replaces the view with its stored query, so the data is fetched fresh each time.

Can the view store data like a table?

No, as shown in variable_tracker, the view only stores the query definition, not the data itself.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what query is executed?

A SELECT * FROM TopStudents

B CREATE VIEW TopStudents AS SELECT name, score FROM Students WHERE score > 90

C SELECT name, score FROM Students WHERE score > 90

D SELECT name, score FROM Students

💡 Hint

Check execution_table row 3 under 'Query/Condition'

According to variable_tracker, what does the view store after creation?

A The actual student data

B The query definition

C Nothing

D The result set

💡 Hint

Look at 'View Definition' row after Step 2 in variable_tracker

At which step does the database replace the view with its underlying query?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

See execution_table row 2 'Action' column

Concept Snapshot

CREATE VIEW view_name AS SELECT ...;
Query the view like a table: SELECT * FROM view_name;
The database runs the stored query each time you query the view.
Views do not store data, only query definitions.
Useful for simplifying complex queries and reusing logic.

Full Transcript

A view in SQL is like a saved query. When you create a view, you store a query under a name. Later, when you query the view, the database runs the stored query and returns the results. This means views do not hold data themselves, but act like virtual tables. For example, creating a view TopStudents for students scoring above 90 lets you query TopStudents to get those students easily. The execution steps show creating the view, querying it, replacing the view with its query, running that query, and returning the results. Variables track the stored query and the result set. Common confusions include thinking views store data or that querying a view returns stored data directly. The quiz checks understanding of these steps and concepts.

Practice

1. What is a view in SQL?
SELECT * FROM view_name; works because a view is:

easy

A. A saved query that acts like a virtual table

B. A physical table storing data permanently

C. A type of index to speed up queries

D. A backup copy of a database

Solution

1. Step 1: Understand what a view represents

   A view is not a physical table but a stored SQL query that behaves like a table.

2. Step 2: Recognize how views are queried

   You can query a view just like a table because it returns the result of its saved query.

3. Final Answer:

   A saved query that acts like a virtual table -> Option A

4. Quick Check:

   View = saved query acting like table [OK]

Hint: Remember: views are virtual tables, not physical data [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing views with indexes
• Assuming views are backups

2. Which of the following is the correct syntax to create a view named employee_view showing all columns from employees table?

easy

A. CREATE employee_view VIEW AS SELECT * FROM employees;

B. CREATE VIEW employee_view FROM employees;

C. VIEW CREATE employee_view AS SELECT * FROM employees;

D. CREATE VIEW employee_view AS SELECT * FROM employees;

Solution

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

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

2. Step 2: Match the syntax with options

   CREATE VIEW employee_view AS SELECT * FROM employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW employee_view AS SELECT * FROM employees; -> Option D

4. Quick Check:

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

Hint: Use 'CREATE VIEW view_name AS SELECT ...' format [OK]

Common Mistakes:

• Swapping keywords CREATE and VIEW
• Using FROM instead of AS
• Incorrect keyword order

3. Given a view high_salary defined as:

CREATE VIEW high_salary AS SELECT name, salary FROM employees WHERE salary > 70000;

What will this query return?

SELECT * FROM high_salary WHERE salary > 80000;

medium

A. Syntax error because salary filter is repeated

B. All employees with salary greater than 70000

C. All employees with salary greater than 80000

D. Empty result because salary > 70000 is overridden

Solution

1. Step 1: Understand the view definition

   The view returns employees with salary > 70000 only.

2. Step 2: Apply the query filter on the view

   The query further filters those results to salary > 80000, so only employees with salary above 80000 are returned.

3. Final Answer:

   All employees with salary greater than 80000 -> Option C

4. Quick Check:

   View filters 70000+, query filters 80000+ [OK]

Hint: Filters on views stack, narrowing results [OK]

Common Mistakes:

• Thinking the second filter overrides the first
• Assuming syntax error due to repeated conditions
• Believing the result will be empty

4. You have this view:

CREATE VIEW dept_count AS SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department;

Which query will cause an error when run on this view?

medium

A. SELECT department FROM dept_count;

B. SELECT department, salary FROM dept_count;

C. SELECT emp_count FROM dept_count WHERE department = 'Sales';

D. SELECT * FROM dept_count WHERE emp_count > 5;

Solution

1. Step 1: Identify columns in the view

   The view has columns: department and emp_count only.

2. Step 2: Check each query's column usage

   SELECT department, salary FROM dept_count; tries to select 'salary' which does not exist in the view, causing an error.

3. Final Answer:

   SELECT department, salary FROM dept_count; causes error -> Option B

4. Quick Check:

   Querying non-existent column = error [OK]

Hint: Select only columns defined in the view [OK]

Common Mistakes:

• Selecting columns not in the view
• Assuming all original table columns exist in view
• Ignoring GROUP BY effects on columns

5. You want to create a view active_customers that shows customers with at least one order in the last 30 days.
Given tables:
customers(id, name)
orders(id, customer_id, order_date)
Which is the correct SQL to create this view?

hard

A. CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days');

B. CREATE VIEW active_customers AS SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE order_date > CURRENT_DATE - 30);

C. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL 30 DAY;

D. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days';

Solution

1. Step 1: Understand the requirement

   The view must include customers with orders in last 30 days only.

2. Step 2: Analyze each option's correctness

   CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days'); uses EXISTS with correct date interval syntax and correlates orders to customers properly. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL '30 days'; uses JOIN but may duplicate customers if multiple orders exist. CREATE VIEW active_customers AS SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE order_date > CURRENT_DATE - 30); has incorrect date subtraction syntax. CREATE VIEW active_customers AS SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.order_date > CURRENT_DATE - INTERVAL 30 DAY; uses LEFT JOIN but filters on order_date, which can exclude customers without recent orders incorrectly.

3. Final Answer:

   CREATE VIEW active_customers AS SELECT id, name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = customers.id AND order_date > CURRENT_DATE - INTERVAL '30 days'); -> Option A

4. Quick Check:

   Use EXISTS with correct date interval for filtering [OK]

Hint: Use EXISTS with correct date interval for filtering [OK]

Common Mistakes:

• Using incorrect date interval syntax
• Using JOIN causing duplicate rows
• Filtering on LEFT JOIN columns incorrectly