Skip to main content

Database Object

Database Object

Table : Basic unit of storage;
View : Logically represents subsets of data from one or more tables
Sequence : Generates numeric values
Index : Improves the performance of data retrieval queries
Synonym : Gives alternative names to objects

View

Logically represents subsets of data from one or more tables
Use of view:

  • To restrict data access
  • To present different views of the same data
  • To provide data independence
  • To make complex queries easy

Simple view VS complex View

Simple view:
— Contain one table
— no function
Complex View:
— Contain one or more table:
— Contain groups of data
— contain function
Creating a Simple View
Query: Create view which contain Employee data who is in department number 50.


CREATE VIEW Emp50
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 50;

Retrieving Data from a View

SELECT * FROM EMP50;

view-emp50

Modifying view

CREATE OR REPLACE VIEW EMP50
(Employee_id, last_name, salary)
AS SELECT employee_id, 
          first_name || ' '|| last_name, 
          salary
FROM employees WHERE department_id = 50;
Retrieving Data from view:
select *from Emp50;
view-modify

Creating a Complex View

Complex view contain one or more table or group of data.Query:Create a view that contains group functions to display values from two tables:

CREATE OR REPLACE VIEW dept_VIEW
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;

Rules for Performing DML Operations on a View

  • You can usually perform DML operations on simple views
  • You cannot remove a row if the view contains the following

–- Group functions
–- A GROUP BY clause
–- The DISTINCT keyword
–- The pseudocolumn ROWNUM keyword

  • You cannot modify data in a view if it contains:

–- Group functions
–- A GROUP BY clause
–- The DISTINCT keyword
–- The pseudocolumn ROWNUM keyword
–- Columns defined by expressions