Querying through views in SQL

Introduction

Views let you look at data like a saved question. You can ask the view for data without writing the full question again.

You want to simplify complex queries for easier use.

You need to share specific data with others without giving full table access.

You want to reuse the same query many times without rewriting it.

You want to hide some columns or rows from users for security.

You want to organize data in a way that makes sense for your reports.

Syntax

SQL

SELECT column1, column2 FROM view_name WHERE condition;

A view acts like a virtual table based on a saved SELECT query.

You query a view just like a regular table.

Examples

Get all columns and rows from the view named employee_view.

SQL

SELECT * FROM employee_view;

Get names and salaries of employees earning more than 50000 from the view.

SQL

SELECT name, salary FROM employee_view WHERE salary > 50000;

Count how many rows are in the view.

SQL

SELECT COUNT(*) FROM employee_view;

Sample Program

First, we create a view called employee_view that shows active employees. Then, we ask the view for names and departments of employees who earn more than 60000.

SQL

CREATE VIEW employee_view AS
SELECT id, name, department, salary FROM employees WHERE active = 1;

SELECT name, department FROM employee_view WHERE salary > 60000;

Important Notes

Views do not store data themselves; they show data from the original tables.

If the original data changes, the view shows the updated data automatically.

Some views cannot be updated directly depending on how they are created.

Summary

Views are saved queries that act like tables.

You query views just like normal tables.

Views help simplify and secure data access.

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