Class 1 | Week 12 | DAT601

Week twelve notes have been released which are summed up as:

Milestone Two feedback

SQL server permissions and views

Introduction to milestone Three, how dig is the database?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Views can also be thought of as virtual tables, they behave like tables but are derived from queries themselves, views are saved within databases and indexed. This allows them to be used much in the same way as regular tables.

How do I create a view?

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

 Once a view is created using this code then it is stored in the database for reuse.

Views may be updated to add additional columns as required.

A VIEW is generally used:

To present the information data from a different point of view;
To simplify the access to the information of a high-level complexity of the database schema;
To prevent direct access to database tables (for security purposes)(less popular these days because of ORM tools);
Some "reality" is just simpler with a VIEW.

If you alter a view you WILL lose all your indexes to this view in the process.

Updating through views is possible, derived columns cannot be updated, it is also important to note that you cant derive a column from another derived column.

UPDATE vwCategoriesProducts
 SET CategoryName = 'Drinks'
 WHERE ProductID = 1

Renaming views is possible although it should be approached with caution as anything dependant on knowing the name of this view may break considering that it wouldn't be able to find the view its dependant on, Renaming is essentially just dropping and creating again under a different name, this also means that indexes have to be done again.


Comments

Post a Comment

Popular posts from this blog

Class 2 | Week 9 | DAT601

Today's lesson pertains to more SQL language beginning with the GROUP by clauses, little examples are being shown on the projector for us, these kind of physical examples are a lot easier to digest than writing on the whiteboard. Group by must appear after the FROM and WHERE clauses, GROUP by groups rows into subgroups based on values of columns or expressions. Todd's example is used to group columns together to show a sum of total sales for a specific region. Another example of a group by rollup which is described as a niche function which caused some debate in class as to what it actually does. GROUP BY GROUPING SETS is used to allow multiple group-by clauses to operate in a single statement, GROUP BY GROUPING SETS is equivalent to the 'union' of two or more GROUP BY operation in the same result set. Next topic is Views within SQL, which is used to present the information data in a different point of view. This can simplify the access to specific information w...
Read more

Class 2 | Week 2 | DAT601

Image
Beginning this weeks second class Todd has provided an example document for our milestone one and is walking through it to help us understand the different types of tables he desires. Upon learning that this milestone is about 15 pages max I am feeling comfortable. After this he has continued with last weeks power point and explaining more conceptual database concepts in depth, including but not limited to: cardinality, weak vs strong entities, multiplicity constraints and so on. Below is some pictures from my teams brainstorming over our teams conceptual design done earlier today, nothing perfect but getting together to do this ignited some interesting conversation and I felt like we made progress in the end. Below are my class notes for today Cardinality     cardinality is the count of entities within the relationship Participation     specifies minimum number of relationship instances each entity can participate in     this is cal...
Read more

Class 1 | Week 14 | DAT601

Today's lesson pertained to an explanation of the Milestone three steps to get everyone on the same page before Todd started into a talk about database file structures and the different structures they may take. The following discussion resulted in a lot of in-depth information regarding storage sizes that is ideally relevant to the estimation of database storage size task we have to complete within milestone 3. The rest of the class has been assigned to milestone 3 work.
Read more