Why Querying through views in SQL? - Purpose & Use Cases

The Big Idea

What if you could open a simple window to complex data anytime, without the hassle of digging through endless tables?

The Scenario

Imagine you have a huge spreadsheet with thousands of rows and many columns. Every time you want to see just a few important details, you have to scroll, filter, and copy data manually. It takes a lot of time and you might make mistakes.

The Problem

Manually searching and filtering data is slow and tiring. You can easily miss important information or copy wrong data. If the original data changes, you have to redo all your work again. This wastes time and causes frustration.

The Solution

Using views in databases lets you save a custom way to look at data. Instead of repeating complex searches, you just ask the view for the information you want. It's like having a ready-made window that always shows the right data, updated and error-free.

Before vs After

✗ Before

SELECT * FROM orders WHERE status = 'shipped' AND order_date > '2024-01-01';

✓ After

SELECT * FROM shipped_orders_view WHERE order_date > '2024-01-01';

What It Enables

Views let you quickly and safely access complex data without rewriting queries, making your work faster and more reliable.

Real Life Example

A sales manager can use a view to instantly see all shipped orders without worrying about the underlying tables or filters, saving hours every week.

Key Takeaways

Manual data filtering is slow and error-prone.

Views save complex queries as reusable windows into data.

Using views makes data access faster, easier, and more accurate.

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