Updatable views in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple updatable view showing employee names and salaries.

PostgreSQL

CREATE VIEW employee_salaries AS SELECT name, [1] FROM employees;

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

Aage

Bdepartment

Csalary

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column unrelated to salary like age or department.

Selecting the employee ID instead of salary.

The view should select the salary column to show employee salaries.

2fill in blank

medium

Complete the code to update the salary of an employee through the view.

PostgreSQL

UPDATE employee_salaries SET [1] = 60000 WHERE name = 'Alice';

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

Aname

Bsalary

Cdepartment

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to update the name or department instead of salary.

Using the employee ID which is not updatable in this view.

To update the salary, set the salary column in the view.

3fill in blank

hard

Fix the error in the view definition to make it updatable by adding the clause that enforces the WHERE condition.

PostgreSQL

CREATE VIEW employee_info AS SELECT name, salary FROM employees WHERE department = 'Sales' [1];

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

AWITH CHECK OPTION

BWITH LOCAL CHECK OPTION

CWITH CASCADED CHECK OPTION

DWITH PRIMARY KEY

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'WITH PRIMARY KEY' which is not valid syntax here.

Omitting the check option, causing update errors.

The WITH CHECK OPTION ensures updates through the view meet the view's conditions, making it updatable.

4fill in blank

hard

Fill both blanks to create an updatable view that includes the employee ID and enforces updates only for the HR department.

PostgreSQL

CREATE VIEW hr_employees AS SELECT [1], name, salary FROM employees WHERE department = [2] WITH CHECK OPTION;

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

Aid

B'HR'

C'Sales'

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong department name in the filter.

Omitting the primary key column which is needed for updates.

The employee id is needed for updates, and the filter must be for the 'HR' department.

5fill in blank

hard

Fill all three blanks to create an updatable view that shows employee ID, name, and salary, and only allows updates for employees with salary greater than 50000.

PostgreSQL

CREATE VIEW high_earners AS SELECT [1], [2], [3] FROM employees WHERE salary > 50000 WITH CHECK OPTION;

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

Aid

Bname

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out the primary key column 'id'.

Including columns not needed or missing salary.

The view must include id, name, and salary columns to be updatable and show the correct data.