VIEWS: CREATE, DROP, MATERLIZED

Create a view to display employee name, salary, and department number from EMP table.

(VIEW – EMP TABLE)

Create a view to display employee name and department name by joining EMP and DEPT tables.

(VIEW – EMP + DEPT JOIN)

Create a view to display employees working in department 10.

(VIEW – FILTER CONDITION)

Create a view to display employee name, job, and annual salary (SAL * 12).

(VIEW – CALCULATED COLUMN)

Create a view to display department name and location from DEPT table.

(VIEW – DEPT TABLE)

Create a view to display employee name, department name, and location.

(VIEW – JOIN EMP & DEPT)

Create a view to display employees earning more than 3000.

(VIEW – CONDITION)

Create a view to display department-wise total salary using EMP table.

(VIEW – GROUP FUNCTION)

Create a view to display only managers (JOB = 'MANAGER').

(VIEW – FILTER)

Create a view with column aliases for employee name and salary.

(VIEW – ALIASING)

Create a view for employees in department 20 with WITH CHECK OPTION.

(WITH CHECK OPTION – EMP)

Insert a new employee into the above view with department 20.

(WITH CHECK OPTION – VALID INSERT)

Try inserting an employee with department 30 into the same view.

(WITH CHECK OPTION – INVALID INSERT)

Update department number through the view from 20 to 30. What happens?

(WITH CHECK OPTION – UPDATE RESTRICTION)

Create a view for employees with salary > 2000 and enforce condition using WITH CHECK OPTION.

(WITH CHECK OPTION – SALARY CONDITION)

Create a READ ONLY view to display employee details.

(READ ONLY VIEW – EMP)

Create a READ ONLY view to display employee name and department name.

(READ ONLY VIEW – JOIN EMP & DEPT)

Try updating salary through a READ ONLY view.

(READ ONLY VIEW – UPDATE RESTRICTION)

Try inserting a new employee through a READ ONLY view.

(READ ONLY VIEW – INSERT RESTRICTION)

Create a READ ONLY view for employees in department 10.

(READ ONLY VIEW – FILTER)

Create a materialized view to store employee name and salary from EMP table.

(MATERIALIZED VIEW – BASIC)

Create a materialized view to store employee name and department name using EMP and DEPT tables.

(MATERIALIZED VIEW – JOIN)

Create a materialized view with REFRESH COMPLETE for EMP table.

(MATERIALIZED VIEW – COMPLETE REFRESH)

Create a materialized view with REFRESH FAST (assume logs exist).

(MATERIALIZED VIEW – FAST REFRESH)

Create a materialized view with REFRESH ON COMMIT.

(MATERIALIZED VIEW – AUTO REFRESH)

Create a materialized view to store department-wise total salary.

(MATERIALIZED VIEW – GROUP BY)

Create a materialized view with BUILD IMMEDIATE option.

(MATERIALIZED VIEW – BUILD OPTION)

Create a materialized view with BUILD DEFERRED option.

(MATERIALIZED VIEW – BUILD OPTION)

Refresh a materialized view manually.

(MATERIALIZED VIEW – REFRESH COMMAND)

Drop a materialized view created on EMP table.

(MATERIALIZED VIEW – DROP)

Create a view joining EMP and DEPT and restrict it using WITH CHECK OPTION.

(VIEW + CHECK OPTION)

Create a READ ONLY view that shows employee name, department name, and salary.

(READ ONLY VIEW – JOIN)

Create a complex view to display department name and highest salary in each department.

(VIEW – AGGREGATE + JOIN)

Create a materialized view based on the above complex query.

(MATERIALIZED VIEW – ADVANCED)

Create a view that hides salary but shows employee name and department name.

(VIEW – SECURITY PURPOSE)