CREATE VIEW syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple CREATE VIEW query

Given the table Employees with columns id, name, and department, what will be the result of the following SQL query?

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

SQL

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

A Returns all employees with their id and name who work in the Sales department

B Returns all employees with their id, name, and department regardless of department

C Returns all employees with only their department column

D Returns an error because CREATE VIEW cannot be followed by SELECT

💡 Hint

Think about what the WHERE clause inside the view does.

The view SalesEmployees selects only id and name from Employees where department is 'Sales'. The SELECT from the view returns those filtered rows.

📝 Syntax

intermediate

Identify the syntax error in CREATE VIEW statement

Which option contains a syntax error in the CREATE VIEW statement?

A CREATE VIEW AS DeptView SELECT id, name FROM Employees;

B CREATE VIEW DeptView AS SELECT id, name FROM Employees WHERE department = 'HR';

C CREATE VIEW DeptView AS SELECT * FROM Employees;

D CREATE VIEW DeptView AS SELECT id, name FROM Employees WHERE department = 'IT';

💡 Hint

Check the order of keywords in the CREATE VIEW syntax.

The correct syntax is CREATE VIEW view_name AS SELECT ... The option A has 'AS' before the view name, which is invalid syntax.

❓ optimization

advanced

Choosing the best CREATE VIEW for performance

You want to create a view that shows only active customers from the Customers table. Which CREATE VIEW statement is most efficient if the Customers table has millions of rows and an index on the status column?

A CREATE VIEW ActiveCustomers AS SELECT id, name FROM Customers;

B CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE status = 'active';

C CREATE VIEW ActiveCustomers AS SELECT id, name FROM Customers WHERE status = 'active';

D CREATE VIEW ActiveCustomers AS SELECT * FROM Customers;

💡 Hint

Selecting only needed columns can improve performance.

Option C selects only id and name columns for active customers, reducing data size and using the index on status. Option C selects all columns which may be large. Options C and D do not filter by status.

🔧 Debug

advanced

Why does this CREATE VIEW statement fail?

Consider this SQL statement:

CREATE VIEW EmployeeSummary AS SELECT id, name, salary * 1.1 adjusted_salary FROM Employees;

Why might this statement fail in some SQL databases?

A Because the alias 'adjusted_salary' is not allowed in views

B Because some databases require the keyword 'AS' before the alias in the SELECT clause

C Because you cannot use expressions like salary * 1.1 in a view definition

D Because the database requires parentheses around the expression salary * 1.1

💡 Hint

Check the syntax for aliasing columns in SELECT statements inside views.

Some SQL databases require the keyword AS before the alias in the SELECT clause. Omitting AS can cause syntax errors.

🧠 Conceptual

expert

Understanding limitations of CREATE VIEW

Which of the following is NOT a limitation of SQL views created with CREATE VIEW?

A Views cannot contain subqueries in their SELECT statement

B Views cannot have indexes created directly on them

C Views cannot contain ORDER BY clauses unless used with TOP or LIMIT

D Views cannot be used in JOIN operations

💡 Hint

Think about how views behave like tables in queries.

Views can be used in JOIN operations just like tables. The other options are true limitations: ORDER BY is restricted, indexes cannot be created on views directly, and some databases restrict subqueries in views.

Practice

1. What is the main purpose of the CREATE VIEW statement in SQL?

easy

A. To save a SELECT query as a virtual table

B. To permanently store data in the database

C. To delete rows from a table

D. To update existing records in a table

Solution

1. Step 1: Understand what a view is

   A view is a virtual table created by saving a SELECT query.

2. Step 2: Identify the purpose of CREATE VIEW

   CREATE VIEW stores the SELECT query so you can use it like a table without storing data.

3. Final Answer:

   To save a SELECT query as a virtual table -> Option A

4. Quick Check:

   CREATE VIEW = virtual table [OK]

Hint: Views save SELECT queries as virtual tables [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing CREATE VIEW with INSERT or UPDATE
• Assuming views delete or modify data

2. Which of the following is the correct syntax to create a view named EmployeeView that selects all columns from the Employees table?

easy

A. CREATE EmployeeView VIEW AS SELECT * FROM Employees;

B. CREATE VIEW EmployeeView AS SELECT * FROM Employees;

C. VIEW CREATE EmployeeView AS SELECT * FROM Employees;

D. CREATE VIEW Employees AS SELECT * FROM EmployeeView;

Solution

1. Step 1: Recall the correct CREATE VIEW syntax

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

2. Step 2: Match the syntax with options

   CREATE VIEW EmployeeView AS SELECT * FROM Employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW EmployeeView AS SELECT * FROM Employees; -> Option B

4. Quick Check:

   CREATE VIEW view_name AS SELECT ... [OK]

Hint: CREATE VIEW view_name AS SELECT ... [OK]

Common Mistakes:

• Swapping keywords CREATE and VIEW
• Mixing table and view names incorrectly
• Using wrong keyword order

3. Given the view creation:
CREATE VIEW ActiveUsers AS SELECT id, name FROM Users WHERE active = 1;
What will the query SELECT * FROM ActiveUsers; return?

medium

A. Only users with active = 1

B. Only user IDs without names

C. An error because views cannot filter data

D. All users including inactive ones

Solution

1. Step 1: Understand the view definition

   The view selects id and name from Users where active = 1, so only active users are included.

2. Step 2: Analyze the SELECT from the view

   Selecting * from ActiveUsers returns all columns defined in the view, filtered by active = 1.

3. Final Answer:

   Only users with active = 1 -> Option A

4. Quick Check:

   View filters rows = active users only [OK]

Hint: View returns filtered rows as defined in SELECT [OK]

Common Mistakes:

• Assuming view returns all table rows
• Thinking views cannot filter data
• Expecting columns not in view to appear

4. Identify the error in this view creation statement:
CREATE VIEW SalesView SELECT * FROM Sales;

medium

A. SELECT * is not allowed in views

B. View name cannot be SalesView

C. Missing AS keyword before SELECT

D. CREATE VIEW must include WHERE clause

Solution

1. Step 1: Check the syntax of CREATE VIEW

   The correct syntax requires AS before the SELECT statement.

2. Step 2: Identify the missing keyword

   The statement misses AS, causing a syntax error.

3. Final Answer:

   Missing AS keyword before SELECT -> Option C

4. Quick Check:

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

Hint: Always include AS before SELECT in CREATE VIEW [OK]

Common Mistakes:

• Omitting AS keyword
• Misplacing SELECT clause
• Assuming WHERE clause is mandatory

5. You want to create a view TopProducts that shows product names and total sales only for products with sales over 1000. Which SQL statement correctly creates this view?

hard

A. CREATE VIEW TopProducts AS SELECT product_name, sales FROM Products WHERE sales > 1000;

B. CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name WHERE SUM(sales) > 1000;

C. CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products WHERE SUM(sales) > 1000 GROUP BY product_name;

D. CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name HAVING SUM(sales) > 1000;

Solution

1. Step 1: Understand the requirement

   We need product names and total sales, only for products with total sales over 1000.

2. Step 2: Use GROUP BY and HAVING correctly

   SUM(sales) requires GROUP BY product_name, and filtering on aggregated values uses HAVING.

3. Step 3: Check each option

   CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name HAVING SUM(sales) > 1000; uses GROUP BY and HAVING correctly; others misuse WHERE or clause order.

4. Final Answer:

   CREATE VIEW TopProducts AS SELECT product_name, SUM(sales) FROM Products GROUP BY product_name HAVING SUM(sales) > 1000; -> Option D

5. Quick Check:

   Use HAVING for aggregated filters in views [OK]

Hint: Use HAVING for conditions on aggregates in views [OK]

Common Mistakes:

• Using WHERE with aggregate functions
• Placing WHERE after GROUP BY
• Omitting GROUP BY when using SUM