Lesson 1 : Views

Just like a normal entity a view has its own attributes. It will look like a normal table but without a primary key itself and all data or information are borrowed from one or more table from the same database. For example personal information from the EMPLOYEE table is combined with the information in SALARY table to create a new set of information ready for retrieval. The good side of having views in your database is that you can distribute an information to any other system or people without having given them the actual editable table, meaning, whatever the changes on the view will not affect the original table.

Syntax

CREATE VIEW MonthlySalary AS (SELECT * FROM ...);

Sample

Create view MonthlySalary as Select e.Fullname as Employee, e.Position as Position, s.Amount * 20 as Salary from Employee as e inner join salary as s on e.SalaryGrade = s.SalaryGrade

Activity

Create a database for elementary grading system which will house views that gives this information:

1. List of students per subject with their average grades arrange in a descending order by average.

2. List of students per classroom with the average grade for all subjects {English, Math, Filipino, Science, PE, MAPEH, Araling Panlipunan and GMRC } arrange in a descending order by average.

Download PDF File