Querying through views in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of querying a simple view

Given a table Employees with columns id, name, and department, and a view View_DeptSales defined as SELECT id, name FROM Employees WHERE department = 'Sales', what is the output of SELECT * FROM View_DeptSales ORDER BY id; if the Employees table contains:

id | name | department
1 | Alice | Sales
2 | Bob | HR
3 | Carol | Sales

SQL

CREATE VIEW View_DeptSales AS SELECT id, name FROM Employees WHERE department = 'Sales';

SELECT * FROM View_DeptSales ORDER BY id;

A

1 | Alice
2 | Bob

B

2 | Bob
3 | Carol

C

1 | Alice
3 | Carol

D

1 | Alice
2 | Bob
3 | Carol

💡 Hint

Remember the view filters only employees in the Sales department.

The view selects only rows where department is 'Sales'. So only Alice (id 1) and Carol (id 3) appear.

📝 Syntax

intermediate

Identify the syntax error in view creation

Which option contains a syntax error when creating a view that selects name and salary from Employees where salary is above 50000?

A CREATE VIEW HighEarners AS SELECT name, salary FROM Employees WHERE salary > 50000;

B CREATE VIEW HighEarners AS SELECT name, salary FROM Employees WHERE salary > 50000 ORDER BY salary;

C CREATE VIEW HighEarners AS SELECT name, salary FROM Employees WHERE salary > 50000

D CREATE VIEW HighEarners AS SELECT name salary FROM Employees WHERE salary > 50000;

💡 Hint

Check the SELECT clause for missing commas.

Option D misses a comma between 'name' and 'salary', causing a syntax error.

❓ optimization

advanced

Optimizing queries using indexed views

You have a large Sales table with millions of rows. You create a view View_TotalSales that sums sales by region. Which option best improves query performance when selecting total sales per region?

A Create an indexed view (materialized view) with SUM aggregation on region and query it.

B Create a regular view with SUM aggregation and query it directly.

C Create a view without aggregation and perform SUM in each query.

D Avoid views and always query the base table with SUM aggregation.

💡 Hint

Think about pre-computing aggregates for faster queries.

Indexed views store pre-aggregated data, speeding up queries on large datasets.

🔧 Debug

advanced

Debugging unexpected results from a view

A view View_ActiveUsers is defined as SELECT id, name FROM Users WHERE active = TRUE. However, querying SELECT * FROM View_ActiveUsers; returns no rows, even though the Users table has active users. Which option explains the most likely cause?

A The <code>active</code> column is stored as '1' and '0' strings, not boolean TRUE/FALSE.

B The view definition is missing a semicolon at the end.

C The <code>Users</code> table is empty.

D The query on the view is missing a WHERE clause.

💡 Hint

Check data types and values in the active column.

If active uses strings '1'/'0' instead of boolean TRUE/FALSE, the filter fails.

🧠 Conceptual

expert

Understanding view update limitations

Which of the following statements about updating data through views is correct?

A All views can be updated directly regardless of complexity.

B Views that include joins or aggregations generally cannot be updated directly.

C Views created with SELECT * are never updatable.

D Views with WHERE clauses are never updatable.

💡 Hint

Think about what makes a view updatable or not.

Views with joins or aggregations usually do not allow direct updates because the database cannot map changes back to base tables unambiguously.

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