Views

Back to our Gym database: A table that shows member's names and IDs along with their subscribed plan would be very useful. However, storing this information would be wasteful and redundant. Do we have to use a join query every time we needed such a plan? SQL allows users to create VIEWS that functions just that - Views into the database to suit your needs. The View is a virtual table. It does not use up any space. In MS Access, every saved Query behaves like a View. In other words, you just design a query and save it; the result set of the query is henceforth available and you check it any time without having to write the query again. Let us explore how we create a View that shows the member's names, IDs, and subscribed plans. We start by looking at the Join that will give us the required data

SELECT GymMembers.MemberID, GymMembers.MemberName, EnrollmentScheme.SchemeType
FROM MemberPayment, GymMembers, EnrollmentScheme
WHERE MemberPayment.MemberID = GymMembers.MemberID
AND MemberPayment.SchemeID = EnrollmentScheme.SchemeID
AND MemberPayment.PaymentExpire >= CURRENT_DATE

SQL puts in the current date and time wherever it sees 'CURRENT_DATE'(Note: to run this query in Acces, substitute Now() for CURRENT_DATE). This is called a built in function. We will look at other built in functions later on in this chapter. To create a view containing the results of the above query, we just add the following line in front of the query:

CREATE VIEW MemberScheme AS
SELECT GymMembers.MemberID, GymMembers.MemberName, EnrollmentScheme.SchemeType
FROM MemberPayment, GymMembers, EnrollmentScheme
WHERE MemberPayment.MemberID = GymMembers.MemberID
AND MemberPayment.SchemeID = EnrollmentScheme.SchemeID
AND MemberPayment.PaymentExpire >= CURRENT_DATE

Although MemberScheme is just a View and not a table, we can use it in Select statements and so on just as if it were a table. To create the same View in MS Access, just create a new query by cutting and pasting the SQL into the SQL view window and save it as 'MemberScheme'. Everytime the view is mentioned, the database engine runs the query behind the view and sends the view to the user. Some DBMS offer snapshot views. Here, the query is not run every time the view is asked for; the engine runs the query just once at the time of view creation and stores the results. Snapshot Views simply show a peek into the data at the exact time the view was created; they don’t reflect additions or modifications beyond that point.