Views for security and abstraction in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a view named 'employee_view' that shows only the 'name' and 'salary' columns from the 'employees' table.

SQL

CREATE VIEW employee_view AS SELECT [1] FROM employees;

Drag options to blanks, or click blank then click option'

Aid, department

Bsalary, department

C*

Dname, salary

Attempts:

3 left

💡 Hint

Common Mistakes

Using '*' selects all columns, which is not secure.

Selecting columns not needed in the view.

The view should include only the 'name' and 'salary' columns, so the SELECT statement must specify these columns.

2fill in blank

medium

Complete the code to select all data from the view named 'employee_view'.

SQL

SELECT [1] FROM employee_view;

Drag options to blanks, or click blank then click option'

A*

Bname, salary

Cid

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not present in the view.

Using column names instead of '*'.

Using '*' selects all columns available in the view.

3fill in blank

hard

Fix the error in the code that tries to create a view with a WHERE clause filtering salary greater than 50000.

SQL

CREATE VIEW high_salary_view AS SELECT name, salary FROM employees WHERE salary [1] 50000;

Drag options to blanks, or click blank then click option'

A=

B<=

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' selects only salaries exactly 50000.

Using '<' or '<=' selects lower salaries.

The condition should filter salaries greater than 50000, so the operator must be '>'.

4fill in blank

hard

Fill both blanks to create a view named 'dept_view' that shows 'name' and 'department' columns only for employees in the 'Sales' department.

SQL

CREATE VIEW dept_view AS SELECT [1] FROM employees WHERE [2] = 'Sales';

Drag options to blanks, or click blank then click option'

Aname, department

Bsalary

Cdepartment

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not needed in the view.

Filtering on the wrong column.

The view should show 'name' and 'department' columns, and filter rows where 'department' equals 'Sales'.

5fill in blank

hard

Fill all three blanks to create a view named 'secure_view' that shows uppercase employee names, their salary, and filters salaries greater than 60000.

SQL

CREATE VIEW secure_view AS SELECT [1] AS name_upper, [2] FROM employees WHERE [3] > 60000;

Drag options to blanks, or click blank then click option'

AUPPER(name)

Bsalary

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Not using UPPER() for the name.

Filtering on the wrong column or using wrong operator.

The view selects the uppercase version of 'name' as 'name_upper', includes 'salary', and filters salaries greater than 60000.