Updatable views and limitations in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple updatable view that selects all columns from the employees table.

SQL

CREATE VIEW employee_view AS SELECT * FROM [1];

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

Asalaries

Bdepartments

Cemployees

Dprojects

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting from a table unrelated to employees.

Using a table that does not exist.

The view must select from the employees table to be relevant and updatable for employee data.

2fill in blank

medium

Complete the code to update the salary column in the updatable view for employee with id 101.

SQL

UPDATE employee_view SET salary = [1] WHERE employee_id = 101;

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

A50000

B'50000'

C'fifty thousand'

Dsalary + 5000

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around numeric values.

Using expressions instead of a direct numeric value.

Salary is a numeric column, so the value must be a number without quotes.

3fill in blank

hard

Fix the error in the view definition to make it updatable by removing the aggregate function.

SQL

CREATE VIEW dept_salary AS SELECT department_id, [1] FROM employees;

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

Asalary

BAVG

CSUM

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving aggregate functions in the view.

Not grouping properly after removing aggregates.

Aggregate functions like SUM, AVG, MAX make views non-updatable. Selecting the column directly allows updates.

4fill in blank

hard

Fill both blanks to create a view that is not updatable because it uses a join and a computed column.

SQL

CREATE VIEW [1] AS SELECT e.employee_id, e.name, d.department_name, e.salary * [2] AS adjusted_salary FROM employees e JOIN departments d ON e.department_id = d.department_id;

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

Aemployee_department_view

B1.1

Cemployees_view

D0.9

Attempts:

3 left

💡 Hint

Common Mistakes

Using a view name that does not reflect the content.

Using a string instead of a numeric multiplier.

Views with joins and computed columns like salary * 1.1 are generally not updatable.

5fill in blank

hard

Fill all three blanks to create an updatable view that selects only specific columns and filters active employees.

SQL

CREATE VIEW [1] AS SELECT employee_id, [2], department_id FROM employees WHERE status = [3];

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

Aactive_employee_view

Bname

C'active'

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using salary instead of name in the select list.

Not quoting the status string value.

Filtering by status = 'active' and selecting columns without aggregates keeps the view updatable.