Querying through views in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a SQL view?

A SQL view is like a saved query. It looks like a table but does not store data itself. It shows data from one or more tables when you ask it.

beginner

How do you query data from a view?

You use a SELECT statement just like with a normal table. For example: SELECT * FROM view_name;

beginner

Can you filter data when querying a view?

Yes! You can add WHERE, ORDER BY, and other clauses to a query on a view to get only the data you want.

beginner

Does a view store data permanently?

No, a view does not store data. It shows data from the underlying tables each time you query it.

intermediate

Why use views instead of tables?

Views help simplify complex queries, hide details, and provide a consistent way to see data without copying it.

What does a SQL view represent?

A A saved query that looks like a table

B A physical table storing data

C A backup of a database

D A user account in the database

A view is a saved query that shows data from tables but does not store data itself.

How do you get data from a view named 'employee_view'?

A SELECT * FROM employee_view;

B GET employee_view;

C SHOW employee_view;

D OPEN employee_view;

You use SELECT * FROM view_name; to query data from a view.

Can you use WHERE clause when querying a view?

A Only with special permissions

B No, views do not support filtering

C Only if the view is indexed

D Yes, just like with tables

Views behave like tables in queries, so you can filter data with WHERE.

Does a view store data permanently?

A Only if created with special options

B Yes, it stores a copy of data

C No, it shows data from tables dynamically

D Yes, but only for a short time

Views do not store data; they show current data from underlying tables.

Which is a benefit of using views?

A Increase storage space

B Simplify complex queries

C Make data permanent

D Replace tables completely

Views help simplify queries and hide complexity without storing data.

Explain what a SQL view is and how you can use it to get data.

Think of a view as a window to see data from tables.

Describe why views are useful in databases.

Consider how views help users see data easily without changing tables.

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