Views for security and abstraction in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple view query

Given the table Employees with columns id, name, salary, and a view HighEarners defined as CREATE VIEW HighEarners AS SELECT id, name FROM Employees WHERE salary > 70000;, what will be the output of SELECT * FROM HighEarners; if the table contains:

id | name | salary
1 | Alice | 80000
2 | Bob | 60000
3 | Carol | 90000

SQL

CREATE VIEW HighEarners AS SELECT id, name FROM Employees WHERE salary > 70000;
SELECT * FROM HighEarners;

A [{"id":1,"name":"Alice"},{"id":3,"name":"Carol"}]

B []

C [{"id":2,"name":"Bob"}]

D [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"},{"id":3,"name":"Carol"}]

💡 Hint

Remember the view filters employees with salary greater than 70000.

The view selects only employees with salary above 70000, so Bob is excluded.

🧠 Conceptual

intermediate

Purpose of using views for security

Why are views often used to improve security in databases?

A They automatically encrypt data stored in the database.

B They allow restricting user access to specific columns or rows without giving full table access.

C They speed up query execution by storing data physically.

D They prevent users from running any queries on the database.

💡 Hint

Think about how views can limit what data users see.

Views can show only certain columns or rows, hiding sensitive data from users.

📝 Syntax

advanced

Identify the syntax error in view creation

Which option contains a syntax error when creating a view that shows only active customers from a Customers table with a boolean active column?

A CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE active == TRUE;

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

C CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE active IS TRUE;

D CREATE VIEW ActiveCustomers AS SELECT * FROM Customers WHERE active = 1;

💡 Hint

Check the comparison operator used in SQL.

SQL uses a single equals sign (=) for comparison, not double (==).

❓ optimization

advanced

Optimizing view performance with indexes

You have a view RecentOrders defined as SELECT * FROM Orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'. Which approach will best improve query performance when selecting from this view?

A Drop the view and query the <code>Orders</code> table without any indexes.

B Create an index on the view <code>RecentOrders</code> directly.

C Rewrite the view to select all orders without filtering.

D Create an index on the <code>order_date</code> column in the <code>Orders</code> table.

💡 Hint

Think about how databases use indexes to speed up filtering.

Indexes on the underlying table columns used in the view's WHERE clause speed up filtering.

🔧 Debug

expert

Why does this view cause an error on update?

Consider the view CREATE VIEW EmployeeNames AS SELECT id, name FROM Employees;. A user tries to run UPDATE EmployeeNames SET name = 'John' WHERE id = 5; but gets an error. Why?

A The user does not have permission to update the Employees table.

B Views cannot be updated in any database system.

C The view does not include all columns needed for an update, so it is not updatable.

D The UPDATE statement syntax is incorrect for views.

💡 Hint

Think about what makes a view updatable.

Views that do not include all key columns or have complex logic often cannot be updated directly.

Practice

1. What is the main purpose of using a VIEW in SQL?

easy

A. To permanently store data in the database

B. To provide a simplified and secure way to access specific data

C. To create a backup of the database

D. To speed up the database server hardware

Solution

1. Step 1: Understand what a VIEW is

   A VIEW is a virtual table created by a SELECT query that shows data without storing it separately.

2. Step 2: Identify the purpose of a VIEW

   It helps users see only the data they need, hiding sensitive details and simplifying complex queries.

3. Final Answer:

   To provide a simplified and secure way to access specific data -> Option B

4. Quick Check:

   VIEW = Simplify + Secure access [OK]

Hint: Views show selected data safely without storing it separately [OK]

Common Mistakes:

• Thinking views store data permanently
• Confusing views with backups
• Believing views improve hardware speed

2. Which of the following is the correct syntax to create a view named EmployeeView showing only EmployeeID and Name from Employees table?

easy

A. CREATE VIEW EmployeeView AS SELECT EmployeeID, Name FROM Employees;

B. MAKE VIEW EmployeeView SELECT EmployeeID, Name FROM Employees;

C. CREATE TABLE EmployeeView AS SELECT EmployeeID, Name FROM Employees;

D. VIEW CREATE EmployeeView SELECT EmployeeID, Name FROM Employees;

Solution

1. Step 1: Recall the correct SQL syntax for creating a view

   The syntax is: CREATE VIEW view_name AS SELECT columns FROM table;

2. Step 2: Match the syntax with options

   CREATE VIEW EmployeeView AS SELECT EmployeeID, Name FROM Employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW EmployeeView AS SELECT EmployeeID, Name FROM Employees; -> Option A

4. Quick Check:

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

Hint: Use CREATE VIEW ... AS SELECT ... to define views [OK]

Common Mistakes:

• Using MAKE VIEW instead of CREATE VIEW
• Confusing CREATE VIEW with CREATE TABLE
• Incorrect keyword order

3. Given the table Employees with columns EmployeeID, Name, Salary, and a view EmployeeView defined as:

CREATE VIEW EmployeeView AS SELECT EmployeeID, Name FROM Employees;

What will the query SELECT * FROM EmployeeView; return?

medium

A. Only Salary column

B. All columns: EmployeeID, Name, Salary

C. Only EmployeeID and Name columns

D. Syntax error because Salary is missing

Solution

1. Step 1: Understand the view definition

   The view selects only EmployeeID and Name columns from Employees.

2. Step 2: Determine what SELECT * from the view returns

   It returns only the columns defined in the view, which are EmployeeID and Name.

3. Final Answer:

   Only EmployeeID and Name columns -> Option C

4. Quick Check:

   View columns = Selected columns only [OK]

Hint: View shows only columns defined in its SELECT query [OK]

Common Mistakes:

• Expecting all original table columns
• Thinking missing columns cause syntax errors
• Confusing view columns with table columns

4. Consider this incorrect SQL statement to create a view:

CREATE VIEW SalesView SELECT OrderID, Amount FROM Sales;

What is the error and how to fix it?

medium

A. Incorrect view name; fix by renaming view

B. Missing FROM keyword; fix by adding FROM before Sales

C. SELECT statement is not allowed in views

D. Missing AS keyword; fix by adding AS after view name

Solution

1. Step 1: Identify the syntax error in CREATE VIEW

   The correct syntax requires AS keyword after the view name.

2. Step 2: Correct the statement

   Add AS after SalesView: CREATE VIEW SalesView AS SELECT OrderID, Amount FROM Sales;

3. Final Answer:

   Missing AS keyword; fix by adding AS after view name -> Option D

4. Quick Check:

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

Hint: Always use AS after view name in CREATE VIEW [OK]

Common Mistakes:

• Omitting AS keyword
• Misplacing FROM keyword
• Thinking SELECT is disallowed in views

5. You want to create a view PublicEmployeeData that hides the Salary column from the Employees table but allows users to see EmployeeID, Name, and Department. Which SQL statement correctly creates this view and ensures security by restricting access to sensitive data?

hard

A. CREATE VIEW PublicEmployeeData AS SELECT EmployeeID, Name, Department FROM Employees;

B. CREATE VIEW PublicEmployeeData AS SELECT * FROM Employees WHERE Salary IS NULL;

C. CREATE VIEW PublicEmployeeData AS SELECT EmployeeID, Name, Department, Salary FROM Employees;

D. CREATE VIEW PublicEmployeeData AS SELECT EmployeeID, Name FROM Employees;

Solution

1. Step 1: Identify columns to include and exclude

   We want EmployeeID, Name, Department but NOT Salary to protect sensitive data.

2. Step 2: Choose the correct SELECT statement for the view

   CREATE VIEW PublicEmployeeData AS SELECT EmployeeID, Name, Department FROM Employees; selects only the allowed columns, hiding Salary effectively.

3. Final Answer:

   CREATE VIEW PublicEmployeeData AS SELECT EmployeeID, Name, Department FROM Employees; -> Option A

4. Quick Check:

   View excludes Salary to secure sensitive data [OK]

Hint: Select only non-sensitive columns in view to protect data [OK]

Common Mistakes:

• Including Salary column accidentally
• Using WHERE Salary IS NULL which filters rows, not columns
• Selecting too few columns missing Department