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.